Oracle statement restart Principle

SQL code
Update test set x = 1 where y = 1;
Delete from test where y = 1;
It is divided into two steps:
1. Locate the data rows that meet the Update Conditions in the table based on the where condition;
2. Update the x value of the data row found in step 1.

If the test table is large, update takes several minutes, And someone changes the value of y in a row to 2 during step 1, then in step 2, will the row with y = 2 be updated with column x?
In this case, Oracle selects "restart" to update.

In fact, Oracle uses two different methods to read data blocks when executing DML statements:
1. consistent read: When you find the data to be modified, the consistent read
2. current read: When "getting" data blocks to actually update data, the current read
Oracle uses this method to determine whether a restart is required.

In the above example, Oracle will first use consistent read to find all data rows with y = 1, therefore, even if another session modifies the y value of a row during the read period (for example, from y = 1 to y = 2 ), oracle will also use undo data to restore column y of the row to the value at the read time (y = 1), so this row will still be read. Then, when you want to update this row, Oracle will use the current read to obtain the latest version of the data block. Because it has been submitted, the value of y read is 2, it is inconsistent with y = 1 in the previous consistent read. Therefore, Oracle knows that the "restart" update operation is required.

Oracle restart process:
1. modification operations before rollback
2. Enter select for update mode and lock the row to be modified
3. Modify the locked row
(This process refers to a ORA-08177 error in SERIALIZABLE mode at the Oracle default read commited isolation level)