Snapshot Isolation (SI)
SQLite implements a single isolation level between transactions:
'serializable'. It is implemented such that it allows many concurrent
read operations, but only a single concurrent write operation. This means
that a write can only occur once all pending reads have finished and that
new reads can only start once the write has finished. For many use cases
this is not a problem, including serving low-to-medium volume web
However, to scale further it is desirable to offer 'snapshot isolation'
between transactions. This is slightly less strict isolation than
'serializable', but it allows a design where readers do not block writers
and vice versa. It was first mentioned in a 1978 doctoral thesis by
The first database to implement this scheme was the 1984 product Edb/Rln by
Digital Equipment. Today nearly all databases offer this isolation level.
Snapshot Isolation works by keeping multiple versions of the data in
the database, each version reflecting a consistent state of the database.
This is referred to under various names, such as 'Multigenerational Architecture, MGA'
(Rdb/Eln, Interbase, Firebird), 'Multiversion Concurreny Control, MVCC' (Oracle,
Postgres) or 'Row Level Versioning, RLV' (SQLServer).
Implementing snapshot isolation requires more communication between
SQLite processes than file locks can offer (file locks are the synchronisation
mechanism used in SQLite from version 2.0.0 to the current version). The
challenge is to find an efficient mechanism that works with embedded SQLite
libraries. Perhaps SI should be an option only for client/server enhancements of
SQLite, where 'disk bounce' communication delays can be avoided.