Transactions, ACID & Isolation

The basic shape

BEGIN;                                  -- start a transaction
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                                 -- atomically apply both
-- or:
ROLLBACK;                               -- discard everything since BEGIN

Either both updates happen, or neither. No "money disappeared mid-transfer" possible.

(MySQL/Postgres alias: START TRANSACTION;. SQL Server: BEGIN TRANSACTION;.)

ACID — the four guarantees

A transaction can be rolled back automatically by the engine on:

• a constraint violation
• a deadlock victim selection
• a serialization failure (under SERIALIZABLE)

Savepoints — partial rollback

BEGIN;
  INSERT INTO logs(...);
  SAVEPOINT before_risky;
    UPDATE big_table SET ...;
    -- error or "nope, undo just this part"
    ROLLBACK TO SAVEPOINT before_risky;
  -- the INSERT into logs is still alive
COMMIT;

Useful when one branch may fail and you don't want to abort the whole transaction.

The four classic isolation anomalies

Concurrent transactions can step on each other in four well-known ways:

-- Set for the next transaction
BEGIN;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  ...
COMMIT;

Postgres detail: REPEATABLE READ actually gives snapshot isolation — your whole transaction sees a consistent snapshot taken at BEGIN. SERIALIZABLE adds detection of read-write conflicts and may abort one transaction with a serialization failure (which the app retries).

Optimistic vs pessimistic concurrency

Pessimistic: lock the row immediately. Other writers wait.

BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- exclusive row lock
  -- compute new balance, then:
  UPDATE accounts SET balance = ... WHERE id = 1;
COMMIT;

Optimistic: don't lock; assume no one else will touch it; check at write time using a version column or the snapshot:

-- read with version
SELECT id, balance, version FROM accounts WHERE id = 1;
-- application computes new_balance and tries to commit:
UPDATE accounts
SET    balance = :new_balance, version = version + 1
WHERE  id = 1 AND version = :version_read;
-- 0 rows updated → another transaction won → retry

Pessimistic is simpler under high contention; optimistic scales better when conflicts are rare.

Locks — the things contention is made of

You almost never write LOCK directly. SELECT ... FOR UPDATE (X), SELECT ... FOR SHARE (S) and the implicit locks taken by UPDATE/DELETE cover 99% of needs.

Deadlocks

Two transactions wait on each other forever:

T1: lock row A, then wants row B
T2: lock row B, then wants row A

The engine detects this, picks a victim, and aborts it with a deadlock error. The app must catch and retry. Avoid by:

1. Always locking rows in the same order across all code paths (e.g. lower id first). 2. Keeping transactions short — open BEGIN, do the minimum, COMMIT. 3. Doing slow / external work (HTTP calls, file I/O) outside the transaction.

Common mistakes

• Calling external services inside a long transaction → row locks held forever, app stalls.
• Catching deadlock errors and silently swallowing them — you must retry.
• Using READ UNCOMMITTED in production "for speed" — almost never the actual bottleneck and reintroduces dirty reads.
• Assuming an UPDATE that depends on a previous SELECT is safe — it isn't, unless wrapped in a transaction with FOR UPDATE or done atomically (UPDATE … WHERE balance >= 100).
• DDL inside transactions: Postgres yes, MySQL no (DDL auto-commits). Plan migrations accordingly.

Quick recipe — money transfer the right way

BEGIN;
  -- Lock both rows in deterministic order (lower id first)
  SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  -- Optional: fail loudly if it would overdraw
  -- (better: a CHECK or a WHERE balance >= 100 + retry)
COMMIT;

That's it: ACID, isolation level appropriate to your engine's defaults, deterministic lock order, short transaction, no external I/O between BEGIN and COMMIT.