Transactions and Locks

This article is built on diagrams and read-only code

Transactions only really come alive on a real RDBMS where multiple users access the database at the same time, and this course's in-browser console can't faithfully reproduce that behavior.

That's why this article doesn't have runnable exercises — read the code blocks in each section as examples meant to be run on a real RDBMS (MySQL / PostgreSQL / Oracle, etc.).

What's a transaction — treating multiple SQL statements as one unit

A transaction is a way to treat multiple SQL statements as a single unit: if they all succeed, they get committed together; if anything goes wrong in the middle, they all get rolled back together.

It's essential for any process where having only one half of the work succeed would be a problem — think bank transfers (one UPDATE to debit, one to credit), or confirming an online order (decrement stock, insert the order row).

The basic shape is BEGIN; (start) → several statements → COMMIT; (confirm) / ROLLBACK; (undo).

The code blocks in this article use an account table that represents bank accounts. The three columns are account_id, owner (account holder name), and balance. Assume the following initial data as you read along.

Atomicity — BEGIN ... COMMIT / ROLLBACK

When you BEGIN; and then COMMIT;, every change between those two statements gets applied together.

Run ROLLBACK; instead and every change after BEGIN; is undone, putting the database back to its state before the transaction started.

This "either all or nothing" property is called atomicity — meaning a unit that can't be split into smaller pieces.

When something like a CHECK constraint violation goes wrong mid-transaction, the standard move is ROLLBACK; to safely unwind everything.

-- Transfer: Alice -> Bob, 100 (meant to run on a real RDBMS)
BEGIN;
UPDATE account SET balance = balance - 100 WHERE owner = 'Alice';
UPDATE account SET balance = balance + 100 WHERE owner = 'Bob';
COMMIT;   -- both UPDATEs are confirmed at the same instant

-- Undo: changed our mind / something went wrong
BEGIN;
UPDATE account SET balance = balance - 300 WHERE owner = 'Alice';
UPDATE account SET balance = balance + 300 WHERE owner = 'Bob';
ROLLBACK; -- both UPDATEs are thrown away together

SAVEPOINT — rolling back just part of a transaction

SAVEPOINT name; puts a marker partway through a transaction, and ROLLBACK TO name; then rolls back only as far as that marker.

Changes made before the marker stay in place, so you can keep going with other operations and COMMIT; at the end.

A typical use: a multi-step process like "confirm order → award points → decrement stock", where only the stock decrement failed and you want to retry just that step. It's finer-grained than throwing the whole transaction away with ROLLBACK;.

-- Undo only the second update with SAVEPOINT (meant to run on a real RDBMS)
BEGIN;
UPDATE account SET balance = balance - 100 WHERE owner = 'Carol';
SAVEPOINT sp1;
UPDATE account SET balance = balance - 100 WHERE owner = 'Dave';
ROLLBACK TO sp1;   -- undo only Dave (Carol -100 stays)
COMMIT;            -- confirm only Carol -100

AUTOCOMMIT — by default, each statement commits itself

When you run a standalone UPDATE or INSERT without writing BEGIN; first, each statement gets committed automatically on its own.

That's called AUTOCOMMIT — the default mode in which each statement is committed as soon as it runs.

To commit or undo multiple statements as one unit, you have to explicitly start a transaction with BEGIN;.

Locks and deadlocks — concurrency basics

Real-world databases are hit by many users at the same time. Say Alice has a balance of 1000, and two stores (X and Y) try to charge her at the same moment — both might read "1000" and subtract from it, and one of those debits silently disappears.

The defense against this is a lock: while one user's transaction is touching a row, anyone else trying to touch the same row has to wait. The first one in locks the row, and the second one waits.

But there's a flip side: if user A locks accounts in the order "account 1 → account 2" and user B locks them in the order "account 2 → account 1", they end up waiting for each other's locks forever. That's a deadlock. The classic fix is to always acquire locks in the same order (e.g., always by account_id ascending). If a deadlock does occur, the database will forcibly abort one of the transactions to break the cycle.

Isolation levels and ACID — terminology recap

An isolation level controls how much of other concurrent transactions' in-progress work a given transaction can see.

Weaker settings (like READ COMMITTED) are faster but let you see more of others' changes, while stronger settings (like SERIALIZABLE) are safer but force more waiting.

Together with atomicity (A), consistency (C), isolation (I), and durability (D — once a COMMIT happens, the data stays on disk and survives crashes or power loss), these four make up the ACID properties.

The exact behavior of locks and isolation levels varies by product, so when you build something for real, check the spec of the database you're using.