Transaction Basics

A transaction groups multiple SQL changes into one logical unit.

Either all steps succeed together, or none of them are kept.

Core Concepts

flowchart LR
  A[BEGIN] --> B[Write step 1]
  B --> C[Write step 2]
  C --> D{All good?}
  D -->|Yes| E[COMMIT]
  D -->|No| F[ROLLBACK]

Property	Meaning
Atomicity	All-or-nothing changes
Consistency	Rules remain valid before/after
Isolation	In-progress transaction boundaries respected
Durability	Committed changes persist

Without Transaction

• If statement 1 succeeds and statement 2 fails, data can become partially updated.

With Transaction

• Group related statements under BEGIN ... COMMIT.
• If anything fails, ROLLBACK restores pre-transaction state.

Code Examples

-- Setup account table for transaction demos.
CREATE TABLE accounts_tx (
  account_id INTEGER PRIMARY KEY,
  owner      TEXT NOT NULL,
  balance    REAL NOT NULL
);

INSERT INTO accounts_tx (account_id, owner, balance)
VALUES
  (1, 'Ada', 500.0),
  (2, 'Alan', 300.0);

Expected output
Table created and two accounts inserted.

-- Atomic transfer pattern with transaction boundaries.
BEGIN TRANSACTION;

UPDATE accounts_tx
SET balance = balance - 50
WHERE account_id = 1;

UPDATE accounts_tx
SET balance = balance + 50
WHERE account_id = 2;

COMMIT;

Expected output
Both updates commit together as one transfer unit.

-- Verify balances after commit.
SELECT account_id, owner, balance
FROM accounts_tx
ORDER BY account_id;

account_id	owner	balance
1	Ada	450.0
2	Alan	350.0

SQLite-Specific Nuances

SQLite Nuance

SQLite automatically wraps individual statements in implicit transactions when needed.

Use explicit transactions when multiple statements must succeed or fail together.

Common Pitfalls / Best Practices

Pitfall

Running multi-step business operations as separate autocommit statements.

Failures mid-sequence can leave inconsistent data.

Best Practice

Wrap every logically related write sequence in an explicit transaction.

Quick Challenge

Write a transaction that moves 20 from account 2 to account 1.

View Solution

BEGIN TRANSACTION;

UPDATE accounts_tx
SET balance = balance - 20
WHERE account_id = 2;

UPDATE accounts_tx
SET balance = balance + 20
WHERE account_id = 1;

COMMIT;

Expected output
Transfer is applied atomically when committed.