SQSQL · Lesson 7 of 8

Transactions & Safety

Transfer $100 between accounts: subtract from one, add to the other. If the server crashes between those two statements, money vanishes. Transactions make multiple statements succeed or fail as one unit.

SQL

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;    -- both changes become permanent together

-- If something went wrong instead:
-- ROLLBACK;  -- undoes everything since BEGIN

This is the A in ACID: Atomicity — all or nothing. The other letters: Consistency (constraints always hold), Isolation (concurrent transactions don't see each other's half-finished work), Durability (once committed, it survives a crash). ACID guarantees are the main reason banks run on SQL databases.

✦ Tip
Transactions are also your safety net for risky manual changes: BEGIN, run your UPDATE, SELECT to verify it did what you meant — then COMMIT if happy or ROLLBACK if not.

One more safety topic you must know: SQL injection. Never build queries by gluing user input into strings. Use parameterized queries — every language's database library supports them.

Python

# WRONG — user input becomes part of the SQL:
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")
# If name is:  ' OR '1'='1  — the query returns every user.

# RIGHT — parameterized query; input can never become SQL:
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))