[sqlite] Shared cache mode and busy wait
Today I saw a APSW note about shared cache mode at "https://rogerbinns.github.io/apsw/tips.html#shared-cache-mode;, which led me to "https://sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f;. Reading the sqlite page at "https://sqlite.org/sharedcache.html; I see that section "2.2. Table Level Locking" is not very clear or straight-forward. One must read between the lines and surmise in order to understand that the sqlite connection timeout (or callback) mechanisms become useless in this mode in a multi-threaded program. It would be useful if there was a statement that the normal mechanism is rendered useless and that the calling thread must implement its own busy-wait if it must succeed. The problem report was closed as "works as designed" (works as implemented?). If the normal busy-wait or callback mechanisms are not supported, then it becomes prohibitive to enable this mode on an existing code base. Given that each sqlite connection duplicates the entire database schema in RAM, this shared cache mode becomes quite useful on limited memory systems. It would be good if it worked `properly'. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Shared-Cache Mode
Hello, Is there any way to known if one connection participate to shared-cache mode ? I've read http://sqlite.org/sharedcache.html which specifies how to set but not how to get the mode! Regards. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache mode and 'LOCKED'
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
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
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
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 tip
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 ___
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 mail
Re: [sqlite] shared cache mode and 'LOCKED'
and here is the link to the thread where i received the below advice: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2009-October/016404.html -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: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 scann
Re: [sqlite] shared cache mode and 'LOCKED'
It appears to be up to date. John -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 1:45 PM 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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
[sqlite] Shared-cache mode doc page needs a version
On this page: http://sqlite.org/sharedcache.html in item 3.0 there's a missing version number at the end of the last sentence. Best regards, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] shared cache mode locking
So did my post. We are talking about the same thing. Definately confusing, at least to me.. The problem exists wherein you have two shared connections and one connection performs a begin exclusive... The other connection was just ignoring the exclusivity lock and continuing on its merry way and acquiring a table level lock. Which causes the first connection to get a SQLITE_LOCKED upon an insert to a table that the second connection is reading. The documentation is quite clear that once a connection acquires an EXCLUSIVE lock that it has controll and should not be locked out from writing by any other connections. The dual locking model (prior to the resolution) is ambiguous and could possibly lead application to deadlocks. These are just my thoughts on the matter, and are probably not 100% correct. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: No, you did not confuse me. We are talking about different things it appears. My post refers to the shared-cache locking model (http:// sqlite.org/sharedcache.html). The document is clear by itself. What makes it confusing, is that a shared cache instance exist as a single normal connection which may coincide with other, "normal" database connections. Quite a generous feature. But it means that the two locking models do apply at the same time. The joint connections within a shared cache are subject to the regular locking model in relation to possible other connections to the same database. Confusing or not? Ken wrote: > Ed, > > Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock > per sqlite documentation. I used the two interchangeably, pardon my > error. > > A begin exclusive indicates the beginning of a transaction, It > escalates the database lock to an EXCLUSIVE lock. The begin > transaction does not immediately do this, rather it waits until the > buffer cache spills to disk. At this point it attempts to escalate > the Reserved lock to a Pending then an Exclusive lock. > > There is only 1 type of EXCLUSIVE (write) lock, It is database > wide and is all or nothing. Once you have the lock, it prevents > other access to the DB. > > Ken > > > Ed Pasma wrote: The ticket has already been > resolved, I see. So it has been > considered a bug. In my earlier reply I tried to defend the current > behavour to be in line with the document, http://sqlite.org/ > sharedcache.html. I'm happy to change my mind now. Only I miss > something in the model as described in the document. This may > either be: > - exclusive transactions as a new kind of transactions, apart form > read- and write-transactions > or > - database-level locking as a new level above transaction-level > locking. > May be this suggestion is too naive, anyway it helps me explain the > wonderful cache sharing. > > Ken wrote: > >> Ed, >> >> Dan opened a ticket. I agree the documentation isn't clear on the >> Exlusive locking state. >> >> Not really sure, if this is by design or a bug at this stage. I do >> think its a great feature of the Shared cache mode to allow table >> level locking. But I'm curious with this table level locking what >> would happen if two threads performed writes to two seperate tables >> concurrently using only a begin immediate. >> >> Thread a writes to tab1, >> Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked >> kicked returned?) >> >> If it is allowed then would there be two journal files concurrently >> existing? And What happens during a crash with two journals ? >> >> This gets complicated very quickly. >> >> Ken >> >> Ed Pasma wrote: Hello,` >> Empirically I found that it is exactly true. >> Must admit I'm confused but may it is in line with the Shared-Cache >> locking model. >> This does not mention the EXCLUSIVE locking state. >> The most 'secure' locking state it mentions is a write-transaction >> and this can coexist with read-transactions from others. >> Thus "begin exclusive" starts a write-transaction and the on-going >> read does not interfere. >> The error message seems to clarify the situation further: database >> table is locked. Thus the collision occurs at the table-level. And >> yes, taking different tables for read and write, it does not occur. >> Practically this may not help very much. But may be the following >> does in case you have a busy_timeout setting. >> When having Shared-Cache mode enabled, the timeout setting appears to >> be ignored by SQLite. This makes locking situations surface rather >> soon, also when there is no dead-lock. >> The situation may be handled by a programmatic retry? >> Regards, Ed >> >> Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: >> >>> Some additional info: >>> >>> when the sqlite_lock is returned there is another thread that >>> appears to be reading the same table. Does the sqlite3 step return >>> sqlite_locked in this case? >>> >>> Thanks, >>> Ken >>> >>> >>> Ken wrote: >>> While using the new 3.5.4
Re: [sqlite] shared cache mode locking
No, you did not confuse me. We are talking about different things it appears. My post refers to the shared-cache locking model (http:// sqlite.org/sharedcache.html). The document is clear by itself. What makes it confusing, is that a shared cache instance exist as a single normal connection which may coincide with other, "normal" database connections. Quite a generous feature. But it means that the two locking models do apply at the same time. The joint connections within a shared cache are subject to the regular locking model in relation to possible other connections to the same database. Confusing or not? Ken wrote: Ed, Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock per sqlite documentation. I used the two interchangeably, pardon my error. A begin exclusive indicates the beginning of a transaction, It escalates the database lock to an EXCLUSIVE lock. The begin transaction does not immediately do this, rather it waits until the buffer cache spills to disk. At this point it attempts to escalate the Reserved lock to a Pending then an Exclusive lock. There is only 1 type of EXCLUSIVE (write) lock, It is database wide and is all or nothing. Once you have the lock, it prevents other access to the DB. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: The ticket has already been resolved, I see. So it has been considered a bug. In my earlier reply I tried to defend the current behavour to be in line with the document, http://sqlite.org/ sharedcache.html. I'm happy to change my mind now. Only I miss something in the model as described in the document. This may either be: - exclusive transactions as a new kind of transactions, apart form read- and write-transactions or - database-level locking as a new level above transaction-level locking. May be this suggestion is too naive, anyway it helps me explain the wonderful cache sharing. Ken wrote: Ed, Dan opened a ticket. I agree the documentation isn't clear on the Exlusive locking state. Not really sure, if this is by design or a bug at this stage. I do think its a great feature of the Shared cache mode to allow table level locking. But I'm curious with this table level locking what would happen if two threads performed writes to two seperate tables concurrently using only a begin immediate. Thread a writes to tab1, Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked kicked returned?) If it is allowed then would there be two journal files concurrently existing? And What happens during a crash with two journals ? This gets complicated very quickly. Ken Ed Pasma wrote: Hello,` Empirically I found that it is exactly true. Must admit I'm confused but may it is in line with the Shared-Cache locking model. This does not mention the EXCLUSIVE locking state. The most 'secure' locking state it mentions is a write-transaction and this can coexist with read-transactions from others. Thus "begin exclusive" starts a write-transaction and the on-going read does not interfere. The error message seems to clarify the situation further: database table is locked. Thus the collision occurs at the table-level. And yes, taking different tables for read and write, it does not occur. Practically this may not help very much. But may be the following does in case you have a busy_timeout setting. When having Shared-Cache mode enabled, the timeout setting appears to be ignored by SQLite. This makes locking situations surface rather soon, also when there is no dead-lock. The situation may be handled by a programmatic retry? Regards, Ed Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: Some additional info: when the sqlite_lock is returned there is another thread that appears to be reading the same table. Does the sqlite3 step return sqlite_locked in this case? Thanks, Ken Ken wrote: While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken - - --- To unsubscribe, send email to [EMAIL PROTECTED] - - --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
Ed, Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock per sqlite documentation. I used the two interchangeably, pardon my error. A begin exclusive indicates the beginning of a transaction, It escalates the database lock to an EXCLUSIVE lock. The begin transaction does not immediately do this, rather it waits until the buffer cache spills to disk. At this point it attempts to escalate the Reserved lock to a Pending then an Exclusive lock. There is only 1 type of EXCLUSIVE (write) lock, It is database wide and is all or nothing. Once you have the lock, it prevents other access to the DB. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: The ticket has already been resolved, I see. So it has been considered a bug. In my earlier reply I tried to defend the current behavour to be in line with the document, http://sqlite.org/ sharedcache.html. I'm happy to change my mind now. Only I miss something in the model as described in the document. This may either be: - exclusive transactions as a new kind of transactions, apart form read- and write-transactions or - database-level locking as a new level above transaction-level locking. May be this suggestion is too naive, anyway it helps me explain the wonderful cache sharing. Ken wrote: > Ed, > > Dan opened a ticket. I agree the documentation isn't clear on the > Exlusive locking state. > > Not really sure, if this is by design or a bug at this stage. I do > think its a great feature of the Shared cache mode to allow table > level locking. But I'm curious with this table level locking what > would happen if two threads performed writes to two seperate tables > concurrently using only a begin immediate. > > Thread a writes to tab1, > Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked > kicked returned?) > > If it is allowed then would there be two journal files concurrently > existing? And What happens during a crash with two journals ? > > This gets complicated very quickly. > > Ken > > Ed Pasma wrote: Hello,` > Empirically I found that it is exactly true. > Must admit I'm confused but may it is in line with the Shared-Cache > locking model. > This does not mention the EXCLUSIVE locking state. > The most 'secure' locking state it mentions is a write-transaction > and this can coexist with read-transactions from others. > Thus "begin exclusive" starts a write-transaction and the on-going > read does not interfere. > The error message seems to clarify the situation further: database > table is locked. Thus the collision occurs at the table-level. And > yes, taking different tables for read and write, it does not occur. > Practically this may not help very much. But may be the following > does in case you have a busy_timeout setting. > When having Shared-Cache mode enabled, the timeout setting appears to > be ignored by SQLite. This makes locking situations surface rather > soon, also when there is no dead-lock. > The situation may be handled by a programmatic retry? > Regards, Ed > > Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: > >> Some additional info: >> >> when the sqlite_lock is returned there is another thread that >> appears to be reading the same table. Does the sqlite3 step return >> sqlite_locked in this case? >> >> Thanks, >> Ken >> >> >> Ken wrote: >> While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a >> strange lock situation. >> >> SQLITE_LOCK is returned from an insert statement, even though >> the thread/connection performed a successful "begin exclusive" >> transaction. >> >>begin exclusive >> insert into table... ---> returns SQLITE_LOCKED >> >> Is it possible for both connections to begin exclusive transactions >> whilst having the shared cache anabled? >> >> Thanks, >> ken >> >> > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
The ticket has already been resolved, I see. So it has been considered a bug. In my earlier reply I tried to defend the current behavour to be in line with the document, http://sqlite.org/ sharedcache.html. I'm happy to change my mind now. Only I miss something in the model as described in the document. This may either be: - exclusive transactions as a new kind of transactions, apart form read- and write-transactions or - database-level locking as a new level above transaction-level locking. May be this suggestion is too naive, anyway it helps me explain the wonderful cache sharing. Ken wrote: Ed, Dan opened a ticket. I agree the documentation isn't clear on the Exlusive locking state. Not really sure, if this is by design or a bug at this stage. I do think its a great feature of the Shared cache mode to allow table level locking. But I'm curious with this table level locking what would happen if two threads performed writes to two seperate tables concurrently using only a begin immediate. Thread a writes to tab1, Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked kicked returned?) If it is allowed then would there be two journal files concurrently existing? And What happens during a crash with two journals ? This gets complicated very quickly. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: Hello,` Empirically I found that it is exactly true. Must admit I'm confused but may it is in line with the Shared-Cache locking model. This does not mention the EXCLUSIVE locking state. The most 'secure' locking state it mentions is a write-transaction and this can coexist with read-transactions from others. Thus "begin exclusive" starts a write-transaction and the on-going read does not interfere. The error message seems to clarify the situation further: database table is locked. Thus the collision occurs at the table-level. And yes, taking different tables for read and write, it does not occur. Practically this may not help very much. But may be the following does in case you have a busy_timeout setting. When having Shared-Cache mode enabled, the timeout setting appears to be ignored by SQLite. This makes locking situations surface rather soon, also when there is no dead-lock. The situation may be handled by a programmatic retry? Regards, Ed Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: Some additional info: when the sqlite_lock is returned there is another thread that appears to be reading the same table. Does the sqlite3 step return sqlite_locked in this case? Thanks, Ken Ken wrote: While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
Ed, Dan opened a ticket. I agree the documentation isn't clear on the Exlusive locking state. Not really sure, if this is by design or a bug at this stage. I do think its a great feature of the Shared cache mode to allow table level locking. But I'm curious with this table level locking what would happen if two threads performed writes to two seperate tables concurrently using only a begin immediate. Thread a writes to tab1, Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked kicked returned?) If it is allowed then would there be two journal files concurrently existing? And What happens during a crash with two journals ? This gets complicated very quickly. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: Hello,` Empirically I found that it is exactly true. Must admit I'm confused but may it is in line with the Shared-Cache locking model. This does not mention the EXCLUSIVE locking state. The most 'secure' locking state it mentions is a write-transaction and this can coexist with read-transactions from others. Thus "begin exclusive" starts a write-transaction and the on-going read does not interfere. The error message seems to clarify the situation further: database table is locked. Thus the collision occurs at the table-level. And yes, taking different tables for read and write, it does not occur. Practically this may not help very much. But may be the following does in case you have a busy_timeout setting. When having Shared-Cache mode enabled, the timeout setting appears to be ignored by SQLite. This makes locking situations surface rather soon, also when there is no dead-lock. The situation may be handled by a programmatic retry? Regards, Ed Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: > Some additional info: > > when the sqlite_lock is returned there is another thread that > appears to be reading the same table. Does the sqlite3 step return > sqlite_locked in this case? > > Thanks, > Ken > > > Ken wrote: > While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a > strange lock situation. > > SQLITE_LOCK is returned from an insert statement, even though > the thread/connection performed a successful "begin exclusive" > transaction. > >begin exclusive > insert into table... ---> returns SQLITE_LOCKED > > Is it possible for both connections to begin exclusive transactions > whilst having the shared cache anabled? > > Thanks, > ken > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
Hello,` Empirically I found that it is exactly true. Must admit I'm confused but may it is in line with the Shared-Cache locking model. This does not mention the EXCLUSIVE locking state. The most 'secure' locking state it mentions is a write-transaction and this can coexist with read-transactions from others. Thus "begin exclusive" starts a write-transaction and the on-going read does not interfere. The error message seems to clarify the situation further: database table is locked. Thus the collision occurs at the table-level. And yes, taking different tables for read and write, it does not occur. Practically this may not help very much. But may be the following does in case you have a busy_timeout setting. When having Shared-Cache mode enabled, the timeout setting appears to be ignored by SQLite. This makes locking situations surface rather soon, also when there is no dead-lock. The situation may be handled by a programmatic retry? Regards, Ed Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: Some additional info: when the sqlite_lock is returned there is another thread that appears to be reading the same table. Does the sqlite3 step return sqlite_locked in this case? Thanks, Ken Ken <[EMAIL PROTECTED]> wrote: While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
Some additional info: when the sqlite_lock is returned there is another thread that appears to be reading the same table. Does the sqlite3 step return sqlite_locked in this case? Thanks, Ken Ken <[EMAIL PROTECTED]> wrote: While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken
[sqlite] shared cache mode locking
While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken
Re: [sqlite] Shared cache mode issue
On Tue, 2007-01-09 at 08:01 -0800, Peter James wrote: > On 1/9/07, Dan Kennedy <[EMAIL PROTECTED]> wrote: > But it looks to me like commit #3341 (August 2006) covers this > up. #3341 > changes things so that the shared-schema is reset whenever any > connection handle is closed, so it's not possible for the > pointer in > question to go stale. > > > Hey Dan... > > Thanks for confirming this, and I'll check out that patch. Would you > suggest that I file a bug on the issue I reported, or is resetting the > shared schema like that on connection close here to stay? I don't see why it's not here to stay. You could file a bug against 3.3.6 for reference purposes if you like. Dan. > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared cache mode issue
On Mon, 2007-01-08 at 16:03 -0800, Peter James wrote:
> Hey folks...
>
> The context of this message is sqlite library version 3.3.6, using the
> shared-cache mode, effectively following the test_server.c example.
> Immediately upon switching to shared-cache mode we started seeing errors
> like so when preparing statements:
>
> [ERROR] (lib/sqlite/src/build.c:1220) no such collation sequence: garbage>
>
> Drilling down, this is what I'm understanding to be the case... Collators
> are attached to the individual sqlite* handles, remaining valid only while
> the connection to which the handle refers is valid. On the other hand, it
> appears that indexes are stored inside of the schema, and use a lookup
> string ("BINARY", "NOCASE") to find the contained column collators. This
> lookup string is actually in memory allocated as part of the collator, and
> is freed when the connection is closed, leaving a dangling pointer in the
> index.
This only happens with the default collation sequence. In build.c, a
pointer may be copied from sqlite3.pDfltColl->zName into the schema.
This is a bug, for the reasons identified above. (in cvs sources:
line 2479 of build.c).
But it looks to me like commit #3341 (August 2006) covers this up. #3341
changes things so that the shared-schema is reset whenever any
connection handle is closed, so it's not possible for the pointer in
question to go stale.
So if you upgrade to 3.3.7 or newer you should be Ok. Or if you can't
upgrade for your own reasons, maybe add something similar to #3341
(only one line).
Dan.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Shared cache mode issue
On 1/8/07, Peter James <[EMAIL PROTECTED]> wrote: Thanks for your response, Ken. I'm not sure I've explained myself properly. It's not that I'm calling sqlite3_enable_shared_cache() multiple times. It's that if I don't maintain a persistent connection while the server is running I end up with a dangling pointer and an error. 1. start server thread a. calls sqlite3_enable_shared_cache() b. waits for incoming commands 2. open connection #1 3. open connection #2 4. prepare and step a query with connection #1 (through the server) 5. close connection #1 6. prepare a query with connection #2 (through the server) I believe multiple connections are specifically warned against in the XUL/javascript documentation for using the firefox version of sqlite. They redesigned it in such a way that it works well only for firefox and can't be used with anything else easily. I use it from a firefox addon. -- The PixAddixImage Collector suite: http://groups-beta.google.com/group/pixaddix SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Shared cache mode issue
On 1/8/07, Ken <[EMAIL PROTECTED]> wrote: You could always implement a sqlite3_open call and store it in the g variable, and close it when the server quits. Thanks for your response, Ken. I'm not sure I've explained myself properly. It's not that I'm calling sqlite3_enable_shared_cache() multiple times. It's that if I don't maintain a persistent connection while the server is running I end up with a dangling pointer and an error. 1. start server thread a. calls sqlite3_enable_shared_cache() b. waits for incoming commands 2. open connection #1 3. open connection #2 4. prepare and step a query with connection #1 (through the server) 5. close connection #1 6. prepare a query with connection #2 (through the server) The issue is that at step #6, I get the "no such collation sequence" error because some memory (the collation sequence names) that the shared schema structures depended on went away in step 5. I'm wondering if I'm misunderstanding something, and if there's any RTFM'ing I should have done, cuz I'm not seeing this as a requirement in any of the docs I read on the subject. Thanks, Pete.
Re: [sqlite] Shared cache mode issue
Here is a code snipet from my version if the server thread code
I found that it was doing an enable/disable on the shared cache with the
original logic.
You could always implement a sqlite3_open call and store it in the g variable,
and close it when the server quits.
void *sqlite3_server(void *NotUsed){
if( pthread_mutex_trylock() ){
sqlite3_enable_shared_cache(0);
return 0; /* Another server is already running */
}
// Only enable the shared cache 1 time
sqlite3_enable_shared_cache(1);
Peter James <[EMAIL PROTECTED]> wrote: Hey folks...
The context of this message is sqlite library version 3.3.6, using the
shared-cache mode, effectively following the test_server.c example.
Immediately upon switching to shared-cache mode we started seeing errors
like so when preparing statements:
[ERROR] (lib/sqlite/src/build.c:1220) no such collation sequence:
garbage>
Drilling down, this is what I'm understanding to be the case... Collators
are attached to the individual sqlite* handles, remaining valid only while
the connection to which the handle refers is valid. On the other hand, it
appears that indexes are stored inside of the schema, and use a lookup
string ("BINARY", "NOCASE") to find the contained column collators. This
lookup string is actually in memory allocated as part of the collator, and
is freed when the connection is closed, leaving a dangling pointer in the
index.
>From reading mozilla's docs on how they used the shared cache mode, I have
to guess this dangling pointer thing isn't normally a problem since the
"standard" thing to do is open a (dummy) connection at the beginning of the
server and maintain it until the server ends. In which case, the dummy
connection is the one containing default collator defs and that lookup
string's memory is always valid.
The error above surfaced in our initial implementation of the test_server.c
architecture, where we hadn't yet started using a dummy connection. We were
testing functionality and just opening and closing connections as needed
through the server thread, and noticed that if the first connection happened
to close after the second connection opened, we would end up with the above
error. Before realizing this was perhaps an artefact of not having a dummy
connection, we worked around it by doing a strcpy of the collator name into
malloc'd memory for the index, rather than just pointing at the collator..
Is this a correct interpretation of this situation? If there's a dependency
in shared cache mode where the client must maintain at least one persistent
connection, did I miss documentation on this fact?
I hope I explained myself properly. If not, let me know and I'll try
again. :-)
Thanks,
Pete.
Peter James <[EMAIL PROTECTED]> wrote: Hey folks...
The context of this message is sqlite library version 3.3.6, using the
shared-cache mode, effectively following the test_server.c example.
Immediately upon switching to shared-cache mode we started seeing errors
like so when preparing statements:
[ERROR] (lib/sqlite/src/build.c:1220) no such collation sequence:
garbage>
Drilling down, this is what I'm understanding to be the case... Collators
are attached to the individual sqlite* handles, remaining valid only while
the connection to which the handle refers is valid. On the other hand, it
appears that indexes are stored inside of the schema, and use a lookup
string ("BINARY", "NOCASE") to find the contained column collators. This
lookup string is actually in memory allocated as part of the collator, and
is freed when the connection is closed, leaving a dangling pointer in the
index.
>From reading mozilla's docs on how they used the shared cache mode, I have
to guess this dangling pointer thing isn't normally a problem since the
"standard" thing to do is open a (dummy) connection at the beginning of the
server and maintain it until the server ends. In which case, the dummy
connection is the one containing default collator defs and that lookup
string's memory is always valid.
The error above surfaced in our initial implementation of the test_server.c
architecture, where we hadn't yet started using a dummy connection. We were
testing functionality and just opening and closing connections as needed
through the server thread, and noticed that if the first connection happened
to close after the second connection opened, we would end up with the above
error. Before realizing this was perhaps an artefact of not having a dummy
connection, we worked around it by doing a strcpy of the collator name into
malloc'd memory for the index, rather than just pointing at the collator..
Is this a correct interpretation of this situation? If there's a dependency
in shared cache mode where the client must maintain at least one persistent
connection, did I miss documentation on this fact?
I hope I explained myself properly. If not, let me know and I'll try
again. :-)
Thanks,
Pete.
Re: [sqlite] shared-cache mode and firefox
Thanks Vitali, and Trevor. I'll poke them instead ;) > Firefox is now using sqlite. They use shared-cache mode because they want it > to work over networked drives and they don't want to pay for the > latency involved. The "shared cache mode" in sqlite only changes certain behavior for threads in the same process. It's unrelated to external locks, which work as normal. It's my understanding that Firefox has done modifications of their own to disable external locking. -- The JS Image Collector suite: http://groups-beta.google.com/group/js-image-collector?hl=en SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared-cache mode and firefox
On 12/6/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: Firefox is now using sqlite. They use shared-cache mode because they want it to work over networked drives and they don't want to pay for the latency involved. The "shared cache mode" in sqlite only changes certain behavior for threads in the same process. It's unrelated to external locks, which work as normal. It's my understanding that Firefox has done modifications of their own to disable external locking. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared-cache mode and firefox
http://www.sqlite.org/sharedcache.html It's controlled at runtime by the function int sqlite3_enable_shared_cache(int); Thus that behaviour is probably controlled by Firefox (assuming it uses a version of sqlite with cache support compiled in). You'd have to ask them. Jay Sprenkle wrote: Good evening, I'd like to make a request for the next version of sqlite. It's a big change and will probably get shot down, but you won't know until you ask. Firefox is now using sqlite. They use shared-cache mode because they want it to work over networked drives and they don't want to pay for the latency involved. I personally wouldn't have made this choice since it trades away something I think that could be useful to a large number of people to support something that I believe almost nobody will use. It's not my decision though. If shared-cache mode was something that could be turned off at run time it would allow everyone to get what they want. I believe this would be a complete refactoring of the code though. Thanks for your time Dr. Hipp, and for sharing Sqlite with all of us! Jay - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] shared-cache mode and firefox
Good evening, I'd like to make a request for the next version of sqlite. It's a big change and will probably get shot down, but you won't know until you ask. Firefox is now using sqlite. They use shared-cache mode because they want it to work over networked drives and they don't want to pay for the latency involved. I personally wouldn't have made this choice since it trades away something I think that could be useful to a large number of people to support something that I believe almost nobody will use. It's not my decision though. If shared-cache mode was something that could be turned off at run time it would allow everyone to get what they want. I believe this would be a complete refactoring of the code though. Thanks for your time Dr. Hipp, and for sharing Sqlite with all of us! Jay -- -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
As you can tell from this forum, locking and synchronization is the area where there is least intuitive understanding among users and is the most consistent source of problems. There must be a deep psychological reason. I don't think it's deep really, just the most complex part to understand. That and it's a nightmare to debug because of the time dimension. In my last project it took me three tries to get the locking issues really resolved. Did you know on SQL server that if you don't have a clustered index it can promote row level locking to page locking? I learned all about it the hard way! Ack! -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Jay Sprenkle wrote: On 8/29/06, John Stanton <[EMAIL PROTECTED]> wrote: Thankyou. The Firefox people have merely removed their dependance upon an unreliable resource, cross OS file locking. A prudent design choice. If they come up with an elegant distributed lock protocol it would be worth propagating universally in the light of the success of Firefox and its consequent broad distribution. I see that it has already grabbed perhaps 30% of browser users. I agree as long as they don't replace it with something that is less robust. IMHO something that's broken as simply as running two instances doesn't seem robust or elegant. I hope I'm wrong about it though - To unsubscribe, send email to [EMAIL PROTECTED] - As long as I can remember poorly conceived and implemented file locking mechanisms have been a nosebleed in IT. If you were ever involved in porting to multiple OS's you quickly got burned. On that basis I hope the Firefox people develop something elegant, but wouldn't bet on it. As you can tell from this forum, locking and synchronization is the area where there is least intuitive understanding among users and is the most consistent source of problems. There must be a deep psychological reason. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
On 8/29/06, John Stanton <[EMAIL PROTECTED]> wrote: Thankyou. The Firefox people have merely removed their dependance upon an unreliable resource, cross OS file locking. A prudent design choice. If they come up with an elegant distributed lock protocol it would be worth propagating universally in the light of the success of Firefox and its consequent broad distribution. I see that it has already grabbed perhaps 30% of browser users. I agree as long as they don't replace it with something that is less robust. IMHO something that's broken as simply as running two instances doesn't seem robust or elegant. I hope I'm wrong about it though - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Thankyou. The Firefox people have merely removed their dependance upon an unreliable resource, cross OS file locking. A prudent design choice. If they come up with an elegant distributed lock protocol it would be worth propagating universally in the light of the success of Firefox and its consequent broad distribution. I see that it has already grabbed perhaps 30% of browser users. [EMAIL PROTECTED] wrote: "Jay Sprenkle" <[EMAIL PROTECTED]> wrote: If you run two instances of firefox you trash your own database. No, you didn't read what I said. Firefox implements their own locking mechanism, so two instances of firefox will play nicely together. The problem is when some other application, that does not follow firefox's locking protocol, tries to access the database while firefox is running. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Ritesh Kapoor wrote: Can you - DRH or someone else provide some more background information on why locking dosen't work on NFS mounted file systems. I just tried to find out what the locking problem was but couldn't find a web page discussing it in any detail. Lots of pages saying there *was* a problem with locking but none describing *what* the problem was. Someone asks for a reliable NFS locking method in a short thread at http://www.exim.org/pipermail/exim-users/Week-of-Mon-19990531/012756.html The gist of that thread is that opening a file with O_EXCL isn't atomic. This problem (and a workaround) is mentioned in Linux's man page for open(2) and apparently this workaround is used by exim and procmail. http://blogs.sun.com/erickustarz/entry/integrated_locking claims there's no problem with NFS locking and mentions the SQLite FAQ as making this claim. Apparently it's an "issue with early implementations rather than with the NFS protocol issue". Stevens said pretty much the same back in 1999 and he's probably about as authoritative as you'll get. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > > If you run two instances of firefox you trash > your own database. No, you didn't read what I said. Firefox implements their own locking mechanism, so two instances of firefox will play nicely together. The problem is when some other application, that does not follow firefox's locking protocol, tries to access the database while firefox is running. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
On 8/29/06, Ritesh Kapoor <[EMAIL PROTECTED]> wrote: Can you - DRH or someone else provide some more background information on why locking dosen't work on NFS mounted file systems. If its a known issue then is there an SQLite compile time option that would remove locking - i couldn't find one. When I got stuck with this problem I had to remove all locking code in sqlite. I googled and did find some information related to NFS-locking on different mailing lists but all of this information wasn't connected. This seems to be a problem known for a long time now - Why hasn't it been fixed? The operating systems in question don't work correctly. Sqlite can't fix it. It could be documented better or a test put together that would warn you though. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
On 8/29/06, John Stanton <[EMAIL PROTECTED]> wrote: Jay Sprenkle wrote: > > If it breaks because of something you did, then YOU are the bum. > If it's broken because of the operating system THEY are the bums. > Having someone to blame still doesn't make it work. They are have made the rational decision, based on building a product which runs everywhere. In either case it's a problem. If you run two instances of firefox you trash your own database. Given the choice of "It's broken and I get blamed" or "It's broken and the operating system is at fault" (Which is entirely true) then the choice is clear in my mind. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Can you - DRH or someone else provide some more background information on why locking dosen't work on NFS mounted file systems. If its a known issue then is there an SQLite compile time option that would remove locking - i couldn't find one. When I got stuck with this problem I had to remove all locking code in sqlite. I googled and did find some information related to NFS-locking on different mailing lists but all of this information wasn't connected. This seems to be a problem known for a long time now - Why hasn't it been fixed? -- Regards, Ritesh Kapoor "living in interesting times..." --- Begin Message --- "Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > Good morning all, > > I'm in the design stage of a project and had a question about sqlite > shared-cache mode. > The new version of firefox will use mozStorage, which is based on > sqlite using shared-cache mode. I want other programs to be able > read/write to the database but I was told this might > be a problem. Are there any issues with two completely separate > processes accessing > the database when in this mode? This will be important if other apps > want to read or > manipulate the downloaded files list or the bookmarks. > The shared-cache mode of SQLite does nothing to prevent other programs from reading and writing a database at the same time. (Well - not exactly the same time - but interleaving their reads and writes dynamically just like regular SQLite - you know what I mean) However, I believe mozilla may be making other changes to SQLite. In particular, I think they may be disabling the locking mechanism since some people use Firefox on NFS mounting filesystems where the locking is broken. Mozilla creates its own lock-file based locks to use in place of the posix advisory locks that SQLite uses by default - or so I am lead to believe. So if you try to access a Firefox SQLite database at the same time that Firefox is trying to access it, the locking might not work right and you could run into some contention and corrupt the database or else read invalid data because firefox is updating the database at the same time you are trying to read it. Let me emphasize that everything in the previous paragraph is hearsay and supposition and could be wildly incorrect. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - --- End Message --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
Jay Sprenkle wrote: The problem is that they have to work on broken operating systems. I don't know of another way to patch around the problem. Do you? nope. If it breaks because of something you did, then YOU are the bum. If it's broken because of the operating system THEY are the bums. Having someone to blame still doesn't make it work. They are have made the rational decision, based on building a product which runs everywhere. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
The problem is that they have to work on broken operating systems. I don't know of another way to patch around the problem. Do you? nope. If it breaks because of something you did, then YOU are the bum. If it's broken because of the operating system THEY are the bums. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > On 8/28/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Let me emphasize that everything in the previous paragraph is > > hearsay and supposition and could be wildly incorrect. > > Thanks Dr.H. > > I hope it's not true. If it's true It really seems a poor decision > that will come back > to haunt them later. > The problem is that they have to work on broken operating systems. I don't know of another way to patch around the problem. Do you? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite shared-cache mode usage
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > Good morning all, > > I'm in the design stage of a project and had a question about sqlite > shared-cache mode. > The new version of firefox will use mozStorage, which is based on > sqlite using shared-cache mode. I want other programs to be able > read/write to the database but I was told this might > be a problem. Are there any issues with two completely separate > processes accessing > the database when in this mode? This will be important if other apps > want to read or > manipulate the downloaded files list or the bookmarks. > The shared-cache mode of SQLite does nothing to prevent other programs from reading and writing a database at the same time. (Well - not exactly the same time - but interleaving their reads and writes dynamically just like regular SQLite - you know what I mean) However, I believe mozilla may be making other changes to SQLite. In particular, I think they may be disabling the locking mechanism since some people use Firefox on NFS mounting filesystems where the locking is broken. Mozilla creates its own lock-file based locks to use in place of the posix advisory locks that SQLite uses by default - or so I am lead to believe. So if you try to access a Firefox SQLite database at the same time that Firefox is trying to access it, the locking might not work right and you could run into some contention and corrupt the database or else read invalid data because firefox is updating the database at the same time you are trying to read it. Let me emphasize that everything in the previous paragraph is hearsay and supposition and could be wildly incorrect. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] sqlite shared-cache mode usage
Hi everybody, I am in the same situation, wondering if two or more processes can access the database if one of them is in shared-cache mode, e.g. one process act as in test_server.c serving multiple clients, another process reads/writes the database through the ODBC driver of C. Werner. Thanks in advance. Marc Ruff -Ursprüngliche Nachricht- Von: Jay Sprenkle [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 27. August 2006 19:07 An: sqlite-users@sqlite.org Betreff: [sqlite] sqlite shared-cache mode usage Good morning all, I'm in the design stage of a project and had a question about sqlite shared-cache mode. The new version of firefox will use mozStorage, which is based on sqlite using shared-cache mode. I want other programs to be able read/write to the database but I was told this might be a problem. Are there any issues with two completely separate processes accessing the database when in this mode? This will be important if other apps want to read or manipulate the downloaded files list or the bookmarks. Thanks! - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite shared-cache mode usage
Good morning all, I'm in the design stage of a project and had a question about sqlite shared-cache mode. The new version of firefox will use mozStorage, which is based on sqlite using shared-cache mode. I want other programs to be able read/write to the database but I was told this might be a problem. Are there any issues with two completely separate processes accessing the database when in this mode? This will be important if other apps want to read or manipulate the downloaded files list or the bookmarks. Thanks! - To unsubscribe, send email to [EMAIL PROTECTED] -
