Transactions: BEGIN, COMMIT, ROLLBACK and ACID

Make your writes atomic with transactions and understand what the ACID guarantees mean in day-to-day practice.

8 min · 3 questions

A bank transfer is two UPDATEs: debit one account, credit the other. If the second fails, the database lies. The transaction makes the block indivisible, with the ACID guarantees: Atomicity (all or nothing), Consistency (constraints stay valid), Isolation (concurrent transactions never see each other half-done), Durability (a COMMIT survives a crash). For this lesson, prefer the real sqlite3 client: some web sandboxes auto-commit every query.

SQL

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  balance REAL NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance)
VALUES
  ('alice', 500.0),
  ('bruno', 120.0);

The CHECK constraint forbids any negative balance.

The nominal case: BEGIN opens the transaction, the writes pile up, COMMIT makes them final all at once. In between, no other connection sees the intermediate state (alice debited, bruno not yet credited).

SQL

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE owner = 'alice';

UPDATE accounts
SET balance = balance + 200
WHERE owner = 'bruno';

COMMIT;

SELECT owner, balance FROM accounts;

Atomic transfer: 300 for alice, 320 for bruno.

Now the failure case. Debiting bruno by 400 would violate CHECK (balance >= 0): the statement fails. A SQLite subtlety: by default, an error cancels the offending statement but leaves the transaction open. It is up to your application code to decide: ROLLBACK to undo everything (the sane reflex), or continue if the error is recoverable.

SQL

BEGIN;

UPDATE accounts
SET balance = balance - 400
WHERE owner = 'bruno';
-- Error: CHECK (balance >= 0) rejects the row.

ROLLBACK;

SELECT owner, balance FROM accounts;

After ROLLBACK, the balances from the last COMMIT are intact.

Performance reflex: outside an explicit transaction, SQLite wraps each statement in its own implicit transaction, with a disk sync every time. Wrapping 10,000 INSERTs in a single BEGIN/COMMIT commonly makes them 50 to 100 times faster: one journal write instead of 10,000.

SQLite allows only one writer at a time: a transaction left open during a network call or a wait for user input blocks all writes and triggers SQLITE_BUSY errors. Open late, commit early. If you know you are going to write, BEGIN IMMEDIATE takes the write lock right away instead of failing halfway through.

Knowledge check

Make sure you remember the key points of this lesson.

What does atomicity, the A in ACID, guarantee?
- The transaction queries run faster
- The transaction applies in full or not at all
- Two transactions can never read the same table
After a ROLLBACK, what state is the database in?
- That of the last COMMIT, as if the BEGIN had never happened
- The UPDATEs that succeeded before the error are kept
- The database stays locked until the next BEGIN
Why are 10,000 INSERTs much faster in a single SQLite transaction?
- SQLite compresses the inserted data in bulk
- A single disk sync at COMMIT instead of one per statement
- Indexes are disabled during the transaction