Stages for DML processing are the same as for SELECT processing except for the last step (=FETCH)
I will skip the first stages because the principle is the same as for any other query. Where it differs is the execution. Let me demonstrate it on the following query
UPDATE employee SET id_department = 50 -- original value = 10 WHERE id_employee = 1001;
The first step is very similar to the one in the previous article about SELECT statement processing. The server process picks the particular data block containing our desired information and brings it to the buffer cache (we are talking now about the original value of id_department = 10).
Before the value in the buffer cache is updated, Oracle will store the requested change in the REDO log buffer. For simplicity’s sake, I will use a simplified version of log_buffer records as follows (id_employee, old_value, new_value). The record in the REDO log buffer will be then -> 1001; 10; 50;
Then the old value is brought to the UNDO (where all changed values are stored until the transaction is committed) with a reference of the employee and the original buffer is rewritten with the new value. Once this is over, the user will get a message that the statement was processed.
Since this is neither committed nor rolled back we have two choices:
In the case of rollback, it is very simple. The old value from UNDO will be set back, in the REDO log buffer there is no need to write anything else and the lock is released. Everything is back in its original state.
In case of a commit, it is a little more … complex 🙂 Every transaction generates SCN (stands for System Change Number). Right after the whole entry of REDO Log buffer is stored in the REDO Log file by LGWR (Log Writer) process and REDO Log buffer is emptied. After that, the associated row is locked (so the others cannot FETCH it or UPDATE it). Before the user issues a COMMIT, every other user will be able to see the old value only. The user who performed the UPDATE is the only user able to see the new value. The old value in Buffer Cache is replaced by the new value and the lock is released.
This changed buffer is called DIRTY BUFFER and once the database writer process (DBWR) is initiated, all dirty buffers (changes in the database) are saved in data files. DBWR can be initiated by several circumstances, such as CKPT is initiated (usually is executed every couple of seconds) or when the Buffer cache is running out of free blocks. However, there is no necessity that whenever we commit an issue the data file is updated. That is why Oracle first writes changes into the REDO log file.