Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 33 ยท Changing Data

Transactions

Learn SQLite transactions: autocommit, BEGIN and COMMIT, ROLLBACK, DEFERRED vs IMMEDIATE vs EXCLUSIVE, savepoints, and why batching inserts is faster.

A transaction groups several changes into one unit that either happens completely or not at all. Moving money between two accounts is the classic case: subtracting from one account and adding to the other must succeed together, even if the power fails between the two statements. SQLite is fully transactional (ACID), and it is the reason a crash does not leave your database half-written.

You are always inside a transaction, whether you ask for one or not. This lesson shows what happens by default, how to take control with BEGIN, COMMIT and ROLLBACK, how the three locking modes differ, how savepoints give you nested undo, and why wrapping bulk inserts in a transaction can make them dramatically faster.

All or nothing

BEGIN Start a transaction.
COMMIT Make every change permanent.
ROLLBACK Undo every change since BEGIN.

Atomic, Consistent, Isolated, Durable.

1 / 2

Autocommit: every statement is its own transaction

If you never write BEGIN, SQLite wraps each statement in a transaction of its own and commits it as soon as the statement finishes. That mode is called autocommit. It is safe, but each commit on a file database forces data to be flushed to disk, which is the slow part of writing. Autocommit is also why a half-finished script leaves its earlier statements in place.

BEGIN, COMMIT and ROLLBACK

BEGIN opens an explicit transaction. Everything you do afterwards is visible to your own connection immediately but is only made permanent by COMMIT. ROLLBACK throws all of it away. Here two accounts exchange money, with a CHECK constraint to keep balances from going negative.

SQL Commit and rollback

Press Run (or Ctrl + Enter) to see the result here.

After the first transaction ana has 70 and ben has 80. In the second transaction ana briefly shows 20 inside the open transaction (the extra SELECT proves the change is visible to you), but we then roll back and the final listing shows 70 and 80 again. Had the update tried to go below zero, the CHECK constraint would have raised an error instead, and your error handler would issue the ROLLBACK.

You can ask whether a transaction is open. In SQL there is no function for it, but drivers expose it, for example sqlite3_get_autocommit() in C and connection.in_transaction in Python. Trying to BEGIN while one is already open is an error ("cannot start a transaction within a transaction"), and so is COMMIT with none open. Use savepoints (below) when you need nesting.

When a statement fails inside a transaction

A failing statement undoes only itself by default, not the whole transaction. The transaction stays open and it is your code's job to decide whether to continue, commit what succeeded, or roll back everything.

SQL A failed statement does not end the transaction

Press Run (or Ctrl + Enter) to see the result here.

The second insert violates the UNIQUE constraint and the run stops with an error. In a real program the transaction would still be open after that error, and the first insert would still be pending, waiting for your COMMIT or ROLLBACK. The ON CONFLICT modes on the statement (ROLLBACK, ABORT, FAIL, IGNORE, REPLACE) decide how much is undone. The default ABORT undoes just the failing statement.

Locking modes: DEFERRED, IMMEDIATE, EXCLUSIVE

SQLite allows many readers or one writer at a time (see Concurrency, locking and WAL for the full story). The word after BEGIN decides when your transaction takes its locks:

Statement Behavior
BEGIN or BEGIN DEFERRED No lock until the first read (shared lock) or first write (reserved lock).
BEGIN IMMEDIATE Takes the write reservation immediately. Other writers must wait, readers still work.
BEGIN EXCLUSIVE Like IMMEDIATE, and in rollback-journal mode also blocks readers. In WAL mode it behaves like IMMEDIATE.

The practical rule: if a transaction will write, start it with BEGIN IMMEDIATE. A deferred transaction that reads first and writes later can find, at the moment it tries to upgrade, that another connection already holds the write lock, and it fails with SQLITE_BUSY after possibly doing a lot of work. With IMMEDIATE, you wait (or fail) at the start, when giving up is cheap.

TEXT
BEGIN IMMEDIATE;
-- read some rows, decide, write some rows
COMMIT;

This example is read-only because the in-browser database has a single connection, so there is nobody to contend with. The busy_timeout setting, covered in PRAGMAs and tuning, controls how long a connection waits for a lock before giving up.

Savepoints: nested undo

A SAVEPOINT marks a point inside a transaction that you can roll back to without abandoning everything before it. Savepoints have names and can be nested. RELEASE name merges a savepoint into its parent, and ROLLBACK TO name undoes the work since it was set (and leaves the savepoint in place, so you can retry).

A SAVEPOINT outside a transaction starts one. In that case RELEASE of the outermost savepoint commits it, which makes savepoints a handy way to write library code that works both inside and outside a caller's transaction.

SQL Savepoints: undo part of a transaction

Press Run (or Ctrl + Enter) to see the result here.

Inside the savepoint there are three rows. After ROLLBACK TO risky only the first remains. After step 4 and the commit, the table holds step 1 and step 4. Step 4 gets id 2, not 4, because the rolled-back rows no longer exist and the new id is the highest remaining id plus one.

Why batching inserts is faster

In autocommit mode each INSERT is its own transaction, and on a real file database every commit waits for the operating system to confirm that data is on disk. Disks are slow at that: a few hundred commits per second is typical on an ordinary drive. One transaction around many inserts pays that cost once.

A typical import loop in Python looks like this (read-only, since the browser database is in memory and has no disk to wait for):

Python
import sqlite3

con = sqlite3.connect("big.db")
rows = [(i, f"item {i}") for i in range(100_000)]

with con:  # one transaction, committed when the block ends
    con.executemany("INSERT INTO items (id, name) VALUES (?, ?)", rows)

Expect the batched version to be tens or even hundreds of times faster than committing after every row on a disk database. Batches of a few thousand to a few hundred thousand rows are fine; the transaction does not need to be tiny, but huge ones hold the write lock longer, so other writers wait. The same idea applies to updates and deletes.

You can verify that one statement is atomic by itself: a multi-row INSERT either adds all its rows or none of them.

SQL One statement is atomic

Press Run (or Ctrl + Enter) to see the result here.

The run ends with a UNIQUE constraint failed error caused by the duplicate B. If you follow it with SELECT * FROM codes in a real session you would see only A and B: C, D and E were never kept, because the statement as a whole was undone.

Group related writes in a transaction for correctness, and group bulk writes in a transaction for speed. Keep transactions short, and never hold one open while waiting for user input or a slow network call.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You run an INSERT with no BEGIN. What happens?

    Autocommit mode wraps each statement in its own transaction and commits it right away.
  2. 2. Which transaction mode should you use when you know you will write?

    A deferred transaction that reads first can fail when it later tries to upgrade to a write lock. IMMEDIATE reserves the write right away.
  3. 3. What does ROLLBACK TO name do?

    ROLLBACK TO undoes the changes made after the savepoint was set. The transaction continues, and you still need RELEASE, COMMIT or ROLLBACK.
  4. 4. Why is inserting 100,000 rows inside one transaction much faster than in autocommit mode?

    Each commit must flush to durable storage. One commit for the whole batch removes almost all of that waiting.

Summary

  • Without BEGIN, every statement is its own transaction (autocommit); with BEGIN you control where the transaction ends.
  • COMMIT makes changes permanent; ROLLBACK discards them. A single statement is always atomic.
  • A failed statement undoes only itself by default; your code decides whether to roll back the rest.
  • Use BEGIN IMMEDIATE for transactions that will write; EXCLUSIVE only differs from it in rollback-journal mode.
  • SAVEPOINT, ROLLBACK TO and RELEASE give nested undo and work even outside an explicit transaction.
  • Wrap bulk writes in one transaction to pay the commit cost once, and keep transactions short.

Next: Foreign keys, keeping related tables consistent.