SQL 你如何检查一行是否被锁定以进行更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1572068/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you check if a row is locked for update?
提问by Vincent Malgrat
Is there a way that one can test if a row has been locked for update in Oracle?
有没有一种方法可以测试一行是否已被锁定以在 Oracle 中进行更新？
As an example, suppose the following query, performed by one user:
例如，假设以下查询由一位用户执行：
select * from SOME_TABLE where THE_ID = 1000 for update;
With another user I want to check if the row with THE_ID = 1000is locked. If I try an update or something the second user gets blocked and remains waiting (do not want that).
对于另一个用户，我想检查该行THE_ID = 1000是否被锁定。如果我尝试更新或其他内容，第二个用户会被阻止并保持等待（不希望那样）。
I have also tried running the following query with the second user:
我还尝试与第二个用户运行以下查询：
select * from SOME_TABLE where THE_ID = 1000 for update NOWAIT;
Since I can not place two locks on the same row this will fail. And it does. I get an "ORA-00054: resource busy and acquire with NOWAIT specified error". Can I always count on this error to check the presence of the lock, or is there a simpler and cleaner way of determining if a row is locked?
由于我不能在同一行上放置两个锁，这将失败。确实如此。我得到一个“ORA-00054：资源繁忙并获得NOWAIT指定的错误”。我可以总是依靠这个错误来检查锁的存在，还是有一种更简单、更清晰的方法来确定一行是否被锁定？
Thank you!
谢谢！
回答by Vincent Malgrat
You can write a procedure with the FOR UPDATE NOWAIT and return an error message when the row is locked:
您可以使用 FOR UPDATE NOWAIT 编写过程并在行被锁定时返回错误消息：
SQL> CREATE OR REPLACE PROCEDURE do_something(p_id NUMBER) IS
2 row_locked EXCEPTION;
3 PRAGMA EXCEPTION_INIT(row_locked, -54);
4 BEGIN
5 FOR cc IN (SELECT *
6 FROM some_table
7 WHERE ID = p_id FOR UPDATE NOWAIT) LOOP
8 -- proceed with what you want to do;
9 NULL;
10 END LOOP;
11 EXCEPTION
12 WHEN row_locked THEN
13 raise_application_error(-20001, 'this row is locked...');
14 END do_something;
15 /
Procedure created
Now let's build a small example with two sessions:
现在让我们用两个会话构建一个小例子：
session_1> select id from some_table where id = 1 for update;
ID
----------
1
session_2> exec do_something(1);
begin do_something(1); end;
ORA-20001: this row is locked...
ORA-06512: at "VNZ.DO_SOMETHING", line 11
ORA-06512: at line 2
session_1> commit;
Commit complete
session_2> exec do_something(1);
PL/SQL procedure successfully completed
回答by kdgregory
It's neither simple nor clean, but the information is available in the V$LOCKand V$SESSIONviews.
它既不简单也不干净，但信息在V$LOCK和V$SESSION视图中可用。
However, if you feel the need to use something like this as part of your normal application code, you need to think again. Applications should not care about how the database does locking. If you're running into deadlocks, you need to restructure your queries so that they don't happen.
但是，如果您觉得需要将这样的内容用作正常应用程序代码的一部分，则需要重新考虑。应用程序不应该关心数据库如何锁定。如果遇到死锁，则需要重构查询，以免它们发生。
