Almost. Locking happens at a table level in this case, not a database level. Three different threads can all write at the same time, if they write to different tables. But, if two threads write try to the same table at the same time, one of them will return SQLITE_LOCKED.
John -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent Sent: Wednesday, October 28, 2009 3:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' oh, right. my bad. i don't mean to share a connection between two threads, but rather that each thread (with its own connection) in the same process where shared cache mode is enabled will cause SQLITE_LOCKED error rather than SQLITE_BUSY error when these threads contend for the DB. is this right? -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw Sent: Wednesday, October 28, 2009 12:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' I don't know. Elsewhere it says you really shouldn't use the same connection in multiple threads. I use a different connection in each thread. With the shared cache, this results in very little overhead, so I'm unsure why you would need to do this the "not recommended" way. The contention between connections only applies to other processes if the shared cache is enabled. With the shared cache each process will lock the whole database, but connections in threads within that process will only lock individual tables. This is really the right way to do a multithreaded application, because otherwise contention is too great. John -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent Sent: Wednesday, October 28, 2009 3:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' to be clear... "in other words, two threads sharing a connection in shared cache mode will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when contention occurs _between the two threads_. if contention occurs from another connection (i.e. a connection in a different process) SQLITE_BUSY will be returned." i believe this is correct. experts? -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tom Broadbent Sent: Wednesday, October 28, 2009 12:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' i'm no expert on this, but my understanding is that since shared cache mode 'shares a connection' you won't get SQLITE_BUSY but rather SQLITE_LOCKED since the contention is 'internal' to the connection. in other words, two threads sharing a connection in shared cache mode will always cause SQLITE_LOCKED (rather than SQLITE_BUSY) when contention occurs. experts: pls correct me if i'm wrong here. here is the advice i received when asking about a similar situation: >> If other threads may also need a write lock on that table, you should >> handle SQLITE_LOCKED by incrementing a waiter count and calling >> sqlite3_unlock_notify. The thread doing the inserting can check to >> see if anybody is waiting (blocked) and yield by committing the >> current transaction and waiting for the blocked thread to unblock. Be >> aware, you should also close any open cursors before yielding, >> because open cursors will prevent write locks and you'll waste time >> yielding for nothing. >> >> John hope this helps (and isn't incorrect). thanks tom -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of O'Neill, Owen Sent: Wednesday, October 28, 2009 10:45 AM To: General Discussion of SQLite Database Subject: [sqlite] shared cache mode and 'LOCKED' Hi Everyone, Does anyone know if this page is still up to date with respect to when you get "SQLITE_LOCKED" when operating in shared cache mode ? http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked (I'm trying to solve a two writers problem and am trying to understand the best way to solve it) (and I think part of my problem is not understanding the difference between 'locked' and 'busy' ) I've seen the notify example here. http://www.sqlite.org/unlock_notify.html and a 'busy' example here http://www.sqlite.org/cvstrac/wiki?p=MultiThreading http://www.sqlite.org/cvstrac/wiki?p=SampleCode Is it possible for something to be 'locked' then after being unlocked at it tries again it gets 'busy' ? Should my re-try strategy be the same or different for 'busy' and 'locked'.... and I guess if I get SQLITE_IOERR_BLOCKED (http://www.sqlite.org/c3ref/busy_handler.html) I should always back off and wait a while ? Any tips for the 'best' way to tackle this gratefully received. (I have one thread writing a lot but it can block for a 'long' time and still be ok (up to 5 seconds) - and another one mostly reading and doing a few occasional writes, but it can't block for long (>250ms) because it's servicing the UI and repainting will stop.) many thanks Owen _______________________________________________ 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 _______________________________________________ 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
