Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 38 ยท Exceptions and Transactions

Autonomous Transactions and Locking in Oracle

Use PRAGMA AUTONOMOUS_TRANSACTION for logging, understand Oracle row locks, SELECT FOR UPDATE with NOWAIT and SKIP LOCKED, optimistic locking and deadlocks.

Normally everything a session does belongs to one transaction. Two topics extend that picture: autonomous transactions, which let a subprogram run a separate transaction inside the main one, and locking, which explains what happens when two sessions want the same row. Both matter the moment a real system has more than one user.

Oracle cannot run on this site. Oracle snippets are read-only, with outputs shown only where they are certain. The runnable examples are Python stand-ins that model two sessions and two transactions.

Autonomous transactions

Putting PRAGMA AUTONOMOUS_TRANSACTION; in the declaration section of a procedure, function, trigger or package subprogram makes it run in its own, independent transaction:

SQL
CREATE OR REPLACE PROCEDURE write_audit (p_text IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log (logged_at, message) VALUES (SYSTIMESTAMP, p_text);
  COMMIT;                          -- commits ONLY the audit row
END;
/

BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
  write_audit('Debited account 1');
  ROLLBACK;                        -- undoes the update, but the audit row stays
END;
/

How it works:

  1. When the autonomous routine is called, the main transaction is suspended.
  2. The routine starts a new transaction with its own locks, commits or rolls back on its own.
  3. Control returns and the main transaction resumes, unaffected.

The autonomous routine must end with a COMMIT or ROLLBACK (or it raises ORA-06519: active autonomous transaction detected and rolled back). It cannot see uncommitted changes of the main transaction, because they are a separate transaction.

Python Two transactions, one session (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Good uses: error and audit logging, sequence-number generators that must not roll back, progress messages from a long batch. Risky uses: anything that changes business data, because the main transaction can later roll back while the autonomous part stays committed, leaving the data inconsistent. Also remember that a function in a SELECT that is autonomous may do DML (the only exception to the earlier rule), but it can be called many times per query and in any order, so keep it for logging only.

A trigger may carry the pragma too, which is the only way a trigger can issue COMMIT.

How Oracle locks rows

Oracle locks at row level. When a session changes a row, it holds that row's lock until it commits or rolls back. A second session that tries to change the same row waits. Everything else continues normally:

  • Other sessions can still read the row (they see the last committed version, with no waiting).
  • Other sessions can change other rows in the same table.
  • Plain INSERT of different keys never blocks. Inserting the same unique key into two sessions makes the second wait for the first to commit or roll back.

You rarely request locks explicitly, since DML does it for you. To lock rows before deciding what to do with them, use SELECT ... FOR UPDATE:

SQL
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;     -- wait forever
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE NOWAIT;    -- ORA-00054 immediately
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE WAIT 5;    -- wait up to 5 seconds

The locks last until COMMIT or ROLLBACK. SKIP LOCKED is the key to job queues: each worker picks rows nobody else holds, with no waiting:

SQL
DECLARE
  CURSOR c_jobs IS
    SELECT job_id FROM jobs
     WHERE status = 'NEW'
     ORDER BY created_at
       FOR UPDATE SKIP LOCKED;
  v_id jobs.job_id%TYPE;
BEGIN
  OPEN c_jobs;
  FETCH c_jobs INTO v_id;          -- take one job nobody else holds
  -- ... process it, then COMMIT to release the lock
  CLOSE c_jobs;
END;
/

The row limiting clause (FETCH FIRST) cannot be combined with FOR UPDATE, so fetch only as many rows from the cursor as you need.

Preventing lost updates

Under READ COMMITTED, two sessions can both read a balance of 100, both compute 100 + 50, and both write 150, losing one update. Two cures:

Pessimistic: lock first, with FOR UPDATE, then read and write. The second session waits for the first to finish and then reads the new value.

Optimistic: read a version number, and update only if it has not changed:

SQL
UPDATE accounts
   SET balance = :new_balance, version = version + 1
 WHERE account_id = :id AND version = :version_read;

IF SQL%ROWCOUNT = 0 THEN
  RAISE_APPLICATION_ERROR(-20301, 'Row was changed by someone else, reload it');
END IF;

Optimistic locking holds no locks while the user thinks, which suits web applications with long idle times. Oracle can also supply the version for free with ORA_ROWSCN, though a dedicated column is more exact.

Python Lost update and the version check (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Deadlocks

A deadlock occurs when two sessions each hold a lock the other needs:

  1. Session 1 updates row A.
  2. Session 2 updates row B.
  3. Session 1 tries to update row B and waits.
  4. Session 2 tries to update row A and waits. Neither can ever proceed.

Oracle detects this within a few seconds and picks a victim: one statement fails with ORA-00060: deadlock detected while waiting for resource. Only that statement is rolled back, not the whole transaction; the session must decide to retry or roll back, and the trace file names the sessions involved. The best prevention is to always lock rows in a consistent order (for example by ascending account id when transferring money between two accounts), keep transactions short, and index foreign keys, since an unindexed foreign key makes deleting a parent row lock the entire child table.

Seeing who blocks whom

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

This lists the waiting sessions and who they wait for. Fixing it usually means asking the blocker to commit or roll back, or, as an administrator, ending that session with ALTER SYSTEM KILL SESSION.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What must an autonomous transaction routine do before it returns?

    Otherwise Oracle raises ORA-06519 and rolls the autonomous work back.
  2. 2. A session updates row 5 and has not committed. What can another session do with row 5?

    Readers see the last committed version without waiting. Writers of the same row wait.
  3. 3. What is SKIP LOCKED useful for?

    Rows locked by others are skipped instead of waited for, so workers never block each other.
  4. 4. Which practice most reduces deadlocks?

    If everyone locks A before B, no cycle can form.

Summary

  • PRAGMA AUTONOMOUS_TRANSACTION runs a routine in its own transaction that commits independently, ideal for logging, risky for business data.
  • Oracle locks rows, not tables: readers never block, and writers block only writers of the same row.
  • FOR UPDATE with NOWAIT, WAIT n or SKIP LOCKED controls waiting.
  • Prevent lost updates with pessimistic (FOR UPDATE) or optimistic (version column) locking.
  • Deadlocks raise ORA-00060; lock in a consistent order and keep transactions short.

Next: Execution Plans and Statistics