Transactions

A transaction is a group of SQL operations that are treated as a single atomic unit. Either all operations in the transaction succeed and are committed to disk, or none of them are — the database rolls back to its state before the transaction began. Transactions are essential for data integrity and for achieving good write performance in SQLite.

Basic Transaction Syntax

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If anything goes wrong between BEGIN and COMMIT, issue ROLLBACK to undo all changes:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- An error occurs here...

ROLLBACK;
-- The first UPDATE is undone; the database is unchanged

Autocommit Mode

When you run a statement outside of an explicit transaction, SQLite wraps it in an implicit transaction automatically (autocommit mode). Each statement is its own transaction. This is convenient for interactive exploration but very slow for bulk inserts — each INSERT waits for a full fsync to disk.

Batch Inserts — Why Transactions Matter for Performance

-- SLOW: 10 000 implicit transactions (10 000 disk syncs)
INSERT INTO logs (message) VALUES ('line 1');
INSERT INTO logs (message) VALUES ('line 2');
-- ... 9998 more individual inserts

-- FAST: 1 explicit transaction (1 disk sync)
BEGIN;
INSERT INTO logs (message) VALUES ('line 1');
INSERT INTO logs (message) VALUES ('line 2');
-- ... 9998 more inserts
COMMIT;

Wrapping bulk inserts in a single transaction can improve performance by 100x or more because SQLite only needs to write the WAL (write-ahead log) and sync to disk once.

SAVEPOINT — Nested Transactions

SQLite supports SAVEPOINT for partial rollbacks within a transaction:

BEGIN;

INSERT INTO orders (customer_id, total) VALUES (1, 200);

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, qty) VALUES (last_insert_rowid(), 5, 2);
-- Something went wrong with line items only
ROLLBACK TO before_items;

-- The order row still exists; only the item insert was undone
COMMIT;

Transaction Types

SQLite offers three transaction types that control locking behaviour:

BEGIN DEFERRED;    -- Default. Acquires no lock until first read or write.
BEGIN IMMEDIATE;   -- Acquires a reserved lock immediately; allows readers.
BEGIN EXCLUSIVE;   -- Acquires an exclusive lock; blocks all other connections.