Thank you for your valuable suggestion. As you mentioned, the possibility of a concurrent transaction with auto-commit transaction is already handled in our design using some flag variables.
I would like to understand some points discussed in sqlite3 documentation in detail. 1. Compile time configuration of sqlite3 library is serialized threading mode. As per Sqlite3 documentation, this mode can be used reliably in multithreaded application with no restriction. 2. Excerpt from https://sqlite.org/faq.html#q5 is as follows: SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. 3. *High Concurrency* SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution. The above three items are quite confusing. Our application requires a little bit of concurrency for a small amount of time. As these words are quite subjective, I do a funny stress experiment with sqlite3. Considering all this, I have written a test application running on Linux with sqlite3 library in serialized mode. My test application has 200 parallel threads in which 100 threads are executing SELECT * operation from a table and 100 are executing update table (alternate fields in alternate run) command in auto-commit mode (while(1)). I haven't verified the data correctly written in database as I only rely on return code and I was stress testing. I expect at some point it should produce SQLITE_BUSY command at some point of time. But it didn't. Only thing I got is:- while the test application is running, in a separate SQLite command prompt I open the same database and executed .tables command. This time, I got a database locked error in my test application. So my questions are:- 1. In auto-commit mode in serialized threading mode, how command queueing works? 2. Multiple simultaneous calls to sqlite_exec() performing Multiple write commands or read commands while write is in progress - will this be handled by sqlite_exec() itself? Or does the application need to do some kind of locking to avoid such situation as mentioned in the FAQ? In serialized mode, sqlite3 implements its own locking, right? Do application need to do a high level locking beyond this? 3. Will there be a case the database can become corrupt or some operations missed to get performed in such a case? Thank you, Lullaby On Fri, Apr 26, 2019, 9:07 PM Jens Alfke <j...@mooseyard.com> wrote: > > > > On Apr 25, 2019, at 6:09 PM, Lullaby Dayal <lullaby.tec...@gmail.com> > wrote: > > > > A single database connection is shared > > among all these services. More than one service access the service API to > > read/write database at the same time. No locking is implemented in our > > service accessing the database. > > The one issue that comes to mind is transactions: your design has no > isolation (the I in ACID) between threads. > > In more detail: if a thread executes "BEGIN", does some > inserts/updates/deletes, and then executes "END", other threads that issue > SQLite calls at the same time will see the uncommitted changes being made > by the first thread. Depending on your design, this can cause problems, > especially if the first thread ever ends up aborting the transaction > (leaving the other threads with stale data that isn't in the database.) > > It gets even more "fun" if the secondary threads are making their own > changes (without BEGIN/END), because those changes will become part of the > first thread's transaction, so if the first thread aborts, the other > threads' changes will be lost. > > For this reason it's usually good to have a mutex for transactions: you > lock the mutex before calling BEGIN and unlock it after calling END. (If > you make one-off changes without BEGIN/END, you have to lock the mutex > around those calls too.) > > If this isn't a problem for you because you never use transactions, then > you may have a different problem: write performance. Issuing multiple > writes without a transaction is inefficient, because each write has to > begin and commit its own transaction, and the commits tend to be expensive > (depending on the filesystem) because they have to ensure durability. > > —Jens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
