ACID with a bank transfer
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)ACID is the name for four promises a database makes about transactions: Atomicity, Consistency, Isolation and Durability. A bank transfer needs all four, which is why it is the standard example in textbooks and interviews.
Atomicity: all or nothing
Atomicity means a transaction cannot be split: either every change in it is saved, or none is. "Atomic" means "cannot be cut into parts".
A transfer is two UPDATEs: take money from one account, add it to another. Below is the table the next examples use.
DROP TABLE IF EXISTS bank_accounts;CREATE TABLE bank_accounts ( acc_no INT PRIMARY KEY, holder VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL, CONSTRAINT balance_not_negative CHECK (balance >= 0));INSERT INTO bank_accounts (acc_no, holder, balance) VALUES (101, 'Asha Rao', 5000.00), (102, 'Ravi Kumar', 1200.00);SELECT acc_no, holder, balance FROM bank_accounts ORDER BY acc_no;+--------+------------+---------+ | acc_no | holder | balance | +--------+------------+---------+ | 101 | Asha Rao | 5000.00 | | 102 | Ravi Kumar | 1200.00 | +--------+------------+---------+
Without atomicity, a crash between the debit and the credit would leave money missing. The database keeps an undo log, a record of the old values, so it can put every row back when the transaction is rolled back or the server crashes before the commit.
The trap: in MySQL, a statement that fails inside a transaction undoes only itself. The transaction stays open with the earlier changes still pending. Your application must notice the error and send ROLLBACK; if it sends COMMIT instead, the half transfer is saved.
Example 1 · A failing transfer: the application must roll back
-- Uses bank_accounts from above. Type one statement at a time.-- Ravi (102) sends Rs. 2000 to Asha (101), but he has only Rs. 1200.START TRANSACTION;UPDATE bank_accounts SET balance = balance + 2000 WHERE acc_no = 101; -- credit: worksUPDATE bank_accounts SET balance = balance - 2000 WHERE acc_no = 102; -- debit: fails
-- The failed statement is undone, but the credit is still pending:SELECT acc_no, holder, balance FROM bank_accounts ORDER BY acc_no;
ROLLBACK; -- the application must do thisSELECT acc_no, holder, balance FROM bank_accounts ORDER BY acc_no;ERROR 3819 (HY000): Check constraint 'balance_not_negative' is violated. +--------+------------+---------+ | acc_no | holder | balance | +--------+------------+---------+ | 101 | Asha Rao | 7000.00 | | 102 | Ravi Kumar | 1200.00 | +--------+------------+---------+ +--------+------------+---------+ | acc_no | holder | balance | +--------+------------+---------+ | 101 | Asha Rao | 5000.00 | | 102 | Ravi Kumar | 1200.00 | +--------+------------+---------+
What this code does
- The debit breaks the CHECK rule, so MySQL undoes that one statement and reports error 3819. The credit to Asha is still pending: the SELECT shows Rs. 7000.
ROLLBACKrestores both rows, which is atomicity. Typed one at a time, as here, the client keeps going after an error; a script run withmysql < file.sqlstops at the error instead, so this is a sample run.
Example 2 · A safe transfer: check the debit before the credit
DROP TABLE IF EXISTS bank_accounts;CREATE TABLE bank_accounts ( acc_no INT PRIMARY KEY, holder VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL, CONSTRAINT balance_not_negative CHECK (balance >= 0));INSERT INTO bank_accounts (acc_no, holder, balance) VALUES (101, 'Asha Rao', 5000.00), (102, 'Ravi Kumar', 1200.00);
-- Try to move Rs. 2000 from Ravi (102) to Asha (101): the debit checks the balance.START TRANSACTION;UPDATE bank_accounts SET balance = balance - 2000WHERE acc_no = 102 AND balance >= 2000;SELECT ROW_COUNT() AS debited; -- 0 rows changed: not enough moneyROLLBACK;
-- Move Rs. 800 instead: the debit changes 1 row, so credit and commit.START TRANSACTION;UPDATE bank_accounts SET balance = balance - 800WHERE acc_no = 102 AND balance >= 800;SELECT ROW_COUNT() AS debited;UPDATE bank_accounts SET balance = balance + 800 WHERE acc_no = 101;COMMIT;
SELECT acc_no, holder, balance FROM bank_accounts ORDER BY acc_no;+---------+ | debited | +---------+ | 0 | +---------+ +---------+ | debited | +---------+ | 1 | +---------+ +--------+------------+---------+ | acc_no | holder | balance | +--------+------------+---------+ | 101 | Asha Rao | 5800.00 | | 102 | Ravi Kumar | 400.00 | +--------+------------+---------+
What this code does
AND balance >= 2000makes the debit change no row when the money is not there, andROW_COUNT()says how many rows the last UPDATE changed. 0 means stop and roll back.- An application does the same: run the debit, read the affected row count, and only then run the credit and commit. The second transfer of Rs. 800 goes through.
Find the bug
Find the line that breaks atomicity
Each transfer should be all or nothing. Click the line that can save half of it.
The debit on line 2 fails: it would make the balance negative.
Round 1 of 3Consistency: rules always hold
Consistency means a transaction takes the database from one valid state to another valid state. Every rule holds before it starts and after it ends; inside the transaction a rule may break for a moment.
After every transfer the total money must be the same, and no balance may go below zero.
Example 3 · The total stays the same; the CHECK rule stops bad data
DROP TABLE IF EXISTS bank_accounts;CREATE TABLE bank_accounts ( acc_no INT PRIMARY KEY, holder VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL, CONSTRAINT balance_not_negative CHECK (balance >= 0));INSERT INTO bank_accounts (acc_no, holder, balance) VALUES (101, 'Asha Rao', 5000.00), (102, 'Ravi Kumar', 1200.00);
SELECT SUM(balance) AS total_before FROM bank_accounts;
START TRANSACTION;UPDATE bank_accounts SET balance = balance - 1500 WHERE acc_no = 101;UPDATE bank_accounts SET balance = balance + 1500 WHERE acc_no = 102;COMMIT;
SELECT SUM(balance) AS total_after FROM bank_accounts;
-- A rule the database itself enforces: no negative balanceUPDATE bank_accounts SET balance = balance - 5000 WHERE acc_no = 102;+--------------+ | total_before | +--------------+ | 6200.00 | +--------------+ +-------------+ | total_after | +-------------+ | 6200.00 | +-------------+ ERROR 3819 (HY000): Check constraint 'balance_not_negative' is violated.
What this code does
- Rs. 6,200 before and after: the transfer keeps the business rule because both of its statements are in one transaction.
- The last UPDATE would make Ravi's balance negative, so the CHECK constraint refuses it with error 3819, and the row is not changed.
There are two kinds of rules. The database checks the ones you declared: primary keys, UNIQUE, NOT NULL, foreign keys and CHECK constraints (CHECK is enforced from MySQL 8.0.16). Business rules such as "a transfer does not create or destroy money" are your job: the transaction must contain every statement the rule needs.
Constraints matter so much because a rule written as a constraint is checked on every change, from every application, even a buggy one.
Isolation: users do not see half-done work
Isolation means transactions running at the same time do not see each other's unfinished changes. Each one behaves as if it were alone, at least for the problems the chosen isolation level prevents.
While a transfer is half done, the bank's balance report must not show less money than there really is.
Example 4 · Session B never sees the half-done transfer
-- Uses bank_accounts from above. Two mysql windows: Session A and Session B.
-- Step 1 · Session A: a transfer of Rs. 1000 from 101 to 102, not committed yetSTART TRANSACTION;UPDATE bank_accounts SET balance = balance - 1000 WHERE acc_no = 101;UPDATE bank_accounts SET balance = balance + 1000 WHERE acc_no = 102;SELECT acc_no, balance FROM bank_accounts ORDER BY acc_no;
-- Step 2 · Session B: the bank's report reads the balancesSELECT acc_no, balance FROM bank_accounts ORDER BY acc_no;
-- Step 3 · Session ACOMMIT;
-- Step 4 · Session B: reads againSELECT acc_no, balance FROM bank_accounts ORDER BY acc_no;-- Step 1 · Session A +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 4000.00 | | 102 | 2200.00 | +--------+---------+ -- Step 2 · Session B +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 5000.00 | | 102 | 1200.00 | +--------+---------+ -- Step 4 · Session B +--------+---------+ | acc_no | balance | +--------+---------+ | 101 | 4000.00 | | 102 | 2200.00 | +--------+---------+
What this code does
- In step 2, A has changed both rows but not committed, so B still reads the old committed balances, 5000 and 1200, without waiting.
- After A commits in step 4, B sees the new balances. Step 3 has no result table. This is a sample run of two real sessions, so the checker does not run it.
Without isolation, the balance report could run between the debit and the credit of a Rs. 1000 transfer.
To try it, open two mysql client windows on the same database: each window is its own session. In InnoDB, a plain SELECT in Session B does not wait for Session A. It reads the last committed version of each row. A later lesson covers sessions and isolation levels in detail.
Durability: committed means saved
Durability means that once COMMIT returns, the change survives a crash, a power cut or a restart. The database never tells you "saved" and then forgets.
The customer is told the transfer is done. After a power cut, the money must still be moved.
SELECT @@innodb_flush_log_at_trx_commit AS flush_setting;+---------------+ | flush_setting | +---------------+ | 1 | +---------------+
A simplified model of how InnoDB does it: at commit, it writes the change to the redo log, a file on disk that lists recent changes, and waits for the disk to confirm. The table files are updated later. After a crash, MySQL reads the redo log and replays every committed change that had not reached the table files yet.
The setting innodb_flush_log_at_trx_commit = 1, the default, gives this full guarantee. The values 0 and 2 are faster but can lose about the last second of commits in a crash (with 2, only when the whole machine crashes or loses power).
Next step
Check your understanding
12 quick questions