Oracle has many locks, and the effects of various locks are different. The following describes Oracle row-level locks. Oracle row-level locks only lock the row that the user is accessing. It can better ensure data security. You can find out what you need.
Oracle has many locks, and the effects of various locks are different. The following describes Oracle row-level locks. Oracle row-level locks only lock the row that the user is accessing. It can better ensure data security. You can find out what you need.
Oracle has many locks, and the effects of various locks are different. The following describes Oracle row-level locks. Oracle row-level locks only lock the row that the user is accessing. This ensures data security.
If the user is modifying a row, other users can update data outside the row in the same table.
An Oracle row-Level Lock is an exclusive lock that prevents other transactions from modifying this row, but does not prevent reading this row.
When using INSERT, UPDATE, DELETE, and SELECT... For update and other statements, Oracle automatically applies the Oracle row-Level Lock lock. The SELECT... for update statement allows you to UPDATE multiple rows of records each time. These records are locked and can only be edited by the user who initiates the query. Locks are released only after rollback or transaction commit. Other users can edit these records.
The syntax of the SELECT... for update statement is as follows:
The Code is as follows:
SELECT... for update [OF column_list] [WAIT n | NOWAIT] [skip locked];
Where:
The OF clause is used to specify the columns to be updated, that is, to lock specific columns on the row.
The WAIT clause specifies the number of seconds to WAIT for other users to release the lock, to prevent an indefinite WAIT.
The "for update wait" clause has the following advantages:
Wait to prevent waiting for locked rows indefinitely;
Wait allows the application to control the lock wait time.
Notebook is useful for interactive applications because these users cannot wait for uncertainty
4. If skip locked is used, the locked row can be crossed and the 'Resource busy' exception report caused by wait n is not reported.
Example:
The Code is as follows:
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 ');
Perform the following operations:
Open two SQL windows in plsql develope,
Run SQL in window 1
The Code is as follows:
Select * from t where a = '1' for update;
Run sql1 in window 2
The Code is as follows:
1. select * from t where a = '1 ';
This is not a problem because row-level locks do not affect pure select statements.
Run sql2 again
The Code is as follows:
2. select * from t where a = '1' for update;
The SQL statement is always in the waiting state during execution, unless the SQL statement in window 1 is submitted or rolled back.
How can I keep sql2 from waiting or waiting for the specified time? Run sql3 again.
3
The Code is as follows:
Select * from t where a = '1' for update nowait;
When the SQL statement is executed, an exception is reported.
If
The Code is as follows:
Select * from t where a = '1' for update wait 6;
After 6 seconds, the system reports a resource Busy Exception.
If we execute sql4
4.
The Code is as follows:
Select * from t where a = '1' for update nowait skip Locke d;
When the SQL statement is executed, the system neither waits nor reports a resource Busy Exception.
Now let's take a look at what will happen when we perform the following operations?
In window 1, execute:
Select * from t where rownum 3 nowait skip Locked; in window 2, execute:
Select * from t where rownum "6 nowait skip Locked;
Select for update. Row-level locks are applied for insert, update, and delete operations by default. The principle and operation are the same as that for select for update operations.
Select for update of. This of clause plays a major role in associating multiple tables. for example, if you do not use the column of the Table to be locked, the related rows of all tables are locked. If you specify the columns to be modified in of, only the rows of the tables associated with these columns are locked.
