CODEWITHSUNDEEP
sqloracle-databaserowlocked
Cr1ms0nStraY
Cr1ms0nStraY

Reputation: 81

How can I lock a single row in Oracle SQL

It seems simple but I struggle with it. The question is how can I lock for example a single row from the table JOBS with JOB_ID = IT_PROG. I want to do it, because I want to try an exception from a procedure, where it displays you a message when you try to update a locked row. Thanks in advance for your time.

Upvotes: 1

Views: 36336

Answers (3)

Elza Christy Karunakaran
Elza Christy Karunakaran

Reputation: 9

It's not possible to manually lock a row in Oracle. You can manually lock an object,though. Exclusive lock is placed on the row automatically when performing DML operations to ensure that no other sessions could update the same row or any other DDL operations could drop that row- other sessions can read it any time.

The first session to request the lock on the rows gets it and any other sessions requesting write access must wait.
If you don't want to be get locked ,that means , if you don't want to wait , you can use

Select .... For update ( nowait / wait(n) ) ( skiplocked) statement

Upvotes: -3

Marmite Bomber
Marmite Bomber

Reputation: 21053

You may lock the record as described in other answers, but you will not see any exception while UPDATEing this row.

The UPDATE statement will wait until the lock will be released, i.e. the session with SELECT ... FOR UPDATE commits. After that the UPDATE will be performed.

The only exeption you can manage is DEADLOCK, i.e.

Session1   SELECT FOR UPDATE record A
Session2   SELECT FOR UPDATE record B
Session1   UPDATE record B  --- wait as record locked
Session2   UPDATE record A  --- deadlock as 1 is waiting on 2 and 2 waiting on 1

Upvotes: 6

zschallz
zschallz

Reputation: 103

AskTom has an example of what you're trying to do:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4515126525609

From AskTom:

    declare
            resource_busy   exception;
            pragma exception_init( resource_busy, -54 );
            success boolean := False;
    begin

        for i in 1 .. 3
        loop
             exit when (success);
             begin
                         select xxx from yyy where .... for update NOWAIT;
                         success := true;
             exception
                         when resource_busy then
                                 dbms_lock.sleep(1);
             end;
         end loop;

         if ( not success ) then
                     raise_application_error( -20001, 'row is locked by another session' );
         end if;
    end;

This attempts to get a lock, and if it can't get one (i.e. ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired is raised) it will raise an error.

Upvotes: 3

Related Questions