>_ IzzyLab

Hands-on task

This task is step 22 of the course “SQL with SQLite”

SQLite: transfer money in a transaction

medium~7 min

The database /root/bank.db has a table accounts(name TEXT, balance INTEGER) with these accounts: Anna has a balance of 100, Boris has 30. Transfer 50 from Anna's account to Boris's. Do it the way it's done with money: both UPDATEs in one transaction (BEGIN ... COMMIT), so they apply together or not at all. In the end Anna should have 50, Boris 80, and the total balance must stay unchanged.

Write the transaction as the script /root/transfer.sql (type it in the Editor tab) and run it in sqlite3.

What you'll practise:
  • transactions
  • begin commit
  • atomicity
  • update
  • sqlite3

Topic breakdown

A transaction combines several operations into one indivisible unit: either all changes are applied, or none of them. For a money transfer this is fundamental — it is unacceptable for an amount to be debited from one account but not credited to the other. A transaction is opened with BEGIN and committed with COMMIT (while ROLLBACK would undo everything); to get several UPDATEs into one transaction, they must be run in a single sqlite3 session. Correctness is easy to verify with an invariant — the total balance before and after the transfer must match.

What's checked

  1. 50 debited from the sender's account
  2. 50 credited to the recipient's account
  3. total balance preserved
  4. the transfer is a transaction in /root/transfer.sql

What it looks like