SQLite Transactions Explained with Examples
For a developer writing to a SQLite file from an application or the shell, who needs two statements to land together or not at all.
On this page
A transaction groups several statements so that SQLite writes all of them to the database, or none of them. For example, a transfer between two bank accounts runs its debit and its credit in one transaction.
Without a transaction, SQLite commits each statement as soon as it finishes. If the credit of a transfer fails, the debit is already in the database file.
Every transaction has the same shape:
BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] [TRANSACTION];
statement;
...
COMMIT | ROLLBACK;
BEGINstarts the transaction. The word TRANSACTION is optional.DEFERRED,IMMEDIATEandEXCLUSIVEset when the transaction locks the database for writing. DEFERRED is the default.statementis any SQL statement, such as SELECT, UPDATE or CREATE TABLE.COMMITwrites every change of the transaction to the database. END is another name for COMMIT.ROLLBACKdiscards every change of the transaction.
Open the sample database in the sqlite3 shell with sqlite3 Bank.db, which creates the file if it doesn't exist. SQLite CREATE DATABASE shows how to install the shell. Then create a table of two accounts:
CREATE TABLE Accounts (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO Accounts (name, balance) VALUES ('Alice', 500), ('Bob', 300);
The CHECK constraint stops a balance from going below zero.
Every statement in this article was run on SQLite 3.50.4. Run the examples in order. Each transaction changes the balances, and the next transaction starts from the new ones.
BEGIN and COMMIT
COMMIT ends a transaction and writes all of its changes to the database at once. For example, you can move 100 from Alice to Bob between BEGIN and COMMIT.
BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;
SELECT id, name, balance FROM Accounts;
| id | name | balance |
|---|---|---|
| 1 | Alice | 400 |
| 2 | Bob | 400 |
Alice went from 500 to 400, and Bob from 300 to 400. Until the COMMIT, a second connection to Bank.db reads 500 and 300. After the COMMIT, every connection reads the new balances, and SQLite keeps them through a crash or a power failure.
ROLLBACK
ROLLBACK ends a transaction and discards all of its changes. For example, you can cancel a transfer after both of its UPDATE statements have run.
BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
ROLLBACK;
SELECT id, name, balance FROM Accounts;
| id | name | balance |
|---|---|---|
| 1 | Alice | 400 |
| 2 | Bob | 400 |
Both UPDATE statements ran, and ROLLBACK undid both, so the balances are still 400 and 400. ROLLBACK also undoes a CREATE TABLE or a DROP TABLE that ran inside the transaction. When a connection closes with a transaction open, SQLite rolls that transaction back.
BEGIN IMMEDIATE
BEGIN IMMEDIATE starts a transaction and takes the database's write lock at once. SQLite gives the write lock to one connection at a time. For example, you can read Alice's balance before you debit it, and no other connection can change the balance in between.
BEGIN IMMEDIATE;
SELECT balance FROM Accounts WHERE name = 'Alice';
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;
| balance |
|---|
| 400 |
Until the COMMIT, a BEGIN IMMEDIATE on a second connection fails with the error "database is locked", or waits when that connection has a busy timeout. A SELECT on that second connection still reads 400. After the COMMIT, Alice has 300 and Bob has 500.
A plain BEGIN, which is BEGIN DEFERRED, takes the write lock only at the first statement that writes. BEGIN EXCLUSIVE takes the write lock at BEGIN, like IMMEDIATE. In the default journal mode, BEGIN EXCLUSIVE also stops other connections from reading.
SAVEPOINT and ROLLBACK TO
SAVEPOINT marks a point inside a transaction, and ROLLBACK TO undoes every change made after that point. For example, you can keep a credit to Bob and undo a debit from Alice in the same transaction.
BEGIN;
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
SAVEPOINT before_debit;
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
ROLLBACK TO before_debit;
COMMIT;
SELECT id, name, balance FROM Accounts;
| id | name | balance |
|---|---|---|
| 1 | Alice | 300 |
| 2 | Bob | 600 |
Bob's credit ran before the savepoint, so the COMMIT wrote it, and Bob went from 500 to 600. Alice's debit ran after the savepoint, and ROLLBACK TO undid it, so Alice kept 300. After ROLLBACK TO, the transaction is still open and still needs a COMMIT or a ROLLBACK.
RELEASE before_debit removes the savepoint and keeps every change made after the savepoint. Like every other change in the transaction, those changes become permanent only at COMMIT.
A second BEGIN inside a transaction fails with "cannot start a transaction within a transaction". Use a savepoint instead, because savepoints can sit inside each other.
What goes wrong
A failed statement leaves the transaction open
When a statement inside a transaction fails, SQLite undoes that one statement and keeps the transaction open. Here the credit to Bob runs first. The debit then breaks the CHECK constraint, because Alice has only 300:
BEGIN;
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
SELECT id, name, balance FROM Accounts;
The debit fails with "CHECK constraint failed: balance >= 0", and the SELECT still shows the credit:
| id | name | balance |
|---|---|---|
| 1 | Alice | 300 |
| 2 | Bob | 1100 |
Run ROLLBACK, which puts Bob back at 600. A COMMIT instead would give Bob 500 that nobody paid.
A few errors, such as a full disk (SQLITE_FULL), may roll back the whole transaction. The transaction documentation advises a ROLLBACK after those errors too.
An UPDATE that matches no row
An UPDATE whose WHERE clause matches no row is not an error. Here the credit goes to Carol, who has no account:
BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Carol';
SELECT changes();
| changes() |
|---|
| 0 |
SQLite reports no error, and changes() shows that the second UPDATE changed 0 rows. A COMMIT now would take 100 from Alice and give it to nobody. Check the row count after each statement, with changes() or the count that your driver returns, and run ROLLBACK when it is 0.
database is locked
Two transactions that read before they write can fail when both start with a plain BEGIN. Run the BEGIN IMMEDIATE example on two connections, A and B, at the same time, with BEGIN in place of BEGIN IMMEDIATE.
Both connections read Alice's balance. A's UPDATE then takes the write lock, and B's UPDATE fails with "database is locked", the error code SQLITE_BUSY. B fails at once, even when B has a busy timeout, which is a time to wait for a lock.
Waiting can't help here. In the default journal mode, A can't commit while B still holds its read of the database, as the busy handler documentation explains.
Use BEGIN IMMEDIATE for a transaction that reads and then writes. Also set a busy timeout on each connection, in milliseconds:
PRAGMA busy_timeout = 5000;
With this timeout, B's BEGIN IMMEDIATE waits until A's transaction ends, and then B reads the new balance. Without a timeout, B's BEGIN IMMEDIATE fails at once with "database is locked".
Keep transactions short. The write lock stays held until COMMIT or ROLLBACK, and every other writer waits for it (file locking).
In WAL mode, which PRAGMA journal_mode = WAL turns on, readers and the writer don't block each other. SQLite still allows one writer at a time.
Run the transfer in DbSchema
The DbSchema SQL Editor runs a transaction as a script. Connect DbSchema to SQLite and choose Bank.db in the connection dialog. DbSchema downloads the JDBC driver on the first connection.
Press Editor in the toolbar and choose SQL Editor. Paste the BEGIN and COMMIT example, and press Run Script, which runs the statements one after the other.
DbSchema leaves auto-commit on for a SQLite connection. The BEGIN and COMMIT in the script then start and end the transaction, as in the sqlite3 shell. The statements change Bank.db itself, not the DbSchema model file.
Download DbSchema at https://dbschema.com/download.html, connect DbSchema to Bank.db, and run the transfer and the savepoint example in its SQL Editor. The free Community Edition includes the database connection and the SQL Editor.

