Oracle's forupdate row lock syntax!

Source: Internet
Author: User
The syntax of the SELECT... FORUPDATE statement is as follows: SELECT... FORUPDATE [OFcolumn_list] [WAITn | NOWAIT] [SKIPLOCKED]; where:

The syntax OF the SELECT... for update statement is as follows: SELECT... for update [OF column_list] [WAIT n | NOWAIT] [skip locked]; where:

The syntax of the SELECT... for update statement 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:

1. Avoid waiting for locked rows indefinitely;
2. Allow more control over the lock wait time in the application.
3. It is very 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:
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
Select * from t where a = '1' for update;
Run sql1 in window 2
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
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. select * from t where a = '1' for update nowait; the exception of resource busy is reported directly when this SQL statement is executed.
If select * from t where a = '1' for update wait 6 is executed, a resource exception is reported after 6 seconds.
If we execute sql4
4. select * from t where a = '1' for update nowait skip Locked; when the SQL statement is executed, no waiting or resource Busy Exception is reported.
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.