Transactions & ACID

Most of the statements you have seen so far run on their own — you issue one INSERT, UPDATE, or DELETE, and the database applies it immediately. But real-world operations are often made of several statements that only make sense together. A transaction groups multiple statements into a single, all-or-nothing unit of work: either every statement in the group succeeds and is saved, or none of them are.

The classic example: transferring money

Imagine moving $100 from Account A to Account B. That operation is really two separate statements:

Subtract $100 from Account A (a debit).
Add $100 to Account B (a credit).

If the debit succeeds but the credit fails — say, the database connection drops between the two statements, or a constraint violation aborts the second one — the bank has just destroyed $100. If it happened the other way around, the bank would have created $100 out of nothing. Neither statement, on its own, is safe to treat as complete. They must succeed or fail together.

Two statements that must act as one

SQL

UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- debit A
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- credit B

Wrapping both statements in a transaction is what gives you the guarantee that either both updates land, or neither does.

The ACID properties

Transactions are defined by four guarantees, commonly remembered by the acronym ACID:

Property	Meaning
Atomicity	All statements in the transaction succeed, or none of them are applied — there is no partial result.
Consistency	A transaction takes the database from one valid state to another, never leaving it in a state that violates constraints or business rules.
Isolation	Concurrent transactions don't interfere with each other, even when running at the same time against the same data.
Durability	Once a transaction is committed, the change survives — even a power loss or crash immediately afterward cannot undo it.

Starting a transaction

You mark the beginning of a transaction with BEGIN (or the standard START TRANSACTION, depending on the database). Every statement you run after that point belongs to the same unit of work, until you explicitly end it.

Beginning a transaction

SQL

BEGIN;

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

-- nothing is permanent yet...

What happens next?

A transaction that has been started isn't finished — you still need to decide whether to keep the changes or discard them. That is exactly what COMMIT and ROLLBACK do, and they are the subject of the next page.