Transactions and locking

In plain English

A transaction is a group of statements that are treated as one unit: either they all succeed or none of them do. You start one with BEGIN, save the work with COMMIT, or undo it all with ROLLBACK. ACID is the set of guarantees a database gives you: Atomicity (all-or-nothing), Consistency (a valid state), Isolation (transactions do not interfere), and Durability (committed work survives). When one transaction changes a row, the database can lock that row so other transactions wait, which prevents conflicting writes. A deadlock happens when two transactions each wait on a row the other holds; keeping transactions short makes deadlocks much less likely.

Step by step

1. 1Start a transaction with BEGIN.
2. 2Run the related statements, like moving money from one account to another.
3. 3Check the result, then COMMIT to save it or ROLLBACK to undo it.
4. 4Keep the transaction short so row locks are released quickly.

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  balance NUMERIC(12, 2) NOT NULL DEFAULT 0
);

INSERT INTO accounts (balance) VALUES (1000.00), (500.00);

BEGIN;

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

COMMIT;

BEGIN;

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

ROLLBACK;

Common mistakes

• Forgetting to COMMIT, which leaves a transaction open and its row locks held.
• Assuming a failed statement automatically rolls back everything in every database.
• Wrapping unrelated, slow work in one big transaction and blocking other users.
• Ignoring deadlock errors instead of retrying the transaction.