oracle FOR UPDATE OF 和 FOR UPDATE 的区别

Difference between FOR UPDATE OF and FOR UPDATE

提问by Ravi

What makes difference, when I use FOR UPDATE OF SALor simply write FOR UPDATE.

当我使用FOR UPDATE OF SAL或简单地编写FOR UPDATE.

According to O'Reilly

根据奥莱利的说法

The OF list of the FOR UPDATE clause does not restrict you to changing only those columns listed. Locks are still placed on all rows; the OF list just gives you a way to document more clearly what you intend to change. If you simply state FOR UPDATE in the query and do not include one or more columns after the OF keyword, then the database will then lock all identified rows across all tables listed in the FROM clause.

FOR UPDATE 子句的 OF 列表不限制您仅更改列出的那些列。锁仍然放在所有行上；OF 列表只是让您更清楚地记录您打算更改的内容。如果您只是在查询中声明 FOR UPDATE 并且不在 OF 关键字之后包含一列或多列，那么数据库将锁定 FROM 子句中列出的所有表中的所有标识行。

Which means, when I specify column name with FOR UPDATE OF SAL, other user can make change with SALcolumn only. But, practically, this is not the case. I'm still getting lock in other session. Can anyone explain the difference.

这意味着，当我用 指定列名时FOR UPDATE OF SAL，其他用户只能对SAL列进行更改。但实际上，情况并非如此。我仍然在其他会话中锁定。谁能解释一下区别。

UPDATE

更新

 ----- SESSION 1

declare
 emp_info emp.ename%type;
 cursor emp_cur is select ename from emp join dept using(deptno) where deptno=&no for update of sal;
 begin
 open emp_cur;
 loop
 fetch emp_cur into emp_info;
 exit when emp_cur%notfound;
 dbms_output.put_line(emp_info);
 end loop;
 close emp_cur;
 end;

  ----- SESSION 2

  update emp set comm=5 where deptno=10;
  ---- hanged/waiting in session 2

回答by Alex Poole

From the Oracle documentation:

从Oracle 文档：

Use the OF ...column clause to lock the select rows only for a particular table or view in a join. The columns in the OFclause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, then the database locks the selected rows from all the tables in the query.

使用OF ...column 子句仅锁定连接中特定表或视图的选择行。OF子句中的列仅指示哪些表或视图行被锁定。您指定的特定列不重要。但是，您必须指定实际的列名，而不是列别名。如果省略此子句，则数据库会锁定查询中所有表中的选定行。

If your query references a single table then there is no difference between FOR UPDATEand FOR UPDATE OF ..., but the latter may still be useful as self-documentation to indicate which columns you intend to update. It doesn't restrict what you can update though. If you have:

如果您的查询引用单个表，则FOR UPDATE和之间没有区别FOR UPDATE OF ...，但后者可能仍可用作自文档以指示您打算更新哪些列。不过，它不限制您可以更新的内容。如果你有：

CURSOR cur IS SELECT * FROM emp FOR UPDATE OF sal;

then you can still do:

那么你仍然可以这样做：

UPDATE emp SET comm = comm * 1.1 WHERE CURRENT OF cur;

But if there is more than one table then FOR UPDATE OF ...will only lock the rows in the tables that contain the columns you specify in the OFclause.

但是如果有多个表，那么FOR UPDATE OF ...只会锁定包含您在OF子句中指定的列的表中的行。

Contrary to what I think you're saying in the question. specifying FOR UPDATE OF saldoes not only lock the salcolumn; you can never lock a single column, the minimum lock is at row level. (Read more about locks). It locks all rows in the table that contains the SALcolumn, which are selected by the query.

与我认为您在问题中所说的相反。指定FOR UPDATE OF sal不仅锁定sal列；您永远无法锁定单个列，最小锁定在行级别。（阅读有关锁的更多信息）。它锁定包含SAL查询选择的列的表中的所有行。

---

---

In the update to your question, your cursor query is joining empand dept, but the OFclause only has sal, a column in the emptable. The rows in the emptable will be locked when the cursor is opened, and those locks won't be released until you commitor rollbackthat session. Within your cursor loop you can do:

在对您的问题的更新中，您的游标查询正在加入empand dept，但该OF子句只有sal,emp表中的一列。在该行emp的游标打开时表将被锁定，这些锁不会被释放，直到你commit或rollback该会话。在游标循环中，您可以执行以下操作：

UPDATE emp SET ... WHERE CURRENT OF emp_cur;

... to update the row in the emptable that relates to this iteration of the loop. You cannotdo:

...更新emp表中与此循环迭代相关的行。你不能这样做：

UPDATE dept SET ... WHERE CURRENT OF emp_cur;

... because rows in the depttable are not locked, because no columns were in the OF. That also means that in your second session the deptrows can be updated freely, as they are not locked by the first session.

... 因为dept表中的行没有被锁定，因为OF. 这也意味着在您的第二个会话中，dept行可以自由更新，因为它们没有被第一个会话锁定。

回答by user2225399

one additional comment on for update. If you select from multiple tables and don't have where clause for referencing each table for the update then it will lock all of the tables until the update is done.

关于更新的附加评论。如果您从多个表中选择并且没有用于引用每个表进行更新的 where 子句，那么它将锁定所有表，直到更新完成。