I took traces of the lock/unlock pattern -
After App1 SELECT ------------------ fcntl -1212610880 7 SETLK WRLCK 124 1 0 0 WAL806F9D8: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 ok fcntl -1212610880 7 SETLK UNLCK 124 1 0 0 WAL806F9D8: release EXCLUSIVE-READ-LOCK[1] cnt=1 fcntl -1212610880 7 SETLK RDLCK 124 1 0 0 WAL806F9D8: acquire SHARED-READ-LOCK[1] ok After App2 UPDATE ----------------- fcntl -1212344640 7 SETLK RDLCK 124 1 0 0 WAL806F570: acquire SHARED-READ-LOCK[1] ok fcntl -1212344640 7 SETLK WRLCK 120 1 0 0 WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok WAL806F570: frame write ok fcntl -1212344640 7 SETLK UNLCK 120 1 0 0 WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1 fcntl -1212344640 7 SETLK UNLCK 124 1 0 0 WAL806F570: release SHARED-READ-LOCK[1] After App1 SELECT ------------------ fcntl -1212610880 7 SETLK WRLCK 120 1 0 0 WAL806F9D8: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok fcntl -1212610880 7 SETLK UNLCK 120 1 0 0 WAL806F9D8: release EXCLUSIVE-WRITE-LOCK cnt=1 error @ 225, 1,5,database is locked After App2 UPDATE ( a second UPDATE ) ------------------------------------- fcntl -1212344640 7 SETLK WRLCK 124 1 0 -1 fcntl-failure-reason: RDLCK 124 1 15295 WAL806F570: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 failed fcntl -1212344640 7 SETLK WRLCK 125 1 0 0 WAL806F570: acquire EXCLUSIVE-READ-LOCK[2] cnt=1 ok fcntl -1212344640 7 SETLK UNLCK 125 1 0 0 WAL806F570: release EXCLUSIVE-READ-LOCK[2] cnt=1 fcntl -1212344640 7 SETLK RDLCK 125 1 0 0 WAL806F570: acquire SHARED-READ-LOCK[2] ok fcntl -1212344640 7 SETLK WRLCK 120 1 0 0 WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok fcntl -1212344640 7 SETLK UNLCK 120 1 0 0 WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1 fcntl -1212344640 7 SETLK UNLCK 125 1 0 0 WAL806F570: release SHARED-READ-LOCK[2] Additional Info - You can also see that acquiring an EXCLUSIVE-READ-LOCK FAILED during the a second update by App2. The fcntl error translated means "resource temporarily unavailable" - Sreekumar On Fri, Feb 10, 2012 at 8:31 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > One last question or series (I hope)... > > From my background, I'm used to SQL statements blocking until appropriate > locks are acquired. From what I've seen, it looks like sqlite doesn't > block, but returns BUSY, is that correct? > > If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will > it block and wait? > > If it returns BUSY, how does sqlite3_exec() handle it? > > (Btw.. I know there are books on sqlite, so if you want to point me to one > that answers all these questions, or if I'm missing it from the online > docs, just let me know. I just haven't found them.) > > Thanks > > > -----Original Message----- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Marc L. Allen > > Sent: Friday, February 10, 2012 9:45 AM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Database locked in multi process scenario > > > > Thanks so much for clarifying that. I was unaware of the BEGIN > > IMMEDIATE. Sorry.. new to sqlite, used to MySQL and MSSQL. > > > > > -----Original Message----- > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > > boun...@sqlite.org] On Behalf Of Igor Tandetnik > > > Sent: Friday, February 10, 2012 9:43 AM > > > To: sqlite-users@sqlite.org > > > Subject: Re: [sqlite] Database locked in multi process scenario > > > > > > Marc L. Allen <mlal...@outsitenetworks.com> wrote: > > > > I see. So, the implied commit doesn't occur until you finalize? > > > > > > Or reset. > > > > > > > As a result, the subsequent update in step 5 was added to his > > > > non-finalized select? > > > > > > The update was attempted within the same transaction. > > > > > > > Still.. what is the correct way to handle the explicit scenario? I > > > > mean, having one process do a BEGIN SELECT UPDATE and another do > > > BEGIN > > > > UPDATE is perfectly reasonable, isn't it? How do you protect from > > a > > > problem? Detect the error, rollback, and try again? > > > > > > That's one way. The other is for the first connection to start its > > > transaction with BEGIN IMMEDIATE, thus marking itself as a writer > > from > > > the start. > > > -- > > > Igor Tandetnik > > > > > > _______________________________________________ > > > 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
