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 列表不限制您更改仅列出那些列.锁仍然放置在所有行上；的清单只是为您提供了一种更清楚地记录您打算做什么的方法改变.如果您只是在查询中声明 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屋！