A brief analysis of the special uses of row level locks in Oracle

  • 2021-01-25 08:03:14
  • OfStack

Oracle has a number of locks, all of which work differently. Oracle row-level locks are highlighted below. Oracle row-level locks only lock the rows that the user is accessing. Can better ensure the security of data.
If this user is modifying a row, other users can update data outside that row in the same table as 1.
The Oracle row-level lock is an exclusive lock that prevents other transactions from modifying the row, but does not prevent operations from reading the row.
In using INSERT, UPDATE, DELETE and SELECT... Oracle row level locks are automatically applied to Oracle row level locks when FOR, UPDATE, etc. SELECT... FOR The UPDATE statement allows the user to select multiple rows of records for updates at a time. These records are locked and can only be edited by the user who initiated the query. Only after the transaction is rolled back or committed will the lock be released and other users can edit the records.
SELECT... The syntax of the FOR statement is as follows:

Among them:
The OF clause is used to specify the column to be updated, that is, to lock a specific column on a row.
The WAIT clause specifies the number of seconds to wait for another user to release the lock, preventing an indefinite wait.
The advantages of the "Use FOR UPDATE WAIT" clause are as follows:
1. Prevent waiting indefinitely for a locked line;
⒉ Allows more control over the wait time for locks in an application.
It is very useful for interactive applications because these users cannot wait for uncertainty
4. If skip locked is used, the locked rows can be skipped and the 'resource busy' exception report caused by wait n is not reported
create table t(a varchar2(20),b varchar2(20)); insert into t values('1','1'); insert into t values('2','2'); insert into t values('3','3'); insert into t values('4','4'); 

Now do the following:
Open two sql Windows in plsql develope,
Run sql in window 1
select * from t where a='1' for update; 

Run ES54en1 in window 2
1. select * from t where a='1'; 

This is fine, because row-level locking does not affect pure select statements
Then run sql2
2. select * from t where a='1' for update; 

When this sentence is executed, it is always in the wait state unless sql is committed or rolled back in window 1.
How can we make sql2 not wait or wait for a specified time? Let's run ES68en3 again
select * from t where a='1' for update nowait; 

When this sql is executed, a resource busy exception is reported directly.
If the execution
select * from t where a='1' for update wait 6; 

After waiting for 6 seconds, report resource busy exception.
If we execute ES80en4
select * from t where a='1' for update nowait skip Locke d; 

When ES86en is executed, it does not wait or report a resource busy exception.
Now let's see what happens if we do the following.
Execute in window 1:
select * from t where rownum "3 nowait skip Locked;
select * from t where rownum "6 nowait skip Locked;
insert, update and delete use row-level locking by default, which is the same as select, for and update.
select for update of This clause is useful when associated with multiple tables. If you do not use of to specify a locked table column, then all table rows are locked. If you specify columns to be modified in of, then only table rows associated with these columns are locked.

Related articles: