I will leave aside the use of SHARED_CACHE which massively trades performance for memory usage (that is, it "totally kills" performance but also "hugely reduces" memory requirements) -- it trades a huge reduction in performance for a huge reduction in memory usage and changes some other things as well. It is designed for truly itty bitty things with no memory that only perform relatively simple tasks. Technically, it places multiple connections to the same shared cache (within the same process) within the same context and implements concurrency between those connections by software emulation rather than locking semantics. The context unit is the shared cache, not the connection to the shared cache. While this description is not perfectly true, it suffices to demonstrate that using SHARED_CACHE significantly changes the semantics and its use, where appropriate, significantly changes connection semantics and below describes normal (non SHARED_CACHE) semantics only.
All concurrency in SQLite3 is based on the CONNECTION (created by one of the sqlite3_open APIs). All children (example STATEMENTS) of the same CONNECTION share the identical single context which at its very lowest level includes concurrency and transaction control. Concurrency only applies between statements executed against different connections. Statements executed against the same connection all occur within the same context. When a transaction is commenced (whether implicitly or explicitly) that transaction applies to the context (connection). In other words, if all statements are executed against the same connection, then they all share the same context, and there is no concurrency (isolation) of execution between the statements whatsoever. The default SERIALIZED multithreading mode is NOT a concurrency control. It is there to prevent multiple threads from mutating the context (connection) at the same time. If that were permitted, great explosions would occur. >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. The word "process" and "processes" is incorrect. It should say: >SQLite allows multiple connections to have the database file open >at once (and these connections may be within the same or separate >processes), and for multiple connections to read the database at >once. When any connection 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 connections just wait on the >writer to finish then continue about their business*. >*assuming that busy_timeout is set to a non-zero value Does this help explain what you are seeing? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Lullaby Dayal >Sent: Sunday, 28 April, 2019 11:23 >To: SQLite mailing list >Subject: Re: [sqlite] Regarding sqlite3 reliability in using in >service oriented architecture > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
