Transactions and Locking
Try it: Transactions and Locking
How a transaction groups statements so they commit or roll back together, why a session's uncommitted changes are invisible to other sessions, and how SQLite's locks let many readers in but only one writer — producing "database is locked".
How it works
- BEGIN starts a transaction but takes no lock yet. The first read takes a SHARED lock; the first write takes the single RESERVED (write) lock.
- Writes go into the writer's private copy of the pages; other sessions keep reading the last committed data.
- A second session that tries to write while the first holds the write lock gets "database is locked".
- COMMIT needs every other reader to be gone (EXCLUSIVE lock); if one is still in a read transaction, COMMIT fails with "database is locked" and the transaction stays open. ROLLBACK discards the changes.
- Outside BEGIN, every statement is its own transaction and commits at once (autocommit).
Default run (3 steps): Two sessions, A and B, share one database file. Committed: 1 Ana 100, 2 Ben 50. Locks: A NONE, B NONE. Run a statement in either session. Example: A runs BEGIN and UPDATE, then B tries to UPDATE. … B needs the RESERVED (write) lock, but A already holds RESERVED — only one writer at a time → Error: database is locked.
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Models SQLite's default rollback-journal mode with two connections to one file and no busy timeout (WAL mode lets readers and a writer overlap). Locks are shown per session; pages and the journal are not drawn. Every statement's outcome was checked against two real sqlite3 connections.
Loading the simulation…