Transactions and Read Consistency

18.1 Transaction Control

A transaction starts implicitly with the first DML. It ends with COMMIT, ROLLBACK, DDL (implicit commit), or a normal exit (SQL*Plus commits on EXIT). An abnormal termination rolls back.

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
ROLLBACK TO SAVEPOINT after_debit;   -- partial rollback
COMMIT;

SET TRANSACTION READ ONLY;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION NAME 'monthly_close';
COMMIT WRITE BATCH NOWAIT;           -- asynchronous commit

18.2 Read Consistency and Multi-Version Concurrency

Readers don't block writers, and writers don't block readers.

Queries see data as of the query start SCN (statement-level read consistency). Older versions are rebuilt from undo (consistent read blocks).

ORA-01555: snapshot too old means the needed undo was overwritten. Fix it with a larger undo tablespace, UNDO_RETENTION, retention guarantee, or shorter queries.

18.3 Isolation Levels

READ COMMITTED (default) Transaction-level consistency. ORA-08177 on conflicting updates.

Transaction-level consistency, no DML Oracle has no READ UNCOMMITTED and never allows dirty reads.

18.4 Locking

Row-level locks (TX) are stored in the data block. There's no lock escalation in Oracle.

Table locks (TM): RS, RX, S, SRX, X.

DDL locks and latches / mutexes (internal memory protection).

SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;            -- lock rows
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE NOWAIT;     -- fail immediately (ORA-00054)
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE WAIT 5;
SELECT * FROM job_queue WHERE status = 'NEW' FOR UPDATE SKIP LOCKED;  -- queue processing
LOCK TABLE orders IN EXCLUSIVE MODE NOWAIT;

18.5 Blocking and Deadlocks

Deadlock: ORA-00060. Oracle rolls back one statement (not the whole transaction) and writes a trace file with the deadlock graph.

Common causes: inconsistent access order, unindexed foreign keys, bitmap indexes under concurrent DML, ITL shortage (INITRANS).

SELECT sid, serial#, username, blocking_session, event, seconds_in_wait, sql_id
FROM v$session WHERE blocking_session IS NOT NULL;

SELECT * FROM dba_blockers;  SELECT * FROM dba_waiters;
ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;

18.6 Autonomous Transactions

CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (msg, logged_at) VALUES (p_msg, SYSTIMESTAMP);
  COMMIT;   -- independent of the caller's transaction
END;

18.7 Newer Concurrency Features

Lock-free reservations (23ai): concurrent updates to numeric "reservable" columns (e.g., inventory) without row locks.

Priority transactions (23ai): automatically roll back low-priority blockers.

Session-less transactions (23ai/26ai): suspend and resume transactions across sessions.