Re: [sqlite] Using sqlite3 from multiple threads
You have to write the non-busy wait handlers yourself. As an example the thread that has acquired the DB and has performed its writes. At the time it commits it could post a condition variable/mutex pair. Any thread that gets a busy could simply undo its work, and wait on the condition variable. Once it gets the condition variable it goes about performing a retry.. The code is left as an excercise for the reader. This can be really really hard to implement successfully, consider error handling in the case were the thread that has the "lock" suddenly aborts (assuming pthreads and its detached) then no harm to the process. But the other threads will never be signalled either, then they will be stuck. HTH, Ken Andreas Volz <[EMAIL PROTECTED]> wrote: Am Wed, 9 Jan 2008 10:20:31 -0800 (PST) schrieb Ken: > Definately use 3.5.4. > > Not sure how to determine at compile time if the threadsafe part is > enabled. You can always compile yourself to guarantee its set, thats > what I do. > > sqlite will lock the database file for you automatically. Your > threads do not need to implement locking. But they do need to handle > in some fashion. SQLITE_BUSY and/or SQLITE_LOCKED error codes. > > You should also look at "Begin", "Begin Exlusive" and "Begin > Immediate" sql commands. Ok, I found the docs. But I exec only single SQL queries at the moment. So I think I don't need that. > As a user you don't get to pick the lock state, sqlite does that > automatically for you. You must handle the return codes suche as > SQLITE_BUSY, SQLITE_LOCKED etc. Now I implemented a multi-threading example that reads and writes in a database file. Currently I check return values and if in BUSY or LOCKED state I wait some time and try it again until I get the lock. It works, but currently I have around 20 BUSY operations for 5 successful write operations in 5 threads parallel threads. Is it really the way of choose to do it this way? Isn't there a way without active waiting? I would prefer exec() blocking until I get a write lock to not have to loop with a sleep until it works. regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
I find it much simpler to put a mutex around the accesses (or make them a critical section). That serializes the access and avoids busy waits, retries etc. It will prevent a certain amount of read concurrency. but that may be insignificant. If you use pthreads and have plenty of reads for each write you might use pthread read/write locks instead of plain mutexes. You can synthesize read/write locks from windows locking calls if you are patient. Andreas Volz wrote: Am Wed, 9 Jan 2008 10:20:31 -0800 (PST) schrieb Ken: Definately use 3.5.4. Not sure how to determine at compile time if the threadsafe part is enabled. You can always compile yourself to guarantee its set, thats what I do. sqlite will lock the database file for you automatically. Your threads do not need to implement locking. But they do need to handle in some fashion. SQLITE_BUSY and/or SQLITE_LOCKED error codes. You should also look at "Begin", "Begin Exlusive" and "Begin Immediate" sql commands. Ok, I found the docs. But I exec only single SQL queries at the moment. So I think I don't need that. As a user you don't get to pick the lock state, sqlite does that automatically for you. You must handle the return codes suche as SQLITE_BUSY, SQLITE_LOCKED etc. Now I implemented a multi-threading example that reads and writes in a database file. Currently I check return values and if in BUSY or LOCKED state I wait some time and try it again until I get the lock. It works, but currently I have around 20 BUSY operations for 5 successful write operations in 5 threads parallel threads. Is it really the way of choose to do it this way? Isn't there a way without active waiting? I would prefer exec() blocking until I get a write lock to not have to loop with a sleep until it works. regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Am Wed, 9 Jan 2008 10:20:31 -0800 (PST) schrieb Ken: > Definately use 3.5.4. > > Not sure how to determine at compile time if the threadsafe part is > enabled. You can always compile yourself to guarantee its set, thats > what I do. > > sqlite will lock the database file for you automatically. Your > threads do not need to implement locking. But they do need to handle > in some fashion. SQLITE_BUSY and/or SQLITE_LOCKED error codes. > > You should also look at "Begin", "Begin Exlusive" and "Begin > Immediate" sql commands. Ok, I found the docs. But I exec only single SQL queries at the moment. So I think I don't need that. > As a user you don't get to pick the lock state, sqlite does that > automatically for you. You must handle the return codes suche as > SQLITE_BUSY, SQLITE_LOCKED etc. Now I implemented a multi-threading example that reads and writes in a database file. Currently I check return values and if in BUSY or LOCKED state I wait some time and try it again until I get the lock. It works, but currently I have around 20 BUSY operations for 5 successful write operations in 5 threads parallel threads. Is it really the way of choose to do it this way? Isn't there a way without active waiting? I would prefer exec() blocking until I get a write lock to not have to loop with a sleep until it works. regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
On Wed, Jan 09, 2008 at 10:06:30PM +, [EMAIL PROTECTED] wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > > Would you recommend that we not make SQLite 3.x in Solaris available to > > third parties? > > I think having a libsqlite3.so available is great. There will likely > be smaller apps that want to compile with -lsqlite3. I think it is > nice to accommodate them. Presumably, Solaris will be using sqlite3 > internally (in place of sqlite2?) so it does not cost you anything > to make it available to third parties. SMF will probably be the last thing to move away from SQLite 2.x (it's been stable and performant, and SMF is such a critical early boot component that dealing with incompatible schema or DB changes presents serious challenges in some of the install/update/patch scenarios). But, yes, we will use SQLite 3.x internally, as well as in bundled apps like Firefox. There is a cost to making something available to third parties, namely: more work or delays in the event that the need arises for an incompatible change. > On the other hand, I recommend to anybody building an application > that you should statically link against SQLite. OK. I'll pass this advice along. > Regardless of whether or not you make libsqlite3.so available, > you definitely should make the sqlite3 CLI available. That is > a very handy thing to have when using a shell. Agreed! Thanks, Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Andreas Volz <[EMAIL PROTECTED]> wrote: > > I don't understand how to use the sqlite3_io_methods. It seems I need a > sqlite3_file. Not sure I get it from the API. The sqlite3_open call > returns an int. So how do I use this? > > Perhaps you could sketch some pseudo code or paste some code where > locking with SQLITE_BUSY and SQLITE_LOCKED is used. > There are seven examples of using sqlite3_io_methods in the source tree. All three of the operating-system adaptors for sqlite are implemented using sqlite3_io_methods. See os_unix.c, os_win.c, and os_os2.c. The test_onefile.c file contains an example backend for use on embedded platforms that lack a file system. test_onefile.c is designed to read and write directly to hardware (such as a flash memory chip.) The test_async.c file demonstrates how to modify SQLite to implement asynchronous I/O. This gives the appearance of much faster COMMITs at the cost of Durability. The test6.c source file simulates the kind of file system damage that occurs during a sudden power loss. This module is used to test SQLite in order to verify that it will not corrupt the database file during a power loss. The journal.c file contains an optimization which avoids creating a journal file during commits on systems that support atomic sector writes. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Nicolas Williams <[EMAIL PROTECTED]> wrote: > On Wed, Jan 09, 2008 at 09:03:51PM +, [EMAIL PROTECTED] wrote: > > In fact, the only company > > I know of that makes use of shared libraries for SQLite is Apple. > > Solaris will be shipping SQLite 3.x as a shared library. > > > They can get away with this because they control the operating > > system. But notice that 3rd-party products that run on a Mac > > still generally statically link against their own SQLite rather > > than use whatever shared library that Apple supplies. > > The same applies to Solaris. 3rd party products will be free to use the > version shipped with the OS, but depending what stability we decide to > accord to SQLite 3.x, we may either not update it across incompatible > changes (and/or ship multiple versions), or we may update it across > incompatible changes at any time. In theory, all updates to SQLite3 should be backwards compatible. Though, of course, there have been cases where updates have broken older code that depended upon bugs that were subsequently fixed. :-) The vendors that I work with who are concerned about stability (which is to say "all" vendors that I work with) always statically link against SQLite. So they don't care if you have a shared library available or not - they aren't going to use it. > > As for DLL hell... My impression is that multiple instances of SQLite > 3.x can exist in the same process provided that: a) no two instances > share the same database files, b) they are linked/loaded in such a way > as to avoid symbol conflicts (this can be done with group+local > linker/rtld options). This sort of situation is likely to arise in > pluggable frameworks (e.g., PAM). > > > I think you are better of picking a version of SQLite that > > you like, making it a part of your source tree, and going > > with that. It is simple enough to upgrade - just drop in a > > new file. Trying to "install" SQLite or making dependencies > > on SQLite just complicates matters unnecessarily. > > Would you recommend that we not make SQLite 3.x in Solaris available to > third parties? > I think having a libsqlite3.so available is great. There will likely be smaller apps that want to compile with -lsqlite3. I think it is nice to accommodate them. Presumably, Solaris will be using sqlite3 internally (in place of sqlite2?) so it does not cost you anything to make it available to third parties. On the other hand, I recommend to anybody building an application that you should statically link against SQLite. Regardless of whether or not you make libsqlite3.so available, you definitely should make the sqlite3 CLI available. That is a very handy thing to have when using a shell. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Am Wed, 9 Jan 2008 10:20:31 -0800 (PST) schrieb Ken: > Definately use 3.5.4. > > Not sure how to determine at compile time if the threadsafe part is > enabled. You can always compile yourself to guarantee its set, thats > what I do. > > sqlite will lock the database file for you automatically. Your > threads do not need to implement locking. But they do need to handle > in some fashion. SQLITE_BUSY and/or SQLITE_LOCKED error codes. > > You should also look at "Begin", "Begin Exlusive" and "Begin > Immediate" sql commands. > > As a user you don't get to pick the lock state, sqlite does that > automatically for you. You must handle the return codes suche as > SQLITE_BUSY, SQLITE_LOCKED etc. > > I don't have any simple examples available, sorry. I don't understand how to use the sqlite3_io_methods. It seems I need a sqlite3_file. Not sure I get it from the API. The sqlite3_open call returns an int. So how do I use this? Perhaps you could sketch some pseudo code or paste some code where locking with SQLITE_BUSY and SQLITE_LOCKED is used. regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
On Wed, Jan 09, 2008 at 09:03:51PM +, [EMAIL PROTECTED] wrote: > In fact, the only company > I know of that makes use of shared libraries for SQLite is Apple. Solaris will be shipping SQLite 3.x as a shared library. > They can get away with this because they control the operating > system. But notice that 3rd-party products that run on a Mac > still generally statically link against their own SQLite rather > than use whatever shared library that Apple supplies. The same applies to Solaris. 3rd party products will be free to use the version shipped with the OS, but depending what stability we decide to accord to SQLite 3.x, we may either not update it across incompatible changes (and/or ship multiple versions), or we may update it across incompatible changes at any time. As for DLL hell... My impression is that multiple instances of SQLite 3.x can exist in the same process provided that: a) no two instances share the same database files, b) they are linked/loaded in such a way as to avoid symbol conflicts (this can be done with group+local linker/rtld options). This sort of situation is likely to arise in pluggable frameworks (e.g., PAM). > I think you are better of picking a version of SQLite that > you like, making it a part of your source tree, and going > with that. It is simple enough to upgrade - just drop in a > new file. Trying to "install" SQLite or making dependencies > on SQLite just complicates matters unnecessarily. Would you recommend that we not make SQLite 3.x in Solaris available to third parties? Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Andreas Volz <[EMAIL PROTECTED]> wrote: > > The reason is that I had some bad luck integrating applications into > Gentoo that include dependency sources. The Linux (here: Gentoo) way is > to have shared objects of all dependencies and the ability to let all > applications automatic benefit from a new compatible library release. > For windows it's for sure easier to simply use the amalgamation version. No need for a source dependency. Simply include sqlite3.c and sqlite3.h as part of your source tree. When you want to upgrade, grab a new copy of sqlite3.[ch] from the website and drop them in place of the old. In my experience, this is what most developers do. Certainly this is what I do. (Witness the sqlite3.c source file found in the source to http://fossil-scm.hwaci.com/) The SQLite source code is also found in trees for and statically linked with PHP, Monotone, Firefox, Skype clients, McAfee products, Google Gears, Adobe Lightroom and Acroread, and countless other programs, known and unknown. In fact, the only company I know of that makes use of shared libraries for SQLite is Apple. They can get away with this because they control the operating system. But notice that 3rd-party products that run on a Mac still generally statically link against their own SQLite rather than use whatever shared library that Apple supplies. I think you are better of picking a version of SQLite that you like, making it a part of your source tree, and going with that. It is simple enough to upgrade - just drop in a new file. Trying to "install" SQLite or making dependencies on SQLite just complicates matters unnecessarily. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Am Wed, 09 Jan 2008 18:17:55 + schrieb [EMAIL PROTECTED]: > Andreas Volz <[EMAIL PROTECTED]> wrote: > > > > I've only 3.4.1 installed. But Gentoo has 3.5.4 in the unstable > > tree. I'll install that one if needed. > > > > I see this idea expressed often, Andreas. Please help me to > understand how I can improve the SQLite website or documentation > to make it clear that SQLite does *not* need to be "installed"? > > SQLite is available as a single file of ANSI-C code. The name > of the file is "sqlite3.c". There is a companion header file > named "sqlite3.h" which defines the interface. You download > these two files from > > http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip > > and add them to the other source code files in your project, > and add a line to your Makefile so that sqlite3.c gets compiled > in. > > No installation. No setup. No worries about Gentoo unstable. > It's just a file of C code. > > Have I not done enough to get this point across? Please suggest > what else I can do so that people understand? I've understood that sqlite could be easy included in my applications source. My idea was to do it two different ways parallel. Have a configure option that let me choose to use the amalgamation version included or the distribution wide installed version. The reason is that I had some bad luck integrating applications into Gentoo that include dependency sources. The Linux (here: Gentoo) way is to have shared objects of all dependencies and the ability to let all applications automatic benefit from a new compatible library release. For windows it's for sure easier to simply use the amalgamation version. I hope you could understand this reason. Both ways have their pros and cons. regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
[EMAIL PROTECTED] wrote: I see this idea expressed often, Andreas. Please help me to understand how I can improve the SQLite website or documentation to make it clear that SQLite does *not* need to be "installed"? I think the sqlite.org make this very clear, but people just can't believe this: It's to good to be true :) Best regards Daniel - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Definately use 3.5.4. Not sure how to determine at compile time if the threadsafe part is enabled. You can always compile yourself to guarantee its set, thats what I do. sqlite will lock the database file for you automatically. Your threads do not need to implement locking. But they do need to handle in some fashion. SQLITE_BUSY and/or SQLITE_LOCKED error codes. You should also look at "Begin", "Begin Exlusive" and "Begin Immediate" sql commands. As a user you don't get to pick the lock state, sqlite does that automatically for you. You must handle the return codes suche as SQLITE_BUSY, SQLITE_LOCKED etc. I don't have any simple examples available, sorry. Ken Andreas Volz <[EMAIL PROTECTED]> wrote: Am Wed, 9 Jan 2008 09:03:35 -0800 (PST) schrieb Ken: > Andears, > > SQLITE 3.5.x is thread safe when configured and compiled with > --enable-threadsafe. I've only 3.4.1 installed. But Gentoo has 3.5.4 in the unstable tree. I'll install that one if needed. And is it possible to find out at compile time (or runtime) if sqlite was compiled with treadsave? > You can create multiple db connections to a single database. > > But only one connection will be allowed to write to the Database at a > time. > > Take a look at > http://www.sqlite.org/lockingv3.html > http://www.sqlite.org/34to35.html (section 2.1.5 part about locking) Thanks for the links. I think the PENDING state should be the correct one for my application. But not sure if I understand it correct. So my threads could read and write without locking. Sqlite does then execute the write if a lock is available. So I don't have to trouble about locking. Is this correct? Are there any examples for sqlite multi-threading access? regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Andreas Volz <[EMAIL PROTECTED]> wrote: > > I've only 3.4.1 installed. But Gentoo has 3.5.4 in the unstable tree. > I'll install that one if needed. > I see this idea expressed often, Andreas. Please help me to understand how I can improve the SQLite website or documentation to make it clear that SQLite does *not* need to be "installed"? SQLite is available as a single file of ANSI-C code. The name of the file is "sqlite3.c". There is a companion header file named "sqlite3.h" which defines the interface. You download these two files from http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip and add them to the other source code files in your project, and add a line to your Makefile so that sqlite3.c gets compiled in. No installation. No setup. No worries about Gentoo unstable. It's just a file of C code. Have I not done enough to get this point across? Please suggest what else I can do so that people understand? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Am Wed, 9 Jan 2008 09:03:35 -0800 (PST) schrieb Ken: > Andears, > > SQLITE 3.5.x is thread safe when configured and compiled with > --enable-threadsafe. I've only 3.4.1 installed. But Gentoo has 3.5.4 in the unstable tree. I'll install that one if needed. And is it possible to find out at compile time (or runtime) if sqlite was compiled with treadsave? > You can create multiple db connections to a single database. > > But only one connection will be allowed to write to the Database at a > time. > > Take a look at > http://www.sqlite.org/lockingv3.html > http://www.sqlite.org/34to35.html (section 2.1.5 part about locking) Thanks for the links. I think the PENDING state should be the correct one for my application. But not sure if I understand it correct. So my threads could read and write without locking. Sqlite does then execute the write if a lock is available. So I don't have to trouble about locking. Is this correct? Are there any examples for sqlite multi-threading access? regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite3 from multiple threads
Andears, SQLITE 3.5.x is thread safe when configured and compiled with --enable-threadsafe. You can create multiple db connections to a single database. But only one connection will be allowed to write to the Database at a time. Take a look at http://www.sqlite.org/lockingv3.html http://www.sqlite.org/34to35.html (section 2.1.5 part about locking) Andreas Volz <[EMAIL PROTECTED]> wrote: Hello, I like to use sqlite3 from a multi-threaded application. The situation is that I've several threads that like to write into a single DB file and into the same table. I read something about sqlite is thread save. But I'm not sure how much. Is it allowed to open the DB file and table parallel multiple times from multiple threads? Do I've to take care of locking in my application or is this handled by sqlite? Or should I open only one DB handle and handle parallel access in my application and do the real access from one single point? So what is the correct way to access the same sqlite DB/table from multiple threads? regards Andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
