Oracle Database – Transactions and Locking

Oracle's concurrency model is its best-engineered part, and it behaves differently from what you may expect if you learned databases on MySQL or SQL Server. Two sentences carry most of it:

• Readers never block writers, and writers never block readers.
• A reader never sees uncommitted data, and never blocks waiting for it.

Both fall out of one mechanism: undo.

Read consistency via undo

When you change a row, Oracle writes the previous version into the undo tablespace before overwriting the block. Every query records the system change number (SCN) at which it started, and if it meets a block modified after that SCN it reconstructs the older version from undo.

So a SELECT sees a consistent snapshot of the database as of the moment it began — no locks taken, nothing blocked, no dirty reads possible. This is Oracle's MVCC, and there is no "read uncommitted" mode because there is no need for one.

The cost shows up as one specific error. If undo is overwritten before a long-running query finishes reconstructing what it needs:

ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small

Almost always a long report running against a table under heavy DML. The fixes are a bigger UNDO_RETENTION, a bigger undo tablespace, or making the query faster.

Where a transaction begins and ends

There is no BEGIN TRANSACTION. The first DML statement starts one implicitly, and it runs until you end it.

UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- transaction starts here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

SAVEPOINT after_transfer;
DELETE FROM audit_log WHERE logged_at < sysdate - 365;
ROLLBACK TO SAVEPOINT after_transfer;   -- undoes the delete, keeps the transfer

COMMIT;

Three things to know about ending one:

• SQL*Plus and SQLcl do not autocommit. Close the window without committing and your work is gone. JDBC, by contrast, does autocommit by default — every statement is its own transaction — which is the opposite mistake and quietly breaks multi-statement logic. Spring's @Transactional turns it off for you.
• DDL commits. CREATE, ALTER, DROP and TRUNCATE issue a commit before and after themselves. A TRUNCATE in the middle of a transaction commits everything before it, and cannot be rolled back.
• ROLLBACK TO SAVEPOINT does not end the transaction, and it releases only the locks acquired after the savepoint.

Isolation levels

Oracle implements two of the four ANSI levels, and that is sufficient because its READ COMMITTED is stronger than the standard requires.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;   -- for every subsequent transaction

Dirty reads are impossible at either level. Non-repeatable reads and phantoms are possible under READ COMMITTED and not under SERIALIZABLE.

Oracle's serializable is optimistic: it does not lock ahead, it fails at the end. Update a row that changed since your transaction started and you get:

ORA-08177: can't serialize access for this transaction

which is not a bug — it is the level working. Anything running at SERIALIZABLE needs retry logic, because ORA-08177 is expected under contention. Roll back the whole transaction and run it again; retrying just the statement is wrong, because the snapshot is fixed for the transaction.

Locking

Oracle locks rows, and only rows, and only for writes. There is no lock escalation to a page or table lock — a common source of surprise for people arriving from SQL Server. Row locks are stored in the data block itself, so there is no lock manager to run out of and no memory cost to locking a million rows.

SELECT FOR UPDATE

Pessimistic locking, for read-then-write logic that must not race:

SELECT balance INTO v_balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Nobody else can modify this row until we commit.
UPDATE accounts SET balance = v_balance - 100 WHERE id = 1;
COMMIT;

SELECT ... FOR UPDATE NOWAIT;        -- ORA-00054 immediately if locked
SELECT ... FOR UPDATE WAIT 5;        -- ORA-30006 after 5 seconds
SELECT ... FOR UPDATE SKIP LOCKED;   -- silently omit rows somebody else holds

Plain FOR UPDATE waits forever, which in a web request means a thread hanging until somebody's session times out. Use NOWAIT or WAIT n in anything interactive and turn ORA-00054 into a "please try again".

SKIP LOCKED as a work queue

SKIP LOCKED is the feature worth remembering. It turns a table into a queue that many workers can drain concurrently without coordinating, because each worker skips rows another worker has claimed:

-- Every worker runs exactly this. No two ever get the same job.
DECLARE
  CURSOR c IS
    SELECT id FROM job_queue
    WHERE  status = 'PENDING'
    ORDER  BY created_at
    FOR UPDATE SKIP LOCKED;
  v_id job_queue.id%TYPE;
BEGIN
  OPEN c;
  FETCH c BULK COLLECT INTO ... LIMIT 10;   -- claim a batch
  CLOSE c;
  -- process, then mark done and COMMIT to release the locks
END;
/

Before reaching for a message broker, check whether this is enough. It very often is, and it comes with transactional semantics for free — the job and the work it produced commit together.

Deadlocks

Two sessions each holding what the other wants. Oracle detects the cycle in about three seconds and breaks it:

ORA-00060: deadlock detected while waiting for resource

An important detail: Oracle rolls back only the statement that detected the deadlock, not the transaction. Your transaction is still open, still holding its other locks, and now in a partially-applied state. The application must catch ORA-00060 and ROLLBACK explicitly. Code that logs it and carries on is corrupting data.

Oracle also writes a trace file with both SQL statements and the rows involved — the fastest route to the cause:

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

The usual cause is two code paths updating the same rows in different orders. Fix it by ordering consistently — always parent before child, always ascending id — rather than by retrying, though retrying is a reasonable belt-and-braces addition.

Finding a blocker

When something is hung, this is the query:

SELECT s.sid, s.serial#, s.username, s.status,
       s.blocking_session, s.event, s.seconds_in_wait,
       q.sql_text
FROM   v$session s
LEFT   JOIN v$sql q ON q.sql_id = s.sql_id
WHERE  s.blocking_session IS NOT NULL
    OR s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);

The whole waiting chain, with the head of it — the session that is blocking and not itself blocked — showing blocking_session = NULL. That session is the problem, and nine times out of ten it is INACTIVE: somebody ran an UPDATE in a SQL client, went to lunch, and never committed.

-- Which rows, specifically
SELECT l.session_id, o.object_name, l.locked_mode
FROM   v$locked_object l JOIN dba_objects o ON o.object_id = l.object_id;

-- Last resort
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;

Killing a session rolls its transaction back, which on a large uncommitted change can itself take a while. The session shows as KILLED and holds its locks until the rollback finishes; there is nothing to do but wait.

Optimistic locking instead

For a web application, pessimistic locks held across a user's think time are the wrong shape. Add a version column and check it on write:

UPDATE orders
SET    total = :new_total, version = version + 1
WHERE  id = :id AND version = :expected_version;

-- SQL%ROWCOUNT = 0 means somebody else changed the row: report a conflict.

This is exactly what JPA's @Version does, and it is the default for a reason: no locks are held between reading and writing, so a user who abandons a form costs nothing.

Oracle also exposes ORA_ROWSCN, a per-row change number that can serve as a version column with no schema change — but it is block-granular unless the table was created with ROWDEPENDENCIES, so an unrelated row in the same block will produce a false conflict. A real version column is worth the four bytes.

Next

Last in the track: wiring all of this up from Spring Boot — driver, connection string, dialect, sequence allocation and the mistakes that only show up under load.