SQLite Transactions: BEGIN, COMMIT, ROLLBACK Explained

A Transaction Is an All-or-Nothing Bundle

A transaction groups several statements so they either all take effect or none of them do. If anything goes wrong halfway through, you can roll back and the database is exactly where it started.

The classic example is moving money:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

The two UPDATEs belong together. If the database crashed between them, Ada would be 2000 cents poorer and Boris would have nothing extra. Wrapping them in BEGIN ... COMMIT makes the pair atomic — both happen, or neither does.

Autocommit: The Default You're Already Using

Every SQL statement you've run so far has been a transaction. SQLite is in autocommit mode by default: each statement gets its own implicit BEGIN and COMMIT wrapped around it.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('first');
INSERT INTO notes (body) VALUES ('second');
INSERT INTO notes (body) VALUES ('third');

SELECT * FROM notes;

Three inserts, three separate transactions, three trips to disk to fsync the change. That's fine for one-off writes but slow for bulk loads — and it means you can't undo a group of statements as a unit. BEGIN turns autocommit off until the next COMMIT or ROLLBACK.

ROLLBACK: Pretending It Never Happened

ROLLBACK discards everything done since the matching BEGIN. The database reverts to its pre-transaction state.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

Both the UPDATE and the DELETE vanish — the table looks the way it did before BEGIN. This is the safety net that lets application code abort cleanly when it hits an error halfway through a multi-statement operation.

A constraint violation inside a transaction doesn't automatically roll the whole thing back, by the way. It rolls back the offending statement and leaves the transaction open, waiting for you to decide. If you want all-or-nothing, the application has to issue ROLLBACK when it sees an error.

Speeding Up Bulk Inserts

Because each autocommitted statement does its own fsync, wrapping a batch in one transaction is often 100x faster:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

One disk sync at the COMMIT instead of one per row. If you're ever importing thousands of rows and wondering why it's crawling, this is almost always the answer.

DEFERRED, IMMEDIATE, EXCLUSIVE

BEGIN accepts a mode that controls when SQLite takes locks:

• BEGIN DEFERRED (the default) — no lock at all until you read or write. The write lock is acquired lazily, on the first write statement.
• BEGIN IMMEDIATE — grab the write lock right away. Other connections can still read, but no other connection can start writing.
• BEGIN EXCLUSIVE — like IMMEDIATE, plus no other connection can read either. In WAL mode this behaves the same as IMMEDIATE; the difference only matters in the older rollback journal mode.

BEGIN DEFERRED;     -- same as plain BEGIN
BEGIN IMMEDIATE;    -- reserve the write lock now
BEGIN EXCLUSIVE;    -- reserve everything (rollback-journal mode)

The choice matters for concurrency. With a plain BEGIN, two connections can both start a transaction, both read happily, and then race when they try to write — the second one to ask for the write lock gets SQLITE_BUSY, and worse, it's already done some reads it now has to throw away.

BEGIN IMMEDIATE solves that: if you know you're going to write, ask for the write lock first. The second connection blocks (or fails fast) immediately, before doing any work it'd have to discard.

Rule of thumb: if your transaction will write, use BEGIN IMMEDIATE.

Reading Inside a Transaction Sees a Snapshot

While a transaction is open, your reads see a consistent snapshot of the database as it was when the transaction started (in WAL mode) or when you first read (in rollback-journal mode). Other connections committing changes won't suddenly appear in your queries.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, your own write is visible
COMMIT;

SELECT n FROM counter;

You see your own uncommitted writes; other connections don't. Once you COMMIT, the new value becomes visible to everyone. This is what people mean when they say SQLite is serializable — there's no READ COMMITTED knob to turn, because the default is already the strongest level.

A Transaction in Application Code

In a real program, the pattern is usually try/except (or try/catch) around the body, with a ROLLBACK on the error path:

-- Pseudocode for any client library
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

Most client libraries (Python's sqlite3, better-sqlite3, etc.) wrap this for you with a with block or a transaction() helper. Worth checking your library's docs — the defaults aren't always what you'd expect. Python's sqlite3 in particular has historically had quirky autocommit behavior; recent versions added a proper autocommit parameter to fix it.

Things That Trip People Up

• DDL inside transactions works. CREATE TABLE, ALTER TABLE, even DROP TABLE can be rolled back. SQLite is unusual that way — many databases auto-commit DDL.
• VACUUM cannot run inside a transaction. Neither can a few other maintenance commands. Run them in autocommit mode.
• A failed COMMIT is still a real failure. If COMMIT returns SQLITE_BUSY (rare but possible), the transaction is not committed. Your code needs to handle that — usually by retrying.
• Long transactions block writers. A transaction that stays open for minutes will block other writers for minutes. Open them late, commit them quickly.

Next: Savepoints

BEGIN and COMMIT are all-or-nothing. Sometimes you want to roll back just part of a transaction — say, abandon one risky step but keep the rest. That's what savepoints are for, and they're up next.