SQL Transactions and ACID Properties Explained

A transaction is a group of SQL operations that succeed or fail as a unit. Without transactions, a crash halfway through a multi-step update leaves the database in a broken intermediate state. ACID properties define the guarantees your database makes so you can build reliable software on top of it.

Consider a bank transfer: you debit one account and credit another. If the process crashes after the debit but before the credit, money disappears. This is not a theoretical concern — servers crash, network connections drop, and applications have bugs. Transactions exist to ensure that either both operations happen, or neither does.

The basic transaction syntax

Every relational database supports the same core transaction commands: BEGIN, COMMIT, and ROLLBACK.

-- Transfer $100 from account 1 to account 2

BEGIN;  -- start the transaction

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

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

COMMIT;  -- make both changes permanent

-- If anything goes wrong between BEGIN and COMMIT, use:
ROLLBACK;  -- undo everything since BEGIN

Inside a transaction, your queries see the database as if they were the only user — the changes from your UPDATE statements are visible to subsequent queries in the same transaction, but not yet visible to other concurrent users. When you COMMIT, the changes become durable and visible to everyone. When you ROLLBACK, all changes are discarded as if the transaction never started.

Most application frameworks and ORMs handle transaction management through a context manager or a unit-of-work pattern:

# Python with psycopg2 (PostgreSQL)
import psycopg2

conn = psycopg2.connect(DSN)
try:
    with conn:  # context manager: commits on exit, rolls back on exception
        with conn.cursor() as cur:
            cur.execute(
                "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                (100, 1)
            )
            cur.execute(
                "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                (100, 2)
            )
    # if no exception: committed automatically
except Exception:
    # if exception: rolled back automatically
    raise

ACID properties

Atomicity: A transaction is an atomic unit. All operations in the transaction either all succeed (commit) or all fail (rollback). There is no partial success. If your application crashes after the first UPDATE but before the second, the database rolls back the first UPDATE as part of crash recovery.

Consistency: A transaction brings the database from one valid state to another valid state. "Valid" means all constraints are satisfied: foreign keys, unique indexes, check constraints, not-null constraints. If a transaction would violate any constraint, the entire transaction is rejected.

-- Consistency in action: foreign key enforcement
BEGIN;

INSERT INTO orders (user_id, amount) VALUES (9999, 50.00);
-- user_id 9999 does not exist

COMMIT;
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- Transaction is rolled back. No row is inserted.

Isolation: Concurrent transactions do not interfere with each other. Each transaction executes as if it were the only one running. In practice, full isolation has a throughput cost, so databases offer configurable isolation levels (covered below).

Durability: Once a transaction is committed, its changes are permanent, even if the server crashes immediately afterward. The database achieves this through write-ahead logging (WAL): changes are written to a durable log before being applied to the main data files. On crash, the database replays the WAL to recover committed transactions.

Isolation levels

Full isolation requires locking that reduces throughput. Most databases let you choose a weaker isolation level to improve performance at the cost of allowing specific anomalies.

The four standard isolation levels, from weakest to strongest:

• READ UNCOMMITTED: A transaction can read changes from other transactions that have not yet committed (dirty reads). Almost never used in practice because you can read data that might be rolled back.
• READ COMMITTED: A transaction can only read committed data. No dirty reads. However, if you run the same query twice in the same transaction, you might get different results if another transaction committed a change in between (non-repeatable read). This is the default in PostgreSQL and Oracle.
• REPEATABLE READ: Rows you have read in the transaction cannot be changed by other transactions until you commit. Prevents dirty reads and non-repeatable reads. MySQL InnoDB uses this as its default.
• SERIALIZABLE: The strongest level. Transactions behave as if they were run serially, one at a time, even though they run concurrently. Prevents all anomalies but can cause significant contention under high load.

-- Setting the isolation level in PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Or for the session:
SET default_transaction_isolation TO 'read committed';

-- The anomalies each level prevents:
--
-- Isolation Level   | Dirty Read | Non-Repeatable Read | Phantom Read
-- -----------------+------------+---------------------+-------------
-- Read Uncommitted | Possible   | Possible            | Possible
-- Read Committed   | Prevented  | Possible            | Possible
-- Repeatable Read  | Prevented  | Prevented           | Possible *
-- Serializable     | Prevented  | Prevented           | Prevented
--
-- * PostgreSQL's Repeatable Read prevents phantom reads too (implementation detail)

Savepoints

A savepoint marks a point within a transaction you can roll back to without aborting the entire transaction. This is useful when you want to attempt an optional operation but continue the transaction if it fails.

BEGIN;

INSERT INTO orders (user_id, amount) VALUES (42, 50.00);

SAVEPOINT before_discount;

-- Attempt to apply a promotional discount
INSERT INTO discounts (order_id, code) VALUES (currval('orders_id_seq'), 'SAVE10');
-- If this fails (e.g., invalid discount code):
ROLLBACK TO SAVEPOINT before_discount;
-- The order INSERT is still intact; only the discount attempt is undone

RELEASE SAVEPOINT before_discount;

COMMIT;  -- commits the order, without the failed discount

Common mistakes

Transactions that are too long. A long-running transaction holds locks and prevents other transactions from making progress. Keep transactions as short as possible: do your computation before opening the transaction, do the database writes inside it, and commit immediately.

Missing rollback on error. If your application code throws an exception inside a transaction and you do not roll back, the connection returns to the pool with an open transaction. Subsequent queries from other users using that connection will run inside your abandoned transaction. Always wrap transaction logic in try/finally or use a context manager that handles rollback automatically.

Assuming autocommit where there is none. Some drivers run in autocommit mode by default (each statement is its own transaction). Others require an explicit COMMIT. Read your driver documentation to know which mode you are in, and set it explicitly to avoid surprises.