What is a transaction, and what does it guarantee?
A transaction is a piece of work where either all of it happens or none of it happens.
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
owner VARCHAR(120) NOT NULL,
balance_cents INTEGER NOT NULL
);
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 5000 WHERE id = 2;
COMMIT;
So what does it guarantee? Four things, and they are the four letters that people use to describe it.
Atomicity means that all of the changes happen, or none of them do. If the program dies half way through, the database is left exactly as it was.
Consistency means that the rules of the schema still hold when the work finishes. If a rule says that a balance cannot be negative, then it is not negative after the commit.
Isolation means that another transaction cannot see the half finished work. It sees the data as it was before, or as it is after, and never in between.
Durability means that once the commit has happened, the change is really there. The machine can crash a second later and the data will still be there.
There is one thing to know about the words BEGIN and COMMIT. In some engines the word is START TRANSACTION, and in some engines the transaction starts by itself and you only say COMMIT. So the exact words differ, but the idea does not.
And there is one habit that matters more than the others. A transaction is only worth anything if it is short. A transaction that waits for a slow network call holds its locks the whole time, and everything that wants the same rows has to wait.
The next question in this topic shows the atomicity in action.