Transactions and ACID properties

Intermediate5 min read·eng-06-006

sqlinterview

Concept

A transaction is a unit of work that must be treated as a whole — either all operations succeed, or all are rolled back. Transactions enforce the ACID properties.

ACID:

A — Atomicity: All operations in the transaction succeed or all fail. No partial updates. If the server crashes mid-transaction, the rollback is automatic on restart.
C — Consistency: A transaction brings the database from one valid state to another valid state. Foreign key constraints, check constraints, and unique constraints are always upheld.
I — Isolation: Concurrent transactions behave as if they ran serially. Changes made by one transaction are not visible to others until committed (at default isolation levels). Controlled by isolation level.
D — Durability: Once committed, the data is permanently stored — even if the server crashes. Achieved via Write-Ahead Log (WAL) / binary log.

Atomicity example: Transfer $100 from Account A to Account B. Two statements: UPDATE accounts SET balance = balance - 100 WHERE id = A and UPDATE accounts SET balance = balance + 100 WHERE id = B. If the second fails, the first must be rolled back. Without a transaction: A loses $100, B gains nothing.

Savepoints: Named points within a transaction. You can rollback to a savepoint without rolling back the entire transaction. Useful for nested operations.

Auto-commit: By default, each SQL statement is its own transaction (auto-committed). BEGIN/START TRANSACTION disables auto-commit until COMMIT or ROLLBACK.

Code Example

sql

-- Basic transaction
START TRANSACTION;

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

-- Check if both updates look correct
SELECT id, balance FROM accounts WHERE id IN (1, 2);

COMMIT;   -- make permanent
-- or ROLLBACK;  -- undo everything since START TRANSACTION

-- Savepoint — rollback to a point without losing earlier work
START TRANSACTION;

INSERT INTO orders (id, customer_id, total) VALUES (101, 5, 250.00);

SAVEPOINT order_inserted;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 99, 2);
-- Assume this product doesn't exist → FK violation
ROLLBACK TO SAVEPOINT order_inserted;  -- only undoes the order_items insert

-- Order 101 is still in the transaction, order_items rolled back
COMMIT;

php

<?php
// Laravel — DB::transaction() (auto-commit on success, auto-rollback on exception)
DB::transaction(function () {
    $order = Order::create([...]);

    foreach (request('items') as $item) {
        $order->items()->create($item);
    }

    Payment::charge($order->total); // throws on failure → auto-rollback
});

// Manual transaction
DB::beginTransaction();
try {
    $from = Account::lockForUpdate()->findOrFail($request->from_id);
    $to   = Account::lockForUpdate()->findOrFail($request->to_id);

    if ($from->balance < $request->amount) {
        throw new \DomainException('Insufficient funds');
    }

    $from->decrement('balance', $request->amount);
    $to->increment('balance', $request->amount);

    DB::commit();
} catch (\Throwable $e) {
    DB::rollBack();
    throw $e;
}

// Nested transactions in Laravel use savepoints automatically
DB::transaction(function () {
    Order::create([...]);

    DB::transaction(function () { // creates a SAVEPOINT internally
        OrderItem::create([...]);
    }); // RELEASE SAVEPOINT on success, ROLLBACK TO SAVEPOINT on failure
});

PreviousNormalization — 1NF, 2NF, 3NF, BCNF with real examplesSQL Deep Dive NextIsolation levels — READ UNCOMMITTED through SERIALIZABLESQL Deep Dive