Transactions

A transaction is a logical unit of work consisting of one or more SQL operations that must execute as an indivisible whole. Databases are designed to run all the incoming queries at the same time atomically, satisfying ACID properties.

Transactions & Atomicity

Databases use locks to isolate areas before it starts a SQL command to prevent other commands accessing this data, All other access to the data must wait until the area is unlocked, this is called atomicity.

All single SQL queries are atomic. For multi-statement transactions, you must use explicit transaction control.

Transaction Lifecycle

stateDiagram-v2
    [*] --> Active: BEGIN
    Active --> PartiallyCommitted: Last statement executes
    Active --> Failed: Error / constraint violation
    PartiallyCommitted --> Committed: COMMIT
    PartiallyCommitted --> Failed: System failure
    Failed --> Aborted: ROLLBACK
    Committed --> [*]
    Aborted --> [*]

Transaction Control

BEGIN;                     -- Start transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                    -- Make changes permanent

If something goes wrong:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Oops, wrong amount
ROLLBACK;                  -- Undo everything since BEGIN

Savepoints

Create intermediate checkpoints within a transaction to partially roll back without aborting the entire transaction:

BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 500);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 99, 1);
-- Wrong product, roll back just this insert
ROLLBACK TO before_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 42, 1);
COMMIT;

Transactions in Application Code

Most applications interact with transactions through database drivers, not raw SQL:

Python (SQLAlchemy):

session = Session()
session.begin()
try:
    session.execute(text("UPDATE accounts SET balance = balance - 100 WHERE id = 1"))
    session.execute(text("UPDATE accounts SET balance = balance + 100 WHERE id = 2"))
    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()

Java (JDBC):

Connection conn = DriverManager.getConnection(url, user, password);
try {
    conn.setAutoCommit(false);  // Start transaction
    // ... execute statements ...
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
} finally {
    conn.close();
}

Note: many DBMS operate in auto-commit mode by default — each individual SQL statement is its own transaction. You must explicitly disable auto-commit or use BEGIN to group multiple statements.

Isolation Levels

The degree to which concurrent transactions can see each other’s changes. Set per transaction:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... queries run under serializable isolation ...
COMMIT;

See SQL Isolation Levels for the full breakdown of Read Uncommitted, Read Committed, Repeatable Read, and Serializable levels.

Concurrency Control

How the DBMS handles multiple transactions running simultaneously:

• Two-Phase Locking (2PL) — Lock-based; growing phase acquires locks, shrinking phase releases
• MVCC — Version-based; readers see a snapshot, never block writers
• Isolation levels determine the trade-off between correctness and performance

See Database Concurrency for concurrency problems (dirty reads, lost updates, phantom reads).

On Conflict

Whenever we try to add a new record that already exists, the query will fail due to the constraints in most scenarios, but we can still update the record that exists with the new value if we wish to do so, using ON CONFLICT keyword.

INSERT INTO foo (bar,car)
VALUES (dead,alive)
ON CONFLICT (bar)
DO UPDATE SET car = car+1
RETURNING *;

In this manner we can update a column based on the constraints without having queries failing! This pattern is called an upsert (insert-or-update).

Compensating Transactions

Committed transactions are irreversible — you cannot abort them after commit. The only way to undo a committed transaction’s effects is by executing a compensating transaction that logically reverses the original. For example, to reverse a $100deposit,executea$100 withdrawal. The DBMS does not auto-generate compensating transactions — the programmer must write them.

This is especially important for observable external writes (e.g., sending data to a partner system, printing) — once committed and sent externally, the effects cannot be rolled back by the database.

Nested Transactions

Nested transactions are a hierarchy where a parent transaction contains sub-transactions that can independently commit or abort, providing finer-grained control:

• If a nested (child) transaction commits, its changes are provisional — only visible to the parent. The parent must also commit for changes to become permanent.
• If any child fails, the parent can choose to abort the child without rolling back the entire parent transaction.
• Useful for complex operations with multiple semi-independent steps (e.g., airline booking with separate flight legs and mileage purchases).

Support: Oracle and SQL Server support nested transactions. SQLite does not — use savepoints as an alternative for partial rollback.

Read Transactions

Wrapping SELECT statements in a transaction guarantees reading from a consistent snapshot — all tables reflect the same point-in-time state. Essential for report generation where multiple queries must see consistent data:

BEGIN;
SELECT * FROM accounts;       -- sees consistent state
SELECT * FROM transactions;   -- sees same point-in-time as above
COMMIT;                        -- or ROLLBACK (no changes made, just releases the snapshot)

Recovery

If a transaction is committed but the system crashes before changes are flushed to disk, the WAL ensures durability. The ARIES Recovery Algorithm replays committed transactions and undoes uncommitted ones during recovery.