Managing Data Concurrency
Transactions and read consistency
A transaction begins with its first DML and ends with COMMIT, ROLLBACK or an implicit commit from DDL. Oracle provides statement-level read consistency under READ COMMITTED using undo: readers never wait for writers and writers never wait for readers, because a query reads the SCN-consistent image of each block from undo. Only writers block writers, and only for the same row.
Higher isolation levels are SERIALIZABLE (transaction-level consistency; may raise ORA-08177 if the transaction cannot be serialized) and READ ONLY.
Locks
Two families matter: DML locks and DDL locks. A DML transaction holds one TX lock (its undo segment and slot) and one TM lock per modified table. TM modes run from 2 (row share, from SELECT FOR UPDATE) through 3 (row exclusive, from INSERT/UPDATE/DELETE), 4 (share), and 5 (share row exclusive) to 6 (exclusive, from DDL). Oracle never escalates row locks to table locks; row locks live in block headers and waiters queue on ITL entries.
Deadlocks are detected automatically: one statement is rolled back with ORA-00060 and a trace file is written while the other session proceeds. Typical causes are unindexed foreign keys and bitmap indexes.
Diagnostics: V$LOCK, V$LOCKED_OBJECT, V$SESSION.BLOCKING_SESSION, DBA_BLOCKERS/DBA_WAITERS, and DML_LOCKS for the maximum number of concurrent DML locks.
Rule of thumb: to untangle a blocking chain, start from the waiting session and follow BLOCKING_SESSION until it is NULL - the session at the end holds the lock.