Suppose you reassign an employee from Sales to Marketing. At minimum that is one UPDATE. But a real move might be several statements: change the department, rewrite a budget line, close one project and open another. If the connection drops after the first statement, the database is left half-moved — inconsistent, with no clean way to know how far it got.
A transaction bundles statements into a single unit that is all-or-nothing. Either every statement inside it takes effect, or none of them do. That guarantee is the difference between a database and a pile of files: when something goes wrong halfway through, you can put the data back exactly as it was.
flowchart TD
B["BEGIN"] --> S1["statement 1"] --> S2["statement 2"] --> E{"how it ends"}
E -->|"COMMIT"| C["both changes saved"]
E -->|"ROLLBACK"| R["both changes undone"]
style C fill:#0e7490,color:#fff
style R fill:#b45309,color:#fff
BEGIN, COMMIT, and ROLLBACK
A transaction has a clear shape: open it with BEGIN, run your statements, then end it with either COMMIT to keep the changes or ROLLBACK to throw them away:
BEGIN;
UPDATE employees SET department_id = 2 WHERE id = 8;
UPDATE employees SET department_id = 2 WHERE id = 7;
COMMIT;
Until COMMIT runs, those two updates are provisional — visible only inside this transaction. COMMIT makes them permanent and visible to everyone else; ROLLBACK would undo both as if they never happened. The two statements are now a single decision: they land together or not at all.
BEGIN; UPDATE employees SET department_id = 2 WHERE id = 8; UPDATE employees SET department_id = 2 WHERE id = 7; COMMIT; SELECT name, department_id FROM employees WHERE id IN (7, 8) ORDER BY id;
The four guarantees: ACID
A transaction's safety rests on four properties, remembered by the initials ACID. Atomicity means all-or-nothing: the statements either all take effect or none do, so a partial transfer can never leave money deducted from one place but never added to another. Consistency means the database moves from one valid state to another — every constraint and key still holds when the transaction finishes. Isolation means concurrent transactions behave as though they ran one at a time, so two people editing the same row cannot corrupt each other's work. Durability means once COMMIT returns, the change survives even a power cut.
flowchart LR A["Atomic<br/>all or nothing"] --> T["ACID"] C["Consistent<br/>rules hold"] --> T I["Isolated<br/>concurrent safe"] --> T D["Durable<br/>survives crashes"] --> T style T fill:#0e7490,color:#fff
ROLLBACK is an undo button
ROLLBACK discards every change the transaction made, restoring the database to the state it was in at the BEGIN. It is how you abandon a batch you no longer trust — a validation check failed, a total came out wrong, the user pressed cancel.
Run the statements inside the transaction first, inspect the results, and if they are wrong issue ROLLBACK rather than trying to hand-write corrective updates. Undoing a whole transaction is always cleaner than reversing its statements one by one, because there is nothing to forget and nothing to get out of order.
BEGIN; DELETE FROM projects WHERE id = 7; ROLLBACK; SELECT name FROM projects WHERE id = 7;
flowchart LR
S1["step 1<br/>debit account"] --> X{"step 2 ok?"}
X -->|"fails"| BAD["inconsistent state"]
X -->|"in a txn"| SAFE["rolled back<br/>both undone"]
style BAD fill:#b45309,color:#fff
style SAFE fill:#0e7490,color:#fff
Durability and the default of autocommit
Outside any transaction, SQLite runs in autocommit mode: each statement is its own tiny transaction, committed the instant it finishes. That is convenient for one-off changes, which is why the earlier lessons never needed BEGIN. The moment your changes must hang together, you switch off autocommit by opening an explicit transaction. COMMIT is also the point where durability is honoured — the database writes the changes to durable storage before it tells you it succeeded, so a crash a millisecond later cannot lose them.
BEGIN; UPDATE employees SET salary = salary + 10000 WHERE department_id = 5; COMMIT; SELECT name, salary FROM employees WHERE department_id = 5 ORDER BY id;
In one transaction, move Hana Suzuki (id 8) and Gustav Berg (id 7) to the Marketing department (department_id = 2). Wrap both updates in BEGIN and COMMIT so they land together.
BEGIN; -- update id 8 -- update id 7 COMMIT;
Open with BEGIN and close with COMMIT.
Two UPDATE statements, each with
WHERE id = ....
BEGIN; UPDATE employees SET department_id = 2 WHERE id = 8; UPDATE employees SET department_id = 2 WHERE id = 7; COMMIT;
Which ACID property guarantees that all changes in a transaction happen together, or none of them do?
Atomicity is the all-or-nothing guarantee — the statements form one indivisible unit, so a failure cannot leave a transaction half-applied. The name comes from the Greek for indivisible.
Start a transaction, delete the Gamma Helpdesk project (id 7), then ROLLBACK so the deletion is undone. The project must still be present afterward.
BEGIN; -- delete project 7 -- then undo it ;
Open with BEGIN, delete with
DELETE FROM projects WHERE id = 7.End with ROLLBACK instead of COMMIT to undo the delete.
BEGIN; DELETE FROM projects WHERE id = 7; ROLLBACK;
Create a new department 'Data' (id 6, city 'Bergen') and immediately its first employee 'Sam Idris' (id 16, department_id 6, role 'Data Engineer', salary 800000). Do both in one transaction so the parent and child commit together.
BEGIN; -- insert the department -- insert its first employee COMMIT;
Insert the parent department first, then the employee that references it.
Wrap both inserts in BEGIN and COMMIT.
BEGIN; INSERT INTO departments (id, name, city) VALUES (6, 'Data', 'Bergen'); INSERT INTO employees (id, name, department_id, role, salary) VALUES (16, 'Sam Idris', 6, 'Data Engineer', 800000); COMMIT;
Transfer skill: in one transaction, give every employee hired before 2020 (hired_on < '2020-01-01') a raise of 100000. Combine a filter, an update, and a transaction.
BEGIN; -- update everyone hired before 2020 COMMIT;
Filter by date:
WHERE hired_on < '2020-01-01'.Add to the current salary with
salary + 100000, inside BEGIN ... COMMIT.
BEGIN; UPDATE employees SET salary = salary + 100000 WHERE hired_on < '2020-01-01'; COMMIT;
Recap
- A transaction groups statements into an all-or-nothing unit via
BEGIN,COMMIT, andROLLBACK. COMMITkeeps every change and makes it durable;ROLLBACKundoes them all.- ACID is the four guarantees: Atomic (all or nothing), Consistent (rules hold), Isolated (concurrent safe), Durable (survives crashes).
- Outside a transaction SQLite runs in autocommit — each statement commits on its own.
- Every
BEGINmust be paired with exactly oneCOMMITorROLLBACK.
Next you will see how foreign keys keep related tables consistent, so a transaction's guarantees are reinforced by rules the database itself enforces.