Transactions, ACID & Concurrency

A transaction is a sequence of database operations treated as a single unit: either everything succeeds, or nothing does. Transactions are how databases protect your data from crashes, bugs, and concurrent users stepping on each other.

What is a Transaction?

If the server crashes between the two UPDATE statements, the partially-applied transfer would leave Alice's account debited but Bob's not credited — money destroyed. The transaction prevents this: either both updates apply, or neither does.

ACID Properties

Atomicity

The transaction is all-or-nothing. If any statement fails (or the server crashes), the entire transaction is rolled back as if it never happened.

Consistency

A transaction takes the database from one valid state to another valid state. All constraints — NOT NULL, UNIQUE, foreign keys, CHECK constraints — must hold after every commit.

Isolation

Concurrent transactions behave as if they run serially — they don't see each other's uncommitted changes.

Durability

Once a transaction commits, the changes survive crashes. The DB writes a Write-Ahead Log (WAL) to disk before applying changes. On restart, uncommitted WAL entries are discarded; committed ones are replayed.

Concurrency Problems

Without careful isolation, concurrent transactions cause anomalies:

Dirty Read

Reading uncommitted changes from another transaction. If that transaction rolls back, you read data that never existed.

Non-Repeatable Read

Reading the same row twice within a transaction and getting different values because another transaction committed a change in between.

Phantom Read

Running the same query twice and getting different sets of rows because another transaction inserted or deleted rows in between.

Isolation Levels

SQL defines four isolation levels, trading consistency for concurrency:

Level	Dirty Read	Non-Repeatable Read	Phantom Read	How
Read Uncommitted	Possible	Possible	Possible	No locks
Read Committed	No	Possible	Possible	Lock on write, release on read
Repeatable Read	No	No	Possible (MySQL: No)	Hold read locks until commit
Serializable	No	No	No	Full serial-equivalent execution

Most production databases default to Read Committed (PostgreSQL, SQL Server) or Repeatable Read (MySQL InnoDB).

MVCC — Multi-Version Concurrency Control

Modern databases (PostgreSQL, MySQL InnoDB, Oracle) implement isolation using MVCC rather than locking everything:

• Each row maintains multiple versions with timestamps
• Writers create new versions; old versions remain for existing readers
• Readers see a snapshot of the database as of their transaction start time
• No read-write conflicts — readers never block writers

Savepoints

You can create named checkpoints within a transaction and roll back to them partially:

Deadlocks in the Database

Two transactions can deadlock if each holds a lock the other needs:

The database detects this and kills one transaction (the "victim"). The victim's transaction is rolled back automatically — the application should retry.

Prevention: always lock rows in the same order across all transactions.

Python: transactions with psycopg2

Key Takeaways

• Transactions are all-or-nothing: COMMIT applies everything, ROLLBACK undoes everything
• ACID guarantees data integrity across failures and concurrent users
• Isolation levels trade performance for correctness: higher isolation = fewer anomalies = more locking
• MVCC lets readers and writers proceed concurrently without blocking each other
• Database deadlocks are detected automatically; design transactions to avoid them by locking in consistent order