Thankyou John and Tom ! I feel more enlightened now !
So I think I could summarise it by saying.... It is assumed that every thread (within every process) has it's own instance of a database connection object. When multiple processes share a *database* file contention will be indicated by SQL_BUSY being returned. In addition when using shared cache mode within a single process *table* contention will be indicated by SQL_LOCKED being returned. In both cases the application should take care to release any locks it has or is attempting to gain by finalising all the open handles it has by calling finalize or reset before backing off for a period and trying again. Hence robust code should deal with both of these situations in a similar ( possibly identical) manner. In shared cache mode this backing off period can be minimised by the use of the 'notify' callback. The 'busy' callback can be used to simplify application code's handling of the busy situation. Neither of these callbacks may be called and BUSY/ LOCKED returned directly to the application if a potential deadlock is detected. In addition SQLITE_IOERR_BLOCKED may be returned which is a more serious condition under which a connection should immediately attempt to finalise all it's open handles. Deadlock is more likely to be detected when two connections have existing open locks (e.g. a shared lock when doing non-dirty reads (read uncommitted mode is switched off)) and attempt to either escalate the existing lock or gain a new lock to write. If you know your connection is going to write imminently then it may be better to attempt to obtain a reserved or exclusive lock during begin. This may reduce deadlock potential with a trade-off of reduced concurrency. (I guess these last two paragraphs are the most subjective and open to different opinions) ....are there any corrections/ improvements to this ?? and does it bring anything extra to what's already in the wiki on the subject - hence is it worth adding ? many thanks for everyone's help in clarifying this. Owen. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw Sent: Thursday, October 29, 2009 5:49 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' Let's say we have the three connections in that diagram, and two tables named t1 and t2. I'll use a simple syntax to describe some concurrency scenarios: con#>>t# will mean con# writes to t# Commas will separate concurrent attempted operations After the operations will be a pipe '|' followed by the error code that would result, if any Here goes: 1. con1 >> t1, con2 >> t2 | SQLITE_BUSY 2. con2 >> t1, con2 >> t2 | SQLITE_OK 3. con1 >> t1, con2 >> t1 | SQLITE_BUSY 4. con2 >> t1, con2 >> t1 | SQLITE_LOCKED Does that clarify this? 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 6:33 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' i guess this isn't that complicated. the error codes even say basically what you've said: #define SQLITE_BUSY 5 /* The database file is locked */ #define SQLITE_LOCKED 6 /* A table in the database is locked */ i guess the point is that separate connections normally lock the entire DB file but in shared cache mode two connections (in the same process) can both have access to the DB file but not to the same table. you've said this below as well. the point is that in the diagram here (http://www.sqlite.org/sharedcache.html) if conn1 writes to tab1 then conn2 and conn3 will get SQLITE_BUSY, yes? if conn2 writes to tab1 then conn1 will get SQLITE_BUSY but conn3 will get SQLITE_LOCKED (if trying to write to tab1; will succeed if trying to write to tab2). correct? -----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:49 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] shared cache mode and 'LOCKED' 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 ______________________________________________________________________ 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
