Thanks for your feedback. We can confirm that the database is in WAL mode. After some further digging, we have found out It only happens with the very first query. SQLite calls sqlite3InitOne when DbHasProperty(db, 1, DB_SchemaLoaded) is false. Apparently, the schema is loaded into memory via the common SHARED locking mechanism. So when multiple threads perform their first query at the very same moment, they lock each other out.
We assume that the locking of the PENDING byte during initialization does not lock out a writer on another already initialized connection. Can your confirm this? We can solve the issue by using our own locking mechanism around the first query, so that these queries never occur simultaneously. However, it would be great if there would be a more neat solution, because some of our databases have a lot of tables and therefore loading the database schema can cost a lot of time (relatively). Therefore, we would like to be able to open multiple parallel connections and initialize them at the same time. Do you have any suggestions for this? Best regards, Harmen de Jong -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: donderdag 18 juni 2015 13:00 To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled On 6/18/15, Harmen de Jong - CoachR Group B.V. <harmen at coachr.com> wrote: > 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, Please double-check that you really have WAL mode enabled. From the sqlite3.exe command-line shell run commands like: PRAGMA journal_mode; .dbinfo In the second command, the "write format" and "read format" should both be "2". I do not think you are really in WAL mode because a SHARED lock should never happen in WAL mode. > 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. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
