Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
SELECT is read only. Think of an Sqlite lock as a lock on the journal file. The critical action of an Sqlite insert or update is to transfer the contents of the journal file to disk and verify that the write to disk has completed (the ACID feature). Apart from that it is read only. Somewhere I have code for an efficient read lock for Windows. A write lock is a critical section or a mutex I can seek it out if it would help you. If you have a general purpose RPC server you probably need to parse the SQL to a certain degree to establish read or write ststua or have the RPC announce whether is it is a read or write Skilfully implemented your Sqlite-based RPC server will have the capabilities of a regular RDBMS server but with the advantage that it can be seeded with other functions. For example we embed Sqlite inside an RPC server which sits inside an HTTP web server which also embeds an SSL capability and a web page generator also embedding Sqlite. Sqlite is the silver bullet which removes the need to have inefficient IPCs and CGI-like process creation and destruction. AJAX in one process is realized, thanks to Sqlite. Sendfile/TransmitFile can be used to full advantage. In such a system careful partitioning into separate databases minimizes contentions. [EMAIL PROTECTED] wrote: I'd like to use reader-writer lock in the client code, but at this point I'm not sure if I can determine at which point SQLite is not writing. I mean, INSERT / UPDATE are most likely need a writer lock, but I don't know if SELECT is guaranteed to be read-only in its internal operation within SQLite when I set SQLITE_THREADSAFE=0. Implementing an efficient RW lock on Windows XP is another challenge anyway. -- sword On Sat, 19 Jan 2008 22:56:43 +0100 Jens Miltner <[EMAIL PROTECTED]> wrote: Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]: OK I figured out SQLITE_THREADSAFE=0 for the second question... And it seems the answer for the first question is yes, but if you know a simpler way please share it with us, thanks! You could use a read-write mutex to serialize access to your database connection. That way you can have multiple readers, but modifying the database becomes an exclusive operation. This matches the sqlite requirements. Alternatively, you can just retry your write queries if you get SQLITE_BUSY errors... -- sword On Sat, 19 Jan 2008 09:57:10 +0900 "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: Hello all, I've read http://www.sqlite.org/lockingv3.html but am still not sure about multithread and locking in 3.5.4. I have a multithread application that has a single connection to a single SQLite3 database. Since it's multithreaded, SQL statements are thrown to a single SQLite3 object concurrently. I'm using http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip on VC8 + WindowsXP. Prior to this version (I was using SQLite2) I'd serialized all these database access using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED since they never happen. It was very simple as I didn't need to implement access retry for a busy case. However, I learned that SQLite 3.5 does mutexing by default. So I removed all synchronization stuff in my SQLite access code, and now it seems it's not working as I intended. Unfortunately I can't reproduce it in my development environment and I've not yet implemented logging to see if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe at least. My question is, 1. Do I still have to synchronize all SQLite access in my client code not to encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) 2. If so, how can I turn off all these mutexes (critical sections) in SQLite 3.5.4? They are needless if I serialize all SQLite access in the client code. Regards, -- sword - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
I'd like to use reader-writer lock in the client code, but at this point I'm not sure if I can determine at which point SQLite is not writing. I mean, INSERT / UPDATE are most likely need a writer lock, but I don't know if SELECT is guaranteed to be read-only in its internal operation within SQLite when I set SQLITE_THREADSAFE=0. Implementing an efficient RW lock on Windows XP is another challenge anyway. -- sword On Sat, 19 Jan 2008 22:56:43 +0100 Jens Miltner <[EMAIL PROTECTED]> wrote: > > Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]: > > > OK I figured out SQLITE_THREADSAFE=0 for the second question... > > And it seems the answer for the first question is yes, but if you know > > a simpler way please share it with us, thanks! > > You could use a read-write mutex to serialize access to your database > connection. That way you can have multiple readers, but modifying the > database becomes an exclusive operation. This matches the sqlite > requirements. > Alternatively, you can just retry your write queries if you get > SQLITE_BUSY errors... > > > > > > > -- sword > > > > On Sat, 19 Jan 2008 09:57:10 +0900 > > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > >> Hello all, > >> > >> I've read http://www.sqlite.org/lockingv3.html but am still not > >> sure about > >> multithread and locking in 3.5.4. > >> > >> I have a multithread application that has a single connection to a > >> single > >> SQLite3 database. Since it's multithreaded, SQL statements are > >> thrown to > >> a single SQLite3 object concurrently. I'm using > >> http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip > >> on VC8 + WindowsXP. > >> > >> Prior to this version (I was using SQLite2) I'd serialized all > >> these database access > >> using critical sections and didn't care about SQLITE_BUSY or > >> SQLITE_LOCKED > >> since they never happen. It was very simple as I didn't need to > >> implement access > >> retry for a busy case. > >> > >> However, I learned that SQLite 3.5 does mutexing by default. So I > >> removed > >> all synchronization stuff in my SQLite access code, and now it seems > >> it's not working as I intended. Unfortunately I can't reproduce it > >> in my > >> development environment and I've not yet implemented logging to see > >> if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering > >> sqlite3_mutex_enter multiple times in the debugger though, so it's > >> thread-safe > >> at least. > >> > >> My question is, > >> > >> 1. Do I still have to synchronize all SQLite access in my client > >> code not to > >> encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) > >> > >> 2. If so, how can I turn off all these mutexes (critical sections) > >> in SQLite 3.5.4? > >> They are needless if I serialize all SQLite access in the client > >> code. > >> > >> Regards, > >> > >> -- sword > >> > >> > >> > >> - > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> - > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
Using read and write locks around your statements gives you protection and lets you compile without thread safe so that Sqlite does not use internal mutexes as much for synchronization saving you considerable overhead as well as avoiding the logic necessary to handle BUSYs from Sqlite and skipping any polling or busy waits. Pthreads provides all the capabilities in the API. Windows needs a little work to implement read locks. Jens Miltner wrote: Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]: OK I figured out SQLITE_THREADSAFE=0 for the second question... And it seems the answer for the first question is yes, but if you know a simpler way please share it with us, thanks! You could use a read-write mutex to serialize access to your database connection. That way you can have multiple readers, but modifying the database becomes an exclusive operation. This matches the sqlite requirements. Alternatively, you can just retry your write queries if you get SQLITE_BUSY errors... -- sword On Sat, 19 Jan 2008 09:57:10 +0900 "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: Hello all, I've read http://www.sqlite.org/lockingv3.html but am still not sure about multithread and locking in 3.5.4. I have a multithread application that has a single connection to a single SQLite3 database. Since it's multithreaded, SQL statements are thrown to a single SQLite3 object concurrently. I'm using http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip on VC8 + WindowsXP. Prior to this version (I was using SQLite2) I'd serialized all these database access using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED since they never happen. It was very simple as I didn't need to implement access retry for a busy case. However, I learned that SQLite 3.5 does mutexing by default. So I removed all synchronization stuff in my SQLite access code, and now it seems it's not working as I intended. Unfortunately I can't reproduce it in my development environment and I've not yet implemented logging to see if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe at least. My question is, 1. Do I still have to synchronize all SQLite access in my client code not to encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) 2. If so, how can I turn off all these mutexes (critical sections) in SQLite 3.5.4? They are needless if I serialize all SQLite access in the client code. Regards, -- sword - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]: OK I figured out SQLITE_THREADSAFE=0 for the second question... And it seems the answer for the first question is yes, but if you know a simpler way please share it with us, thanks! You could use a read-write mutex to serialize access to your database connection. That way you can have multiple readers, but modifying the database becomes an exclusive operation. This matches the sqlite requirements. Alternatively, you can just retry your write queries if you get SQLITE_BUSY errors... -- sword On Sat, 19 Jan 2008 09:57:10 +0900 "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: Hello all, I've read http://www.sqlite.org/lockingv3.html but am still not sure about multithread and locking in 3.5.4. I have a multithread application that has a single connection to a single SQLite3 database. Since it's multithreaded, SQL statements are thrown to a single SQLite3 object concurrently. I'm using http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip on VC8 + WindowsXP. Prior to this version (I was using SQLite2) I'd serialized all these database access using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED since they never happen. It was very simple as I didn't need to implement access retry for a busy case. However, I learned that SQLite 3.5 does mutexing by default. So I removed all synchronization stuff in my SQLite access code, and now it seems it's not working as I intended. Unfortunately I can't reproduce it in my development environment and I've not yet implemented logging to see if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe at least. My question is, 1. Do I still have to synchronize all SQLite access in my client code not to encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) 2. If so, how can I turn off all these mutexes (critical sections) in SQLite 3.5.4? They are needless if I serialize all SQLite access in the client code. Regards, -- sword - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
OK I figured out SQLITE_THREADSAFE=0 for the second question... And it seems the answer for the first question is yes, but if you know a simpler way please share it with us, thanks! -- sword On Sat, 19 Jan 2008 09:57:10 +0900 "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hello all, > > I've read http://www.sqlite.org/lockingv3.html but am still not sure about > multithread and locking in 3.5.4. > > I have a multithread application that has a single connection to a single > SQLite3 database. Since it's multithreaded, SQL statements are thrown to > a single SQLite3 object concurrently. I'm using > http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip > on VC8 + WindowsXP. > > Prior to this version (I was using SQLite2) I'd serialized all these database > access > using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED > since they never happen. It was very simple as I didn't need to implement > access > retry for a busy case. > > However, I learned that SQLite 3.5 does mutexing by default. So I removed > all synchronization stuff in my SQLite access code, and now it seems > it's not working as I intended. Unfortunately I can't reproduce it in my > development environment and I've not yet implemented logging to see > if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering > sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe > at least. > > My question is, > > 1. Do I still have to synchronize all SQLite access in my client code not to > encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) > > 2. If so, how can I turn off all these mutexes (critical sections) in SQLite > 3.5.4? > They are needless if I serialize all SQLite access in the client code. > > Regards, > > -- sword > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4
Hello all, I've read http://www.sqlite.org/lockingv3.html but am still not sure about multithread and locking in 3.5.4. I have a multithread application that has a single connection to a single SQLite3 database. Since it's multithreaded, SQL statements are thrown to a single SQLite3 object concurrently. I'm using http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip on VC8 + WindowsXP. Prior to this version (I was using SQLite2) I'd serialized all these database access using critical sections and didn't care about SQLITE_BUSY or SQLITE_LOCKED since they never happen. It was very simple as I didn't need to implement access retry for a busy case. However, I learned that SQLite 3.5 does mutexing by default. So I removed all synchronization stuff in my SQLite access code, and now it seems it's not working as I intended. Unfortunately I can't reproduce it in my development environment and I've not yet implemented logging to see if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering sqlite3_mutex_enter multiple times in the debugger though, so it's thread-safe at least. My question is, 1. Do I still have to synchronize all SQLite access in my client code not to encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?) 2. If so, how can I turn off all these mutexes (critical sections) in SQLite 3.5.4? They are needless if I serialize all SQLite access in the client code. Regards, -- sword - To unsubscribe, send email to [EMAIL PROTECTED] -
