How do you check if a row is locked for update?

Question

Is there a way that one can test if a row has been locked for update in 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).

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?

Thank you!

Answer 1

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

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.

Answer 2

Hi dpb,

You can write a procedure with the FOR UPDATE NOWAIT and return an error message when the row is locked:

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