Transactions and Locks

A transaction is a unit of work that either fully succeeds or fully fails — no in-between states. Locks are how the database keeps concurrent transactions from corrupting each other's data.

Every SQL statement runs inside a transaction whether you notice it or not. Understanding how transactions and locks interact is the difference between an app that works under one user and an app that survives a hundred concurrent users.

ACID — The Four Guarantees

A correctly implemented transaction provides four guarantees, summarised as ACID:

Letter	Property	What it means
A	Atomicity	All statements succeed together or all fail together
C	Consistency	The database moves from one valid state to another valid state
I	Isolation	Concurrent transactions don't see each other's uncommitted work
D	Durability	Once committed, the work survives crashes and power loss

Oracle implements all four through three mechanisms: redo logs (durability), undo segments (rollback and read consistency), and locks (isolation).

The Transaction Lifecycle

A transaction begins implicitly with the first data-modifying statement and ends with COMMIT or ROLLBACK:

-- Implicit transaction start
INSERT INTO employees (employee_id, first_name) VALUES (300, 'Frank');

UPDATE employees SET salary = 7000 WHERE employee_id = 300;

-- One of two endings:
COMMIT;     -- both changes are permanent and visible to others
-- or
ROLLBACK;   -- both changes are discarded

Until you COMMIT, your inserts and updates exist only in your session. Other sessions see the old data. Locks held by your transaction remain held until COMMIT or ROLLBACK.

SAVEPOINTs — Partial Rollback

Inside a transaction you can mark intermediate points to roll back to without losing the whole transaction:

INSERT INTO orders (order_id, total) VALUES (1, 100);

SAVEPOINT after_order;

INSERT INTO order_items (order_id, sku) VALUES (1, 'A1');

SAVEPOINT after_first_item;

INSERT INTO order_items (order_id, sku) VALUES (1, 'BAD-SKU');

ROLLBACK TO after_first_item;   -- undo the bad insert, keep the rest

COMMIT;                          -- commit order + first item only

After the ROLLBACK TO after_first_item, the order and the first item are still in the transaction; only the bad-sku insert is gone. The COMMIT then makes the remaining work permanent.

Useful for routines that catch exceptions and want to retry only the failing portion.

DDL Auto-Commits

A subtle Oracle behaviour: every DDL statement (CREATE, ALTER, DROP, TRUNCATE, GRANT) automatically issues a COMMIT before and after itself.

INSERT INTO employees VALUES (...);
ALTER TABLE employees ADD (note VARCHAR2(100));    -- COMMITs the INSERT, then runs DDL
ROLLBACK;                                          -- nothing to roll back — the INSERT was already committed

This is a well-known footgun. If you're carefully composing a transaction with DML, don't run DDL in the middle of it.

Oracle's Read Consistency Model

Oracle never blocks readers and never holds read locks. This is one of its defining features:

• Writers do not block readers — your SELECT runs against the data as it existed at the moment your query (or your transaction in serializable mode) began.
• Readers do not block writers — UPDATEs proceed regardless of who is reading.
• Writers block writers on the same row — but only on the same row.

How? Oracle uses multi-version concurrency control (MVCC). When you update a row, the old version goes to undo. Concurrent readers see the old version from undo; new readers (after your COMMIT) see the new version.

-- Session A:
UPDATE employees SET salary = 9999 WHERE employee_id = 100;
-- (does not commit)

-- Session B (concurrently):
SELECT salary FROM employees WHERE employee_id = 100;
-- Returns the OLD salary. Session B is not blocked.

-- Session A:
COMMIT;

-- Session B (new query):
SELECT salary FROM employees WHERE employee_id = 100;
-- Returns 9999 now.

This is dramatically different from databases that use shared/exclusive locks for reads (like SQL Server's default). In Oracle, reads are always non-blocking against MVCC undo.

Isolation Levels

The SQL standard defines four isolation levels. Oracle supports two:

Level	Default behaviour	Oracle support
READ UNCOMMITTED	Allows dirty reads	Not supported (Oracle is always at least READ COMMITTED)
READ COMMITTED	Each statement sees data as of the moment the statement started	Default
REPEATABLE READ	Same data within a transaction (re-read returns same rows)	Not supported by name; SERIALIZABLE is close
SERIALIZABLE	Transaction sees data as of when it started; transaction must look like it ran alone	Supported

-- Default (no setting needed):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Strict isolation:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

READ COMMITTED Behaviour

Each statement sees a consistent snapshot for that statement only. If you run two SELECTs in the same transaction with a long pause between them, they may return different data (because other transactions committed in between).

-- Transaction T1:
SELECT COUNT(*) FROM orders;   -- 100 (snapshot at this moment)
-- another session commits 5 new orders
SELECT COUNT(*) FROM orders;   -- 105 (new snapshot)

This is fine for most workloads. Reports running in READ COMMITTED can see different totals across queries.

SERIALIZABLE Behaviour

The entire transaction sees data as of when it started. Both SELECTs in the example above would return 100.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM orders;   -- 100
-- another session commits 5 new orders
SELECT COUNT(*) FROM orders;   -- still 100

The trade-off: if your transaction tries to UPDATE a row that another transaction modified after your snapshot, you get ORA-08177: can't serialize access for this transaction. You must retry.

Use SERIALIZABLE for long-running reports and complex multi-step calculations that must see a stable view of the data.

Row Locks

When you UPDATE or DELETE a row, Oracle acquires a row-level exclusive lock on it. The lock is held until COMMIT or ROLLBACK.

-- Session A:
UPDATE employees SET salary = 9000 WHERE employee_id = 100;
-- (lock held on row 100)

-- Session B:
UPDATE employees SET commission_pct = 0.1 WHERE employee_id = 100;
-- Blocks, waiting for Session A to commit

Two updates to the same row serialise. Updates to different rows do not block each other — row locks are per-row.

SELECT FOR UPDATE — Lock a Row You Plan to Modify

SELECT salary, commission_pct
FROM   employees
WHERE  employee_id = 100
FOR UPDATE;

This locks the row immediately (acquiring a row-level exclusive lock) and prevents anyone else from updating it until you COMMIT/ROLLBACK. It's the standard pattern for "read, decide, update" workflows:

-- 1. Lock and read
SELECT balance INTO :bal FROM accounts WHERE id = 1 FOR UPDATE;

-- 2. Decide based on the value
IF :bal >= :withdrawal THEN
  -- 3. Update with confidence — no one can modify in between
  UPDATE accounts SET balance = balance - :withdrawal WHERE id = 1;
END IF;
COMMIT;

Without FOR UPDATE, two concurrent sessions could both read balance = 100, both see "yes, $50 is allowed", both UPDATE — and overdraft the account.

NOWAIT and WAIT

By default, FOR UPDATE waits indefinitely for the lock. Add NOWAIT to fail immediately if the row is locked, or WAIT n to wait at most n seconds:

SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE NOWAIT;
-- ORA-00054 if locked

SELECT * FROM employees WHERE employee_id = 100 FOR UPDATE WAIT 5;
-- Waits 5 seconds, then ORA-30006 if still locked

Used in apps that don't want to hang the UI waiting for a lock.

SKIP LOCKED — Process What You Can

SELECT * FROM job_queue
WHERE  status = 'PENDING'
FOR UPDATE SKIP LOCKED;

Returns rows that are not currently locked by another transaction; locks the ones it does return. Standard pattern for work queues: multiple workers can pull jobs simultaneously without conflict.

Table Locks

Some operations acquire table-level locks:

Operation	Lock type
LOCK TABLE x IN EXCLUSIVE MODE	Exclusive table lock — blocks all access
LOCK TABLE x IN SHARE MODE	Share lock — multiple sessions can hold; blocks writes
DDL (ALTER, DROP)	Exclusive table lock briefly
TRUNCATE TABLE	Exclusive table lock

-- Hold a share lock while doing a snapshot read:
LOCK TABLE employees IN SHARE MODE;
INSERT INTO emp_snapshot SELECT * FROM employees;
COMMIT;

Manual LOCK TABLE is rare in modern apps. MVCC + row locks handle nearly all use cases.

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs:

Session A: holds lock on row 1, wants lock on row 2
Session B: holds lock on row 2, wants lock on row 1
→ Both wait forever

Oracle detects this in seconds and aborts one of the transactions:

ORA-00060: deadlock detected while waiting for resource

The chosen "victim" gets the error; its transaction is rolled back. The other transaction proceeds normally.

Causes

1. Inconsistent locking order — code path A locks tables in order (X, Y); code path B locks in order (Y, X).
2. Long transactions — wider window for conflicting access.
3. Missing indexes — UPDATEs scan more rows than necessary and lock more rows than necessary.
4. Foreign keys without indexes on the child column — Oracle takes a share lock on the parent that conflicts with writes on the parent.

Diagnosing

The deadlock trace file in BACKGROUND_DUMP_DEST shows which sessions and rows were involved.

-- Find the trace file location:
SELECT value FROM v$parameter WHERE name = 'background_dump_dest';

The trace file contains the SQL and the row identifiers — usually enough to identify the conflicting code paths.

Prevention

1. Always lock objects in the same order across all code paths
2. Keep transactions short — minimise the time locks are held
3. Index FK columns to avoid parent table lock escalation
4. Use FOR UPDATE NOWAIT when possible — fail fast instead of waiting

Autonomous Transactions

An autonomous transaction is a transaction inside another transaction that commits independently of the parent. PL/SQL only — declared with a pragma:

CREATE OR REPLACE PROCEDURE log_event(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO event_log (msg, ts) VALUES (p_msg, SYSTIMESTAMP);
  COMMIT;       -- only the log_event INSERT is committed
END;
/

BEGIN
  INSERT INTO orders VALUES (...);
  log_event('Order created');
  ROLLBACK;     -- the orders INSERT is rolled back
                -- but the log_event INSERT is already permanent
END;
/

Use cases:

• Audit logging that must persist even when the calling transaction rolls back
• Sending notifications regardless of business transaction outcome
• Avoiding mutating table errors in triggers

Don't use autonomous transactions for ordinary work — they break atomicity guarantees and can mask bugs.

Inspecting Locks

-- Who's blocking whom?
SELECT b.sid AS blocked_sid,
       b.username AS blocked_user,
       l.sid     AS blocker_sid,
       l.username AS blocker_user,
       o.object_name
FROM   v$lock lc1
JOIN   v$lock lc2 ON lc1.id1 = lc2.id1 AND lc1.id2 = lc2.id2
                  AND lc2.lmode > 0 AND lc1.request > 0
JOIN   v$session b ON b.sid = lc1.sid
JOIN   v$session l ON l.sid = lc2.sid
JOIN   v$locked_object lo ON lo.session_id = lc2.sid
JOIN   user_objects o ON o.object_id = lo.object_id;

-- Sessions waiting on locks:
SELECT sid, event, seconds_in_wait, blocking_session
FROM   v$session
WHERE  blocking_session IS NOT NULL;

v$lock and v$session are the standard views for live lock investigation.

Worked Example — Bank Transfer With Proper Locking

A transfer from account 1 to account 2 must be atomic and not allow over-drafts. Here's the correct pattern:

DECLARE
  v_balance NUMBER;
  v_amount  NUMBER := 100;
BEGIN
  -- Lock the SOURCE account first (consistent order: low ID first)
  SELECT balance INTO v_balance
  FROM   accounts WHERE id = 1
  FOR UPDATE;

  IF v_balance < v_amount THEN
    RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
  END IF;

  -- Now lock the DESTINATION account
  SELECT 1 INTO v_balance
  FROM   accounts WHERE id = 2
  FOR UPDATE;

  -- Both locks held; do the transfer
  UPDATE accounts SET balance = balance - v_amount WHERE id = 1;
  UPDATE accounts SET balance = balance + v_amount WHERE id = 2;

  -- Audit log via autonomous transaction (survives a rollback above)
  log_event('Transfer ' || v_amount || ' from 1 to 2');

  COMMIT;
END;
/

The "always lock by ID order (low first)" rule prevents deadlocks between simultaneous transfers from 1→2 and 2→1 — both would acquire the lock on account 1 first, so they serialise instead of deadlock.

Common Errors

Error	Cause	Fix
ORA-00060: deadlock detected	Two transactions waiting on each other's locks	Lock objects in consistent order; index FK columns; keep transactions short
ORA-00054: resource busy and NOWAIT specified	NOWAIT lock request blocked by another session	Retry after delay; use WAIT n; or accept failure and surface to user
ORA-08177: can't serialize access for this transaction	SERIALIZABLE transaction detected a conflict	Retry the entire transaction
ORA-01551: extended rollback segment, pinned blocks	Long transaction exhausted undo	Commit more frequently or increase UNDO_RETENTION
ORA-01555: snapshot too old	Long-running SELECT against rapidly changing data	Reduce transaction time; increase UNDO_RETENTION; consider Flashback Data Archive
ORA-02049: timeout: distributed transaction waiting for lock	Cross-database transaction stuck	Tune DISTRIBUTED_LOCK_TIMEOUT; investigate root lock
ORA-06519: active autonomous transaction detected and rolled back	Autonomous transaction left uncommitted	Add COMMIT (or ROLLBACK) inside the autonomous block
Hung session	Session waiting for a lock indefinitely	Query v$session for blocking_session; kill the blocker or commit it

Interview Corner

Related Topics

• DML Commands — INSERT/UPDATE/DELETE are the operations that create lock contention
• Flashback — flashback recovers from accidental commits; undo retention drives both flashback and read consistency
• Indexes — missing FK indexes cause unnecessary locking
• Performance — lock contention shows up as high wait events in AWR reports
• Constraints — FK constraints interact with table locks