Hi, 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. 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. If WAL mode is better - should I use Read Uncommitted with WAL mode for better concurrency ? 3. Should I use locking_mode = EXCLUSIVE for best performance ? Database will be accessed only from single process. 4. BEGIN DEFFERED vs BEGIN IMMEDIATE vs BEGIN EXCLUSIVE for writing transactions. 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 ? Thanks for help and excuse me for bad English. --- With best regards, Max Terentiev. Business Software Products. AMS Development Team. supp...@bspdev.com _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
