Database / SQLite Interview questions
How does SQLite ensure ACID compliance without a separate server process?
ACID guarantees are typically associated with a server process coordinating access, but SQLite achieves the same guarantees entirely within the library linked into the application, using file-level locking and journaling (rollback journal or WAL) as its coordination mechanism instead of a server.
- Atomicity — the journal/WAL ensures a transaction's changes are either fully applied or fully rolled back, even across a crash.
- Consistency — constraints (primary key, unique, foreign key when enabled, check constraints) are validated before a transaction is allowed to commit.
- Isolation — the locking states (SHARED/RESERVED/EXCLUSIVE, or WAL's snapshot mechanism) prevent one transaction from seeing another's uncommitted changes.
- Durability — committed data is flushed (fsynced) to disk so it survives a subsequent crash or power loss.
The key insight is that ACID compliance is a property of how data is written and coordinated, not something that inherently requires a separate server — SQLite just implements all of that coordination logic directly inside the linked-in library instead of a remote process.
More Related questions...