Two users buy the last item in stock at the same time. Your app charges a card, then the payment service fails. A report runs while someone is editing data. These are concurrency problems. Transactions solve them.
What is a Transaction?
A transaction is a group of SQL statements that execute as one unit. Either all succeed or none apply.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
If anything fails between BEGIN and COMMIT, call ROLLBACK to undo everything:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something goes wrong here
ROLLBACK;
-- both accounts unchanged
ACID Properties
Every transaction in PostgreSQL follows ACID:
- Atomic — all or nothing. No partial updates.
- Consistent — the database goes from one valid state to another.
- Isolated — transactions do not see each other’s uncommitted changes.
- Durable — committed changes survive crashes (written to disk via WAL).
Isolation Levels
PostgreSQL has four isolation levels. Each prevents different types of read anomalies:
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Not possible* | Possible | Possible |
| READ COMMITTED (default) | Not possible | Possible | Possible |
| REPEATABLE READ | Not possible | Not possible | Not possible** |
| SERIALIZABLE | Not possible | Not possible | Not possible |
*PostgreSQL treats READ UNCOMMITTED the same as READ COMMITTED — dirty reads are never allowed. **PostgreSQL’s REPEATABLE READ uses Snapshot Isolation, so it also prevents phantom reads (unlike the SQL standard where phantom reads can occur at REPEATABLE READ).
-- Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Another transaction commits a change here
SELECT balance FROM accounts WHERE id = 1;
-- Returns the same value as before — repeatable read
COMMIT;
Use READ COMMITTED (the default) for most operations. Use REPEATABLE READ for reports or calculations that read data multiple times. Use SERIALIZABLE for financial operations where strict correctness is required.
SELECT FOR UPDATE
Lock rows you intend to modify to prevent other transactions from changing them:
BEGIN;
-- Lock the row
SELECT * FROM products WHERE id = 5 FOR UPDATE;
-- Check stock
-- (No other transaction can change this row until we commit)
UPDATE products SET stock = stock - 1 WHERE id = 5 AND stock > 0;
COMMIT;
SELECT FOR UPDATE blocks other transactions that try to lock the same rows. They wait until your transaction commits or rolls back.
SELECT FOR SHARE allows other readers but blocks writers.
Savepoints
Savepoints let you roll back part of a transaction without losing everything:
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100) RETURNING id;
-- order_id = 42
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (42, 5, 1);
-- This fails (product doesn't exist)
ROLLBACK TO SAVEPOINT after_order;
-- order is still there, item insert is undone
-- Try with a different product
INSERT INTO order_items (order_id, product_id, quantity) VALUES (42, 3, 1);
COMMIT;
Deadlocks
A deadlock happens when two transactions each hold a lock the other needs:
Transaction A: Lock row 1 → waiting for row 2
Transaction B: Lock row 2 → waiting for row 1
PostgreSQL detects deadlocks automatically and kills one transaction (raising an error). Your application should catch the error and retry.
To prevent deadlocks: always acquire locks in the same order. If transaction A always locks user first, then order, transaction B should do the same.
-- Always lock in the same order: lower ID first
BEGIN;
SELECT * FROM accounts WHERE id = LEAST(1, 2) FOR UPDATE;
SELECT * FROM accounts WHERE id = GREATEST(1, 2) FOR UPDATE;
-- Now do the transfer
COMMIT;
Optimistic vs Pessimistic Locking
Pessimistic locking (SELECT FOR UPDATE) locks rows immediately. Safe but reduces throughput when contention is low.
Optimistic locking checks for conflicts only at commit time using a version column:
ALTER TABLE products ADD COLUMN version INTEGER DEFAULT 1;
-- Read the current version
SELECT id, stock, version FROM products WHERE id = 5;
-- Returns: id=5, stock=10, version=3
-- Update only if version hasn't changed
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 5 AND version = 3;
-- If another transaction updated it first, version=3 no longer matches
-- affected rows = 0 → retry in application code
Use optimistic locking when conflicts are rare. Use pessimistic locking when conflicts are common or the cost of retrying is high.
Python Example
import psycopg
conn = psycopg.connect("postgresql://localhost/mydb")
def transfer_funds(from_id: int, to_id: int, amount: float) -> bool:
with conn.transaction():
with conn.cursor() as cur:
# Lock both accounts in consistent order (lower ID first)
ids = sorted([from_id, to_id])
cur.execute(
"SELECT id, balance FROM accounts WHERE id = ANY(%s) FOR UPDATE ORDER BY id",
(ids,)
)
accounts = {row[0]: row[1] for row in cur.fetchall()}
if accounts[from_id] < amount:
return False # Insufficient funds — transaction rolls back
cur.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
(amount, from_id)
)
cur.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, to_id)
)
return True
# conn.transaction() auto-commits on success, auto-rollbacks on exception
TypeScript Example
import { Pool, PoolClient } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function transferFunds(fromId: number, toId: number, amount: number) {
const client: PoolClient = await pool.connect();
try {
await client.query("BEGIN");
// Lock both rows in consistent order
const [first, second] = fromId < toId ? [fromId, toId] : [toId, fromId];
await client.query(
"SELECT id, balance FROM accounts WHERE id IN ($1, $2) FOR UPDATE ORDER BY id",
[first, second]
);
const { rows } = await client.query(
"SELECT balance FROM accounts WHERE id = $1",
[fromId]
);
if (rows.length === 0) {
await client.query("ROLLBACK");
throw new Error("Account not found");
}
if (rows[0].balance < amount) {
await client.query("ROLLBACK");
throw new Error("Insufficient funds");
}
await client.query(
"UPDATE accounts SET balance = balance - $1 WHERE id = $2",
[amount, fromId]
);
await client.query(
"UPDATE accounts SET balance = balance + $1 WHERE id = $2",
[amount, toId]
);
await client.query("COMMIT");
} catch (err) {
await client.query("ROLLBACK");
throw err;
} finally {
client.release();
}
}
What’s Next?
You can now write safe concurrent database operations. Next: database migrations and schema design — how to evolve your database structure without breaking production.
Next: Database Tutorial #7: PostgreSQL Migrations and Schema Design