On Aug 25, 2010, at 10:40 PM, Pavel Ivanov wrote: > Nikolaus, > > I've traced your application a bit (with SQLite 3.6.18 sources) and > it looks like SQLite does some nasty thing nobody in this thread > expected. For some reason while doing first delete SQLite actually > commits transaction and degrades lock to SHARED. Then of course second > delete cannot be executed because update has already PENDING lock. > That's pretty strange and indeed reading sources of sqlite3VdbeHalt I > didn't find any condition for committing other than successfulness of > the statement. Maybe somebody somewhere forgot to set autocommit to 0 > when select started executing? > > Dan, can you shed some light on this strange behavior?
When you commit a transaction, SQLite upgrades to an EXCLUSIVE lock so that it can write to the database file. Once it has finished committing the transaction it drops back to a SHARED lock if there are still active SELECT statements running, or to no lock at all if there are no other active SELECTs. In the case where there are active SELECT statements remaining when a transaction is committed, SQLite cannot drop all locks, as this would leave the SELECT statements reading from the database file holding no lock at all. Prior to version 3.6.5 SQLite used to delay committing the transaction until all SELECT statements had finished. But that behavior was deemed to be less intuitive. Dan. > > > Pavel > > On Wed, Aug 25, 2010 at 10:39 AM, Nikolaus Rath <nikol...@rath.org> > wrote: >> Hi, >> >> I only saw http://article.gmane.org/gmane.comp.db.sqlite.general/58835 >> , >> was there anything else? >> >> -Nikolaus >> >> >> Gerry Snyder <mesmerizerfan- >> re5jqeeqqe8avxtiumw...@public.gmane.org> writes: >>> Er, did you not see Dan Kennedy's comments a fed days ago?? >>> >>> On 8/24/10, Nikolaus Rath <nikolaus-bth8mxji...@public.gmane.org> >>> wrote: >>>> Nikolaus Rath >>>> <nikolaus-bth8mxji4b0-xmd5yjdbdmrexy1tmh2...@public.gmane.org >>>> > writes: >>>>> Still no one able to clarify the issues raised in this thread? >>>>> >>>>> Let me try to summarize what I still don't understand: >>>>> >>>>> - Will SQLite acquire and release an EXCLUSIVE lock while >>>>> keeping a >>>>> SHARED lock if one executes a UPDATE query with one cursor >>>>> while a >>>>> different cursor is in the middle of a SELECT query, >>>>> >>>>> -or- >>>>> >>>>> will the EXCLUSIVE lock be held until the SELECT query >>>>> finishes? >>>>> >>>>> - Is there a way to prevent SQLite from keeping the SHARED lock >>>>> while waiting for an EXCLUSIVE lock if doing so would result >>>>> in a >>>>> deadlock (because another connection holding a SHARED lock >>>>> needs to >>>>> get an EXCLUSIVE lock before it can release the SHARED lock)? >>>> >>>> >>>> Hmm. Still no answer. But thanks to Simon I know at least that some >>>> people are reading this thread :-). >>>> >>>> >>>> So different question: does anyone know how to get this thread to >>>> the >>>> attention of an SQLite developer who might be able to help? >>>> >>>> >>>> Best, >>>> >>>> -Nikolaus >>>> >>>> -- >>>> »Time flies like an arrow, fruit flies like a Banana.« >>>> >>>> PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E >>>> 425C >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>> >>> >>> -- >>> Sent from my mobile device >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> -Nikolaus >> >> -- >> »Time flies like an arrow, fruit flies like a Banana.« >> >> PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
