FOR UPDATE (OF)串讲

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都一样！