如何检查行是否被锁定以进行更新？

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 = 1000 is 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!

2 个解决方案

#1

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

#2

It's neither simple nor clean, but the information is available in the V$LOCK and V$SESSION views.

它既不简单也不干净,但可以在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.

但是,如果您觉得需要使用此类内容作为正常应用程序代码的一部分,则需要再次考虑。应用程序不应该关心数据库如何锁定。如果您遇到死锁,则需要重新构建查询,以便不会发生这些问题。

#1