FOR UPDATE (OF)串讲
摘录Oracle 11g官方文档《OracleDatabase SQL Language Reference》对for_update_clause的说明:
for_update_clause
The FOR UPDATE clause lets you lock the selected rows so thatother users cannot lock
or update the rows until you end your transaction. You can specifythis clause only in
a top-level SELECT statement, not in subqueries.
Note: Prior to updating a LOB value, you must lock the row
containing the LOB. One way to lock the row is with an embedded
SELECT ... FOR UPDATE statement. You can do this using one of the
programmatic languages or DBMS_LOB package.For more
information on lock rows before writing to a LOB, see Oracle Database
SecureFilesand Large Objects Developer's Guide.
Nested table rows are not locked as a result of locking the parenttable rows. If you
want the nested table rows to be locked, then you must lock themexplicitly.
Restrictions on the FOR UPDATE Clause This clause is subject to the following
restrictions:
■ You cannot specify this clause with thefollowing other constructs: the DISTINCT
operator, CURSOR expression, set operators, group_by_clause, or aggregate
functions.
■ The tables locked by this clause must all belocated on the same database and on
the same database as any LONG columns and sequences referenced inthe same
statement.
Usingthe FOR UPDATE Clause on Views In general, thisclause is not supported on
views. However, insome cases, a SELECT ... FOR UPDATE query on a view can succeed
without any errors.This occurs when the view has been merged to its containing
query blockinternally by the query optimizer, and SELECT ... FOR UPDATE succeeds
on the internallytransformed query. The examples in this section illustrate when using
the FOR UPDATE clause on a view can succeed or fail.
■ Usingthe FORUPDATE clause on merged views
An error can occur when you use the FOR UPDATE clause on a merged view if both
of the following conditions apply:
– The underlying column of the view is an expression
– TheFORUPDATE clause applies to a column list
The following statement succeeds because the underlying column ofthe view is
not an expression:
e.g1:
SELECT employee_id FROM (SELECT *FROM employees)
FOR UPDATE OFemployee_id;
The following statement succeeds because, while the underlyingcolumn of the
view is an expression,the FORUPDATE clause does not apply to a column list:
e.g2:
SELECT employee_id FROM (SELECT employee_id+1 ASemployee_id FROM employees)
FOR UPDATE;
The following statement fails because the underlying column of theview is an
expression and the FOR UPDATE clause applies to a column list:
e.g3:
SELECT employee_id FROM (SELECTemployee_id+1 AS employee_id FROM employees)
FOR UPDATE OFemployee_id;
*
Error at line 2:
ORA-01733: virtual column notallowed here
注:当然,改成以下这样是允许的,原因同eg1,因为j_id并不是view中的表达式。
SELECTemployee_id,j_id FROM (SELECT employee_id+1 ASemployee_id,job_id j_id FROM employees)
FORUPDATE OF j_id;
■ Using the FOR UPDATE clause on non-merged views
Since the FOR UPDATE clause is not supported on views, anything that prevents
view merging, such as the NO_MERGEhint, parameters that disallow view
merging, or something in the query structure that prevents viewmerging, will
result in an ORA-02014error.
In the following example, the GROUPBY statement prevents view merging, which
causes an error:
SELECT avgsal
FROM (SELECT AVG(salary) AS avgsalFROM employees GROUP BY job_id)
FOR UPDATE;
FROM (SELECT AVG(salary) AS avgsalFROM employees GROUP BY job_id)
*
ERROR at line 2:
ORA-02014: cannot select FOR UPDATEfrom view with DISTINCT, GROUP BY, etc.
Note:Due to the complexityof the view merging mechanism, Oracle
recommendsagainst using the FOR UPDATE clause on views.
OF ... column
Use the OF ... column clause to lock the select rows only for a particular tableor view
in a join. Thecolumns in the OF clause only indicate which table or view rows are
locked. Thespecific columns that you specify are not significant. However, you must
specify an actual columnname, not a column alias. If you omit thisclause, then the
database locks the selected rows from all the tables in the query.
NOWAIT | WAIT
The NOWAIT and WAIT clauses let you tell the database how toproceed if the SELECT
statement attempts to lock a row that is locked by another user.
■ Specify NOWAIT to return control to you immediately if a lock exists.
■ Specify WAIT to instruct the database to wait integer seconds for the row to
become available andthen return control to you.
If you specifyneither WAIT nor NOWAIT, then the database waits until the row is
available and thenreturns the results of the SELECT statement.
SKIP LOCKED SKIP LOCKED is an alternative way to handle a contending
transaction that is locking some rows of interest. Specify SKIP LOCKED to instruct the
database to attempt to lock the rows specified by the WHERE clause and to skip any
rows that are found to be already locked by another transaction.This feature is
designed for use in multiconsumer queue environments, such asOracle Streams
Advanced Queuing. It enables queue consumers to skip rows that arelocked by other
consumers and obtain unlocked rows without waiting for the otherconsumers to
finish. Oracle recommends that you use the Oracle Streams AdvancedQueuing APIs
instead of directly using the SKIP LOCKED functionality. Referto Oracle Streams
Advanced Queuing User's Guide for more information.
Note on the WAIT and SKIP LOCKED Clauses
If you specify WAIT or SKIP LOCKED and the table islocked in exclusive mode,then
the database will not return the results of the SELECT statement until the lock on the
table is released. In the case of WAIT, the SELECT FOR UPDATE clause is blocked
regardless of the wait time specified.
以上为官方文档内容
以下为个人实验总结
继续实验,验证FOR UPDATE (OF)作用。
创建表stars,插入几条数据,如下:
SQL> select * fromstars;
ID NAME BIRTHDAY
------------------------------ ---------
1 jone
2 Theresa
3 Kelly
1 jone
再创建表estate,并插入几条记录,如下:
CREATE TABLE estate(amount NUMBER(10,2),id NUMBER);
INSERT INTO estate VALUES(5000.14,1);
INSERT INTO estate VALUES(6000.26,2);
INSERT INTO estate VALUES(65000.76,3);
SQL> select * fromestate;
AMOUNT ID
--------------------
5000.14 1
6000.26 2
65000.76 3
【实验一】用SELECT FOR UPDATE锁住表stars。
SQL_01> commit;
Commitcomplete.
SQL_01> SELECT * FROMstars FOR UPDATE;
ID NAME BIRTHDAY
------------------------------ ---------
1 jone
2 Theresa
3 Kelly
1 jone
此时,会话2不能修改stars表的任何数据。
SQL_02> commit;
Commitcomplete.
SQL_02> SELECT * FROMstars FOR UPDATE NOWAIT;
SELECT *FROM stars FOR UPDATE NOWAIT
*
ERROR atline 1:
ORA-00054:resource busy and acquire with NOWAIT specified
可见,此种加锁比较‘霸道’,一般情况不推荐使用,实验一结束。
【实验二】使用where子句锁住指定的行。
SQL_01> commit;
Commitcomplete.
SQL_01> SELECT * FROM stars WHERE id=1FOR UPDATE;
IDNAME BIRTHDAY
---------- -------------------- ---------
1 jone
1 jone
此时,会话2尝试锁住id=1的行,结果失败。
SQL_02> commit;
Commitcomplete.
SQL_02> SELECT * FROMstars WHERE id=1 FOR UPDATE NOWAIT;
SELECT *FROM stars WHERE id=1 FOR UPDATE NOWAIT
*
ERROR atline 1:
ORA-00054:resource busy and acquire with NOWAIT specified
因为会话1锁住的是id=1的行,所以会话2必定失败,但除id=1的其他行,会话2可以锁住并更新:
SQL_02> SELECT * FROMstars WHERE id=2 FOR UPDATE NOWAIT;
ID NAME BIRTHDAY
------------------------------ ---------
2 Theresa
SQL_02> UPDATE stars SETname='Hebe' WHERE id=2;
1 rowupdated.
SQL_02> commit;
Commitcomplete.
可以看到,此时会话2是可以锁住其他列的,当然也可以更新。
还需特别注意的是,在使用FOR UPDATE的时候,建议使用NOWAIT/WAIT integer可选项。因为这样一般情况下可以避免不必要的无限等待。为什么说是一般情况呢?上面官方文档有提及到,是因为使用view来进行FOR UPDATE操作时有可能触发次异常。
【实验三】连接stars表和estate表。不使用OF子句,则会讲两个表全部行锁住:
SQL_01> commit;
Commitcomplete.
SQL_01> SELECT * FROMstars s
JOIN estate e
ON s.id = e.id
FOR UPDATE;
ID NAME BIRTHDAY AMOUNT ID
------------------------------ --------- ---------- ----------
1 jone 5000.14 1
2 Hebe 6000.26 2
3 Kelly 65000.76 3
1 jone 5000.14 1
当然会话2是不能锁住任何一个表中任何一行:
SQL_02> SELECT * FROMestate FOR UPDATE WAIT 2;
SELECT *FROM estate FOR UPDATE WAIT 2
*
ERROR atline 1:
ORA-30006:resource busy; acquire with WAIT timeout expired
下面我们使用FOR UPDATE OF子句来只锁住stars表的name列:
SQL_01> commit;
Commitcomplete.
SQL_01> SELECT *
FROMstars s
JOINestate e
ONs.id = e.id
FOR UPDATE OF s.name;
ID NAME BIRTHDAY AMOUNT ID
------------------------------ --------- ---------- ----------
1 jone 5000.14 1
2 Hebe 6000.26 2
3 Kelly 65000.76 3
1 jone 5000.14 1
会话2尝试锁住estate表:
SQL_02> SELECT * FROMestate FOR UPDATE WAIT 2;
AMOUNT ID
--------------------
5000.14 1
6000.26 2
65000.76 3
因为OF子句后接的是s.name,所以我们尝试锁住表stars的其他列或者尝试修改除name列的其他列:
SQL_02> SELECT * FROMstars WHERE id=1 FOR UPDATE NOWAIT;
SELECT *FROM stars WHERE id=1 FOR UPDATE NOWAIT
*
ERROR atline 1:
ORA-00054:resource busy and acquire with NOWAIT specified
SQL_02> UPDATE stars SETid=10 WHERE name='Hebe';
UPDATEstars SET id=10 WHERE name='Hebe'
*
ERROR atline 1:
ORA-01013:user requested cancel of current operation
可以看到,结果都是失败的。
原因在上面摘录的官方文档也有说明。因为FOR UPDATE OF clause中的clause指定哪个列是并不代表只锁住该表的该行,而是指定该列上的表而已,因此clause中指定某表上的任何列结果都一样,务必要记住。
那问题出现了,在多表查询中,是否可以只锁住一个表上的某行吗?答案是肯定的。和之前雷同,当在会话1中的查询添加上WHERE子句,则整个FOR UPDATE FO查询只锁定指定的表中指定的行。继续观察实验:
SQL_01> rollback;
Rollbackcomplete.
SQL_01> SELECT *
FROMstars s
JOINestate e
ONs.id = e.id
WHERE s.id = 1
FORUPDATE OF s.name;
ID NAME BIRTHDAY AMOUNT ID
------------------------------ --------- ---------- ----------
1 jone 5000.14 1
1 jone 5000.14 1
会话2尝试锁住stars表中除id=1的其他行:
SQL_02> SELECT * FROMstars WHERE id=2 FOR UPDATE NOWAIT;
ID NAME BIRTHDAY
------------------------------ ---------
2 Hebe
成功,所以以后如果需要使用FORUPDATE来更新指定行,建议必须指定具体的行,多表查询时,更要注意OF的使用,这样可以一定程度上避免用户的无谓等待。
小结:应特别注意,只有在多表联合查询中,FOR UPDATE OF clause才起到作用,因为OF后的clause作用是通过列来指定表。所以对于单表查询来说,有没有OF clause都一样!