Transactions and atomicity
Group database changes into one commit or rollback.
A transaction groups statements into one unit of work. COMMIT keeps its changes; ROLLBACK discards them. Atomicity means related changes succeed together or are undone together.
Commit or roll back changes
For example, assuming both account rows exist:
BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE id = 1;
UPDATE accounts SET balance = balance + 25 WHERE id = 2;
COMMIT;Use ROLLBACK instead of COMMIT to discard both updates. Without an explicit transaction, SQLite starts transactions automatically and commits when the last active statement finishes.
Check application rules
Atomicity does not check whether the sender has enough funds. Check application rules within the transaction or enforce them through schema constraints before committing.
Transaction modes
BEGIN defaults to deferred mode. It can start with a read and request write access later. BEGIN IMMEDIATE requests write access at the start. BEGIN EXCLUSIVE also blocks other readers in rollback journal mode; in WAL mode it behaves like IMMEDIATE. See SQLite's transaction documentation.
For visibility between connections, see isolation and concurrency. For Nitro SQLite's callback API and error handling, see transactions.