mORMot Open Source

oz

Member

Registered: 2015-09-02

Posts: 95

Getting rid of TSQLRestServerDB giant (read) lock

Hi,

currently there is a giant lock in TSQLRestServerDB database access.
Whenever you are writing OR reading something via the ORM Layer you will find yourself trapped in that lock (DB.Lock/DB.LockJSON/DB.Unlock/DB.UnlockJSON).
When using SQLite3 as your backend DB this makes sense for writing. SQLite only allows one single concurrent writer anyways.
But imho this is very bad for reading from db.
As long as ALL of your SELECT statements are executed fast and TSQLRestServerDB's internal JSON result caches aren't flushed too often (because of INSERT/UPDATE statements) you won't notice any delay.
But things start to become very slow if you have some not-so-fast db reads and a lot of database write operations flushing the cache all the time.

-> Every single SELECT statement will be blocking all other concurrent db access until that SELECT statements result data is fetched.

In my use-case scenario this is exactly what happens. There are some non-predictable and therefore non-optimizable SELECT statements coming from "user-land" taking 1 second + to execute. If those statements are triggered by 10 concurrent client sessions in parallel, then I have an unresponsive DB for 10*1 seconds. All other tasks (may it be background jobs accessing db, or other users trying to read/write anything from db) have to wait for all other db stuff to be finished. This results in unresponsive "system hangs".

I'm currently in the stage of implementing and testing multiple reader/single writer SQLite3 db access in my codebase.
The idea is to have one single main writer connection, but multiple reader connections (one connection per (reader) thread).

This is what i've done so far:

- Add a ThreadSafe SQLite3 connection pool to TSQLRestServerDB.
- Assign one of those pooled connections to TSQLRestServerDB's as it's main connection.
- Make all those pooled SQLite connections use "SQLITE_OPEN_SHAREDCACHE", "WAL mode" and "PRAGMA read_uncommited=true;", allowing multiple reader/single writer pattern without "SQLITE 262/Table is locked" errors.
- In TSQLRestServerDB.MainEngineList() use a thread safe connection from pool instead of main connection. Only a short cache-lookup-lock is required here, the DB.LockJSON() call is gone.
- Make each pooled connection use it's own prepared statement cache.
- Make each pooled connection use a common JSON Result cache shared by all connections.

First results look promising so far...

Are there any opinions why it SHOULDN'T be done like that? Are there any drawbacks I didn't think of?

Cheers,
oz.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Getting rid of TSQLRestServerDB giant (read) lock

One second SELECT is too long.

I would use another way of storing the data.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: Getting rid of TSQLRestServerDB giant (read) lock

Unfortunately, in the real life long select's can occurs independent on data structures we use for storing.
There is too many sources for it:
- huge DB or complex queries (query example: all peoples who have child's under 3 year for 40million peoples table)   
- low qualifications of developers (juniors in the big teams for example)
- low budget \ short development time and as a result - no time for optimizations
- etc

@oz - we use an SQLite DB in the mode similar you describe - see our ThreadSafe connection pol implementation,
but we do not use a mORMot ORM (in terms of mORMot all our endpoints is Interface based services).
And instead of "read_uncommited" we increase a BusyTimeout

In case of ORM I think not only cache but also all kinds of Proxies (and may be some other part of architecture) expect a DB lock.

sakura

Member

From: Germany

Registered: 2018-02-21

Posts: 228

Website

Re: Getting rid of TSQLRestServerDB giant (read) lock

I've seen it myself, you can optimize as much as you like, sometimes you miss something or other and the whole server blocks everything else. Sometimes, only real-life data will show the problems, and often, as a developer you do not have access to such, for reasons of data security.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Getting rid of TSQLRestServerDB giant (read) lock

@mpv
You are right, real life sucks (tm).
I will investigate into optional per-thread SQLite3 connections for mORMot 2 ORM.
My guess is that your code is very close to what @oz expects - but integrated into the ORM.

One limitation I see is that even if the DB layer is not blocking, the REST layer is still blocking.
There is the TRest.AcquireExecution[] locks which block the DB access.
So if you use a remote REST interface to access the data, reads will be serialized.
But for a server-side service, the ORM layer will bypass these TRestServer locks.
Remote REST access is a bad idea anyway for production code (for architecture and security reasons too). Writing services with direct DB/ORM access is a better way.

@sakura
Yes, this is when logging makes sense. mORMot logging includes timing of all DB statements by default, which is a lifesaver to track bottlenecks on production.