Database Transactions and Concurrency Control

Key terminology regarding database transaction properties, isolation levels, common anomalies, and concurrency control mechanisms.

Transaction

A unit of work that either completes entirely or has no effect at all.

ACID

An acronym for the $4$ properties guaranteed by databases: atomicity, consistency, isolation, and durability.

Atomicity

The property ensuring that partial completion of a transaction is impossible.

Durability

The property ensuring that once a transaction commits, its effects survive a crash, often achieved by using a write-ahead log.

Isolation

A property requiring that concurrent transactions produce results as if they had run one after another; often implemented with various levels of strictness.

Serializable

Full isolation where concurrent transactions produce the same result as if they had run $1$ after another.

Read Committed

The default isolation level for PostgreSQL; it prevents dirty reads but permits non-repeatable and phantom reads.

Repeatable Read

The default isolation level for MySQL's InnoDB; it prevents dirty and non-repeatable reads.

Dirty read

An anomaly that occurs when a transaction reads data written by another transaction that has not yet committed.

Non-repeatable read

An anomaly occurring when a transaction reads the same row $2$ times and gets different values because another transaction committed a change in between.

Phantom read

An anomaly occurring when a transaction re-runs a query returning a set of rows and finds that new rows have appeared.

Next-key locking

A technique used by InnoDB to prevent phantom reads in most cases.

Deadlock

A situation where $2$ transactions each hold a lock that the other needs.

Wait-for graph

A structure databases use to detect deadlocks by searching for cycles.

Victivm

The transaction chosen by the database to be aborted in order to resolve a deadlock.

Optimistic concurrency control

An approach that allows transactions to proceed without initial locking, checking for conflicts only at commit time and aborting if any reads have changed.