A transfer that cannot go wrong – Databases Practice

Move money between accounts so that, whatever the request, no money is created or lost.

What to build

A transaction that carries out the transfer in transfer, or does nothing at all if it is not allowed.

Requirements

Read the sender, receiver and amount from the one row of transfer.
Do nothing if the sender does not have enough money, or if either account does not exist.
Otherwise add the amount to the receiver and subtract it from the sender, inside one transaction.
Show every account's name and balance, in order of name.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE accounts (
  name TEXT PRIMARY KEY,
  balance INTEGER NOT NULL
);
CREATE TABLE transfer (
  sender TEXT NOT NULL,
  receiver TEXT NOT NULL,
  amount INTEGER NOT NULL
);

Output

Columns name and balance, in alphabetical order of name. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

transfer holds one row with a positive amount, and the sender and receiver are different. SQLite lets a subquery read the table that is being updated, which MySQL does not allow.

Example

When a test starts with:

INSERT INTO accounts VALUES ('Rahim', 1000), ('Nadia', 500);
INSERT INTO transfer VALUES ('Rahim', 'Nadia', 300);

your SQL should print:

name   balance
-----  -------
Nadia  800
Rahim  700
(2 rows)

Why it works

Each UPDATE checks the same conditions before changing anything, so either both change or neither does, and the total money stays the same. The transaction makes the pair one change, so a crash between them cannot leave half a transfer.

Examples

Example 1 · Input

CREATE TABLE accounts (
  name TEXT PRIMARY KEY,
  balance INTEGER NOT NULL
);
CREATE TABLE transfer (
  sender TEXT NOT NULL,
  receiver TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO accounts VALUES ('Rahim', 1000), ('Nadia', 500);
INSERT INTO transfer VALUES ('Rahim', 'Nadia', 300);

Expected output

name   balance
-----  -------
Nadia  800
Rahim  700
(2 rows)

Example 2 · Input

CREATE TABLE accounts (
  name TEXT PRIMARY KEY,
  balance INTEGER NOT NULL
);
CREATE TABLE transfer (
  sender TEXT NOT NULL,
  receiver TEXT NOT NULL,
  amount INTEGER NOT NULL
);
INSERT INTO accounts VALUES ('Rahim', 1000), ('Nadia', 500);
INSERT INTO transfer VALUES ('Nadia', 'Rahim', 600);

Expected output

name   balance
-----  -------
Nadia  500
Rahim  1000
(2 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Hint 1

Credit the receiver only when the sender has enough money: compare the sender's balance, read with a subquery, with the amount.

Hint 2

Then debit the sender only when their balance is still enough and the receiver exists.

Hint 3

Wrap both UPDATEs in `BEGIN;` and `COMMIT;`, so they count as one change.

Solution

Show one correct solution (try it yourself first)

BEGIN;
UPDATE accounts
SET balance = balance + (SELECT amount FROM transfer)
WHERE name = (SELECT receiver FROM transfer)
  AND (SELECT balance FROM accounts WHERE name = (SELECT sender FROM transfer)) >= (SELECT amount FROM transfer);
UPDATE accounts
SET balance = balance - (SELECT amount FROM transfer)
WHERE name = (SELECT sender FROM transfer)
  AND balance >= (SELECT amount FROM transfer)
  AND EXISTS (SELECT 1 FROM accounts WHERE name = (SELECT receiver FROM transfer));
COMMIT;

SELECT name, balance FROM accounts ORDER BY name;

Lesson: Transactions and concurrency →