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