On 06/23/2015 03:49 PM, Harmen de Jong - CoachR Group B.V. wrote: > Is there anyone who can confirm that the first read transaction with WAL > enabled locks the database because it triggers the SchemaLoaded?
When a database in wal mode is first queried, SQLite takes a SHARED lock on the file. This is the same SHARED lock that is used when reading in rollback mode - except in wal mode it is held until the connection is closed. And acquiring a SHARED lock requires temporarily obtaining a PENDING lock: http://www.sqlite.org/src/artifact/27cc135e2d0b8?ln=3030-3032 So yes, I think your analysis is correct. Dan. > > Does anybody have a better suggestion to circumvent this locking issue than > serializing the first read transaction? > > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Harmen > de Jong - CoachR Group B.V. > Sent: donderdag 18 juni 2015 15:02 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite PENDING lock before SHARED lock with WAL enabled > > 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-bounces at 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
