FOR UPDATE OF和FOR UPDATE之间的区别 [英] Difference between FOR UPDATE OF and FOR UPDATE

问题描述

当我使用FOR UPDATE OF SAL或简单地写FOR UPDATE时，有什么不同.

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

根据 O'Reilly

FOR UPDATE子句的OF列表不限制您进行更改 仅列出的那些列.锁仍放置在所有行上； OF 列表只是为您提供了一种更清晰地记录您打算做什么的方法 改变.如果您只是在查询中声明FOR UPDATE而未包括 在OF关键字之后的一列或多列，然后数据库将 锁定FROM子句中列出的所有表中所有已标识的行.

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 SAL指定列名时，其他用户只能用SAL列进行更改.但是，实际上并非如此.我仍然在其他会话中处于锁定状态.谁能解释其中的区别.

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

更新

 ----- 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

推荐答案

来自 Oracle文档:

使用OF ...列子句仅锁定选择行 联接中的特定表或视图.仅OF子句中的列 指示哪些表或视图行被锁定.具体列 您指定的意义不大.但是，您必须指定一个 实际的列名，而不是列别名.如果您省略此子句，则 数据库将锁定查询中所有表中的选定行.

Use the OF ... column clause to lock the select rows only for a particular table or view in a join. The columns in the OF clause 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.

如果查询引用单个表，则FOR UPDATE和FOR UPDATE OF ...之间没有什么区别，但是后者可能仍可作为自我文档来指示要更新的列.它并没有限制您可以更新的内容.如果您有:

If your query references a single table then there is no difference between FOR UPDATE and 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:

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;

但是，如果有多个表，则FOR UPDATE OF ...将仅锁定包含您在OF子句中指定的列的表中的行.

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 OF clause.

与我认为您在问题中所说的相反.指定FOR UPDATE OF sal不仅会锁定sal列；您永远不能锁定单列，最小锁定是在行级别. (了解有关锁的更多信息).它将锁定表中包含SAL列的所有行，这些行由查询选择.

Contrary to what I think you're saying in the question. specifying FOR UPDATE OF sal does not only lock the sal column; 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 SAL column, which are selected by the query.

在对问题的更新中，您的光标查询正在连接emp和dept，但是OF子句仅具有sal(emp表中的列).当打开游标时，emp表中的行将被锁定，并且只有在您commit或rollback该会话之前，这些锁才会被释放.在游标循环中，您可以执行以下操作:

In the update to your question, your cursor query is joining emp and dept, but the OF clause only has sal, a column in the emp table. The rows in the emp table will be locked when the cursor is opened, and those locks won't be released until you commit or rollback that session. Within your cursor loop you can do:

UPDATE emp SET ... WHERE CURRENT OF emp_cur;

...以更新emp表中与此循环迭代有关的行.您不能这样做:

... to update the row in the emp table that relates to this iteration of the loop. You cannot do:

UPDATE dept SET ... WHERE CURRENT OF emp_cur;

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

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

这篇关于FOR UPDATE OF和FOR UPDATE之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！