What do COMMIT and ROLLBACK actually do, and when does the database make the decision for you?
The commit keeps the work and the rollback throws it away. And when you do neither, the database usually decides for you.
BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;
COMMIT; -- the change is now permanent
BEGIN;
DELETE FROM accounts WHERE id = 9;
ROLLBACK; -- the delete is undone
So when does the database decide for you? When you never open a transaction at all.
Most engines run in a mode where every statement is its own transaction. That means that a statement is kept the moment it finishes, and you never have to say COMMIT. It also means that two statements that should have happened together do not, and that is the problem you solve by opening a transaction yourself.
And there is one more case where the database decides. If you open a transaction and your program crashes or the connection drops before you commit, then the database rolls the transaction back for you. So an uncommitted transaction is never left half applied.
what you wrote what the database does
---------------------------- --------------------------------------------
BEGIN ... UPDATE ... COMMIT the update is kept
BEGIN ... UPDATE ... ROLLBACK the update is undone
BEGIN ... UPDATE ... (crash) the update is undone
UPDATE (no transaction) the update is kept on its own
So the rule to remember is this. If two statements have to happen together, they have to be inside one transaction, and the transaction has to be committed before you leave it.
The next two questions in this topic look at savepoints and at what a crash does.