To answer my own question...
TM> We're upgrading an app from 2.8.11 to 3.0.7 and are seeing fairly
TM> frequent "database is locked" errors that would be explained if this
TM> were not the case. It seems to be happening when two threads start a
TM> transaction; one does its first INSERT ok but the second one gets a
TM> SQLITE_BUSY error when it tries to INSERT.
This is from sqlite3pager_begin() in pager.c:
/* We do not call the busy handler when we fail to get a reserved lock.
** The only reason we might fail is because another process is holding
** the reserved lock. But the other process will not be able to
** release its reserved lock until this process releases its shared
** lock. So we might as well fail in this process, let it release
** its shared lock so that the other process can commit.
*/
This is really problematic. Consider the following common scenario:
THREAD 1 THREAD 2
-------- --------
1) INSERT INTO ...
2) INSERT INTO ...
3) INSERT completes
4) INSERT completes
Under 2.8.11, thread 2 would block at (2) until thread 1 completed its
update. Under 3.0.7, thread 2 continues on to but gets a SQLITE_BUSY
error. Furthermore, there is no way to handle it other than to roll
everything back and try again. In other words, every single place where
we write to the database, we now have to add code to detect SQLITE_BUSY
and retry. This is clearly not desirable - can anyone suggest another
solution? We could shunt all the database updates off to a single thread
but that's ovbiously not great either :-(
