Lesson 7 of 12 · 0 of 1 exercises solved

Transactions & Safety

make several statements succeed or fail together. For example, moving $100 between two accounts should never leave the money taken from one but not added to the other.

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 .

One more safety topic you must know: SQL injection. Never build queries by gluing user input into . Use parameterized queries — every language's database 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,))

Common question

Can I trust a single statement to keep the database consistent?

A transaction groups statements so they take effect together or not at all. BEGIN starts it; COMMIT makes the changes permanent; ROLLBACK throws them away. In between, the database also keeps your work separate from other users, so a report never sees half a transfer.

Wrap every multi-step change in a transaction, keep transactions short so they do not hold locks for long, and let the database enforce constraints (NOT , CHECK, ) so a in the application cannot save nonsense. And always pass values as , no .

Source: ACID (atomicity, consistency, isolation, durability) is the guarantee a relational database makes, and it applies per transaction.

Common mix-ups

Each of these sounds reasonable. Think about why it is wrong, then open the explanation.

Each statement either succeeds or fails, so consistency is automatic.
Transactions are only for banks.
Building a query by pasting user input into the SQL string is fine if the input looks harmless.

Practice

Move 100 from the Savings account (id 1) to the Checking account (id 2) inside a single transaction, so both updates succeed or fail together.

Exercise

An atomic transfer

3 checks

-- BEGIN, two UPDATEs, COMMIT

SELECT * FROM accounts;

Output

Press Run to try your code, then Check answer to grade it.

Finished reading?1 exercise still open. You can come back to it later.

Tip: use the ← and → keys to move between lessons.