Use transactions

A transaction groups several SQL statements into one unit. SQLite commits all of them together, or rolls back all of them if something goes wrong.

Start a transaction before changes that must succeed together:

BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
UPDATE accounts SET balance = balance + 20 WHERE id = 2;
COMMIT;

Without the transaction, a crash after the first UPDATE removes money from one account without adding it to the other. The transaction makes the pair atomic: COMMIT makes both changes durable, ROLLBACK discards both.

Handle failures in application code

Your code should roll back when any statement fails:

db.exec('BEGIN')

try {
  debit.run(20, 1)
  credit.run(20, 2)
  db.exec('COMMIT')
} catch (error) {
  db.exec('ROLLBACK')
  throw error
}

Run this against a fresh pair of accounts and confirm both balances change. Then add a constraint that makes the second update fail and confirm the first balance stays unchanged after the rollback.

When to start one explicitly

SQLite starts an implicit transaction for a single statement when you do not start one yourself. An explicit transaction matters when several statements share one business outcome, or when a batch of inserts should commit once instead of once per row.

BEGIN is deferred by default: SQLite does not grab a write lock until the first write. BEGIN IMMEDIATE tries to acquire the write lock right away. That is useful when you want to discover writer contention before doing preparatory reads.

Keep transactions short

SQLite allows multiple readers, but only one writer at a time. Do not open a transaction, call a remote API, wait for user input, and then commit. That holds the write lock while other writers wait or fail with SQLITE_BUSY.

Compute and validate first. Begin the transaction, run the related statements, commit, then do slow external work.

Try it: add a constraint that makes the second transfer update fail. Confirm that your error path rolls back and the first balance remains unchanged.