SQL Transactions: Concurrency and Isolation

Imagine you are transferring 100 from your savings account to your checking account. The database has to do two things:

1. Subtract 100 from Savings.
2. Add 100 to Checking.

The Nightmare: What if the power goes out after Step 1? The 100 vanishes into thin air. You have lost the customer's money, and your database is in a "Corrupt State." To prevent this, we use Transactions. This 1,500+ word guide covers the "Fortress Tier" of database engineering.

1. ACID: The Four Pillars of Reliability

A transaction is a single "Atomic" unit of work. It either succeeds 100% or fails 100%, with zero in-between. To achieve this in physical silicon, the database follows the ACID protocol.

1. Atomicity: The "All or Nothing" Fuse

If one query in a 10-statement transaction fails, the entire transaction is Rolled Back.

• The Mirror: The engine keeps a "Transaction Log" of every change. If a failure occurs, it reads the log in reverse and un-does every write operation until the data is exactly as it was before the transaction started.

2. Consistency: The "Rule of Law"

The database moves from one valid state to another. Constraints (like NOT NULL, UNIQUE, or FOREIGN KEY) are enforced.

• The Physics: Consistency is not just about data types; it's about business integrity. If a transaction would result in a negative bank balance (and you have a constraint preventing that), the hardware triggers an immediate abort.

3. Isolation: The "Solitude" Mirror

Even if 1,000 people are using the database at the same time, their transactions shouldn't "Leak" into each other.

• The Challenge: True isolation is computationally expensive. As we will see, databases use "Isolation Levels" to balance safety with performance.

4. Durability: The "Permanent Record"

Once the database says "Success," the data is physically saved. If the server loses power 1 millisecond later, the data must still be there when it reboots.

• The Physics: As we will explore in the WAL section, this is achieved through sequential disk appends rather than random file writes.

2. Hardware-Mirror: MVCC vs. 2PL

How does a database handle multiple people editing the same row at the same time? There are two primary schools of thought: Locking vs. Versioning.

The 2PL (Two-Phase Locking) Physics

Older databases (and some modern ones like SQL Server in certain modes) use Two-Phase Locking.

1. Phase 1: Expansion: The transaction acquires all the locks it needs (Shared locks for reading, Exclusive locks for writing).
2. Phase 2: Shrinking: The transaction releases all locks at once upon COMMIT or ROLLBACK.

• The Mirror Limit: In a 2PL system, "Readers block Writers" and "Writers block Readers." If you are running a long report (Read), nobody can update those rows (Write). This causes massive performance bottlenecks in high-traffic mirrors.

The MVCC (Multi-Version Concurrency Control) Mirror

Modern engines (Postgres, Oracle, MySQL/InnoDB) use MVCC.

• The Concept: Instead of locking a row, the database creates a New Version of the row.
• The Snapshot: When you start a transaction, the engine gives you a "Snapshot" of the database at that exact moment. You see the world as it was when you started.
• The Win: In MVCC, "Readers NEVER block Writers" and "Writers NEVER block Readers." You can run a 24-hour report while millions of updates happen in the background without any performance impact.

3. The WAL Internals: Write-Ahead Logging

How does a database guarantee Durability? If it had to write to the massive data files on the SSD every time you hit "Save," it would be too slow.

The Physics of the WAL

1. The Log First: When you change a row, the database writes the change to a lightweight, sequential file called the WAL (Write-Ahead Log). This is very fast because the disk head doesn't have to move (Sequential I/O).
2. The Success: Once the change is safely in the WAL (flushed to the silicon), the database tells you "Success!"
3. The Checkpoint: Later, a background process (the Checkpointer) carries the changes from the WAL to the actual data tables.
4. Crash Recovery: If the power fails, the database re-reads the WAL upon reboot and "Replays" any changes that haven't made it to the main tables yet. This is the physical secret of ACID.

4. The 4 Isolation Levels: The Safety/Speed Trade-off

This is the most complex part of SQL. You must choose how much "Isolation" you want. The ANSI standard defines four levels to prevent three specific "Phenomena."

The Phenomena

• Dirty Read: Seeing data that was written by another transaction but not yet committed.
• Non-Repeatable Read: Reading a row, then reading it again and seeing it has changed.
• Phantom Read: Reading a set of rows, then reading again and seeing new rows have appeared.

The Levels

Architect's Standard:

• In 2026, Read Committed is the industry standard for 95% of tasks.
• For financial transfers or inventory logic (the "Double-Spend" problem), you must use Serializable or explicit locking.

5. Case Study: The "Double-Spend" Disaster

A crypto exchange was using Read Committed for their withdrawals.

• The Scenario: A user has 100. They send two withdrawal requests for 100 at the exact same millisecond.
• The Failure:
  1. Transaction A checks balance: "It's 100. OK."
  2. Transaction B checks balance: "It's 100. OK."
  3. Transaction A subtracts 100 and sends money.
  4. Transaction B subtracts 100 and sends money.
• The Result: The user sent 200 even though they only had 100. The exchange lost millions in a "Race Condition."

The Architect's Fix

1. Pessimistic Locking: Use SELECT ... FOR UPDATE. This tells the database: "Lock this row. Nobody else can even READ it until I'm finished." This forces Transaction B to wait until A is done.
2. Serializable Isolation: Let the database detect the conflict. If Transaction B tries to commit, the database will say "Serialization Failure" and force B to retry.

6. Deadlock Physics: The Infinite Wait

A Deadlock happens when Transaction A is waiting for a lock held by Transaction B, and Transaction B is waiting for a lock held by Transaction A. They are stuck in a logical loop.

The Graph Detection Mirror

Modern databases maintain a Wait-for Graph.

• Nodes: Current Transactions.
• Edges: "A is waiting for a lock held by B."
• The Detection: The engine runs a cycle-detection algorithm on this graph. If it finds a loop (A -> B -> A), it has found a deadlock.
• The Sacrifice: The engine picks one transaction (usually the one that did the least work) and "Kills" it (ABORT), effectively breaking the cycle and allowing the other to survive.

How to Prevent Deadlocks

• Consistent Order: Always access your tables in the Same Order (e.g., always update users then orders).
• Short Transactions: Never put "Human Input" or "External API Calls" inside a transaction. Keep it strictly database-to-database to minimize lock duration.

7. The Savepoint Mirror: Partial Rollbacks

In complex batch processing, you don't always want to roll back the entire transaction if one small step fails.

BEGIN;
INSERT INTO accounts ...;
SAVEPOINT my_check;
UPDATE inventory ...; -- This fails!
ROLLBACK TO SAVEPOINT my_check; -- Only undo the inventory update
COMMIT; -- The account insertion still happens!

The Physics: Savepoints create "Snapshots" of the transaction's internal state. They allow for complex error-handling logic without the heavy performance cost of restarting a massive transaction from scratch.

8. Summary: The Transaction Checklist

1. Keep it Short: Locks are expensive. The longer a transaction lives, the more it slows down the entire system.
2. Prefer MVCC: Understand that Postgres/Oracle readers don't block writers. If you are on SQL Server, ensure READ_COMMITTED_SNAPSHOT is enabled to match this performance.
3. Isolation Awareness: Don't use REPEATABLE READ unless you specifically need to avoid non-repeatable reads; it increases the risk of serialization failures.
4. Audit the WAL: Monitor your WAL write latency. High WAL latency usually indicates a slow disk or a bottleneck in the disk I/O mirror.
5. Clean Up: Always ensure your application code has a finally block that calls ROLLBACK if the transaction wasn't committed. "Zombies" (Idle in Transaction) are the silent killers of enterprise databases.

Transactions are the "Conscience" of your application. By mastering the WAL, the nuances of MVCC, and the physical reality of deadlocks, you gain the power to build systems that are fundamentally Unbreakable. You graduate from "Gathering data" to "Architecting Integrity."

Phase 17: Transaction Mastery Checklist

• Audit Lock Wait Times: Monitor system views (like pg_stat_activity) for transactions waiting on locks.
• Implement Sovereign Savepoints: Use savepoints in long batch processes to handle partial failures.
• Configure WAL Geometry: Ensure your WAL segments are sized correctly for your write throughput to minimize checkpoint spikes.
• Verify Isolation Levels: Ensure financial logic uses SERIALIZABLE or explicit row-locking (FOR UPDATE).

Read next: SQL ACID Properties: The Mechanics of Truth →

Frequently Asked Questions

Q: What are the four ACID properties and why do they matter?

Atomicity ensures a transaction is all-or-nothing — either every operation succeeds or none are applied, preventing partial updates. Consistency ensures a transaction moves the database from one valid state to another, never violating constraints. Isolation ensures concurrent transactions do not see each other's intermediate states, as if they executed serially. Durability ensures committed transactions survive system crashes — the data is permanently saved. Together, these properties make databases reliable for financial and business-critical operations where partial or inconsistent updates are unacceptable.

Q: What is the difference between READ COMMITTED and REPEATABLE READ isolation levels?

READ COMMITTED (the PostgreSQL default) allows a transaction to see committed changes made by other transactions during its execution — a query run twice in the same transaction may return different results if another transaction commits between them (non-repeatable read). REPEATABLE READ guarantees that rows read in a transaction will return the same results for the duration of the transaction, even if other transactions commit changes — it eliminates non-repeatable reads. SERIALIZABLE provides the strongest guarantee: transactions execute as if they ran one at a time, preventing phantom reads. Higher isolation levels prevent more anomalies but increase lock contention.

Q: What is a deadlock and how does the database handle it?

A deadlock occurs when two transactions each hold a lock that the other needs — both are waiting for the other to release, so neither can proceed. Databases detect deadlocks automatically by looking for cycles in the lock wait graph. When detected, the database selects one transaction as the victim (usually the one with the lowest cost to roll back) and aborts it with a deadlock error, allowing the other to proceed. The aborted transaction must be retried by the application. Prevent deadlocks by accessing tables and rows in a consistent order across transactions, keeping transactions short, and acquiring all needed locks upfront.

Part of the SQL Mastery Course — engineering the truth.