On 02/18/2015 06:53 AM, Tim Starling wrote:
> We (Wikimedia) are observing SQLITE_BUSY errors in our integration
> testing. The integration test consists of having a single browser
> instance view a MediaWiki site which uses SQLite 3.8 for its backend.
> The browser sends several parallel requests for CSS, JS, etc., and
> MediaWiki writes to the SQLite database while servicing each of these
> requests. Thus there is some lock contention.
>
> In strace we see SQLite sleeping when it fails to acquire a SHARED
> lock, but when it tries to acquire a RESERVED lock, no sleep is done,
> and an error is immediately reported to the application.
If you already have a busy-handler or busy-timeout configured but it is
not being used when upgrading to a RESERVED lock, it is to avoid
deadlock. Is the transaction something like:
BEGIN;
SELECT .... /* SHARED lock on database */
INSERT ... /* RESERVED lock on database */
In this case, blocking on the RESERVED lock is no good. The process
currently holding it is a writer that will need to obtain the EXCLUSIVE
lock in order to commit its transaction. And obtaining the EXCLUSIVE
lock will of course require it to wait until all readers release their
SHARED locks - including the reader waiting on RESERVED. So deadlock.
One fix is to make sure that an active read-transaction is never
upgraded to a write-transaction. Do this by making all transactions that
may write to the database write-transactions from the start. Either by
opening them with "BEGIN IMMEDIATE" or making sure that the first
statement in the transaction is a writer.
BEGIN IMMEDIATE; /* Blocks for RESERVED lock */
SELECT ....
INSERT ...
Dan.
>
> https://phabricator.wikimedia.org/T89180
>
> The relevant code has a comment indicating that this is expected
> behaviour:
>
> /* Obtain a RESERVED lock on the database file. If the exFlag parameter
> ** is true, then immediately upgrade this to an EXCLUSIVE lock. The
> ** busy-handler callback can be used when upgrading to the EXCLUSIVE
> ** lock, but not when obtaining the RESERVED lock.
> */
> rc = pagerLockDb(pPager, RESERVED_LOCK);
> if( rc==SQLITE_OK && exFlag ){
> rc = pager_wait_on_lock(pPager, EXCLUSIVE_LOCK);
> }
>
>
> Is it possible to open a bug or feature request for making SQLite wait
> on RESERVED locks?
>
> Also, do you have any suggestions for a workaround?
>
> -- Tim Starling
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
