If you’ve ever had an application crash halfway through updating several related database rows and ended up with data in a weird, half-finished state, you already understand why transactions exist. Transactions are the mechanism that lets a database guarantee “all of these changes happen together, or none of them do.” SQLite, despite being a lightweight, file-based database, has a surprisingly robust transaction system that’s worth understanding deeply if you’re building anything more serious than a toy project.
In this article, I’ll walk through SQLite’s transaction syntax from the ground up — the basic commands, the different transaction modes, how locking works, savepoints, and the practical patterns you’ll actually use day to day.
What Is a Transaction?
A transaction is a group of one or more SQL statements that are executed as a single unit of work. Either every statement in the group succeeds and the changes are permanently saved (committed), or if something goes wrong, every statement is undone (rolled back) as if none of them ever happened.
This “all or nothing” guarantee is often summarized by the acronym ACID:
- Atomicity — the transaction happens completely or not at all.
- Consistency — the database moves from one valid state to another valid state.
- Isolation — concurrent transactions don’t interfere with each other’s intermediate states.
- Durability — once committed, changes survive even a crash or power loss.
SQLite implements all four of these properties, which is genuinely impressive for a database that’s just a single file on disk with no separate server process.
The Basic Transaction Commands
SQLite gives you three core statements for managing transactions manually:
BEGIN TRANSACTION;
-- your SQL statements go here
COMMIT;
or, to undo everything:
BEGIN TRANSACTION;
-- your SQL statements go here
ROLLBACK;
You can also just write BEGIN; instead of BEGIN TRANSACTION; — both are valid, and TRANSACTION is optional syntactic sugar. Similarly, COMMIT; and END TRANSACTION; (or just END;) are interchangeable.
Here’s a concrete example of transferring money between two accounts, a classic transaction use case:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
If both UPDATE statements succeed, the COMMIT makes the changes permanent. If anything fails between BEGIN and COMMIT — a constraint violation, an application crash, a power outage — none of the changes are applied. You’ll never end up with money deducted from account 1 but not credited to account 2.
Implicit Transactions
Here’s something that catches a lot of beginners off guard: every single SQL statement in SQLite that changes data actually runs inside an implicit transaction, even if you never type BEGIN yourself. If you run a bare UPDATE statement outside of an explicit BEGIN/COMMIT block, SQLite automatically wraps it in its own mini-transaction and commits it immediately after it finishes.
This is called “autocommit mode,” and it’s SQLite’s default behavior. It’s what lets you run individual statements in the sqlite3 command-line tool without worrying about explicitly committing every single one.
The main reason to use explicit BEGIN/COMMIT blocks is when you want multiple statements to be treated as one atomic unit, rather than each committing independently. It’s also worth knowing that wrapping multiple statements in an explicit transaction is significantly faster than running them individually in autocommit mode, since SQLite has to sync to disk on every commit, and batching that sync across many statements cuts down on disk I/O dramatically.
The Three Transaction Modes: DEFERRED, IMMEDIATE, and EXCLUSIVE
This is where SQLite’s transaction syntax gets more nuanced than a lot of beginner tutorials let on. When you begin a transaction, you can specify how SQLite should acquire its lock on the database file.
BEGIN DEFERRED TRANSACTION;
BEGIN IMMEDIATE TRANSACTION;
BEGIN EXCLUSIVE TRANSACTION;
DEFERRED is the default if you just write BEGIN; with no keyword. In this mode, SQLite doesn’t actually acquire any lock when the transaction starts. It waits until the first statement that actually reads or writes data, and only then grabs the appropriate lock (a shared lock for reads, a reserved lock for writes). This is efficient, but it means a DEFERRED transaction that starts with a read and later tries to write can sometimes fail with a SQLITE_BUSY error if another connection grabbed the write lock in between.
IMMEDIATE acquires a reserved lock right away, as soon as the transaction begins, even before you run your first statement. This means other connections can still read the database, but no other connection can start a write transaction until yours finishes. This is the safer choice when you know your transaction is going to write data, because it avoids the “started reading, tried to write, got blocked” problem that can happen with DEFERRED.
BEGIN IMMEDIATE TRANSACTION;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;
EXCLUSIVE goes a step further and acquires an exclusive lock immediately, blocking all other connections from reading or writing until the transaction finishes. In SQLite’s default rollback-journal mode, EXCLUSIVE behaves similarly to IMMEDIATE for most practical purposes, but in WAL (Write-Ahead Logging) mode, the distinction matters more — EXCLUSIVE actually prevents other readers, whereas IMMEDIATE still allows them.
For most application code that performs writes, BEGIN IMMEDIATE is the generally recommended choice, since it avoids the classic “deferred transaction upgrade failure” that trips up a lot of multi-threaded or multi-process SQLite applications.
Rolling Back a Transaction
If something goes wrong inside a transaction — a constraint violation, a business-logic check that fails, or simply a decision in your application code — you call ROLLBACK to undo everything since the last BEGIN.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- suppose your application logic checks the new balance and finds it's negative
ROLLBACK;
After a ROLLBACK, it’s as though the transaction never happened. This is incredibly useful for enforcing business rules that are hard to express as simple SQL constraints — check a condition in your application code, and roll back if it’s violated.
It’s worth noting that certain runtime errors will cause SQLite to automatically roll back a transaction on its own, without you needing to issue ROLLBACK explicitly. A full disk, a constraint violation depending on configuration, or a corrupt database file can trigger this. Well-written application code should always be prepared to catch these errors and treat the transaction as rolled back.
Savepoints: Nested Transaction-Like Checkpoints
SQLite doesn’t support true nested transactions — you can’t BEGIN a transaction inside another BEGIN/COMMIT block. But it does support savepoints, which give you similar functionality: the ability to roll back part of a transaction without discarding everything.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT before_transfer;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- suppose something about this second update needs to be undone
ROLLBACK TO before_transfer;
-- the first update (id = 1) is still intact, only the savepoint's changes were undone
COMMIT;
You can also release a savepoint once you’re confident you won’t need to roll back to it:
RELEASE before_transfer;
Releasing a savepoint doesn’t commit the transaction — it just merges the savepoint’s changes into the enclosing transaction (or the next outer savepoint) and forgets the checkpoint. Savepoints can even be nested multiple levels deep, which is genuinely useful for building things like undo stacks or complex multi-step operations where different parts might independently need to be reverted.
SAVEPOINT step1;
-- some changes
SAVEPOINT step2;
-- more changes
ROLLBACK TO step2; -- undoes only step2's changes
RELEASE step1; -- commits everything up through step1
Transaction Behavior in the sqlite3 Command-Line Tool
If you’re experimenting in the sqlite3 shell, it’s worth knowing that autocommit is on by default, so each statement commits immediately unless you explicitly BEGIN a transaction. There’s also a .timeout command that controls how long SQLite waits for a lock before giving up with a busy error, which is handy when testing concurrent access patterns.
.timeout 5000
BEGIN IMMEDIATE;
-- statements
COMMIT;
Handling SQLITE_BUSY and Locking Conflicts
Since SQLite is a single-file database, only one connection can hold a write lock at a time. If a second connection tries to write while another transaction is still open, it’ll get an SQLITE_BUSY error (unless you’ve configured a busy timeout, in which case SQLite will retry internally for that duration before giving up).
PRAGMA busy_timeout = 5000;
Setting a reasonable busy timeout at the start of your application’s connection setup is one of the simplest and most effective things you can do to avoid random database is locked errors in multi-threaded or multi-process applications.
WAL Mode and Its Effect on Transactions
By default, SQLite uses a rollback journal for transactions, which means writers block readers and readers block writers. Switching to Write-Ahead Logging (WAL) mode changes this behavior significantly, allowing readers and a single writer to operate concurrently.
PRAGMA journal_mode = WAL;
In WAL mode, write transactions still only allow one writer at a time, but readers can continue reading the pre-transaction state of the database without being blocked. This is a huge win for applications with concurrent read-heavy workloads. It doesn’t change the fundamental transaction syntax (BEGIN, COMMIT, ROLLBACK, SAVEPOINT all work the same way), but it’s worth knowing about since it directly affects how your transactions behave under concurrency.
Common Use Cases for Transactions
- Financial operations — transferring balances, applying charges, anything where partial completion would corrupt data integrity.
- Bulk inserts — wrapping thousands of INSERT statements in a single transaction can be dramatically faster than committing each one individually.
- Multi-table updates — updating an order and its related line items together, ensuring you never end up with an order that has no items or items with no parent order.
- Data migrations — applying schema changes and data transformations together, with the ability to roll back cleanly if something fails partway through.
- Complex business logic — using savepoints to try an operation, check the result, and selectively undo parts of it based on application logic.
Practical Example: Bulk Insert Performance
Here’s a demonstration of why wrapping bulk operations in a transaction matters so much:
-- Without an explicit transaction: each INSERT auto-commits separately (slow)
INSERT INTO logs (message) VALUES ('event 1');
INSERT INTO logs (message) VALUES ('event 2');
-- ... thousands more
-- With an explicit transaction: all inserts commit together (fast)
BEGIN TRANSACTION;
INSERT INTO logs (message) VALUES ('event 1');
INSERT INTO logs (message) VALUES ('event 2');
-- ... thousands more
COMMIT;
The difference in performance here isn’t subtle — batching thousands of inserts into a single transaction can be an order of magnitude faster, sometimes more, because SQLite only needs to flush to disk once at commit time instead of after every single statement.
Best Practices
- Use BEGIN IMMEDIATE for write transactions in multi-threaded or multi-process applications to avoid the deferred-to-write lock upgrade problem.
- Batch bulk operations inside a single transaction rather than letting each statement autocommit individually — the performance gain is significant.
- Set a sensible busy_timeout so your application retries automatically instead of immediately failing with “database is locked.”
- Use savepoints for partial rollback logic, especially in complex multi-step operations where only part of the work might need to be undone.
- Keep transactions short. Long-running transactions hold locks longer, increasing the chance of contention with other connections.
- Always handle errors and ensure ROLLBACK is called if your application logic detects a problem, rather than leaving a transaction open indefinitely.
- Consider WAL mode if your application has concurrent readers and writers, since it substantially reduces locking contention without changing your transaction syntax.
Wrapping Up
Transactions are one of SQLite’s quiet strengths — they’re simple to invoke with BEGIN, COMMIT, and ROLLBACK, but there’s real depth underneath once you start caring about concurrency, performance, and reliability. Understanding the difference between DEFERRED, IMMEDIATE, and EXCLUSIVE transactions, knowing how to use savepoints for partial rollbacks, and being deliberate about batching bulk operations will take you a long way toward writing SQLite-backed applications that behave predictably even under real-world conditions like crashes, concurrent access, and large data volumes.
The syntax itself is small enough to memorize in an afternoon. The judgment about when and how to use each mode is what actually separates a fragile SQLite integration from a rock-solid one.