mORMot Open Source

amsdev

Member

Registered: 2011-09-16

Posts: 23

SynDBSqlite3, threads, concurency questions

Hi, Arnaud and all !

Please can anybody give me more details about using Sqlite via SynDBSqlite3 in multithreaded server (sorry, I can't find needed info in great mORMot documentation):

1. My sqlite3.dll compiled with SQLITE_THREADSAFE=2 option (multithreaded). Should I create TSQLDataBase and TSQLDBSQLite3ConnectionProperties in each thread ? Or I must create it only one time and share between my threads ?

2. Does SynDBSqlite/SynSqlite3 opens sqlite database in WAL journal mode by default (where writers not block readers) ? Or I should switch to WAL mode manually for best concurency and performance ?

3. Should I worry about serializing access to database and use CriticalSection (or MREW for WAL mode) ?

4. Does SynDBSqlite/SynSqlite3 handle SQLITE_BUSY and/or SQLITE_LOCKED errors ? For example, I have several read threads and one writer thread who want to alter some table and add additional field. During ALTER TABLE sqlite_master table is modified and readers may get busy/locked error because of it. How to avoid it ? Use CriticalSection or MREW myself to serialize access, handle busy/locked errors then try again or restart transaction, or SynDB framework do this job automatically ?

5. I see this comment in SynSqlite3.pas:

//  TSQLDataBase is simple wrapper for direct SQLite3 database manipulation
// - embed the SQLite3 database calls into a common object
// - thread-safe call of all SQLite3 queries (SQLITE_THREADSAFE 0 in sqlite.c)

And also I see CriticalSection usage in this class.

Is it's means I can't use Sqlite with SQLITE_THREADSAFE=2 + WAL option and can't read database from several threads (and write from single thread) in same time for best server performance ?

Or (maybe) SQLITE_THREADSAFE=2 + WAL is not best options for multithreaded server ?

My app server needs this operations on database:

- 50% is fast reads (simple selects with LIMIT/OFFSET for pagination)
- 30% is fast insert/update/delete (single record)
- 18-19% is batch insert/update/delete (100k-500k records, 5000 records per transaction)
- 1-2%  is operations like ALTER TABLE requires exclusive lock on entire database.

I think using WAL mode and MREW lock for reads and writes and some global lock like critical section for exclusive operations like ALTER table. But I dont know is this needed with framework ? Or maybe it's not needed at all and simple CriticalSection to serialize everything gives best results ? At this time I not use ORM features, only SynDBSqlite/SynSqlite3.

Many thanks for help !

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBSqlite3, threads, concurency questions

1. As you found out, there is a lock at SynSQLite3 level, so using SQLITE_THREADSAFE=2 will have no effect.
You should rather use the static linked version of SQlite3, shipped with mORMot.

2. We don't set any mode by default. You have to set WAL explicitly.

3. See 1.

4. No, it does not, since it is expected to have locked/unshared access to the DB file.

5. In practice, a single lock is very fast, when you are in exclusive mode.
The idea is
- to abstract all persistence into an interface, implemented in a class
- add some caching, at object level, in this class
- to use prepared statements with parameters, which will be cached by the framework
- to use batches even for single insert/update/delete, grouping all write process in a single background thread if possible, with a batch (there is some methods to do this via the batch or any interface at ORM/TSQLRest level)
- to set exclusive mode and synchronous write off
With this way of use, we have tremendous performance, and would also work with another DB backend.
See https://synopse.info/files/html/Synopse … ml#TITL_60
and https://synopse.info/files/html/Synopse … ml#TITL_59

Trying to use multi-threading won't help much, since you will have locks within the Sqlite3 engine itself, so you may gain a few %, but not more than what we offer.
The statement cache, for instance, expects a locked access to the engine, otherwise it may be less efficient, and you may loose much performance than you gained.

Last but not least, if practice, using the ORM layer may give you better performance than direct SynSQLite3 access, since you may use easily methods like TSQLRest.AsynchBatchAdd.

amsdev

Member

Registered: 2011-09-16

Posts: 23

Re: SynDBSqlite3, threads, concurency questions

Arnaud, thank you very much for your reply ! It's clear now !