SQLite / Intermediate: query structure and transactional design / Lesson 8

Transactions, savepoints, and error outcomes

Combine related SQLite changes into one outcome and handle statement failure, transaction state, savepoint rollback, and connection policy explicitly.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Define transaction ownership and commit boundaries.
Use savepoints instead of nested BEGIN.
Distinguish a rejected statement from a rolled-back transaction.

Choose the business unit that must succeed together

A transfer debit and credit should commit together or neither should remain. Two separate successful autocommit statements do not supply that business atomicity. BEGIN starts an explicit unit, COMMIT accepts it, and ROLLBACK cancels it. SQLite also creates automatic transactions around database work when no explicit transaction exists, with completion tied to statement lifecycle.

Keep the transaction short and exclude user interaction or remote service calls while holding database resources. A rollback cannot undo an email or a completed external payment. The owner must define accepted outcomes and what happens after a connection loss near commit, when the application may not know whether the change became durable. Later integration work adds durable request identity for reconciliation.

Observe rollback on ordinary database tables

The first script creates and funds two accounts before the transfer transaction. It performs both changes and then rolls them back deliberately. The final amounts remain100 and50. This proves the demonstrated state transition was undone in the isolated database; it does not simulate power failure, damaged storage, or every host driver's transaction defaults.

Foreign keys and other constraints remain separate protections. A transaction can atomically commit a mathematically wrong transfer if the predicates or amounts are wrong. Check expected affected rows and validate business input. A zero-row update is not necessarily a SQL error, so a host may need to reject the whole operation explicitly when a required account is absent.

sql Rollback a complete synthetic transfer

CREATE TABLE account(id INTEGER PRIMARY KEY,amount INTEGER NOT NULL);
INSERT INTO account VALUES(1,100),(2,50);
BEGIN;
UPDATE account SET amount=amount-10 WHERE id=1;
UPDATE account SET amount=amount+10 WHERE id=2;
ROLLBACK;
SELECT id,amount FROM account ORDER BY id;

Expected output

id|amount
1|100
2|50

Both updates are undone, leaving the pre-transaction balances100 and50. Setup inserts remain because they precede BEGIN.

Use savepoints for a nested rollback boundary

SQLite does not nest BEGIN transactions. SAVEPOINT names a position inside the transaction stack, ROLLBACK TO rewinds changes after that point without ending the outer transaction, and RELEASE removes the named boundary. Releasing an inner savepoint is not an independent durable commit; an outer rollback can still undo its changes. Document ownership when a helper participates in caller-owned work.

The second script keeps the initial ten-unit debit but abandons a further five-unit adjustment. ROLLBACK TO leaves the savepoint active, so RELEASE completes that boundary before COMMIT. The final amount is90. This pattern is useful only when partial recovery is a valid business policy; a transfer requiring all steps should not silently keep only one side.

sql Keep only the authorized outer change

CREATE TABLE account(id INTEGER PRIMARY KEY,amount INTEGER NOT NULL);
INSERT INTO account VALUES(1,100);
BEGIN;
UPDATE account SET amount=amount-10 WHERE id=1;
SAVEPOINT adjustment;
UPDATE account SET amount=amount-5 WHERE id=1;
ROLLBACK TO adjustment;
RELEASE adjustment;
COMMIT;
SELECT amount FROM account WHERE id=1;

Expected output

amount
90

The ten-unit update survives; the five-unit adjustment is reversed before the outer transaction commits.

A statement rejection is not always a full rollback

Conflict algorithms such as ABORT, FAIL, IGNORE, REPLACE, and ROLLBACK have different effects and do not apply identically to every constraint. Under the usual ABORT behavior, the failing statement is backed out while earlier transaction work can remain active. Do not catch a constraint error and assume the entire business operation has already disappeared.

I/O, memory, full-disk, and busy conditions need their documented handling. The host should inspect transaction state and perform cleanup according to the owning boundary, preserving the original error. A busy COMMIT can leave a transaction active and may permit a commit retry, whereas a stale WAL snapshot requires a different strategy. Error numbers and context are more useful than one generic retry for every exception.

Align connection defaults and transaction intent

BEGIN DEFERRED delays some acquisition until work occurs; BEGIN IMMEDIATE attempts the write transaction up front. Only one connection can write at a time, and choosing IMMEDIATE is not a universal speed or correctness cure. Exclusive behavior differs with journal mode. Select a policy from contention, read/write sequence, and operation semantics rather than copying a lock command.

Host APIs can add implicit transaction behavior. Python3.12 introduced an autocommit interface alongside legacy isolation_level handling; explicitly configured examples avoid mixing those models. Test normal commit, rejection after the first change, savepoint recovery, and connection cleanup. Durability also depends on journaling, synchronization settings, and storage guarantees; disabling those protections is not a harmless performance tweak.

Common errors and fixes

cannot start a transaction within a transaction

Why it happens: An explicit BEGIN was issued inside existing transaction work.
How to fix it: Use a savepoint or honor the caller-owned transaction contract.
Prevent it next time: Inspect host implicit-transaction settings.

Earlier changes survived a constraint error.

Why it happens: Statement rollback was mistaken for transaction rollback.
How to fix it: Let the owner roll back the complete failed operation when required.
Prevent it next time: Inject failure between related changes.

A released inner change vanished later.

Why it happens: RELEASE was treated as an independent commit inside an outer transaction.
How to fix it: Document that the outer transaction controls final durability.
Prevent it next time: Test nested helper calls and outer rollback.

Check your understanding

1. A debit changes one row and a required credit changes zero. Should a transfer commit?

Show worked solution

No, if both are required by the business unit. Detect the missing required effect and roll back through the owning host boundary. A syntactically successful zero-row update does not automatically raise the needed business failure.

2. Does RELEASE of an inner savepoint protect its changes from a later outer ROLLBACK?