How to release locks acquired by a FOR UPDATE clause in Oracle step-by-step demonstration with example

In Oracle, you can release locks acquired by a FOR UPDATE clause by either committing or rolling back the transaction in the session that holds the lock. Here’s how you can do it:

1. Committing the Transaction

Committing the transaction will release the locks held by the session.

COMMIT;

2. Rolling Back the Transaction

Rolling back the transaction will also release the locks.

ROLLBACK;

Releasing Locks in a Session

Here’s a step-by-step demonstration of how to release locks:

Step 1: Open Two Sessions

Open two separate SQL*Plus or SQL Developer sessions to simulate concurrent access.

Step 2: Session 1 - Acquire a Lock

In Session 1, select a row for update to acquire a lock.

-- Session 1: Select row for update
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE;

Step 3: Session 2 - Attempt to Access Locked Row

In Session 2, attempt to update the locked row.

-- Session 2: Try to update the locked row
UPDATE employees
SET salary = salary + 2000
WHERE employee_id = 100;

Session 2 will hang or wait until the lock is released in Session 1.

Step 4: Session 1 - Release the Lock

In Session 1, release the lock by either committing or rolling back the transaction.

Option A: Commit the Transaction

-- Session 1: Commit the transaction
COMMIT;

Option B: Rollback the Transaction

-- Session 1: Rollback the transaction
ROLLBACK;

Step 5: Verify in Session 2

After the lock is released, the update in Session 2 will proceed. Verify the result.

-- Session 2: Check the updated value
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id = 100;

Example Scenario

Here is a complete example to illustrate this process:

1. Session 1: Acquire Lock

   
   -- Session 1: Select row for update
   SELECT employee_id, first_name, last_name, salary
   FROM employees
   WHERE employee_id = 100
   FOR UPDATE;
   

2. Session 2: Attempt to Update

   
   -- Session 2: Try to update the locked row
   UPDATE employees
   SET salary = salary + 2000
   WHERE employee_id = 100;
   

   This will hang until the lock is released.

3. Session 1: Release Lock by Committing or Rolling Back

   
   -- Session 1: Commit the transaction
   COMMIT;
   
   -- or
   
   -- Session 1: Rollback the transaction
   ROLLBACK;
   

4. Session 2: Verify Update

   
   -- Session 2: Check the updated value
   SELECT employee_id, first_name, last_name, salary
   FROM employees
   WHERE employee_id = 100;
   

Conclusion

By committing or rolling back the transaction, you can release the locks acquired by a FOR UPDATE clause in Oracle. This ensures that other sessions can proceed with their operations on the locked rows.