Stage 3 · Build
Relational Databases (PostgreSQL)
Transactions & ACID
BEGIN/COMMIT/ROLLBACK, isolation levels, phantom reads, and deadlocks.
ACID Properties
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties guarantee that database transactions are processed reliably, even in the face of errors, power failures, and concurrency.
| Property | Guarantee | Without It |
|---|---|---|
| Atomicity | All or nothing — partial writes never persist | Corrupted data after crash |
| Consistency | Constraints always hold after transaction | Invalid states like negative balances |
| Isolation | Concurrent transactions don't interfere | Dirty reads, lost updates |
| Durability | Committed data survives crashes | Lost transactions after power failure |
Unlike MySQL (REPEATABLE READ) and SQL Server (READ COMMITTED but with different locking), PostgreSQL uses READ COMMITTED by default. This means each statement within a transaction sees only data committed before that statement began.
Transaction Control
BEGIN;
-- Transfer $500 from Alice to Bob
UPDATE accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 500 WHERE name = 'Bob';
-- Verify totals haven't changed
SELECT SUM(balance) FROM accounts;
COMMIT; -- or ROLLBACK; if something went wrongThe BEGIN...COMMIT pair ensures both UPDATEs succeed or neither does. If the application crashes between BEGIN and COMMIT, PostgreSQL automatically rolls back the transaction.
-- PostgreSQL allows DDL inside transactions
BEGIN;
CREATE TABLE new_orders (
id SERIAL PRIMARY KEY,
total DECIMAL(10, 2)
);
ALTER TABLE orders RENAME TO old_orders;
ALTER TABLE new_orders RENAME TO orders;
-- If anything fails, everything rolls back
COMMIT;This is unique to PostgreSQL. MySQL implicitly commits on DDL statements, making schema changes risky. In PostgreSQL, you can test schema changes and roll back if they cause issues.
Isolation Levels
Isolation levels control how transactions see each other's uncommitted changes. Higher isolation prevents more anomalies but reduces concurrency.
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ COMMITTED | Prevented | Allowed | Allowed |
| REPEATABLE READ | Prevented | Prevented | Prevented (via MVCC) |
| SERIALIZABLE | Prevented | Prevented | Prevented |
-- Set isolation level for a transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE name = 'Alice'; -- reads 1000
-- Another transaction updates Alice's balance to 800 and commits
SELECT balance FROM accounts WHERE name = 'Alice'; -- still reads 1000
COMMIT;
-- SERIALIZABLE adds serialization conflict detection
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- If two transactions would produce an inconsistent result,
-- one is rolled back with a serialization_failure error
COMMIT;REPEATABLE READ in PostgreSQL uses MVCC snapshots — each transaction sees a consistent snapshot of the database at the time it started. SERIALIZABLE adds predicate locking to detect dangerous read/write patterns.
Deadlocks
A deadlock occurs when two transactions wait for each other's locks. PostgreSQL automatically detects deadlocks and rolls back one transaction, allowing the other to proceed.
-- Transaction 1 -- Transaction 2
BEGIN; BEGIN;
UPDATE accounts UPDATE accounts
SET balance = balance - 100 SET balance = balance - 200
WHERE name = 'Alice'; WHERE name = 'Bob';
-- holds lock on Alice -- holds lock on Bob
UPDATE accounts UPDATE accounts
SET balance = balance - 100 SET balance = balance - 200
WHERE name = 'Bob'; WHERE name = 'Alice';
-- waits for Bob's lock -- waits for Alice's lock
-- DEADLOCK DETECTED — one rolls backPostgreSQL detects deadlocks within milliseconds by checking the wait-for graph. The transaction that is cheaper to roll back is chosen as the victim. Your application must retry the rolled-back transaction.
Deadlocks are normal in concurrent systems. The application must catch the deadlock error (SQLSTATE 40P01) and retry the entire transaction. Failing to retry means the transaction was partially applied and rolled back.
Savepoints
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 100.00);
SAVEPOINT order_created;
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (currval('orders_id_seq'), 1, 2);
-- If this fails, rollback to savepoint instead of entire transaction
RELEASE SAVEPOINT order_created;
COMMIT;Savepoints let you undo part of a transaction without starting over. They are useful in batch operations where some inserts might fail but others should succeed.
Optimistic Concurrency
Optimistic concurrency avoids locks by checking that data has not changed since you read it. This is essential for web applications where transactions are long-lived (user interaction time).
-- Add version column
ALTER TABLE products ADD COLUMN version INTEGER DEFAULT 1;
-- Read the current version
SELECT id, name, price, version FROM products WHERE id = 42;
-- Returns: id=42, name='Widget', price=9.99, version=3
-- Update only if version matches
UPDATE products
SET price = 12.99, version = version + 1
WHERE id = 42 AND version = 3;
-- Check if update succeeded
-- If rows_affected = 0, someone else modified the row
-- Application reads the new version and retriesThis pattern avoids pessimistic locking entirely. If a conflict is detected (0 rows updated), the application re-reads the data and retries. Most web applications use this pattern via ORMs like ActiveRecord or SQLAlchemy.
When you must read and immediately update (like inventory decrement), use SELECT ... FOR UPDATE to acquire an exclusive lock. This blocks concurrent readers but guarantees no conflicts.
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.