On 7 August 2018 at 21:25, David Raymond <david.raym...@tomtom.com> wrote:
> Correct. > > In rollback journal mode when one connection says "I'm ready to write now" > it blocks any new transactions from being made, but it can't do anything > about existing read transactions. It has to wait for them to finish their > reads and end their transactions before it can do any actual writing. > To clarify, the "I'm ready to write now" phase only happens at the end of a transaction. Each SQL command implies different lock requests: BEGIN -> is a no-op, it just opens a transaction BEGIN IMMEDIATE -> says "I want to write at some point in the future" (aka RESERVED lock) SELECT -> says "I want to read now" (aka SHARED lock) INSERT/UPDATE/DELETE -> says "I want to write at some point in the future" (aka RESERVED lock) COMMIT (with a RESERVED lock) -> says "I'm ready to write now" (aka PENDING lock) Obtaining a RESERVED lock will fail if any other process already has the RESERVED lock - ie. there can only be one writer. But RESERVED does not block SHARED; other processes can still read. It's only when the writer is ready to commit and obtains the PENDING lock that attempts to obtain a SHARED lock will fail. This is to prevent writer starvation eg. in the case of constant read queries. Once all pending read transactions are finished, the writer obtains the EXCLUSIVE lock and actually updates the main database file. Then all locks are relinquished and everything is fair game again. So in general the writer doesn't need to block readers for very long. The exception is a large write transaction, which can blow sqlite's memory cache and cause it to obtain PENDING+EXCLUSIVE before COMMIT happens (see the cache_size pragma). More problematic is that a long read transaction ends up blocking other readers if a writer wants to COMMIT in the same period. I *think* WAL mode can help with that, but I don't have much experience with it. -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
