There seems to be a few misconceptions in the article regarding the difference between DEFERRED/IMMEDIATE/EXCLUSIVE, and the fine details of how different lock states interact.
Specifically, your diagrams suggest that once a writer obtains a RESERVED lock (as happens when an IMMEDIATE transaction begins), no readers will be able to obtain a SHARED lock. But this is not the case - while a process holds the RESERVED lock it's only other attempts to _write_ the database which will be met with SQLITE_BUSY. It's only once the writer upgrades to a PENDING lock that readers will be rejected via SQLITE_BUSY. Historical note: this wasn't part of sqlite's original locking protocol, but was added later to solve a writer starvation problem. Anyway, the RESERVED -> PENDING transition only happens when either (a) the writer is ready to commit the transaction, or (b) there's a cache spill, ie. the transaction has modified more database page then will fit in the configured cache (see pragmas cache_size and cache_spill for more info). It looks like you go into more detail on the locks later in the article - I only got to the "Shared cache mode" section. The content before that felt misleading in terms of reader/writer locking interaction. I think "single writer, multiple readers" is the simplest way to describe sqlite's approach to isolation, but I'm also pretty biased because I have a _lot_ more experience with sqlite compared to other DBs and I'm only just getting my head around the idea of non-isolated transactions or multiple concurrent writers! -Rowan On Wed, 9 Jan 2019 at 21:48, Rahul Jayaraman <rahul.jayara...@hotmail.com> wrote: > I wrote an article about my high-level understanding of `SQLITE_BUSY` > errors, hoping it might help others understand concurrency in SQLite > better. It covers scenarios under which the error shows up, while SQLite > tries to respect its isolation guarantee. > > https://www.activesphere.com/blog/2018/12/24/understanding-sqlite-busy > > I’d appreciate feedback on the article. Apologies if this is the wrong > place to post such content. > > Thanks, > Rahul > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
