ACID transactions in practice

An ACID transaction is a boundary: every write lands or none does. Measured over 200 single-row transactions in SQLite 3.51.3, a commit costs 0.330 ms with synchronous FULL and 0.018 ms with NORMAL: the gap is one synchronisation call to disk.

What ACID promises, in the order it breaks

Atomicity means the group of writes is indivisible. Consistency means constraints hold at the end: a non-negative balance, an existing foreign key. Isolation means another connection never sees an intermediate state. Durability means a commit survives a crash. The first three break through code mistakes, the fourth breaks through a disk setting.

The rollback, measured

With two accounts of 100, a debit of 60 on the first and one of 150 on the second, the second UPDATE violates the CHECK and the transaction fails. After ROLLBACK both balances are still 100. The first write is gone: that behaviour is what makes a transaction useful, and it does not depend on disk speed.

The price of a commit

With synchronous FULL the commit writes the WAL and asks the disk to confirm: 0.330 ms per transaction on this machine. With NORMAL the confirmation waits for a checkpoint and the cost drops to 0.018 ms. The SQLite documentation, read on 1 November 2026, warns that in WAL mode NORMAL can lose recent commits after a power loss, while OFF is not advised even when only the application crashes.

node -e "const {DatabaseSync}=require('node:sqlite');const db=new DatabaseSync('app.db');db.exec('PRAGMA journal_mode=WAL');db.exec('BEGIN IMMEDIATE');try{db.exec('UPDATE accounts SET balance=balance-60 WHERE id=1');db.exec('UPDATE accounts SET balance=balance-150 WHERE id=2');db.exec('COMMIT')}catch(e){db.exec('ROLLBACK');console.log('rolled back:',e.message)}"

Short transactions, not long ones

A transaction should hold only the work that depends on the database. If an HTTP call, a file upload or a queue wait sits inside it, the lock stays open for the whole wait and other writes queue behind it. The practical rule is: read, decide, write, close. When a decision needs an external answer, get it first and retry if the state changed, instead of holding the transaction open.

Where the boundary ends

In PostgreSQL the default level is READ COMMITTED: each statement sees commits made before it started, so two reads inside one transaction can return different values. SQLite, with one writer at a time, offers simpler isolation. If the code assumes repeatable reads, the level must be declared: it is a programmer's choice, not a database detail.

The limit: durability is not atomicity

Lowering synchronous makes commits fast without breaking atomicity: rollback still works, but a crash can erase the last committed transactions. For a ledger that is unacceptable; for a preferences cache it does not matter. And no setting protects you from a disk that reports a write it never made: durability ends where the hardware ends.

Where to start