SQLite Transactions

A transaction is a group of SQL statements that execute as a single unit. Either all statements succeed and their changes are saved permanently, or none of them are saved. Transactions protect data integrity when multiple related changes must happen together.

The Bank Transfer Analogy

TASK: Transfer $500 from Alice's account to Bob's account.

Two operations must happen together:
  Step 1: Deduct $500 from Alice
  Step 2: Add $500 to Bob

FAILURE SCENARIO WITHOUT TRANSACTION:
  Step 1 succeeds → Alice loses $500
  Step 2 fails    → Bob never receives it
  Result: $500 vanishes from the system ← disaster

WITH TRANSACTION:
  Step 1 succeeds (not yet permanent)
  Step 2 fails    → ROLLBACK both steps
  Result: Alice keeps her $500, nothing changes ← safe

Transaction Syntax

BEGIN;             -- start the transaction
  SQL statement 1;
  SQL statement 2;
  SQL statement n;
COMMIT;            -- make all changes permanent

-- If something goes wrong:
ROLLBACK;          -- undo all changes since BEGIN

Bank Transfer Example

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  balance REAL
);
INSERT INTO accounts VALUES (1,'Alice',1000),(2,'Bob',500);

-- Transfer $500 from Alice to Bob
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Check that neither balance went negative
SELECT name, balance FROM accounts;
name  │ balance
──────┼─────────
Alice │ 500.0
Bob   │ 1000.0

-- Looks correct — make permanent
COMMIT;

Rolling Back on Error

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Simulate discovering an error condition:
-- Bob's account was closed, so we cancel

ROLLBACK;

-- Verify: Alice's balance is unchanged
SELECT balance FROM accounts WHERE id = 1;
balance: 1000.0   ← Alice still has her money

Implicit Transactions

Every SQL statement that you run outside a BEGIN/COMMIT block gets its own automatic transaction. SQLite starts one, runs the statement, and commits or rolls back immediately. This is called an implicit (auto-commit) transaction.

-- This single statement is its own transaction:
INSERT INTO orders (product, amount) VALUES ('Pen', 0.99);
-- Auto-committed immediately

-- This is equivalent to:
BEGIN;
INSERT INTO orders (product, amount) VALUES ('Pen', 0.99);
COMMIT;

Transaction Types

BEGIN DEFERRED;    -- default; acquires locks as needed
BEGIN IMMEDIATE;   -- acquires a write lock immediately
BEGIN EXCLUSIVE;   -- prevents all other connections

Use IMMEDIATE or EXCLUSIVE when you know upfront that
you will be writing data, to avoid "database is locked" errors
in multi-process scenarios.

Bulk Insert Performance with Transactions

Wrapping many inserts in a single transaction is dramatically faster because SQLite writes to disk only once at COMMIT, rather than once per statement.

-- SLOW: each insert commits separately (disk write each time)
INSERT INTO log VALUES (1, 'event A');
INSERT INTO log VALUES (2, 'event B');
...  -- 10,000 inserts = 10,000 disk writes

-- FAST: one commit for all inserts
BEGIN;
INSERT INTO log VALUES (1, 'event A');
INSERT INTO log VALUES (2, 'event B');
...  -- 10,000 inserts
COMMIT;  -- 1 disk write

Speed difference: can be 50–200× faster for bulk inserts.

ACID Properties

PROPERTY      MEANING
──────────────────────────────────────────────────────────────────
Atomicity     All statements succeed or none do
Consistency   Database moves from one valid state to another
Isolation     In-progress transaction is invisible to others
Durability    Committed data survives crashes (written to disk)

SQLite is fully ACID-compliant.

Key Takeaways

• A transaction groups statements into an all-or-nothing unit
• BEGIN starts, COMMIT saves, ROLLBACK undoes all changes
• Without an explicit BEGIN, each statement auto-commits
• Bulk inserts inside one transaction can be 50–200× faster than individual commits
• SQLite is fully ACID-compliant — committed data is durable against crashes