About thread-safety about using TSQLRestBatch with TSQLRestServerDB

edwinsn · 2016-09-29 08:25:54

Hello ab,

I have recently noticed this code example:

https://github.com/synopse/mORMot/blob/ … hIndex.dpr

Which you specifically wrote for answering a stackoverflow.com question - you have broaden my view on the mORMot's capability with two of your answers there, thanks

One quick question, I read some of your code:

var
  db: TSQLRestServerDB;

  procedure DoInsert;
  var i: integer;
      rec: TSQLEntry;
      batch: TSQLRestBatch;
  begin
    rec := TSQLEntry.Create;
    batch := TSQLRestBatch.Create(db,TSQLEntry,10000,[boExtendedJSON]);
    try
      for i := 0 to COUNT-1 do begin
        ......
        batch.Add(rec,true);
      end;
      db.BatchSend(batch);
    finally
      ......
    End;

Assume I have multiple threads doing the same insertion, each thread has its own TSQLRestBatch instance, but all are operating on the same TSQLRestServerDB instance. Is it thread-safe to do that?

Thanks.

ab · 2016-09-29 09:43:50

Yes, since each TSQLRestBatch will be processed one at a time, in its own sequential BatchSend execution.

edwinsn · 2016-09-29 10:10:23

Thanks ab, very helpful!

Vitaly · 2020-08-08 19:33:41

A clarifying question:
Should it work the same thread-safe with BatchSend and parallel CRUD operations (at the same time from other threads) with the same TSQLRestServerDB?
So far it seems that we're getting problems particularly in such situation (Zeos, FB 2.5.9):

20200808 19261957  +            uNextDB.TNextDB(03eed790).EngineBatchSend  inlen=585
20200808 19261957  +                    SynDBZeos.TSQLDBZEOSConnection(020f6610).StartTransaction
20200808 19261957 SQL           SynDBZeos.TSQLDBZEOSStatement(020b76d0) ExecutePrepared 104us select AVTALETEKSTID as ID,AVTALETEKST as AptText from AVTALETEKST where AVTALETEKSTID=55
20200808 19261957 DB            SynDBZeos.TSQLDBZEOSStatement(020b6e10) Prepare 35us select SMSID as ID,SMSId,TLF as PhoneNumber,SMSText,DATO as SMSDate,TID as SMSTime,TimeID from SMSOUTBOX where TimeID=?
20200808 19261957 EXCOS         EExternalException (e06d7363) [TNextHttpSvr 8210/THttpApiSvr] at 7fff427a3e49
20200808 19261957  -                    00.007.439

It highly depends on the load. But if we change everything from TSQLRestBatch.Add/Update/... to using TSQLRestServerDB.Add/Update/... all goes fine.
Does it mean that we should always choose between using Batches and "direct" CRUD methods to make it safe, or have a separate TSQLRestServerDB instance for working with Batches for example?

upd: just in case - settings we're using (although with other settings it seems working the same): USE_SYNCOMMONS, hard_commit, default tmThreadPool

Last edited by Vitaly (2020-08-08 20:05:25)

Vitaly · 2020-08-10 15:52:46

Vitaly wrote:

upd: just in case - settings we're using (although with other settings it seems working the same): USE_SYNCOMMONS, hard_commit, default tmThreadPool

Sorry for the misleading information. We've found in code that there was tmMainConnection ThreadingMode set.
We need to check it more, but so far with tmThreadPool all seems to work fine. My bad

ab · 2020-08-10 17:12:34

tmMainConnection will use the very same connection from all threads, so it should be unsafe from multiple threads.

mORMot Open Source

#1 2016-09-29 08:25:54

About thread-safety about using TSQLRestBatch with TSQLRestServerDB

#2 2016-09-29 09:43:50

Re: About thread-safety about using TSQLRestBatch with TSQLRestServerDB

#3 2016-09-29 10:10:23

Re: About thread-safety about using TSQLRestBatch with TSQLRestServerDB

#4 2020-08-08 19:33:41

Re: About thread-safety about using TSQLRestBatch with TSQLRestServerDB

#5 2020-08-10 15:52:46

Re: About thread-safety about using TSQLRestBatch with TSQLRestServerDB

#6 2020-08-10 17:12:34

Re: About thread-safety about using TSQLRestBatch with TSQLRestServerDB

Board footer