SQL from zero · Chapter 30 of 37
All or nothing
By the end of this chapter you can make several changes happen together or not at all, undo part of a change, lock a row while you edit it, and read data that holds still. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Safe and correct
- 4 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Anya is handing Chai Club over to Rohan. That is three changes: the circle's owner becomes Rohan, Rohan's role becomes owner, and Anya's role becomes admin. Meera asks the obvious question: "What if it stops halfway?"
So far every statement you run is saved the moment it finishes. If the second UPDATE fails, or the connection drops after the first, the first one stays: Chai Club is left with no owner, or two, and someone repairs it by hand. And while the changes are running, everyone else sees the half-done state.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
transfer-ownership.sql: hand Chai Club to rohan
Run setup.sql first. In one transaction, hand Chai Club from anya to rohan: Chai Club's owner_id becomes rohan's id, rohan's role in Chai Club becomes owner, and anya's role in Chai Club becomes admin. Change nothing else.
The file starts with BEGIN and ends with COMMIT, so another session sees all three changes at once. A circle may have only one owner at any moment, even inside a transaction (the unique index in setup.sql), so the order of the changes matters.
- Task 2
dry-run.sql: add yash, then dry-run a delete
Run setup.sql first. In one transaction: add yash to Chai Club with the role member (this change stays). Then set a savepoint, delete the Cricket Nerds circle (its memberships go with it), and count the rows left in circle_members, in every circle. Roll back to the savepoint, so Cricket Nerds and its members come back, and commit.
The count is the last result the file returns, in one column: members_left. Afterwards yash is in Chai Club and nothing else has changed.
- Task 3
lock-circle.sql: lock the Chai Club row
Run setup.sql first. Start a transaction and select the name of the Chai Club circle so that its row is locked: until this transaction ends, no other session can change or delete it. Every other circle must stay free to change, so lock only that row, not the table.
Return one column, name, with the one Chai Club row. Leave the transaction open (no COMMIT): the Check tries to change both circles from another session, then ends it.
- Task 4 · interview question
Interview: snapshot.sql, reads that keep agreeing
Rohan's report reads the posts table several times, and every read must see the same data, even if someone posts in the middle. Start a transaction at an isolation level that keeps one snapshot for the whole transaction, then count the posts that are not deleted.
Return one column: posts. Leave the transaction open (no COMMIT): the Check adds a post from another session, then counts again inside your transaction.
What you will learn
- BEGIN, COMMIT and ROLLBACK: a transaction, saved or thrown away as a whole.
- SAVEPOINT: undo only the last part of a transaction.
- FOR UPDATE: lock the rows you are about to change.
- Isolation levels: whether your transaction sees what others commit while it runs.
More in Safe and correct
Build "All or nothing" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.