如何检查是否为更新而锁定了行?

您可以使用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

现在,让我们用两个会话构建一个小示例:

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

它既不简单也不干净,但信息可在 V$LOCK 和 V$SESSION 意见。

但是,如果您觉得有必要将类似这样的代码作为正常应用程序代码的一部分,那么您需要重新考虑。应用程序不应该关心数据库如何锁定。如果您遇到了死锁,您需要重新构造查询,以便它们不会发生。