[ACCEPTED]-How do you check if a row is locked for update?-ora-00054

Accepted answer

Score: 16

You can write a procedure with the FOR UPDATE 3 NOWAIT and return an error message when 2 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 1 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

Score: 1

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

However, if 7 you feel the need to use something like 6 this as part of your normal application 5 code, you need to think again. Applications 4 should not care about how the database does 3 locking. If you're running into deadlocks, you 2 need to restructure your queries so that 1 they don't happen.

Source: stackoverflow.com

[ACCEPTED]-How do you check if a row is locked for update?-ora-00054

Oracle subquery does not see the variable from the outer block 2 levels up

Oracle exporting SQL of the Database structure

Oracle select for update behaviour

Optimal way to create a histogram/frequency distribution in Oracle?

where to define default value in oracle package

oracle pl/sql results into one string

Pivoting of data using two columns

Table rownum returns 0 records if rownum is tested for value greater than 100

java.sql.SQLException: ORA-03115: unsupported network datatype or representation

conditional order by clause in sql