btw - would shared cache mode play a part in this behavior? for example, if i have shared cache mode enabled then i could have a different thread in this process that has a shared lock at the time my thread is trying to begin trans immediate. would this (effectively) cause a lock promotion from shared to reserved and thus fail immediately?
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent Sent: Monday, November 23, 2009 12:36 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] begin transaction if there are open read cursors this is not by design. i'm reviewing now and ensuring that read cursors are closed. will review more, but all the reads are behind APIs that explicitly finalize (or reset) at the end of the API call..at least this is the intent. it is possible that there is a bug but i've tried to be very explicit about finalizing/resetting statements. thanks tom -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Monday, November 23, 2009 11:34 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] begin transaction > what is interesting is that proc B's 'item not found in locker!' is a read > operation. so possibly i've (inadvertently) hit the 'shared lock upgrade to > reserved lock' immediate fail. > > thoughts? Did you reset/finalize statement after this read operation? Do you have any other active (not reset/finalized) select statements in the same connection in proc B when you try to begin immediate transaction? Pavel On Mon, Nov 23, 2009 at 2:28 PM, Tom Broadbent <tom.broadb...@plasticlogic.com> wrote: > thanks for the interesting comments. here is the log that (i believe) > illustrates the problem: > > 092250 PID:07E50002 TID:07E60002 PLFS!INFO: attempting to begin IMMEDIATE > transaction > 092260 PID:04310006 TID:0548000E PLFS!INFO: item not found in locker! > 092267 PID:04310006 TID:0548000E PLFS!INFO: attempting to begin IMMEDIATE > transaction > 092284 PID:07E50002 TID:07E60002 PLFS!INFO: attempting to end transaction > 092391 PID:04310006 TID:0548000E PLFS!ERROR: [SQLite] > PLFS::PLFSProxy::BeginTransaction: Error 5; (.\PLFSProxy.cpp) > 092399 PID:04310006 TID:0548000E PLFS!ERROR: > PLFS::PLFSProxy::BeginTransaction: Error 205 [PLFS_ERROR_DB_LOCK_FAILED]; > (.\PLFSProxy.cpp:3364) > 092412 PID:04310006 TID:0548000E PLFS!ERROR: PLFS::PLFSProxy::DeleteId: Error > 205 [PLFS_ERROR_DB_LOCK_FAILED]; (.\PLFSProxy.cpp:2025) > 092424 PID:04310006 TID:0548000E PLFS!ERROR: > PLFS::BnLockerProxy::SyncPlfsToLocker: Error 205 [PLFS_ERROR_DB_LOCK_FAILED]; > (.\PLFSBnLockerManager.cpp:272 > > what this shows (to me) is that proc A (07E50002) got the IMMEDIATE > transaction and proc B (04310006) failed to get an IMMEDIATE transaction. > > what is interesting is that proc B's 'item not found in locker!' is a read > operation. so possibly i've (inadvertently) hit the 'shared lock upgrade to > reserved lock' immediate fail. > > thoughts? > > thanks > tom > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov > Sent: Monday, November 23, 2009 7:36 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] begin transaction > >> That's true, but the comment is a bit deceptive, In this >> particular case SQLite is supposed to invoke the busy-handler. What >> should happen is that SQLite grabs the SHARED lock then fails to get >> the RESERVED. But in this case SQLite is smart enough to know that >> it can release the SHARED lock, invoke the busy-handler, then try >> again. > > I'm glad it is so. > BTW, this special case (with your great examples) as long as all cases > when busy handler is invoked/not invoked are better to be mentioned > somewhere in here http://www.sqlite.org/lockingv3.html or here > http://www.sqlite.org/c3ref/busy_handler.html. > > Pavel > > On Mon, Nov 23, 2009 at 10:17 AM, Dan Kennedy <danielk1...@gmail.com> wrote: >> >> On Nov 23, 2009, at 9:26 PM, Pavel Ivanov wrote: >> >>> According to SQLite sources: >>> >>> ** The pager invokes the busy-handler if sqlite3OsLock() returns >>> ** SQLITE_BUSY when trying to upgrade from no-lock to a SHARED lock, >>> ** or when trying to upgrade from a RESERVED lock to an EXCLUSIVE >>> ** lock. It does *not* invoke the busy handler when upgrading from >>> ** SHARED to RESERVED, or when upgrading from SHARED to EXCLUSIVE >>> ** (which occurs during hot-journal rollback). Summary: >>> ** >>> ** Transition | Invokes xBusyHandler >>> ** -------------------------------------------------------- >>> ** NO_LOCK -> SHARED_LOCK | Yes >>> ** SHARED_LOCK -> RESERVED_LOCK | No >>> ** SHARED_LOCK -> EXCLUSIVE_LOCK | No >>> ** RESERVED_LOCK -> EXCLUSIVE_LOCK | Yes >>> >>> >>> When you issue BEGIN IMMEDIATE statement SQLite has first obtain >>> SHARED lock and right after that RESERVED lock too. >> >> That's true, but the comment is a bit deceptive, In this >> particular case SQLite is supposed to invoke the busy-handler. What >> should happen is that SQLite grabs the SHARED lock then fails to get >> the RESERVED. But in this case SQLite is smart enough to know that >> it can release the SHARED lock, invoke the busy-handler, then try >> again. This is different from the situation where the connection >> already has a SHARED lock and tries to upgrade to reserved after >> the user has read from the db. >> >> For example, assuming that some other process has a RESERVED lock >> on our file: >> >> INSERT INTO ... /* busy-handler invoked */ >> >> BEGIN IMMEDIATE; /* busy-handler invoked */ >> >> BEGIN; >> INSERT INTO ... /* busy-handler invoked */ >> >> but if we try: >> >> BEGIN; >> SELECT * FROM ... /* Grab SHARED lock */ >> INSERT INTO ... /* Immediate SQLITE_BUSY */ >> >> The last case is different as SQLite cannot release the SHARED lock >> before calling the busy-handler (doing so might mean the transaction >> does not appear isolated to the user). So it cannot call the >> busy-handler without risking deadlock. Return immediately instead. >> >> I'm not sure why the OP's call is returning immediately without calling >> the busy-handler. >> >> Dan. >> >> >> >> >> >> >> >> >> >> >> >>> In your, Tom, >>> situation obtaining of the SHARED lock will succeed right away unless >>> proc A has already written something to disk but RESERVED lock will >>> fail and no busy handler is called here. >>> >>> Besides that I cannot say what thoughts are behind this design >>> solution. >>> >>> >>> Pavel >>> >>> On Mon, Nov 23, 2009 at 9:15 AM, O'Neill, Owen <oone...@averyberkel.com >>> > wrote: >>>> >>>> >>>> Hi Tom, >>>> >>>> Whilst not knowing much about the process, I have a recollection >>>> about >>>> something in the documentation that said if sqlite thought that there >>>> was a potential for deadlock the busy handler was never even called. >>>> >>>> Could that explain this ? >>>> >>>> Cheers >>>> Owen >>>> >>>> >>>> >>>> -----Original Message----- >>>> From: sqlite-users-boun...@sqlite.org >>>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent >>>> Sent: Friday, November 20, 2009 7:27 PM >>>> To: sqlite-users@sqlite.org >>>> Subject: [sqlite] begin transaction >>>> >>>> i have several processes all trying to write to a given DB. i have >>>> set >>>> sqlite3_busy_timeout to 10000 (10 sec). when proc A successfully >>>> beings >>>> a trasaction IMMEDIATE what appears to happen when proc B attempts to >>>> begin transaction IMMEDIATE is that it fails immediately w/ >>>> SQLITE_BUSY. >>>> i would expect that proc B would wait 10 sec trying to begin >>>> transaction >>>> rather than fail immediately. >>>> >>>> is this expected? >>>> >>>> thanks >>>> tom >>>> >>>> ______________________________________________________________________ >>>> This email has been scanned by the MessageLabs Email Security System. >>>> For more information please visit http://www.messagelabs.com/email >>>> ______________________________________________________________________ >>>> _______________________________________________ >>>> 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 >> >> _______________________________________________ >> 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 > > ______________________________________________________________________ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > ______________________________________________________________________ > > ______________________________________________________________________ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > ______________________________________________________________________ > _______________________________________________ > 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 ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
