ACID transactions
BEGIN, COMMIT, ROLLBACK and SAVEPOINT, autocommit, the four ACID properties (atomicity, consistency, isolation, durability) and the log that upholds them.
- 24 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
A few cards from the deck
Transaction: what is it?
Show the answer
A sequence of operations on the database handled as one indivisible whole: it is committed entirely with COMMIT, or cancelled entirely with ROLLBACK.
A bank transfer is a debit plus a credit. Without a transaction, a crash between the two makes money vanish; with one, both operations go through or neither does.
The balances start at 500 and 200. What does the last SELECT return?
```sql
BEGIN;
UPDATE compte SET solde = solde - 100 WHERE id = 1;
ROLLBACK;
SELECT solde FROM compte WHERE id = 1;`500400100NULL
Show the answer
500ROLLBACK undoes everything the transaction did since BEGIN: the debit is cancelled and the row gets its starting value back.
Outside an explicit BEGIN, most databases commit each statement on its own: this is … mode.
Show the answer
Outside an explicit BEGIN, most databases commit each statement on its own: this is autocommit mode.
Each UPDATE is then its own transaction. Two successive UPDATE statements are therefore not atomic together: wrap them in BEGIN and COMMIT.
ACID: what is it?
Show the answer
The four guarantees of a transaction: atomicity, consistency, isolation, durability.
The four letters give the four properties in order: Atomicity, Consistency, Isolation, Durability. Each one rules out a different kind of incident.
After a crash, 100 euros left account A without reaching account B. The two UPDATE statements of the transfer run one after the other in autocommit mode. Which fix?
- Add a SAVEPOINT between the two UPDATE statements
- Wrap them in BEGIN and COMMIT so they form a single transaction
- Run the transaction at the SERIALIZABLE isolation level
- Add a COMMIT after each UPDATE to make them durable
Show the answer
Wrap them in BEGIN and COMMIT so they form a single transaction
The flaw is atomicity: without boundaries each UPDATE commits alone. A SAVEPOINT does not protect against a crash, and isolation handles simultaneous access, not outages.