Transactions and concurrency

Vocabulary

English	Meaning
transaction/trænˈsækʃn/	A group of database operations treated as one unit with defined commit or rollback behaviour.
atomicity	The transaction property that its changes take effect together or are rolled back together.
consistency	The requirement that a correctly defined transaction preserves database constraints.
isolation/ˌaɪsəˈleɪʃn/	The degree to which concurrent transactions are protected from one another's intermediate effects.
durability	The guarantee that committed changes survive the failures covered by the database's recovery model.
serializability	Equivalence of a concurrent transaction schedule to some serial schedule under the chosen equivalence criterion.
two-phase locking	A locking protocol that permits no new lock acquisition after the first lock release.
isolation levels	Specified concurrency guarantees defining which transaction anomalies are prevented or allowed.

All or nothing

A transaction is all-or-nothing, and ACID names the four promises: atomicity (all or nothing), consistency (constraints keep true), isolation (interleaving looks serial), durability (commits survive the crash).

{exercise:uni-cs0805-acid}

Practice

The A in ACID stands for —

Difficulty 1/5 · 100 coins + a small bonus on first completion today

T1 reads 100, T2 reads 100, T1 writes 90, T2 writes 120: the 10-unit withdrawal vanished — the same race as unit 7.3, now with money.
R1(A) R2(A) W2(A) W1(A) ends 90 with T2's deposit lost; R1(A) W1(A) R2(A) W2(A) ends 110 — equal to serial T1-then-T2. Serializability means: equal to some serial order.

{exercise:uni-cs0805-schedule}

Practice

R1(A) R2(A) W2(A) W1(A) from 100 with -10/+20 ends —

Difficulty 1/5 · 100 coins + a small bonus on first completion today

Two-phase locking: reads and writes lock, locks held to commit — one transaction waits and the outcome equals a serial order. Deadlock is the price: the detector kills a victim, which retries.
Isolation levels name the anomalies you accept: read-uncommitted allows dirty reads (T2 sees T1's uncommitted 90 that later rolls back); read-committed removes them; repeatable-read stabilises re-reads; serializable removes phantoms. Keep transactions short — locks held through user input are deadlock bait.

{exercise:uni-cs0805-levels}

Practice

Reading another transaction uncommitted rollback requires fixing to at least —

Difficulty 1/5 · 100 coins + a small bonus on first completion today