Mastering Oracle Locks: Pessimistic, Optimistic, and Deadlock with Real‑World SQL demos

Lock Types in Oracle

Oracle uses locking to prevent concurrent transactions from corrupting data. Locks are divided into two main categories: data (DML) locks and dictionary locks. Data locks include shared (S), exclusive (X), row‑share (RS), row‑exclusive (RX), and share‑row‑exclusive (SRX) locks, applicable at row or table granularity.

Update‑Lost (Lost Update) Scenario

Two sessions read the same row (id=1, type=0). Session 1 updates type=1 and commits; Session 2 then updates the same row to type=2. After both commits, the change made by Session 1 is lost, demonstrating the classic lost‑update problem.

SYS@ORA11GR2>create table t_lock as select rownum as id,0 as type from dual connect by rownum <=3;

SYS@ORA11GR2>select min(id) from t_lock where type=0;  -- both sessions see 1

SYS@ORA11GR2>update t_lock set type=1 where id=1;  -- Session 1

SYS@ORA11GR2>commit;

SYS@ORA11GR2>update t_lock set type=2 where id=1;  -- Session 2

SYS@ORA11GR2>commit;

Result: row id 1 ends with type=2, the earlier update is overwritten.

Pessimistic Lock Example

Session 1 locks a row using FOR UPDATE NOWAIT. Session 2 attempts the same lock and receives ORA‑00054, indicating the resource is busy. Session 1 then updates the row, commits, and Session 2 can no longer find the original data because the lock condition no longer matches.

SYS@ORA11GR2>select * from t_lock where id=2 and type=0 for update nowait;

-- Session 2: ORA-00054 resource busy

SYS@ORA11GR2>update t_lock set type=1 where id=2 and type=0;

SYS@ORA11GR2>commit;

Optimistic Lock Example

Both sessions read the pseudo‑column ORA_ROWSCN for the same row (id=3). Session 1 updates the row using the original SCN value; the commit changes the SCN. Session 2 then tries to update using the stale SCN and affects zero rows, demonstrating optimistic concurrency control.

SYS@ORA11GR2>select id,type,ora_rowscn from t_lock where id=3;

-- SCN = 1246809 for both sessions

SYS@ORA11GR2>update t_lock set type=1 where ora_rowscn=1246809 and id=3;

SYS@ORA11GR2>commit;

SYS@ORA11GR2>update t_lock set type=1 where ora_rowscn=1246809 and id=3;

-- 0 rows updated (stale SCN)

Deadlock Demonstration

Two tables t_lock_1 and t_lock_2 each contain a row with id 1. Session 1 updates t_lock_1 without committing; Session 2 updates t_lock_2 without committing. Each session then tries to update the other table's row, causing both to wait for each other's lock. Session 1 finally receives ORA‑00060 deadlock error, while Session 2 remains blocked.

SYS@ORA11GR2>create table t_lock_1 (id number(2), name varchar2(15));

SYS@ORA11GR2>create table t_lock_2 as select * from t_lock_1;

SYS@ORA11GR2>insert into t_lock_1 values(1,'liubei');

SYS@ORA11GR2>insert into t_lock_2 values(1,'guanyu');

-- Session 1: update t_lock_1 set name='liuxuande' where id=1; (no commit)

-- Session 2: update t_lock_2 set name='guanyunchang' where id=1; (no commit)

SYS@ORA11GR2>update t_lock_2 set name='guanyunchang' where id=1;  -- Session 1 blocks

SYS@ORA11GR2>update t_lock_1 set name='liuxuande' where id=1;   -- Session 2 blocks

-- Session 1 receives ORA-00060 deadlock detected

Summary

Pessimistic locking assumes data will be contested and acquires locks immediately, causing other sessions to wait. Optimistic locking assumes low contention, checking a version identifier (e.g., ORA_ROWSCN) before committing; it avoids lock overhead but may require retries when conflicts occur. Deadlocks arise when two transactions each hold resources the other needs; Oracle detects them and aborts one transaction to break the cycle.