Transactions

The two statements that must not be separated

A transaction turns several statements into one. Either the whole group is applied, or the database is left exactly as it was — including when the power goes out between them.

between them, the money exists nowhere

update accounts set balance = balance - 500 where id = 1;
-- the process dies here
update accounts set balance = balance + 500 where id = 2;

the shape

begin;                 -- begin transaction, start transaction — dialects vary

update accounts set balance = balance - 500 where id = 1;
update accounts set balance = balance + 500 where id = 2;

commit;                -- or: rollback;

rollback is not only for errors

Rolling back deliberately is how you check a migration, count what a delete would remove, or run a test that leaves no trace. Every statement inside the transaction sees the change; nothing outside it does; and after rollback it never happened.

a dry run

begin;
delete from order_items where order_id = 1;
select count(*) from order_items;      -- 38: the change is visible in here
rollback;
select count(*) from order_items;      -- 40: it never happened

ACID, in one line each

Three of them are absolute. Isolation is the one with a dial on it.


Atomic	all of it, or none of it	the half-transfer is impossible
Consistent	constraints hold at the end	no orphan rows, no negative quantities
Isolated	concurrent transactions do not see each other's half-done work	to a degree you choose
Durable	once committed, it survives the power cut	the write-ahead log is why

Isolation levels: the dial

Level	What can still happen
read uncommitted	you see another transaction's uncommitted rows — almost never used
read committed	the same query twice can give different answers (PostgreSQL's default)
repeatable read	the rows you read stay still; new matching rows can appear (MySQL's default)
serializable	as if transactions ran one after another — and some will be aborted to keep that true

The lost update

Read a balance, add to it in your program, write it back — and two users doing that at once quietly overwrite each other, at every level below serializable. Do the arithmetic in SQL (set balance = balance + 500), or lock the row with select … for update.

Keep them short

An open transaction holds locks. A transaction that waits for an HTTP call, or for a user to click something, holds them for as long as that takes — and everything that needs those rows queues behind it. Read what you need, start the transaction, write, commit. Never span a network call.

Two transactions that lock the same two rows in opposite orders deadlock; the database kills one and returns an error. Touching rows in a consistent order — always customers before orders, always ascending ids — prevents most of it, and application code should be ready to retry the rest.

Where transactions are quietly already happening

Outside an explicit begin, every statement is its own transaction. That is why a single update of a million rows is safe — and why a thousand separate inserts are a thousand transactions, and a hundred times slower than the same inserts wrapped in one.

autocommit

update doors set price = price * 1.05;     -- this is a transaction, all by itself