CODEWITHSUNDEEP
oracle-databasetransactionslocking
Vishal Tyagi
Vishal Tyagi

Reputation: 580

How to check for row lock contention while updating something in the same table in Oracle

How can I make sure that my update queries do not wait for a commit when the same row is locked by another transaction that has not yet committed?

I tried using a trigger before update on a column and wanted to check for contention for the row but since the trigger is itself not executed if there is a contention on the same row, i had to drop that option. This is how I designed the trigger and wished to check inside begin for row contention

create or replace trigger trig_row_contention_sig 
before update of most_used on tbl_sig for each row

But the trigger is also getting queued. Is there any other way? I would also be happy if I can discard those update queries post issuing and checking for contention for the same rows THAN wait.

Upvotes: 1

Views: 1216

Answers (1)

APC
APC

Reputation: 146239

You can implement a pessimistic locking strategy. This attempts to acquire the lock before issuing the commit:

 declare
     cursor c1 is
         select * from your_table
         where id = 1
         for update of whatever -- this is the pessimistic lock
         nowait -- abandon the attempt if row is already locked
         ;
     r1 c1%rowtype;
begin
    open c1;
    fetch c1 into r1;
    update your_table 
    set whatever = whatever + 1
    where current of c1;
    close c1;
    commit; -- release the lock
end;

This toy code has NOWAIT, so the query fails if the row is already locked. We can also WAIT for a specified timeout or wait indefinitely (what you're doing now).

Another neat feature of the SELECT ... FOR UPDATE syntax is SKIP LOCKED, which allows us to implement queuing. Find out more.

"Why does the trigger not work ? I mean when i have set it up to run before update, shouldnt it also be executed before row locking."

That's not what before update means. All triggers are effectively ON <<event>>. Triggers fire when the event happens. The trigger can't fire until the row is actually updated.

The before and after triggers refer to the state of the row. In a before update trigger we can reference :OLD values and manipulate :NEW values. In an after update trigger we can reference :OLD values and :NEW values but cannot change :NEW values.

Upvotes: 4

Related Questions