From System Transactions to User Transactions in SQLite

Source: Dev.to

User Transactions: Escaping Autocommit

By default, SQLite runs in autocommit mode. Every non‑SELECT statement is wrapped in its own transaction:

For write‑heavy workloads, this repeated setup and teardown creates real overhead:

• Journal file reopened for every statement
• Locks repeatedly acquired and released
• Increased contention under concurrency

Explicit User Transactions

To avoid this, applications can explicitly define a user‑level transaction:

BEGIN TRANSACTION;
    INSERT INTO table1 VALUES (100);
    INSERT INTO table2 VALUES (20, 100);
    UPDATE table1 SET x = x + 1 WHERE y > 10;
    INSERT INTO table3 VALUES (1, 2, 3);
COMMIT;   -- or ROLLBACK

When BEGIN executes, SQLite leaves autocommit mode:

• A single write‑transaction spans multiple statements.
• Journaling and locking costs are amortized across all writes.

All subsequent non‑SELECT statements become part of the same user transaction. SELECT statements, however, are still executed as independent read‑transactions.

When the application executes:

• COMMIT → the write‑transaction is committed.
• ROLLBACK → the write‑transaction is aborted.

After that, SQLite automatically returns to autocommit mode.

Important Constraints

• SQLite supports only flat user transactions.
• Nested BEGIN statements are not allowed.
• Only one user transaction per database connection can exist at a time.

This design keeps SQLite’s transaction model simple and predictable.

Savepoints: Controlled Rollback Inside Transactions

While SQLite doesn’t support nested transactions, it does support savepoints, which provide a controlled rollback mechanism inside a transaction.

A savepoint marks a logical “good state” during execution:

SAVEPOINT sp1;
-- do work
ROLLBACK TO sp1;

Key properties

• Multiple savepoints can exist simultaneously.
• You can roll back to any active savepoint.
• Rolling back to a savepoint does not abort the entire transaction.

Savepoints Outside Transactions

If a savepoint is created outside a user transaction:

1. SQLite implicitly opens a user transaction.
2. Executes the savepoint.
3. Commits automatically when the savepoint is released.

Thus, savepoints act as a transaction‑lite abstraction when needed.

Statement Subtransactions: Atomicity at Statement Level

Even inside a user transaction, each non‑SELECT statement runs in its own subtransaction. At any moment only one statement subtransaction exists. SQLite implements this using an implicit (anonymous) savepoint.

The flow looks like this:

Why This Matters

• If a statement fails, SQLite rolls back only that statement.
• The user transaction continues.
• Previous successful statements remain intact.

Unless explicitly instructed, SQLite does not abort the entire transaction because of a single statement failure.

Example: Statement Subtransactions in Action

BEGIN TRANSACTION;

INSERT INTO table1 VALUES (100);
INSERT INTO table2 VALUES (20, 100);
UPDATE table1 SET x = x + 1 WHERE y > 10;
INSERT INTO table3 VALUES (1, 2, 3);

COMMIT;

Execution model

• Each statement runs in its own subtransaction.
• If the UPDATE hits a constraint violation:
  • All changes made by that UPDATE are rolled back.
  • The surrounding INSERTs remain valid.
  • The final COMMIT persists everything that succeeded.

This is why SQLite can recover cleanly from partial failures without forcing the application to restart the transaction.

Conflict Resolution: What Happens on Constraint Violations

When a constraint violation occurs during INSERT or UPDATE, SQLite applies a conflict‑resolution algorithm. Each policy defines how far the rollback propagates.

These modes determine whether a failure stays local to the statement sub‑transaction or escalates to the enclosing user transaction.

Putting It All Together

SQLite’s transaction model is layered:

• System transactions – guarantee atomic execution of individual statements.
• User transactions – amortize journaling and locking costs across many writes.
• Savepoints – allow controlled rollback without aborting the whole transaction.
• Statement sub‑transactions – ensure per‑statement atomicity even inside long transactions.

Despite supporting only flat transactions, SQLite achieves fine‑grained correctness using savepoints and sub‑transactions, without exposing unnecessary complexity to the application.

My experiments and hands‑on executions related to SQLite live here:
lovestaco/sqlite

References

1. SQLite Database System: Design and Implementation – Sibsankar Haldar.

2. 
   👉 Check out: FreeDevTools

Any feedback or contributions are welcome!

It’s online, open‑source, and ready for anyone to use.

⭐ Star it on GitHub: HexmosTech/FreeDevTools