Transactions & ACID

◆Databases◆Intermediate to Advanced🎯Free Tools

Introduction

A database transaction is a logical unit of work that groups multiple operations into a single atomic unit. Either all operations succeed (commit) or none do (rollback). Transactions are the foundation of data integrity in relational databases.

ACID Properties

Property	Description	Example
Atomicity	All operations succeed or all fail	Transfer debit + credit = both or neither
Consistency	Database moves from one valid state to another	Foreign keys always reference existing rows
Isolation	Concurrent transactions don't interfere	Two transfers on same account don't double-spend
Durability	Committed data survives crashes	WAL ensures crash recovery

acid.sql

SQL

1	-- Basic transaction: transfer money between accounts
2	BEGIN;
3
4	UPDATE accounts SET balance = balance - 100 WHERE id = 1;
5	UPDATE accounts SET balance = balance + 100 WHERE id = 2;
6
7	-- Verify the transfer is valid
8	DO $$
9	BEGIN
10	IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
11	RAISE EXCEPTION 'Insufficient funds';
12	END IF;
13	END $$;
14
15	COMMIT;
16	-- If any error occurs, PostgreSQL auto-rolls back the transaction
17
18	-- Explicit rollback on error
19	BEGIN;
20	UPDATE accounts SET balance = balance - 100 WHERE id = 1;
21	-- Oops, wrong account
22	ROLLBACK; -- Undo everything in this transaction

Isolation Levels

Level	Dirty Read	Non-Repeatable	Phantom	Performance
Read Uncommitted	Yes	Yes	Yes	Fastest
Read Committed	No	Yes	Yes	Fast (PostgreSQL default)
Repeatable Read	No	No	Yes	Moderate (MySQL default)
Serializable	No	No	No	Slowest (full isolation)

isolation.sql

SQL

1	-- Set isolation level for a transaction
2	BEGIN ISOLATION LEVEL READ COMMITTED;
3	-- Default in PostgreSQL; most common choice
4
5	BEGIN ISOLATION LEVEL REPEATABLE READ;
6	-- Snapshot at transaction start; consistent reads
7
8	BEGIN ISOLATION LEVEL SERIALIZABLE;
9	-- Full isolation; detects conflicts and aborts
10	-- Use for financial operations or when consistency is critical
11
12	-- PostgreSQL: detect serialization failures
13	BEGIN ISOLATION LEVEL SERIALIZABLE;
14	UPDATE accounts SET balance = balance - 100 WHERE id = 1;
15	UPDATE accounts SET balance = balance + 100 WHERE id = 2;
16	COMMIT;
17	-- If concurrent transaction modified same rows:
18	-- ERROR: could not serialize access due to concurrent update
19	-- Application must retry the transaction

✓

best practice

Use READ COMMITTED for most workloads. Only escalate to SERIALIZABLE for critical sections where consistency is more important than throughput.

Deadlocks

deadlocks.sql

SQL

1	-- Deadlock scenario:
2	-- Transaction A: locks row 1, then tries to lock row 2
3	-- Transaction B: locks row 2, then tries to lock row 1
4	-- Both wait forever → deadlock detected, one is aborted
5
6	-- Prevention: always lock rows in the same order
7	-- Transaction A: UPDATE accounts WHERE id = 1 (then 2)
8	-- Transaction B: UPDATE accounts WHERE id = 1 (then 2)
9
10	-- PostgreSQL deadlock detection
11	-- (deadlock_timeout = 1s by default)
12
13	-- View deadlocks in logs
14	SHOW log_lock_waits; -- off by default
15	SET log_lock_waits = on;
16	SET deadlock_timeout = '500ms';
17
18	-- Monitor locks
19	SELECT
20	blocked.pid AS blocked_pid,
21	blocked.query AS blocked_query,
22	blocking.pid AS blocking_pid,
23	blocking.query AS blocking_query
24	FROM pg_stat_activity blocked
25	JOIN pg_locks bl ON bl.pid = blocked.pid
26	JOIN pg_locks kl ON kl.locktype = bl.locktype
27	AND kl.relation = bl.relation AND kl.pid != bl.pid
28	JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
29	WHERE NOT bl.granted;

Savepoints

savepoints.sql

SQL

1	-- Savepoints: partial rollback within a transaction
2	BEGIN;
3
4	INSERT INTO orders (user_id, total) VALUES (1, 99.99);
5
6	SAVEPOINT order_created;
7
8	INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 42, 2);
9	-- This might fail (foreign key violation)
10	-- We can rollback to savepoint without losing the order
11
12	ROLLBACK TO SAVEPOINT order_created;
13
14	-- The order is still there, but the failed item was undone
15	INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 55, 1);
16	COMMIT;

$Blueprint — Engineering Documentation·Section ID: DB-TX-01·Revision: 1.0

X GitHub YouTube Instagram Facebook Discord Telegram Buy me a coffee Sponsor