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 applications.

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 David Reed. 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.

Reading Material

Wikipedia lemma

Architecture comparisons

Postgres approach

Microsoft approach