A SELECT query obtains a SHARED lock on the database file. Doing so, there can
be many simultaneous readers according to the documentation at
https://www.sqlite.org/lockingv3.html. However, before the SHARED lock is
obtained in winLock(sqlite3_file *id, int locktype), a temporary PENDING lock
is requested.
So when multiple threads start a SELECT query on different sqlite3* objects at
the exact same time, some of them will result in a SQLITE_BUSY error, because
they all request the exclusive PENDING lock. Is there a way to work around
this, so that I can execute SELECT queries really simultaneously?
We have WAL enabled, so we were under the assumption that it should be possible
to have multiple readers at the same time, however the UNLOCKED -> PENDING ->
SHARED transition seems to intervene with the principle of having multiple
readers at the same time.
This is the callstack of one of the threads:
> SQLService.exe!winLock(sqlite3_file * id=0x0e42aab0, int
> locktype=1) Line 35956
SQLService.exe!sqlite3OsLock(sqlite3_file * id=0x0e42aab0, int
lockType=1) Line 16294
SQLService.exe!pagerLockDb(Pager * pPager=0x0e42a998, int
eLock=1) Line 42356
SQLService.exe!pager_wait_on_lock(Pager * pPager=0x0e42a998, int
locktype=1) Line 45027
SQLService.exe!sqlite3PagerSharedLock(Pager * pPager=0x0e42a998)
Line 46249
SQLService.exe!lockBtree(BtShared * pBt=0x0e429ea0) Line 55204
SQLService.exe!sqlite3BtreeBeginTrans(Btree * p=0x0e428250, int
wrflag=0) Line 55562
SQLService.exe!sqlite3InitOne(sqlite3 * db=0x0e427e38, int
iDb=0, char * * pzErrMsg=0x0d861654) Line 104635
SQLService.exe!sqlite3Init(sqlite3 * db=0x0e427e38, char * *
pzErrMsg=0x0d861654) Line 104814
SQLService.exe!sqlite3ReadSchema(Parse * pParse=0x0d861650) Line
104851
SQLService.exe!sqlite3LocateTable(Parse * pParse=0x0d861650, int
isView=0, const char * zName=0x0dd4ad38, const char * zDbase=0x00000000) Line
90098
SQLService.exe!sqlite3LocateTableItem(Parse * pParse=0x0d861650,
int isView=0, SrcList_item * p=0x0dd4adc0) Line 90143
SQLService.exe!selectExpander(Walker * pWalker=0x0cd6ec58,
Select * p=0x0dd4acb8) Line 109480
SQLService.exe!sqlite3WalkSelect(Walker * pWalker=0x0cd6ec58,
Select * p=0x0dd4acb8) Line 80316
SQLService.exe!sqlite3SelectExpand(Parse * pParse=0x0d861650,
Select * pSelect=0x0dd4acb8) Line 109726
SQLService.exe!sqlite3SelectPrep(Parse * pParse=0x0d861650,
Select * p=0x0dd4acb8, NameContext * pOuterNC=0x00000000) Line 109812
SQLService.exe!sqlite3Select(Parse * pParse=0x0d861650, Select *
p=0x0dd4acb8, SelectDest * pDest=0x0cd6ef70) Line 110055
SQLService.exe!yy_reduce(yyParser * yypParser=0x0d8665c0, int
yyruleno=111) Line 124055
SQLService.exe!sqlite3Parser(void * yyp=0x0d8665c0, int
yymajor=1, Token yyminor={...}, Parse * pParse=0x0d861650) Line 125147
SQLService.exe!sqlite3RunParser(Parse * pParse=0x0d861650, const
char * zSql=0x0141a4c4, char * * pzErrMsg=0x0cd6f09c) Line 125987
SQLService.exe!sqlite3Prepare(sqlite3 * db=0x0e427e38, const
char * zSql=0x0141a4c4, int nBytes=-1, int saveSqlFlag=1, Vdbe *
pReprepare=0x00000000, sqlite3_stmt * * ppStmt=0x0cd6f138, const char * *
pzTail=0x0cd6f144) Line 105039
SQLService.exe!sqlite3LockAndPrepare(sqlite3 * db=0x0e427e38,
const char * zSql=0x0141a4c4, int nBytes=-1, int saveSqlFlag=1, Vdbe *
pOld=0x00000000, sqlite3_stmt * * ppStmt=0x0cd6f138, const char * *
pzTail=0x0cd6f144) Line 105139
SQLService.exe!sqlite3_prepare_v2(sqlite3 * db=0x0e427e38, const
char * zSql=0x0141a4c4, int nBytes=-1, sqlite3_stmt * * ppStmt=0x0cd6f138,
const char * * pzTail=0x0cd6f144) Line 105215
SQLService.exe!sqlite3_exec(sqlite3 * db=0x0e427e38, const char
* zSql=0x0141a4c4, int (void *, int, char * *, char * *) *
xCallback=0x00000000, void * pArg=0x00000000, char * * pzErrMsg=0x00000000)
Line 100638
Best regards,
Harmen de Jong
CoachR Group B.V.
