[ACCEPTED]-How do you check if a row is locked for update?-ora-00054
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
It's neither simple nor clean, but the information 8 is available in the
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.
More Related questions