> 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
