How to Lock a Row: SELECT FOR UPDATE

Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.

This can be achieved by locking the row in Oracle before update. Here’s how.

Lock Rows with SELECT…FOR UPDATE

In Oracle, adding a FOR UPDATE clause to a SELECT statement lets you lock the selected rows. Other users cannot lock or update those rows until you end your transaction.

Things to know about row locking in Oracle:

1. SELECT…FOR UPDATE will not be able to lock rows if they are already locked by another transaction. By default, the session will wait for the locks to be released by the other transaction.
2. You can instruct Oracle to not wait for the locks to be released, by adding a NOWAIT to SELECT…FOR UPDATE.
3. To release the locked rows, a COMMIT or ROLLBACK must be issued in the session holding the lock.
4. SELECT…FOR UPDATE is restricted from use in SQLs with the DISTINCT operator, set operators, CURSOR expression, GROUP BY or aggregate functions.
5. Nested table rows are not locked when the parent table rows are locked. You must lock nested table rows explicitly if needed.
6. In a SQL with joins of multiple tables, if you want to lock rows from only a particular table, specify the  OF … column clause to indicate which table to lock. Without this clause, Oracle locks the selected rows from all the tables in the join.
7. When selecting multiple rows for update, you can add the SKIP LOCKED clause to lock rows if not already locked by another transaction, else skip rows if they are already locked. [Note: Oracle recommends that you use the Oracle Streams Advanced Queuing APIs instead of the SKIP LOCKED functionality.]

Try it: SELECT…FOR UPDATE with/without NOWAIT

Without NOWAIT:

Session 1 – Run a plain SQL with SELECT…FOR UPDATE.
Outcome: The SQL returns a row.

Session 2 – Run the same SQL with SELECT…FOR UPDATE.
Outcome: The SQL "hangs" indefinitely without returning a result, till you issue a COMMIT OR ROLLBACK in session one.

With NOWAIT:

Session 1 – Run a plain SQL with SELECT…FOR UPDATE NOWAIT.
Outcome: The SQL returns a row.

Session 2 – Run the same SQL with SELECT…FOR UPDATE NOWAIT.
Outcome: The SQL results in an error.

ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

PL/SQL for Concurrency Conflict Check

We can make use of SELECT…FOR UPDATE in PL/SQL for concurrency control:

1. Create a user-defined exception for ORA-00054
2. Attempt a row lock NOWAIT before starting the transaction
• If the row lock is successful, proceed with the transaction
• If the row lock raises ORA-00054, this means the row is locked by another transaction. Do not proceed with the transaction.

We’ll create a function that attempts a row lock NOWAIT and returns a BOOLEAN value to indicate success or failure. The calling program can accordingly proceed with or abandon the transaction.

lock_row function:

/*--------------------------------------*
* Function to lock a row in Oracle      *
*---------------------------------------*/
create or replace function lock_row(p_id in number)
return boolean
is
  resource_busy exception;
  pragma        exception_init(resource_busy, -54); 
  v_empno       emp.empno%type;
begin
  select empno into v_empno 
  from emp
  where empno = p_id 
  for update nowait;
 
  return true;
  
exception
  when resource_busy then
    return false;
end;

PL/SQL anonymous block that invokes the function:

/*-------------------------------------------------*
* PL/SQL code that checks for concurrency conflict *
*--------------------------------------------------*/
begin
  if lock_row(7900) then
    dbms_output.put_line('Row locked successfully');
    -- Row lock is successful
    -- Proceed with the transaction  
  else
    dbms_output.put_line('Unable to lock row');
    -- Row lock attempt failed
    -- Do not proceed with the transaction
  end if;
end;

Execution result when row lock is successful (that is, no other session is holding the lock — the transaction can proceed):

SQL> /*-------------------------------------------------*
SQL> * PL/SQL code that checks for concurrency conflict *
SQL> *--------------------------------------------------*/
SQL> begin
  2    if lock_row(7900) then
  3      dbms_output.put_line('Row locked successfully');
  4      -- Row lock is successful
  5      -- Proceed with the transaction
  6    else
  7      dbms_output.put_line('Unable to lock row');
  8      -- Row lock attempt failed
  9      -- Do not proceed with the transaction
 10    end if;
 11  end;
 12  /
Row locked for update

PL/SQL procedure successfully completed.

Execution result when row lock is not successful (that is, another session is holding the lock):

SQL> /*-------------------------------------------------*
SQL> * PL/SQL code that checks for concurrency conflict *
SQL> *--------------------------------------------------*/
SQL> begin
  2    if lock_row(7900) then
  3      dbms_output.put_line('Row locked successfully');
  4      -- Row lock is successful
  5      -- Proceed with the transaction
  6    else
  7      dbms_output.put_line('Unable to lock row');
  8      -- Row lock attempt failed
  9      -- Do not proceed with the transaction
 10    end if;
 11  end;
 12  /
Unable to lock row

Summary

This article describes how to lock a row in Oracle, lists important features associated with row locking in Oracle, and demonstrates a sample implementation of acquiring a row lock before initiating a transaction to manage concurrency conflict.

For Further Reading

• Introduction to database concurrency control
• AskTom: Who is locking my rows?