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

Solution 1:

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   SELECTFORUPDATE record A
Session2   SELECTFORUPDATE 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

Solution 2:

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;
    beginfor i in1 .. 3
        loop
             exit when (success);
             beginselect xxx from yyy where .... forupdate 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.

Solution 3:

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