SELECT FOR UPDATE in Oracle

 

The SELECT FOR UPDATE statement in Oracle is a query that locks specific rows in a table to ensure no other session can modify them while your session is working with them. This is useful for ensuring data consistency in multi-user environments.

---

Key Points:

1. Purpose:

   • It locks the rows selected by the query to prevent updates or deletes by other sessions.
   • Other sessions can still read the data, but they cannot modify it until your session releases the lock.

2. How It Works:

   • When you execute SELECT ... FOR UPDATE, Oracle:
     • Retrieves the rows that meet the query's condition.
     • Places an exclusive lock on those rows.
   • The lock stays until your session ends the transaction with either:
     • COMMIT: Save the changes and release the lock.
     • ROLLBACK: Undo the changes and release the lock.

3. Behavior with Locked Rows:

   • If another session already holds a lock on the rows you're trying to select, your query will wait until the lock is released (default behavior).
   • You can use NOWAIT or WAIT n options to handle such scenarios:
     • NOWAIT: Returns an error immediately if the rows are locked.
     • WAIT n: Waits up to n seconds for the lock to be released before throwing an error.

---

Example Scenario:

Step 1: Session 1 locks rows.

SQL> SELECT * FROM EMP WHERE DEPTNO = 10 FOR UPDATE;

• Rows with DEPTNO = 10 are locked by Session 1.

Step 2: Session 2 tries to lock the same rows.

SQL> SELECT * FROM EMP WHERE DEPTNO = 10 FOR UPDATE;

• Session 2 will wait until Session 1 commits or rolls back.

Step 3: Session 1 commits.

SQL> COMMIT;

• Lock is released. Session 2 can now proceed.

---

Real-Life Use Case:

Imagine a banking system:

• Session 1: A user is transferring money and locks their account row.
• Session 2: Another user tries to update the same account during the transfer.
• The SELECT FOR UPDATE ensures no conflicting updates occur until the transfer is complete.

---

Notes:

• Use this feature judiciously, as holding locks for too long can lead to contention or deadlocks.

• Always ensure transactions using SELECT FOR UPDATE end promptly with COMMIT or ROLLBACK.