Concurrency, Undo, and Transaction Management
Concurrency, Undo, and Transaction Management covers isolation, locking and the undo machinery that makes read consistency possible.
Transactions and isolation
A transaction ends with COMMIT or ROLLBACK; DDL commits implicitly. The default isolation level is READ COMMITTED, where each statement sees a consistent snapshot as of statement start. SERIALIZABLE gives transaction-level consistency and can raise ORA-08177; READ ONLY suits reporting.
Locking
Oracle locks rows, not pages: a TX lock on the transaction plus a TM lock at table level. Readers do not block writers, and writers block only conflicting rows. Deadlocks are detected automatically, one statement is rolled back with ORA-00060, and a trace file is written; the fix is consistent application ordering. Find blockers through V$LOCK, V$SESSION.BLOCKING_SESSION and enq waits such as enq: TX - row lock contention.
Undo
Undo segments in the undo tablespace hold before-images. UNDO_RETENTION sets the target; RETENTION GUARANTEE prevents reuse but can fail transactions that run out of undo. TUNED_UNDORETENTION in V$UNDOSTAT shows the size Oracle would choose. ORA-01555 snapshot too old occurs when needed undo is overwritten: typical causes are long queries over frequently changed data, commit-in-a-loop batch jobs, and an undersized undo tablespace.
Worked example
A report fails with ORA-01555 after 40 minutes. Check V$UNDOSTAT for maxquerylen, and compare undo tablespace size and retention. Remedies: size undo for the longest query, raise UNDO_RETENTION with GUARANTEE if space allows, and fix the batch that commits after every row. Merely enlarging the datafile without changing the commit pattern can reproduce the error.
Traps
Believing readers block writers; expecting to kill a deadlock manually; assuming commit-per-row is always best practice; confusing undo with redo; expecting UNDO_RETENTION to be honoured on a fixed-size undo tablespace without GUARANTEE.