PL/SQL Transactional single rows locking with cursors

Oracle already provides an automatic exclusive locking mechanism for the rows you want to update. However you can override it to customize the performance (for example, if you need consistent data and/or exclusive access for the duration of a whole and more complex transaction).

When your application requires consistent data for the duration of the transaction, not reflecting changes by other transactions, you can achieve transaction-level read consistency by using explicit locking, read-only transactions, serializable transactions, or by overriding default locking.

Row locking at transactional level can be achieved with “SELECT FOR UPDATE” statement. The lock is released after a commit or rollback only.

If you want to lock single rows in Oracle PL/SQL you can use cursors, pointing at the rows you want to lock.

The following example show you how to lock single rows:

CREATE OR REPLACE PROCEDURE LOCK_ORDER_ENTRY 
(id_number IN number, system_user IN varchar2)
IS
/*"order_row" is a variable to store the row found by the cursor select statement   */
order_row order_queue%rowtype;
cursor c1 is
    SELECT * 
    FROM order_queue 
    WHERE id=id_number
    FOR UPDATE NOWAIT;
BEGIN
/* first of all you need to open the cursor */
OPEN c1;
/* then you need to fetch the content in the variable */
   LOOP
      FETCH c1 INTO order_row;
/* the lock will be released after the commit */
      IF (c1%found) THEN
         UPDATE order_queue SET processed=1, user=system_user where CURRENT OF c1;
         COMMIT; 
      END IF;
 /* then you need close the cursor */
  END LOOP;
CLOSE c1;
END LOCK_ORDER_ENTRY;

The “select for update” statement has two possible variants
– FOR UPDATE NO WAIT, that triggers an error if the row is locked by another user.
– FOR UPDATE SKIP LOCKED, that fastens the execution by skipping the already locked rows

If you need to lock a single row in the database you donĀ“t need a loop.