Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Master database transactions and concurrency control. Explore ACID properties, ANSI isolation levels (Read Committed to Serializable), MVCC, and locks.
Quick answer
A SQL transaction is an atomic unit of work bounded by BEGIN and COMMIT (or ROLLBACK) that satisfies the ACID properties: Atomicity, Consistency, Isolation, and Durability. Concurrency control manages simultaneous transactions using Multi-Version Concurrency Control (MVCC) and isolation levels ranging from Read Committed to Serializable to prevent anomalies like Dirty Reads and Write Skew.
Atomicity guarantees all operations succeed or all are rolled back. Consistency guarantees database integrity constraints (foreign keys, checks) are satisfied. Isolation ensures concurrent transactions execute without interfering with each other. Durability guarantees committed changes survive crashes or power failures via Write-Ahead Logs (WAL).
What is Multi-Version Concurrency Control (MVCC)?
MVCC is the concurrency mechanism used by PostgreSQL, MySQL (InnoDB), and Oracle where readers never block writers, and writers never block readers. When a row is updated, the database creates a new version of the row rather than overwriting it in place; transactions view historical snapshots based on their start time.
What is Write Skew and why does Repeatable Read not prevent it?
Write Skew occurs when two concurrent transactions read overlapping data, make disjoint changes based on what they read, and violate an application invariant (e.g. two doctors concurrently on-call both see 2 active doctors and both check out, leaving 0 on-call). Only SERIALIZABLE isolation detects and prevents Write Skew.
What is the difference between pessimistic and optimistic locking?
Pessimistic locking acquires explicit database locks upfront (`SELECT ... FOR UPDATE`) to prevent other sessions from modifying rows. Optimistic locking does not acquire database locks; instead, it checks a version or timestamp column upon UPDATE (`WHERE version = 5`) and rolls back if another session modified the row.
In concurrent applications where hundreds of requests hit the database simultaneously, transactions are the safeguard preventing lost updates, double-spending, and phantom records. Understanding how isolation levels and locks function under the hood is non-negotiable for backend reliability.
Key takeaways
ACID guarantees predictable state transitions across distributed software.
Default isolation levels vary: PostgreSQL and SQL Server default to Read Committed; MySQL InnoDB defaults to Repeatable Read.
Prevent deadlocks by locking shared resources in globally consistent ascending order.
The 4 ANSI isolation levels and concurrency anomalies
Isolation levels represent a deliberate engineering trade-off between strict data correctness and query throughput:
Read Uncommitted: Can read uncommitted data from active transactions (Dirty Reads). Almost never acceptable.
Read Committed (Default in Postgres): Each query inside the transaction sees only data committed before that query began. Prevents Dirty Reads; allows Non-Repeatable Reads.
Repeatable Read (Default in MySQL): The transaction sees a frozen snapshot of the database taken when the transaction started. Prevents Dirty and Non-Repeatable Reads.
Serializable: Completely eliminates all concurrency anomalies (including Phantom Reads and Write Skew) by enforcing execution equivalent to serial, one-by-one ordering.
Pessimistic locking with SELECT ... FOR UPDATE
When computing critical balance transfers, standard SELECT queries do not acquire row locks, allowing race conditions. Use explicit row locking:
Thread-safe balance transfer with explicit row locks
BEGIN;-- Lock the source account row against concurrent updatesSELECT balanceFROM billing.accountsWHERE id = 101FOR UPDATE;-- Safely deduct funds knowing no concurrent transaction can modify row 101UPDATE billing.accountsSET balance = balance - 250.00WHERE id = 101;UPDATE billing.accountsSET balance = balance + 250.00WHERE id = 202;COMMIT;