SQL / TRANSACTIONS AND CONCURRENCY
Transfers that never half-finish: atomicity in practice
Write a transfer that either fully lands or leaves no trace, by putting every write in one transaction and rolling back when a statement changes zero rows.
What you will learn
- Wrap the debit, the credit and the ledger row in a single BEGIN...COMMIT
- Guard a debit with WHERE balance >= amount instead of trusting an earlier SELECT
- Turn 'zero rows changed' into a ROLLBACK, because it raises no error
- Prove atomicity by checking SUM(balance) before, during and after a transfer
Understanding Transfers that never half-finish: atomicity in practice
Atomicity is the promise that a transaction leaves either all of its writes or none of them, and engines deliver it by recording undo information before they touch the data, so any uncommitted change can be reversed. That reversal is not only for your explicit ROLLBACK: if the process is killed or the machine loses power, recovery on the next startup throws away every change that has no commit record. The useful mental model for a transfer is a draft — the debit exists only inside your transaction until COMMIT makes it a fact, so 'alice was debited but bob was never credited' is not a state the database can be restarted into.
The catch is that the atomic unit is whatever you declared it to be. The engine has no concept of a transfer; it sees two UPDATE statements, and in autocommit mode it treats each one as a complete transaction of its own. Between those two commits there is a fully durable state where the pair of accounts holds 450 instead of 650, and a crash, dropped connection, or restart at that moment makes the loss permanent. Every write the business event needs — the debit, the credit, the ledger insert, any counter — has to sit between one BEGIN and one COMMIT, or the parts left outside can survive on their own.
Atomicity also says nothing about whether your statements did the right thing. UPDATE accounts SET balance = balance - 900 WHERE id = 1 AND balance >= 900 is a completely successful statement when alice holds 300: it changes zero rows, raises no error, and leaves the matching credit standing. Atomicity will then faithfully preserve everything the transaction actually did, half-transfer included, unless you inspect the affected row count (changes() in SQLite, ROW_COUNT() in MySQL, the row count your driver returns for PostgreSQL) and call ROLLBACK yourself. The other option is to hand the invariant to the engine with CHECK (balance >= 0), so an overdraft becomes an error you cannot quietly skip past.
Both habits are cheap, and together they make the difference between a transfer that fails loudly and one that silently invents money.
-- SQLite
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
owner TEXT NOT NULL,
balance INTEGER NOT NULL
);
INSERT INTO accounts VALUES (1, 'alice', 500), (2, 'bob', 150);
-- a transfer that must land completely, or not at all
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1 AND balance >= 200;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
-- a transfer alice cannot cover: the guarded debit matches no row
BEGIN;
UPDATE accounts SET balance = balance - 900 WHERE id = 1 AND balance >= 900;
SELECT changes();
UPDATE accounts SET balance = balance + 900 WHERE id = 2;
ROLLBACK; -- without this line the credit alone would commit
SELECT id, owner, balance FROM accounts ORDER BY id;
SELECT SUM(balance) FROM accounts;A transfer is all-or-nothing only if every one of its writes lives inside one BEGIN...COMMIT, and even then the engine judges each statement by whether it errored, never by whether it changed a row.
Worked examples
Two statements, two atomic units
Shows the committed, crash-proof state in which the transferred money exists nowhere, because no transaction boundary was drawn.
-- SQLite, no BEGIN anywhere: each statement commits by itself
CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts VALUES (1, 500), (2, 150);
SELECT 'before', SUM(balance) FROM accounts;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
SELECT 'gap', SUM(balance) FROM accounts;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
SELECT 'after', SUM(balance) FROM accounts;Example explained
Line 1With no BEGIN, the debit is durable the instant it returns, not when the credit follows.
Line 2The gap row of 450 is not a temporary artifact: it is committed data that survives a crash and is visible to other sessions.
Line 3Atomicity worked perfectly on each UPDATE; it just protected the wrong unit, since nothing told it the two belong together.
The ledger row belongs inside the boundary
Demonstrates that ROLLBACK removes the audit insert as well, so the ledger can only record transfers the balances agree with.
-- SQLite
CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
CREATE TABLE ledger (id INTEGER PRIMARY KEY, src INTEGER, dst INTEGER, amount INTEGER);
INSERT INTO accounts VALUES (1, 500), (2, 150);
-- 600 is more than account 1 holds
BEGIN;
UPDATE accounts SET balance = balance - 600 WHERE id = 1 AND balance >= 600;
INSERT INTO ledger (src, dst, amount) VALUES (1, 2, 600);
UPDATE accounts SET balance = balance + 600 WHERE id = 2;
ROLLBACK;
SELECT COUNT(*) FROM ledger;
-- 300 is covered
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1 AND balance >= 300;
INSERT INTO ledger (src, dst, amount) VALUES (1, 2, 300);
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT;
SELECT COUNT(*) FROM ledger;
SELECT id, balance FROM accounts ORDER BY id;Example explained
Line 1The first COUNT(*) is 0: ROLLBACK discarded the ledger insert together with the credit, so no audit row claims a transfer that never happened.
Line 2The guarded debit in the first block changed no rows and reported no error, which is precisely the case a caller has to detect.
Line 3The second COUNT(*) is 1, and the balances end at 200 and 450, still summing to the original 650.
Line 4Had the INSERT run outside the transaction, the rolled-back attempt would have left a permanent ledger row with no matching balance change.
Important notes
Error behaviour is dialect-specific: PostgreSQL marks the whole transaction as aborted at the first error and turns a later COMMIT into a rollback, while MySQL/InnoDB and SQLite undo only the failing statement and leave the transaction open, so there atomicity depends on you actually issuing ROLLBACK.
A rolled-back transfer is safe to retry because it left no trace, but atomicity will not stop a retry of one that already committed; add a unique idempotency key to the ledger row if the caller can resend.
Common mistakes
Letting a commit happen between the debit and the credit, either by forgetting BEGIN or by committing after the first UPDATE: the missing money is now durable and there is nothing left to roll back.
Reading 'no error' as 'it worked': a debit whose WHERE matches no row changes nothing, the credit commits with it, and the bug surfaces later as a total that no longer balances rather than as an exception.
Calling COMMIT from the error handler after a failed statement: in MySQL/InnoDB and SQLite only that one statement was undone, so the COMMIT makes the surviving half of the transfer permanent.
Try it yourself
Change, predict, then run
In a browser SQLite editor, create accounts (1, 500) and (2, 150), then run one transaction that moves 400 from account 1 to account 2 and a second that tries to move 400 again with a WHERE balance >= 400 guard on the debit. Use SELECT changes() to decide between COMMIT and ROLLBACK, and finish with SELECT SUM(balance) to show the total is still 650.
Open the SQL workspaceCheck your understanding
In MySQL/InnoDB a transfer runs as BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 99; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; and account 99 does not exist. What is the result?
- COMMIT is rejected, because the failed debit left the transaction unusable.
- COMMIT succeeds and account 2 gains 100 out of nowhere, since an UPDATE that matches no row is a successful statement.
- The engine notices the balances no longer sum to their previous total and rolls the transaction back.
- Only the credit is undone, because InnoDB discards statements whose matching partner changed nothing.
Show answer
An UPDATE whose WHERE clause matches nothing completes normally with zero affected rows, so nothing in the transaction signals a problem; atomicity only guarantees that whatever the transaction did survives together, and it did apply the credit. Option 0 is the tempting one but confuses 'zero rows' with an error, and in InnoDB even a genuine error rolls back just the failing statement and leaves the transaction open. Preventing this takes a ROW_COUNT() check and your own ROLLBACK, or a CHECK constraint that makes the bad state an actual error.