Oracle locks The Select result set, Skip Locked (skips Gathing to get a result set that can be locked)

1. Lock the DataSet with select for Update or select for update wait or select for update nowait

Specific implementations refer to the difference between select for update and select for update wait and select for update nowait

2. Skip Locked (Skip Gathing to get a result set that can be locked)

The Skip locked is introduced by Oracle 11g.

Skip locked enables the Select for UPDATE statement to query the remaining data sets (excluding data rows that have been locked by other sessions) and to lock the remaining data sets.

A, test one,

The code is as follows: Create a new SQL window 1 (equivalent to creating a new session), execute

Update Set Price=6where ID=1

But the commit operation is not performed, at which point the current data is locked.

Then, in a new SQL window 2 (equivalent to creating a new session), execute the

Select *  from  for Update Skip Locked

Based on the result set, we found that the id=1 data rows were excluded.

B, Test two

New SQL Window 1 (equivalent to create a new session) code is as follows: Execute the following statement

Select *  from  for Update

At this point, no commit is made, and all data rows in the table are locked. Based on the results of test one, it is inferred that if you use skip locked, you will not find any results.

New SQL Window 2 (equivalent to create a new session) code is as follows: Execute the following statement

Select *  from  for Update Skip Locked

No result set found, OK, inference correct!

Oracle locks The Select result set, Skip Locked (skips Gathing to get a result set that can be locked)