A bank moves 100 from Ava to Liam. That is two statements:
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Ava
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- LiamText after -- is a comment. PostgreSQL ignores it.
The server can stop between the two statements: a crash, a power cut, a lost network connection.
Ava lost 100 and Liam got nothing. The 100 is gone.
Transactions
A is a group of statements that the database treats as one unit. It keeps the changes of all of them, or of none of them.
You have used transactions since Chapter 1. Without a start command, PostgreSQL puts each statement in its own transaction and commits it when the statement ends. This is .
To put several statements in one transaction, write them between BEGIN and COMMIT:
BEGINstarts a transaction. The standard form isSTART TRANSACTION. PostgreSQL accepts both.- The statements after it change rows, but the changes are not permanent yet.
COMMITends the transaction and makes every change permanent. Other sessions see the changes only from this point.
Who sees what
A session is one connection to the database. Many applications and people use the database at the same time, each in their own session.
| Moment | Your session | Every other session |
|---|---|---|
after BEGIN and the UPDATEs | sees the new balances | sees the old balances |
after COMMIT | sees the new balances | sees the new balances |
In the labs, the editor, the table tabs and the checks all use one session: yours. The tabs show your changes before you commit them.
The account log
The lab tables have a trigger (Chapter 12). Each change to a balance adds a row to account_log. Its tx column is the number that PostgreSQL gives to the transaction that made the change. Two changes with the same tx were in the same transaction.