Transactions & Savepoints

Code

-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK; to discard

-- Savepoint for nested transactions
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 50);
SAVEPOINT sp1;
INSERT INTO orders (user_id, total) VALUES (2, -1);  -- maybe invalid
ROLLBACK TO sp1;
INSERT INTO orders (user_id, total) VALUES (2, 10);
RELEASE sp1;
COMMIT;

-- In application code, use BEGIN IMMEDIATE for write transactions
-- to acquire a write lock early and avoid busy waiting.
BEGIN IMMEDIATE;
-- write statements
COMMIT;

Explanation

SQLite supports serializable transactions, with BEGIN IMMEDIATE acquiring the write lock up front to avoid SQLITE_BUSY failures during commit. Savepoints allow partial rollbacks within a transaction, simulating nested transactions. Without an explicit BEGIN, each statement runs in its own auto-commit transaction.

More SQLite Snippets