Managing Data and Concurrency
Scope
This domain tests transactions, isolation levels, read consistency, locks, and deadlock handling. Expect questions that describe two sessions and ask who blocks whom.
Core rules
- Oracle uses statement-level read consistency in READ COMMITTED, the default. A query sees data as of the statement start; a transaction can see different snapshots across statements. SERIALIZABLE gives transaction-level consistency and can raise ORA-08177. READ ONLY is for consistent reports.
- DML acquires row-level TX locks; DDL acquires DDL locks and commits. SELECT FOR UPDATE locks selected rows. Oracle does not escalate row locks to block locks.
- Undo supplies consistent reads and rollback. Long queries can fail with ORA-01555 if undo is overwritten.
- Deadlocks are detected automatically; one session receives ORA-00060 and the other continues. V$LOCK, V$SESSION, V$TRANSACTION, and V$LOCKED_OBJECT help diagnose blocking.
Worked example
Session A updates row 1 but does not commit; session B updates row 1 and waits on the TX lock. If B also updates row 2, which A later requests, Oracle detects a deadlock and rolls back one statement. The fix is to commit consistently or lock rows in the same order.
Traps
READ COMMITTED does not prevent lost updates by itself. DDL commits. SELECT FOR UPDATE is not a read-consistency query. ORA-01555 is not a deadlock. Oracle does not use lock escalation.