SQLite Transactions

reorchestrate.com

2 pointstodsacerdoti3 comments

> SQLite, by default, offers strict SERIALIZABLE Isolated transactions which is the strongest isolation guarantee

It's not. SQLite is READ COMMITTED at best. Yes, it serializes writes, but it doesn't imply it serializes corresponding reads. Transactions could be easily interleaved and lead to races.

It's amazing how this falsehood is prevalent among devs.

https://www.sqlite.org/isolation.html

"In WAL mode, SQLite exhibits "snapshot isolation".

When a read transaction starts, that reader continues to see an unchanging "snapshot" of the database file as it existed at the moment in time when the read transaction started.

Any write transactions that commit while the read transaction is active are still invisible to the read transaction, ..."

Yes, snapshot isolation is a way to implement READ COMMITTED level.

Reply on news.ycombinator.com