SQL Lab — Transactions and Integrity

🖥️

SQL Terminal

🏦

Accounts

📋

Audit Log

📌 OBJECTIVES

○Start transaction with BEGIN

○Debit sender account (UPDATE)

○Credit receiver account (UPDATE)

○Test ROLLBACK on crash

○Use SAVEPOINT for partial undo

○Complete successful COMMIT

0 / 6 complete

SQL TERMINAL — bank_db

MySQL 8.0 — bank_db (InnoDB engine) Tables: accounts, transactions ⚠ Previous transfer bug: £500 deducted from Account A but never credited to Account B Task: Implement atomic transfer using BEGIN/COMMIT/ROLLBACK/SAVEPOINT ──────────────────────────────────────

mysql>

ACCOUNT BALANCES — live view

Account A — Harlow Enterprises

ACC-10042 · Sender

£5,000.00

Account B — Nordic Tech AS

ACC-10091 · Receiver

£2,000.00

TRANSACTION AUDIT LOG

Log initialised — waiting for transaction...

MISSION BRIEFING

TRANSACTIONS — VAULT BANKING SYSTEMS

SCENARIO

A bug in Vault Banking's transfer system has been deducting money from sender accounts but failing to credit receivers when the server crashes mid-operation. The result: money disappears. Your job is to wrap the transfer in an atomic transaction so it either fully completes or fully rolls back — no partial states.

THE TRANSFER: £1,500 from A to B

BEGIN;
SAVEPOINT before_debit;
UPDATE accounts SET balance = balance - 1500 WHERE id = 'ACC-10042';
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 1500 WHERE id = 'ACC-10091';
COMMIT;

CRASH TEST

Run BEGIN and the first UPDATE, then click "SIMULATE SERVER CRASH" in the Accounts window. The debit should be rolled back automatically, leaving both accounts unchanged. This is how atomicity protects data integrity.

SAVEPOINT SYNTAX

SAVEPOINT name; — mark a point to return to
ROLLBACK TO SAVEPOINT name; — undo back to that point
RELEASE SAVEPOINT name; — discard the savepoint

Display Mode