mORMot Open Source

amsdev

Member

Registered: 2011-09-16

Posts: 23

Proper usage of SynDBSQlite in multithreading/concurency

Hi !

I trying to move one of my projects to mORMot for better performance. This project is relatively complex, for now I try to move
only DB access to SynDBSQlite. I can't use ORM features now because it's requires total project rewrite. I hope starting from DB layer makes me more familiar with framework and I can use ORM features in another prjects.

I have several questions about proper SynDBSQlite usage in threads. mORMot documentation is great but not cover this moments:

1. I have global connection object somewhere in main unit:

GlobalConnection:=TSQLDBSQLite3ConnectionProperties.Create(MyDataPath+'common.db','','','');

How to use it right in threads ?

Method A:

procedure AccessDBInThread;
var
  ThreadConnection: TSQLDBSQLite3Connection;
  ThreadStatement: TSQLDBSQLite3Statement;
begin
ThreadConnection:=GlobalConnection.NewConnection; // call this method if the shared MainConnection is not enough (e.g. for
                                                                             // multi-thread access)
ThreadStatement:=ThreadConnection.NewStatement;
try
  // Prepare, bind, execute statement
finally;
  ThreadStatment.Free;
  ThreadConnection.Free;
end;
end;

Method B:

procedure AccessDBInThread;
var
  ThreadConnection: TSQLDBSQLite3Connection;
  ThreadStatement: TSQLDBSQLite3Statement;
begin
ThreadConnection:=GlobalConnection.ThreadSafeConnection; 
ThreadStatement:=ThreadConnection.NewStatement;
try
  // Prepare, bind, execute statement
finally;
  ThreadStatment.Free; 
  ThreadConnection.Free; // should be freed ?
end;
end;

Method C:

procedure AccessDBInThread;
var
  ThreadStatement: TSQLDBSQLite3Statement;
begin
ThreadStatement:=GlobalConnection.NewThreadSafeStatement;
try
  // Prepare, bind, execute statement
finally;
  ThreadStatment.Free;
end;
end;

Method D:

procedure AccessDBInThread;
begin
// Can I safe use GlobalConnection in threads this way ? :
with GlobalConnection.ExecuteInlined('SELECT * FROM Table',true) do
   begin
   Step;
   // process
   end;
end;

Usage scenario: AccessDBInThread procedure is api request handler, it's executes in existing non-mORMot based api server's thread.

What method (A,B,C) is proper, best and fastest  for threads ? Maybe all is wrong and I must use something different ?
Is statement cache will works in all A,B,C methods ?
Can I use connection's MainSQLite3DB.LastInsertRowID, LastChangeCount, TotalChangeCount properties if I obtain thread statements via GlobalConnection.NewThreadSafeStatement (Method C) ?
Can I use method D in threads or I should create TSQLDBSQLite3ConnectionProperties for each thread ?

2. How locking mechanism works ?

If I understand correctly, after I obtain thread-local statement and/or connection object they have exclusive DB access locked by
RTLCriticalSection in SynSQlite3.pas. But how exactly locking works (how long lock is active) ?

Is database locked only between ThreadStatement.Execute and ThreadStatement.Reset or ThreadStatement.Free ?

I ask because I need to know how to implement long batch operations like importing million records from file. I need to sleep batch thread each time after importing several thousands records to let other threads do their job on unlocked database.

But where to Sleep ? Where lock is released ?
Just after ThreadStatement.Reset ? Or just after ThreadStatement.ExecuteNoResult, etc ?

3. Locking and transactions

Is database access always serialized for all scenarios ?

For example can I run this query in Thread A:

ThreadAConnection.StartTransaction;
ThreadAStatement.ExecuteNoResult('ALTER TABLE MyTable...'); // this requires exsclusive lock because sqlite_master is changed
ThreadAConnection.Commit;

and this query in Thread B at same time:

ThreadBConnection.StartTransaction;
ThreadBStatement.Reset;
ThreadBStatement.Bind;
ThreadBStatement.ExecuteNoResult('INSERT INTO MyTable...'); // do 5000 batch inserts per transaction to same table !
ThreadBConnection.Commit;
// repeat until 1M records imported...

My connections will use LockingMode:=lmExclusive, so my app have exslusive access to database file. Concurency is only in my threads.

Should I need any additional errors handling or all access always serialized in SynSQlite3.pas and I no need to worry about concurency  errors handling like SQLITE_LOCKED, SQLITE_BUSY ?

If I manually start transactions in Thread A and Thread B is I need dedicated connection objects for each thread ?
Or I can get threads statements via GlobalConnection.NewThreadSafeStatement but start transactions via GlobalConnection.MainConnection.StartTransaction ?

Thanks for help and I hope my questions is not to stupid ) Of cource I looking mORMot source but they big and complex, so I ask to be sure.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Proper usage of SynDBSQlite in multithreading/concurency

Short answer - TSQLDBSQLite3Statement Not thread safe.  Mormot use global lock during write operations to sqlite

amsdev

Member

Registered: 2011-09-16

Posts: 23

Re: Proper usage of SynDBSQlite in multithreading/concurency

Short answer - TSQLDBSQLite3Statement Not thread safe.

So, how to access database from threads ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Proper usage of SynDBSQlite in multithreading/concurency

As written, use NewThreadSafeStatement: it IS thread-safe if used in the same thread.
This is the whole purpose of this method - as documentation.

Also you should ensure that you don't mess with transactions: each context, therefore each thread, should have its own transaction flow, exclusive from others - otherwise the result is undefined.
For SQLite3 transactions, you should use a single transaction at once.
If you use the ORM, there is mechanism to ensure this, and the TSQLRestBatch to make this process very efficient.
If you don't use the ORM, you have to ensure that you handle one transaction at the same time. The easiest is to define a global lock around all your transactions and statements.

amsdev

Member

Registered: 2011-09-16

Posts: 23

Re: Proper usage of SynDBSQlite in multithreading/concurency

Arnaud, thank you very much for your answers ! But I still misunderstand some things:

As written, use NewThreadSafeStatement: it IS thread-safe if used in the same thread.
This is the whole purpose of this method - as documentation.

If several threads obtain thread safe statements like this:

GlobalConnection.NewThreadSafeStatement;

Can I use GlobalConnection.MainSQLite3DB.LastInsertRowID, LastChangeCount in these threads ? Is this properties will contains valid values ?

If you don't use the ORM, you have to ensure that you handle one transaction at the same time. The easiest is to define a global lock around all your transactions and statements.

If I define my own global lock and use only one transaction/statement in time for what I need GlobalConnection.NewThreadSafeStatement ?

I see TSQLDataBase.Lock is called before executing every sql query. For what I need additional my own global lock if CriticalSection already used in TSQLDataBase.Lock ?

Thanks for help !

Last edited by amsdev (2018-02-25 21:34:02)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Proper usage of SynDBSQlite in multithreading/concurency

The LastInsertRowID values are valid, but are not thread-safe: it is from the GlobalConnection, so you need to protect the SQlite3 engine state between the INSERT and the LastInsertRowID call.

GlobalConnection.NewThreadSafeStatement is mainly for other SQL engines (e.g. OleDB, Oracle, ODBC...), which sometimes require a per-thread connection.
For SQLite3, which has a single connection, there is no difference with the GlobaConnection.NewStatement method, because ThreadSafeConnection returns the MainConnection.

The SynDB units were meant to supply the ID at insertion - as the ORM does - since there are some DB engines which don't provide automatic ID generation at Insert.
If you expect to work with Sqlite3, and start using GlobalConnection.MainSQLite3DB instance, by-passing the SynDB abstraction, you may rather use SynSQLite3.

If you expect even better integration, and even performance (e.g. caching or batch modification), try the ORM.
For instance, it will be very efficient for bulk insertion, and will manage the transactions for you.

Last but not least, think about abstracting all your storage logic behind a persistence service: don't start from the DB, but from the logic!

amsdev

Member

Registered: 2011-09-16

Posts: 23

Re: Proper usage of SynDBSQlite in multithreading/concurency

Thanks for you answers ! It's more clear now )

I send mail to webcontact1 at synopse info about paid support. I want to pay for you time and expertise and ask some advanced questions. Waiting for your reply...