How SQLite helps you do ACID

Summary

SQLite’s rollback journal provides atomicity, isolation, and durability by preserving database state before a transaction changes the main file. Without journaling, an interrupted 4KB page write could corrupt the database; the journal instead records original pages, then permits recovery if commit steps fail. A journal header stores a nonce, the database’s pre-transaction page count, sector size, and page size, while each page record includes its page number, a copy of prior contents, and a 32-bit checksum. The mode allows concurrent readers but makes writers wait for readers and blocks new readers, using SHARED, RESERVED, and PENDING filesystem locks. Its simplicity brings a concurrency bottleneck; WAL and wal2 provide better support for concurrent readers, which the post says will be covered next.

Takeaways

1. 1

   The rollback journal stores original database pages before they are changed, allowing SQLite to restore the prior state if a transaction fails before commit.

2. 2

   Rollback-journal mode permits multiple read-only transactions but allows only one write transaction and blocks new readers while that write completes.

3. 3

   SQLite’s rollback journal can bottleneck concurrent applications because writes wait for readers; WAL and wal2 were introduced to improve support for concurrent readers.