Database / SQLite Interview questions
Explain the lifecycle of a SQLite transaction from BEGIN to COMMIT?
Calling BEGIN starts an explicit transaction, and every statement afterward operates within it
until either COMMIT makes the changes permanent or ROLLBACK discards them.
flowchart TD
A[BEGIN] --> B[Statements execute, journal/WAL records original or new page state]
B --> C{COMMIT or ROLLBACK?}
C -->|COMMIT| D[Changes finalized; journal deleted or WAL checkpointed]
C -->|ROLLBACK| E[Journal used to restore original data; changes discarded]
Under the hood, in rollback journal mode, SQLite copies each page's original content into the journal file
before modifying it in the main database, so a ROLLBACK simply restores those original pages. On
COMMIT, the journal file is deleted (its job done) and the modified pages remain in place. In WAL
mode, the mechanics differ (new pages go to the WAL rather than the journal), but the guarantee is the same:
until COMMIT actually completes, an interrupted transaction (a crash, a ROLLBACK)
leaves the database exactly as it was beforehand.
What does SQLite do with each page's original content before modifying it, in rollback journal mode?
What guarantee holds if a crash occurs before COMMIT completes?
More Related questions...
