On Saturday, 25 March, 2017 13:15, Max Terentiev <supp...@bspdev.com> wrote:
> I developing app server with sqlite as storage engine. > Sqlite have many settings combinations affecting concurrency please help > me to chose right settings for my task: > 1. SQLITE_THREADSAFE 1 (serialized) vs SQLITE_THREADSAFE 2 (multithreaded) > My app server will execute each client request in their own thread. Should > I use Shared Connection and SQL_THREADSAFE 1 or per thread connections + > SQLITE_THREADSAFE 2 ? I expect about 100-200 requests per second (80% of > them is simple SELECT queries). > Requests will be handled by threads pool, so only 50-70 threads should be > always working. THREADSAFE=1 (serialized) ensures that the same *CONNECTION* (or things derived from the same connection, such as statements) do not simultaneously make calls into the SQLite engine code at the same time from different threads, thus preventing internal corruption of the process. If you yourself ensure that a given *CONNECTION* and all objects derived from that connection are only ever accessed from a single thread, then THREADSAFE=2 removes the mutex checking (overhead) that is used to ensure that CONNECTION associated state data is not corrupted by simultaneous entry on multiple threads. In other words, if you have a connection per thread, and do not access connections (and statements derived from that connection) except in the thread in which the connection was created (*EVER*), then you can use THREADSAFE=2. If you are sharing a connection object across threads then you need THREADSAFE=1 in order to ensure that the connection object (and derivatives thereof such as statements) are not simultaneously accessed from multiple threads concurrently. Note that if you have one connection, and prepare two statements, those statements cannot be used simultaneously from different threads since they refer to the same global state (the connection). In the case of THREADSAFE=1 the access will be serialized by mutexes to prevent you from corruption. If you do that with THREADSAFE=2 then AHWBL (All Hell Will Break Loose). > 2. Shared Cache + Read Uncommitted + busy timeout vs WAL mode > What is better for multithreaded connections ? My app server needs this > operations: > - Simple SELECT queries returning 100-500 records. 80% of requests > - Bulk INSERT or DELETE records, 10000 records per transaction (several > millions records total). 15% of requests. > - INSERT/DELETE/UPDATE of single record. 5% of requests > - CREATE TABLE, ALTER TABLE, DROP TABLE - rare requests. SHARED CACHE is designed for resource constrained systems. Unless you are resource constrained do not use it. Buy more resources instead. READ UNCOMMITTED permits one connection to "see" data that has not been committed in another transaction. Have you read https://www.sqlite.org/isolation.html > If WAL mode is better - should I use Read Uncommitted with WAL mode for > better concurrency ? No. Read Uncommitted has no effect on concurrency. If you use WAL, you can simultaneously have ONE connection writing and ANY number of simultaneous readers. Without WAL mode you can have *either* ONE connection writing *OR* multiple connections reading. WAL also has Repeatable Read isolation for transactions which only read the database. > 3. Should I use locking_mode = EXCLUSIVE for best performance ? Database > will be accessed only from single process. No. You said you have multiple threads. That means that if you have a connection per thread (as you ought to have) then when one thread acquires an EXLUSIVE lock all other threads will be locked out. The purpose of EXCLUSIVE locking is to *eliminate* concurrency. > 4. BEGIN DEFFERED vs BEGIN IMMEDIATE vs BEGIN EXCLUSIVE for writing > transactions. If you know you are going to be doing an update of the database, start the transaction with BEGIN IMMEDIATE. If you are reading, then start the transaction with BEGIN. BEGIN EXCLUSIVE does exactly what it says and grants that connection EXCLUSIVE access to the database for reading or writing to the exclusion of all other connections (and threads, since you have one connection per thread). > Should I use BEGIN EXCLUSIVE for writing transactions ? If BEGIN EXCLUSIVE > success does it guarantee no SQLITE_BUSY/SQLITE_LOCKED errors for nested > sqlite3_step > and COMMIT call ? No and Yes. BEGIN IMMEDIATE does the same thing for transactions in which you KNOW you will be updating the database, without locking out other connections (threads). > Thanks for help and excuse me for bad English. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
