SQL Transactions Quiz: ACID, Isolation Levels and Locking
Test yourself: 12 questions and three SQL tasks on ACID, isolation levels and their anomalies, InnoDB locks and deadlocks, MVCC snapshots and savepoints.
- Course: SQL study plan
- Module: Transactions and concurrency
- Kind: Checkpoint — cleared at 70%
- Reading time: 25 min
- Runtime: MySQL 8 syntax on SQLite 3.49
Checkpoint — Transactions and concurrency is the checkpoint that closes the Transactions and concurrency module: a graded quiz and whole-program exercises, passed at 70%.
Instructions
This checkpoint covers the whole module: transactions and the four ACID properties with the InnoDB machinery behind each, autocommit and implicit commits, the isolation levels and the dirty, non-repeatable and phantom reads they allow, lost updates and write skew, shared and exclusive row locks, locking reads, gap and next-key locks, lock waits and deadlocks, optimistic locking, MVCC with undo logs, read views and purge, and savepoints.
How it works. Twelve questions and three SQL tasks. You need 70% on the questions and every task accepted to clear the module. You can retake it as often as you like; your best score counts.
Before you start, make sure you can answer these from memory:
- What does each letter of ACID guarantee, and which InnoDB log provides atomicity and which durability?
- Why does
CREATE TABLEinside a MySQL transaction make a laterROLLBACKuseless? - Draw the isolation-level table: four levels, three anomalies.
- What is InnoDB's default isolation level, and PostgreSQL's?
- What do
SELECT … FOR UPDATE,FOR SHAREandSKIP LOCKEDdo? - What is a next-key lock, and which anomaly does it prevent?
- How does InnoDB break a deadlock, and what should the application do about it?
- When does a
REPEATABLE READtransaction create its read view, and why does a forgotten open transaction grow the undo history? - What happens to later savepoints when you roll back to an earlier one?
The three tasks: a transfer that also charges a fee, where the fee is undone with a savepoint before the commit; an optimistic-locking update in which a stale second writer changes nothing; and a lock-wait report that ranks the blocking transactions by how many sessions wait on them and for how long.
Common questions
Which anomalies does each SQL isolation level allow?
READ UNCOMMITTED allows dirty reads, non-repeatable reads and phantoms. READ COMMITTED prevents dirty reads but allows the other two. REPEATABLE READ also prevents non-repeatable reads and, by the standard, allows phantoms, although InnoDB blocks them for locking reads with next-key locks. SERIALIZABLE prevents all three.
How does optimistic locking detect a conflicting update?
Each row carries a version number. The application reads it, then updates with WHERE id = ? AND version = the value read, incrementing the version in the same statement. If another transaction changed the row first, the version no longer matches, the update changes zero rows, and the application reloads and retries.
What does MySQL do when it detects a deadlock?
InnoDB rolls back one transaction of the cycle, the one it judges smallest by rows inserted, updated or deleted, with error 1213, and the other transactions continue. The application should retry the rolled-back transaction, which is safe because none of its changes were kept.
Exercises
A transfer with a waived fee
In one transaction, move 300 from account 1 to account 2, logging both movements in ledger with reason 'transfer'. Then SAVEPOINT before_fee, charge account 1 a fee of 10 (balance and a 'fee' ledger row) — and, because the fee is waived for this customer, ROLLBACK TO SAVEPOINT before_fee and COMMIT. Show the balances and the ledger (both SELECTs are in the starter).
accounts: (1, 'Asha', 1000), (2, 'Ravi', 200). ledger (account_id, delta, reason) starts empty.
Output: id, owner, balance ordered by id; then account_id, delta, reason ordered by account_id, then delta.
id | owner | balance
1 | Asha | 700
2 | Ravi | 500
account_id | delta | reason
1 | -300 | transfer
2 | 300 | transferOptimistic locking with a version column
Two editors opened product 7 when its version was 3. Editor A saves first: set price to 549 and increment version, only if the version is still 3. Editor B saves second with the same stale version: set price to 499 and increment version, only if the version is still 3. Write both conditional updates in that order, then show the products (the SELECT is in the starter).
products (id, name, price, version): (7, 'Desk lamp', 599, 3), (8, 'Chair', 2499, 1).
Output columns: id, price, version, ordered by id.
id | price | version
7 | 549 | 4
8 | 2499 | 1Editor B's update changes no row — in an application, "0 rows affected" is the signal to reload and retry.
Who is blocking everyone?
lock_waits is a snapshot of current waits (waiting_txn waits for blocking_txn, for wait_secs so far). For each blocking transaction, report how many transactions wait on it, the longest wait, and whether it is a root blocker — not itself waiting for anyone ('yes'/'no'). Root blockers are the ones a DBA looks at first.
lock_waits:
| waiting_txn | blocking_txn | wait_secs |
|---|---|---|
| 12 | 10 | 41 |
| 13 | 10 | 8 |
| 14 | 12 | 30 |
| 15 | 11 | 5 |
| 16 | 12 | 2 |
Output columns: blocking_txn, waiters, longest_wait, is_root, ordered by waiters descending, then longest_wait descending, then blocking_txn.
blocking_txn | waiters | longest_wait | is_root
10 | 2 | 41 | yes
12 | 2 | 30 | no
11 | 1 | 5 | yesIn this module: Transactions and concurrency
← Savepoints — partial rollback inside a transaction · Gaps and islands — finding runs and holes in ordered data →