How do SQLite transactions keep a group of changes together?
Quick answer
A SQLite transaction lets you treat multiple database changes as one unit: begin the transaction, make the changes, then commit to keep them or roll back to undo the transaction’s changes. An explicit transaction stays active until COMMIT or ROLLBACK. This is useful when a set of writes belongs together; don’t assume every error automatically rolls back the whole transaction. [S1] [S2]
The question, the details, the next step.
What does a transaction actually do in SQLite? I have two related inserts and don’t want to think of them as unrelated changes.
A transaction groups changes so they can be treated as one unit. In SQLite, an explicit transaction started with BEGIN remains active until you end it with COMMIT or ROLLBACK. COMMIT commits the transaction’s changes; ROLLBACK undoes them. [S1] SQLite describes atomic commit as making all changes within a transaction take effect together, or none of them. [S2]
The basic flow is:
BEGIN
make related changes
├─ COMMIT → keep the grouped changes
└─ ROLLBACK → undo the transaction's changesThink of COMMIT and ROLLBACK as alternate endings for the same transaction—not steps you run one after the other.
Can you show that with a harmless example I can adapt?
Assume you have a disposable database and a table named demo_items with an item column. The table setup is just a placeholder for your own example; these inserts are illustrative:
BEGIN;
INSERT INTO demo_items (item) VALUES ('first sample');
INSERT INTO demo_items (item) VALUES ('second sample');
COMMIT;Line by line: BEGIN opens the explicit transaction; each INSERT is a change inside it; COMMIT ends the transaction and commits its changes. [S1] The two writes are grouped in one transaction rather than presented as separate committed units. [S2]
To demonstrate the other ending, start a fresh transaction, make a sample change, and use ROLLBACK instead of COMMIT:
BEGIN;
INSERT INTO demo_items (item) VALUES ('discard this sample');
ROLLBACK;That ends the transaction by undoing its changes. [S1] Use disposable data for practice, and don’t run both endings as if they were one sequence.
If an insert has a problem, does SQLite automatically undo everything? And can I put another BEGIN inside the transaction?
Don’t assume every error automatically rolls back the whole transaction: the transaction-control description explains what explicit COMMIT and ROLLBACK do, but does not establish that every error triggers a full rollback. [S1] If a change fails, check the transaction’s state and choose how to end the transaction rather than treating an error as proof that all earlier changes were undone.
A second BEGIN is not how to create a nested transaction: SQLite’s BEGIN...COMMIT transactions do not nest. The documented alternative for nested units is SAVEPOINT and RELEASE. [S1] That is a separate feature; for a simple pair of related inserts, stick to one BEGIN and one ending.
What should I check before I use this pattern in my own code?
First decide which changes truly belong together and what should happen if you decide not to keep them. Then make the end of the transaction explicit. Here’s a small reusable sketch; replace the table and values with disposable examples before adapting it:
BEGIN
change 1
change 2
review whether to keep the group
if keeping it: COMMIT
otherwise: ROLLBACKSQLite can also start transactions automatically for database-accessing commands when no transaction is already active. [S1] The example here deliberately uses explicit transaction control so the boundary and ending are visible. The cited detailed account of the commit mechanism applies to rollback mode, not write-ahead logging; it supports the general atomicity purpose, but does not establish the detailed mechanism for every journal mode. [S2]
A practical checklist
Name the related changes that should be treated as one unit.
Use a disposable example table and sample values while learning.
Start the explicit transaction with
BEGINbefore the grouped changes.Choose one ending:
COMMITto keep changes orROLLBACKto undo the transaction’s changes.Do not treat a second
BEGINas a nested transaction; look into savepoints if you need nested units.After an error, check the transaction state rather than assuming the entire transaction was rolled back.
A few more useful answers
Does COMMIT undo changes?
No. COMMIT ends the explicit transaction by committing its changes. ROLLBACK is the ending that undoes the transaction’s changes. [S1]
Can SQLite start a transaction without an explicit BEGIN?
Yes. SQLite also starts transactions automatically for database-accessing commands when no transaction is already active. [S1]
Does this explain SQLite’s commit mechanism for every journal mode?
No. The detailed account of atomic commit applies specifically to rollback mode, not write-ahead logging. [S2]
Sources
References used for this guide.
- [S1] SQLite Transaction: BEGIN, COMMIT, and ROLLBACK sqlite.org · Checked
- [S2] Atomic Commit in SQLite: all changes or none sqlite.org · Checked