mORMot Open Source

Chaa

Member

Registered: 2011-03-26

Posts: 249

External databases and thread safe design

Documentation says
(http://synopse.info/files/html/Synopse% … #TITLE_280):

11.5.1. Thread safe design
Remote external tables - use thread-safe connections and statements when accessing the databases via SQL;

When I use TSQLRestServerDB.RetrieveBlob on external SQLite3 table, I get an FastMM errors "The block header has been corrupted".

I see here the call stack:

sqlite3.prepare_v2
TSQLRequest.Prepare
TSQLDBSQLite3Statement.Prepare
TSQLDBConnection.NewStatementPrepared
TSQLDBConnectionProperties.GetMainConnection
TSQLDBConnectionProperties.ThreadSafeConnection
TSQLDBConnectionProperties.NewThreadSafeStatementPrepared
TSQLDBSQLite3Statement.ExecutePrepared
TSQLRestStorageExternal.ExecuteDirect
TSQLRestStorageExternal.EngineRetrieveBlob
TSQLRestServer.EngineRetrieveBlob

In all that function calls I can't see any locking or per thread connections.

But SQLite docs says (http://sqlite.org/compile.html#threadsafe):

When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a multithreaded program so long as no two threads attempt to use the same database connection (or any prepared statements derived from that database connection) at the same time.

What is the right way to using SQLite external tables from multithreaded server?

Last edited by Chaa (2016-05-30 10:45:09)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: External databases and thread safe design

Look closer.
There is a critical section at TSQLDataBase level.
See corresponding Lock/Unlock methods.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: External databases and thread safe design

For my internal needs I implement a TSQLite3ThreadSafeConnectionProperties = class(TSQLDBConnectionPropertiesThreadSafe). Copy-paste from a  TSQLDBSQLite3Statement but enable a WAL mode for a SQLite3 DB. Work well in a multi-thread environment without lock. I can share the code if needed.

Chaa

Member

Registered: 2011-03-26

Posts: 249

Re: External databases and thread safe design

When I call TSQLRestServer.RetrieveBlob from my code, TSQLDataBase.Lock* functions not called at all (I set breakpoints on it).

Instead, there is used TSQLRequest without locking.

When I call TSQLRestServer.Retrieve, the LockJSON function is called, as expected.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: External databases and thread safe design

AFAIK TSQLRestServerDB.MainEngineRetrieveBlob() calls DB.Lock().
Are you talking about external SQLite3 database access?

Then for an external SQlite3 database, ORM thread-safety applies.
See http://synopse.info/files/html/Synopse% … ml#TITL_25
You may have to set:

aServer.AcquireExecutionMode[execORMGet] := amLock;
aServer.AcquireExecutionMode[execORMWrite] := amLock;

But why would you use SQlite3 as external within a mORMot REST server?
It is pointless.
Use internal SQLite3 engine, via mORMotSQLite3.pas.

Chaa

Member

Registered: 2011-03-26

Posts: 249

Re: External databases and thread safe design

But why would you use SQlite3 as external within a mORMot REST server?
It is pointless. Use internal SQLite3 engine, via mORMotSQLite3.pas.

There are two files: main data with multiple tables and one big file for blobs.
The big file contains one table with blob data.

FBlobConnection := TSQLDBSQLite3ConnectionProperties.Create(LBlobDBFileName, '', '', '');
VirtualTableExternalRegister(FModel, TSQLBlobData, FBlobConnection, 'BlobData');

Maybe there is a simpler solution?

AFAIK TSQLRestServerDB.MainEngineRetrieveBlob() calls DB.Lock().
Are you talking about external SQLite3 database access?

Yes, instead of TSQLRestServerDB.MainEngineRetrieveBlob there is called
GetStaticDataServerOrVirtualTable(TableModelIndex).EngineUpdateBlob,
and no locking occurs.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: External databases and thread safe design

IMHO the best practice is to use a 2nd TSQLRestServerDB instance, and point the table to it using http://synopse.info/files/html/api-1.18 … RAGEREMOTE
This would be thread-safe.

Also consider http://synopse.info/files/html/api-1.18 … ORAGESHARD which may allow to create several DB files, sharding them by ID.

Chaa

Member

Registered: 2011-03-26

Posts: 249

Re: External databases and thread safe design

I use the following code:

FBlobConnection := TSQLRestServerDB.CreateWithOwnModel([TSQLBlobData], LBlobDBFileName, False);
FBlobConnection.CreateMissingTables();
FServer.RemoteDataCreate(TSQLBlobData, FBlobConnection);

Now it's worked fine.

Anyway, I think that documentation must point to not to use TSQLDBSQLite3ConnectionProperties with VirtualTableExternalRegister, because TSQLDBSQLite3ConnectionProperties is not thread safe.

And, as mpv wrote, we need to add TSQLDBSQLite3ThreadSafeConnectionProperties for that purpose.