ACID properties

Atomicity: Treats a transaction as a single, indivisible unit. Either all operations succeed completely, or the entire transaction is rolled back and none are applied. Consistency: Ensures the database moves from one valid state to another. It obeys all defined rules, constraints, and relationships. Isolation: Keeps concurrent transactions independent so they do not interfere with each other. The result matches sequential execution. Durability: Guarantees that once a transaction is committed, its changes are permanent. They survive system crashes or power failures

How does SQLite3 rollback journal mode guarantees ACID?

Atomicity

All of the data is backed by memory pages. When a writer writes a transection (e.g. Alice.amount -= 100; Bob.amount += 100),

1. Save the original contents involving Alice and Bob to disk (called rollback-journals).
2. The existing content is then modified and committed/flushed to disk.
3. The rollback journal is then deleted. This is also the commit point of the transaction.

If at any point there is a power failure, the journal is then recovered and applied back to the disk. This is as if the transection did not occur.

Consistency

When creating a SQLite3 table, rules can be added to column, and when inserting, something checks that the invariant/rules is obeyed.

Isolation

SQLite only allow 1 writer (using an exclusive lock (no readers allowed)), so it is as if its a sequential execution.

Durability

This is done by utilizing the guarantee of a persistent disk/SSD. fsync is used to force the OS to flush to the disk, and blocks until the OS returns from flushing data to the disk. This ensures that any commits is persisted in disk before SQLite continues running.

How does SQLite3 Write Ahead Log(WAL) mode guarantees ACID?

Atomicity

On a transaction, write a “start marker” to the WAL, then after transection completes, append to the WAL a “commit” marker to indicate that the transaction has ended. If a “commit” marker is not appended to the log, it is as if the transection did not occur. Because there is only 1 writer, any interleaving writes between multiple writers is not expected.

Consistency

Same as rollback-journal mode

Isolation

Same as rollback-journal mode

Durability

There are two synchronization modes for WAL: synchronous=full implies that every write transaction is flushed to the persistent disk while for synchronous=NORMAL, the WAL is batched and only occasionally flushed to disk. This provides better performance but in the event of a crash, uncommitted data in the WAL is lost.