Transactions (ACID)

What Is a Database Transaction — and Why Does It Exist?

A transaction is a sequence of SQL statements the database engine treats as a single, indivisible unit of work, bounded by BEGIN and either COMMIT or ROLLBACK. Every higher-level abstraction you use — JTA's UserTransaction, Spring's @Transactional, JPA's EntityTransaction — is ultimately a wrapper around exactly this mechanism: turning off autocommit on a JDBC connection, running statements, and issuing commit() or rollback() on that same connection. This page covers what the database engine itself does to make that guarantee hold — the mechanism every framework in this Bible delegates to.

-- WITHOUT an explicit transaction (autocommit ON, the JDBC default):
-- each statement commits independently the instant it runs.
INSERT INTO orders (customer_id, status, total) VALUES (501, 'PENDING', 129.90);
-- ↑ committed immediately, order row now permanent
UPDATE products SET stock = stock - 2 WHERE id = 42;
-- ↑ if the connection drops or this statement fails, the order above
--   already exists in the database with no stock ever decremented.
--   Two customers can now buy the last 2 units of the same product.

-- WITH an explicit transaction: both statements share one fate.
BEGIN;
INSERT INTO orders (customer_id, status, total)
    VALUES (501, 'PENDING', 129.90)
    RETURNING id;                                 -- id = 8842
UPDATE products SET stock = stock - 2
    WHERE id = 42 AND stock >= 2;                 -- guards against overselling
-- application checks affected-row count here:
-- 0 rows updated → insufficient stock → ROLLBACK, reject the order
-- 1 row updated  → proceed → COMMIT
COMMIT;

ACID — What the Engine Actually Does for Each Guarantee

ACID is recited far more often than it's understood. Each letter corresponds to a distinct mechanism inside the storage engine — knowing which mechanism enforces which guarantee is what lets you reason about failure modes instead of just reciting definitions.

Concurrency Anomalies — What Isolation Actually Protects Against

Isolation levels exist to prevent specific, well-defined anomalies that occur when two transactions run at the same time against the same rows. You cannot reason about which isolation level to choose without first being precise about which anomaly you're preventing.

Isolation Levels — And Where Real Engines Diverge From the Standard

The SQL standard defines four isolation levels in terms of which anomalies they permit. In practice, the actual engines you deploy to implement these levels through different mechanisms — locking versus MVCC snapshots — and some provide stronger guarantees than the standard requires for a given level. Knowing your specific engine's behavior, not just the ANSI table, is what separates correct concurrency reasoning from cargo-culting.

Row Locking — Shared, Exclusive, and SKIP LOCKED

A shared lock (read lock) allows other transactions to also read the row but blocks writers. An exclusive lock (write lock) blocks everyone else from reading or writing that row until the lock is released at commit or rollback. SELECT ... FOR UPDATE explicitly takes an exclusive lock on the selected rows for the duration of the transaction.

-- Prevent a lost update on inventory by locking the row before reading it
BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- any other transaction's SELECT ... FOR UPDATE on id=42 now blocks here
UPDATE products SET stock = stock - 2 WHERE id = 42;
COMMIT;   -- lock released

The production pattern: FOR UPDATE SKIP LOCKED

A naive job queue where multiple worker processes poll the same table with FOR UPDATE serializes every worker onto whichever row is locked first — the second worker blocks and waits. SKIP LOCKED tells the engine to simply skip rows already locked by another transaction, letting concurrent workers each grab a different row instead of queuing behind each other. This is the real mechanism behind most hand-rolled outbox and job-queue implementations, before reaching for a dedicated message broker.

-- Worker pattern: N processes competing for pending outbox events,
-- each one claiming a different batch instead of blocking on the same rows
BEGIN;
SELECT id, payload FROM outbox_events
    WHERE status = 'PENDING'
    ORDER BY created_at
    LIMIT 10
    FOR UPDATE SKIP LOCKED;

UPDATE outbox_events SET status = 'PROCESSING'
    WHERE id = ANY(:claimed_ids);
COMMIT;
-- a second worker running the exact same query concurrently gets the
-- NEXT 10 unlocked rows, not a block waiting for the first worker's lock

Deadlocks — How They Form and How the Engine Resolves Them

A deadlock happens when two transactions each hold a lock the other one needs, and each is waiting for the other to release it. Neither can proceed. This is not a bug in the database — it's an inherent risk of any system that locks resources, and it is entirely preventable at the application layer.

-- Transaction 1: transferring stock from product 42 to product 77
-- Transaction 2: transferring stock from product 77 to product 42
-- Running concurrently, in opposite lock order:

-- T1                                   T2
-- ─────────────────────────────────────────────────────────────
-- BEGIN;                               BEGIN;
-- UPDATE products SET stock=stock-5    UPDATE products SET stock=stock-3
--   WHERE id = 42;   -- locks 42         WHERE id = 77;   -- locks 77
--                                        (both locks acquired so far)
-- UPDATE products SET stock=stock+5    UPDATE products SET stock=stock+3
--   WHERE id = 77;   -- BLOCKS,          WHERE id = 42;   -- BLOCKS,
--   waiting for T2 to release 77         waiting for T1 to release 42
--
-- Neither can proceed → circular wait → DEADLOCK

The structural fix — the one that actually prevents deadlocks instead of just handling them after the fact — is always acquiring locks on multiple rows in the same, consistent order across your entire codebase (for example, always by ascending primary key):

// Sort by ID before locking — every caller now acquires locks in the same
// order, so the circular wait above becomes structurally impossible
public void transferStock(Long fromProductId, Long toProductId, int quantity) {
    Long first  = Math.min(fromProductId, toProductId);
    Long second = Math.max(fromProductId, toProductId);

    // Lock in ascending ID order regardless of transfer direction
    em.find(Product.class, first,  LockModeType.PESSIMISTIC_WRITE);
    em.find(Product.class, second, LockModeType.PESSIMISTIC_WRITE);

    // ... apply the actual stock movement using fromProductId / toProductId
}

Raw JDBC Transaction Control — What Every Framework Wraps

Autocommit is true by default on a JDBC Connection — every statement commits on its own the instant it executes. Multi-statement atomicity requires explicitly disabling it, and explicitly restoring it before the connection goes back to the pool, since a pooled connection with autocommit still disabled will silently corrupt the next borrower's behavior.

public void placeOrder(Long customerId, Long productId, int quantity, BigDecimal total) throws SQLException {
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);

        try {
            Long orderId;
            try (PreparedStatement insertOrder = conn.prepareStatement(
                    "INSERT INTO orders (customer_id, status, total) VALUES (?, 'PENDING', ?)",
                    Statement.RETURN_GENERATED_KEYS)) {
                insertOrder.setLong(1, customerId);
                insertOrder.setBigDecimal(2, total);
                insertOrder.executeUpdate();

                try (ResultSet keys = insertOrder.getGeneratedKeys()) {
                    keys.next();
                    orderId = keys.getLong(1);
                }
            }

            // Savepoint: if the stock decrement fails due to insufficient stock,
            // roll back only this part and mark the order as failed —
            // without undoing the order row that was just inserted above.
            Savepoint beforeStockUpdate = conn.setSavepoint("beforeStockUpdate");

            try (PreparedStatement decrementStock = conn.prepareStatement(
                    "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?")) {
                decrementStock.setInt(1, quantity);
                decrementStock.setLong(2, productId);
                decrementStock.setInt(3, quantity);

                if (decrementStock.executeUpdate() == 0) {
                    conn.rollback(beforeStockUpdate);
                    markOrderFailed(conn, orderId, "INSUFFICIENT_STOCK");
                } else {
                    conn.releaseSavepoint(beforeStockUpdate);
                }
            }

            conn.commit();

        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);   // mandatory before returning to the pool
        }
    }
}

int attempts = 0;
while (true) {
    try {
        placeOrder(customerId, productId, quantity, total);
        break;
    } catch (SQLException e) {
        if ("40001".equals(e.getSQLState()) && ++attempts < 3) {
            continue;   // deadlock victim — safe to retry
        }
        throw new RuntimeException("Order failed after retries", e);
    }
}

Optimistic vs Pessimistic Locking — At the SQL Level

Both strategies solve the lost-update anomaly from Section 2, but at opposite points in time: pessimistic locking prevents the conflict from ever happening; optimistic locking lets it happen and detects it at write time.

-- PESSIMISTIC: acquire the lock up front (see Section 4) — nobody else
-- can even read-for-update this row until this transaction ends.
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
UPDATE products SET stock = stock - 2 WHERE id = 42;

-- OPTIMISTIC: no lock at all — read the current version, write only if
-- the version hasn't changed since. If another transaction committed
-- first, this UPDATE affects 0 rows and the application must detect it.
SELECT stock, version FROM products WHERE id = 42;   -- version = 7
UPDATE products
    SET stock = stock - 2, version = version + 1
    WHERE id = 42 AND version = 7;
-- affected rows == 0 → someone else updated first → application retries
--   or surfaces a conflict to the caller (JPA does exactly this via @Version,
--   throwing OptimisticLockException — see JPA & Hibernate)

Best Practices and Common Pitfalls

Interview Questions