mORMot Open Source

turkerali

Member

Registered: 2021-03-30

Posts: 10

Method based web services, TSQLRestServerDB thread safety concerns

Hi everyone,

First of all I would like to thank Arnaud Bouchez for this great ORM framework.

Now back to my question. I have started a new web based project using ExtJS, Mormot and MySQL.
I create the UI with ExtJS library and post the form data to mormot application server for database inserts/updates.
I am using "method based services" via TSQLRestServerFullMemory, and connect to to external MySQL tables using
"VirtualTableExternalRegister. Everything works as expected but I have some concerns.

I handle the incoming web requests using such methods:

procedure TMyRestServer.aet_test_mysql(Ctxt: TSQLRestServerURIContext);
var
     tmp_record: TSQLDriveRecord;
     tmpRestClientDB: TSQLRestClientDB;
begin
     tmp_record := TSQLDriveRecord.Create;
     tmp_record.Drive := 'C';
     tmp_record.Active := 1;
     tmp_record.Checked := 1;

     // ALTERNATIVE 1
     tmpRestClientDB := TSQLRestClientDB.Create(aetDBSchema, nil, ':memory:', TSQLRestServerDB);
     tmpRestClientDB.Server.CreateMissingTables(0);

     // ALTERNATIVE 2, USE THE GLOBAL "GlobalRestServerDB"
     //tmpRestClientDB := TSQLRestClientDB.Create(GlobalRestServerDB);
	
     // INSERT THE RECORD
     if (tmpRestClientDB.Add(tmp_record, true) = 0) then Ctxt.Returns('["MySQL ERROR."]');
     else Ctxt.Returns('["RECORD ADDED."]');

     tmpRestClientDB.Free;
     tmp_record.Free;
end;

As you will see in the above code, there are 2 alternatives for creating the TSQLRestClientDB for database access:
- The first one will create a hidden TSQLRestServerDB server, and call it internaly.
- The second one initializes the class for an existing (global) TSQLRestServerDB.

I don't like the first approach because I need to call the "CreateMissingTables" for every request, which is totally unneccessary
because the database structure does not change in every request. Besides, it creates a hidden TSQLRestServerDB  which is
additional overhead.

The advantage of the second approach is, you don't need to call the "CreateMissingTables" in every single web request,
because it is already called by a global TSQLRestServerDB instance when the application starts:

    // Global TSQLRestServerDB
    GlobalRestServerDB := TSQLRestServerDB.Create(aetDBSchema, ':memory:', false);
    GlobalRestServerDB.CreateMissingTables(0);

But I believe this approach is not thread-safe, because multiple threads can modify the global GlobalRestServerDB simultaneously.

How can I protect the global GlobalRestServerDB? If I use locks, I believe it will be the bottleneck for the application, because 32
threads will need to wait for this lock for database inserts/updates, although the MySQL server can handle multiple requests
simultaneously. Or is there a better multithread-friendly database ORM approach for method-based services?

Thanks in advance.

Last edited by turkerali (2021-03-30 07:10:15)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Method based web services, TSQLRestServerDB thread safety concerns

Why do you use the TSQLRestClientDB?
Just use the GlobalRestServerDB instance.
It also has ORM methods. And it will be thread-safe.

Note that our ORM writes already have a lock. So whatever you use TSQLRestClientDB or not, you will have a lock.
So if you need the best performance, use a temporary TSQLRestBatch instance, protect it with its lock, and append the data in it. Then call Send after a while.
It will be almost non blocking with huge performance, since the SQL sent to MySQL will be optimized for insertion.
We do it on production since years, with good stability and no bottlenck.

turkerali

Member

Registered: 2021-03-30

Posts: 10

Re: Method based web services, TSQLRestServerDB thread safety concerns

Hi Arnaud,

Why do you use the TSQLRestClientDB?

I decided to use "TSQLRestClientDB" for inserts/updates after I saw the benchmarks under the 11.7.3. Proven behavior section
of Mormot documentation:

Multi thread process:
  - Create thread pool: 1 assertion passed  3.11ms
  - TSQLRestServerDB: 24,061 assertions passed  903.31ms
     1=41986/s  2=24466/s  5=14041/s  10=9212/s  30=10376/s  50=10028/s
  - TSQLRestClientDB: 24,062 assertions passed  374.93ms
     1=38606/s  2=35823/s  5=30083/s  10=32739/s  30=33454/s  50=30905/s
  - TSQLRestClientURINamedPipe: 12,012 assertions passed  1.68s
     1=4562/s  2=5002/s  5=3177/s
  - TSQLRestClientURIMessage: 16,022 assertions passed  616.00ms
     1=16129/s  2=24873/s  5=8613/s  10=11857/s
  - TSQLHttpClientWinHTTP_HTTPAPI: 24,056 assertions passed  1.63s
     1=5352/s  2=7441/s  5=7563/s  10=7903/s  30=8413/s  50=9106/s
  - TSQLHttpClientWinSock_WinSock: 24,061 assertions passed  1.10s
     1=11528/s  2=10941/s  5=12014/s  10=12039/s  30=9443/s  50=10831/s
  Total failed: 0 / 124,275  - Multi thread process PASSED  6.31s

For direct in-process access, TSQLRestClientDB sounds the best candidate: its abstraction layer is very thin, and much more
multi-thread friendly than straight TSQLRestServerDB calls. It also will feature a cache, on need - see ORM Cache. And it will
allow your code to switch between TSQLRestClientURI kind of classes, from its shared abstract methods.

Is this a different scenario than mine? Or did I misinterpret this part?

Just use the GlobalRestServerDB instance.
It also has ORM methods. And it will be thread-safe.

If I use the only global TSQLRestServerDB instance for method based services as you suggested, wouldn't the application bottleneck?
Because multiple web requests will be competing for the same resource (GlobalRestServerDB). So there will be say 32 simultaneous insert
requests, but they will need to be serialized due to the global TSQLRestServerDB lock.

If I remember correctly, every application thread created by web server has an associated db connection. Is there a way to utilize this
connection directly and bypass the global TSQLRestServerDB instance?

So if you need the best performance, use a temporary TSQLRestBatch instance, protect it with its lock, and append the data in it. Then call Send after a while.
It will be almost non blocking with huge performance, since the SQL sent to MySQL will be optimized for insertion.
We do it on production since years, with good stability and no bottlenck.

I tested this solution, and I really like it. Very neat and straightforward. Now the Alternative 3 is as follows:

var
   tmp_record: TSQLDriveRecord;
   tmpBatchRest: TSQLRestBatch;

begin
     tmp_record := TSQLDriveRecord.Create;
     tmp_record.Drive := 'C';
     tmp_record.Active := 1;
     tmp_record.Checked := 1;

     tmpBatchRest := TSQLRestBatch.Create(GlobalRestServerDB, TSQLDriveRecord);
     tmpBatchRest.Add(tmp_record, true);
     Ctxt.Returns('["' + IntToStr(GlobalRestServerDB.BatchSend(tmpBatchRest)) + '"]');

     tmpBatchRest.Free;
     tmp_record.Free;
end;

This method works fine, but I can not catch the SQL Exceptions, it always returns 200, even if the SQL fails. Here is the failed log:

20210330 10412002  +    project1.TMyRestServer(ffffa63271b0).URI GET root/aet_test_mysql_batch in=0 B
20210330 10412002 trace         mORMotSQLite3.TSQLRestServerDB(ffffa630f2f0) BatchSend {"TSQLRestBatch(ffffa448b040)":{"Count":1,"SizeBytes":35}}
20210330 10412002  +            mORMotSQLite3.TSQLRestServerDB(ffffa630f2f0).EngineBatchSend TSQLDriveRecord inlen=36
20210330 10412002 EXC                   EZSQLException ("SQL Error: Duplicate entry 'C' for key 'Drive' \nCode: 1062 SQL: insert into DriveRecord (ID,Drive,Active,Checked) values (?,?,?,?)") [HttpSvr 8888/root THttpSvrResp] at 676fb8 758b74 60d19c 51c540 5189dc 4e0c44 4e2958 4c6aac 4c6bbc 4020ac 4d46c8 4d4118 4da41c 51ed78 655dcc 656e7c 65841c
20210330 10412002 trace                 mORMotSQLite3.TSQLRestServerDB(ffffa630f2f0) EngineBatchSend json=36 B add=1 update=0 delete=0 TSQLDriveRecord

How can I catch the failed SQL errors with TSQLRestBatch?

Thanks in advance.

Last edited by turkerali (2021-03-30 10:53:35)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Method based web services, TSQLRestServerDB thread safety concerns

Using a Batch each time has no benefit, and is slower.

A Batch is for several writes at the same time.
What I propose is to reuse the batch between calls, then only call BatchSend once in a while.

turkerali

Member

Registered: 2021-03-30

Posts: 10

Re: Method based web services, TSQLRestServerDB thread safety concerns

Hi Arnaud,

Today I deep dived into the mORMot source code to find the best way for parallel database updates,
and I must say that I am astonished at the beauty of the code.

The excerpt below can be attributed to you I guess:

I have often felt that programming is an art form,
     whose real value can only be appreciated
     by another versed in the same arcane art;
     there are lovely gems and brilliant coups
     hidden from human view and admiration, sometimes forever,
     by the very nature of the process.
     You can learn a lot about an individual
     just by reading through his code,
     even in hexadecimal.

The Story of Mel