Synopse Open Source

By design

In order to achieve this thread-safety without sacrificing performance, the following rules were applied in TSQLRestServer.URI:

•  Most of this methods's logic is to process the incoming parameters, so is thread-safe by design (e.g. Model and RecordProps access do not change during process); 
•  The SQLite3 engine access is protected at SQL/JSON cache level, via DB.LockJSON() calls in TSQLRestServerDB methods; 
• TSQLRestServerStatic main methods (EngineList, EngineRetrieve, EngineAdd, EngineUpdate, EngineDelete, EngineRetrieveBlob, EngineUpdateBlob) are thread-safe: e.g. TSQLRestServerStaticInMemory uses a per-Table Critical Section; 
• TSQLRestServerCallBack methods (i.e. published methods of the inherited TSQLRestServer class) must be implemented to be thread-safe; 
• Interface-based services have several execution modes, including thread safe automated options (see TServiceMethodOption), or manual thread safety expectation, for better scaling; 
•  A protected fSessionCriticalSection is used to protect shared fSession[] access between clients; 
•  Remote external tables use thread-safe connections and statements when accessing the databases via SQL; 
•  Access to fStats was not made thread-safe, since this data is indicative only: a mutex was not used to protect this resource.

We tried to make the internal Critical Sections as short as possible, or relative to a table only (e.g. for TSQLRestServerStaticInMemory).

There is some kind of "giant lock" at the SQLite3 engine level, so all requests process will be queued.
This was not found to be a major issue (see benchmark results below), since the internal SQL/JSON cache implementation need such a global lock, and since most of the SQLite3 resource use will consist in hard disk access, which gain to be queued.
It also allows to use the SQLite3 in lmExclusive locking mode if needed, with both benefits of high performance and multi-thread friendliness.

From the Client-side, the REST core of the framework is expected to be Client-safe by design, therefore perfectly thread-safe: it's the benefit of the stateless architecture.

By proof

When we are talking about thread-safety, nothing compares to a dedicated stress test program.
An average human brain (like ours) is not good enough to ensure proper design of such a complex process.
So we have to prove the abilities of our little mORMot.

In the supplied regression tests, we designed a whole class of multi-thread testing, named TTestMultiThreadProcess.
Its methods will run every and each Client-Server protocols available (direct access via TSQLRestServerDB or TSQLRestCLientDB, GDI messages, named pipes, and both HTTP servers - i.e. http.sys based or WinSock-based).

Each protocol will execute in parallel a list of INSERTs - i.e. TSQLRest.Add() - followed by a list of SELECTs - i.e. TSQLRest.Retrieve().
Those requests will be performed in 1 thread, then 2, 5, 10, 30 and 50 concurrent threads.
The very same SQLite3 database (in lmExclusive locking mode) is accessed at once by all those clients.
Then the IDs generated by each thread are compared together, to ensure no cross-insertion did occur during the process.

Those automated tests did already reveal some issues in the initial implementation of the framework. We fixed any encountered problems, as soon as possible.
Feel free to send us any feedback, with code to reproduce the issue: but do not forget that multi-threading is also difficult to test - problems may occur not in the framework, but in the testing code itself!

When setting OperationCount to 1000 instead of the default 200, i.e. running 1000 INSERTions and 1000 SELECTs in concurrent threads, the numbers are the following, on the local machine (compiled with Delphi XE4):

 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.
And it will allow your code to switch between TSQLRestClientURI kind of classes, from its shared abstract methods.

Named pipes and GDI messages are a bit constrained in highly parallel mode, but HTTP does pretty good.
The server based on http.sys (HTTP API) is even impressive: the more clients, the more responsive it is.
It is known to scale much better than the WinSock-based class supplied, which shines with one unique local client (i.e. in the context of those in-process regression tests), but sounds less reliable on production.

Check yourself before you wreck yourself

In addition, you can make yourself an idea, and run the "21 - HTTP Client-Server performance" sample programs, locally or over a network, to check the mORMot abilities to scale and serve a lot of clients with as few resources as possible.

Compile both client and server projects, then launch Project21HttpServer.exe.
The server side will execute as a console window.

This Server will define the same TSQLRecordPeople as used during our multi-thread regression tests, that is:

type
  TSQLRecordPeople = class(TSQLRecord)
  private
    fFirstName: RawUTF8;
    fLastName: RawUTF8;
    fYearOfBirth: integer;
    fYearOfDeath: word;
  published
    property FirstName: RawUTF8 read fFirstName write fFirstName;
    property LastName: RawUTF8 read fLastName write fLastName;
    property YearOfBirth: integer read fYearOfBirth write fYearOfBirth;
    property YearOfDeath: word read fYearOfDeath write fYearOfDeath;
  end;

The server main block is just the following:

  aModel := TSQLModel.Create([TSQLRecordPeople]);
  try
    aDatabaseFile := ChangeFileExt(paramstr(0),'.db3');
    DeleteFile(aDatabaseFile);
    aServer := TSQLRestServerDB.Create(aModel,aDatabaseFile);
    try
      aServer.DB.Synchronous := smOff;
      aServer.DB.LockingMode := lmExclusive;
      aServer.NoAJAXJSON := true;
      aServer.CreateMissingTables;
      // launch the server
      aHTTPServer := TSQLHttpServer.Create('888',[aServer]);
      try
        writeln(#13#10'Background server is running at http://localhost:888'#13#10+
                #13#10'Press [Enter] to close the server.');
        ConsoleWaitForEnterKey;
        with TSQLLog.Family do
          if not (sllInfo in Level) then // let global server stats be logged
            Level := Level+[sllInfo];
      finally
        aHTTPServer.Free;
      end;
    finally
      aServer.Free;
    end;
  finally
    aModel.Free;
  end;

It will give CRUD access to the TSQLRecordPeople table, from HTTP.
We defined Synchronous := smOff and LockingMode := lmExclusive to have the best performance possible.
Our purpose here is not to have true ACID behavior, but test concurrent remote access.

The Client is just a RAD form which will execute the very same code than during the regression tests, i.e. a TTestMultiThreadProcess class instance, as shown by the following code:

    Tests := TSynTestsLogged.Create;
    Test := TTestMultiThreadProcess.Create(Tests);
    try
      Test.ClientOnlyServerIP := StringToAnsi7(lbledtServerAddress.Text);
      Test.MinThreads := ThreadCount;
      Test.MaxThreads := ThreadCount;
      Test.OperationCount := OperationCount;
      Test.ClientPerThread := ClientPerThread;
      Test.CreateThreadPool;
      txt := Format
        ('%s'#13#10#13#10'Test started with %d threads, %d client(s) per thread and %d rows to be inserted...',
        [txt,ThreadCount,ClientPerThread,OperationCount]);
      mmoInfo.Text := txt;
      Timer.Start;
      Test._TSQLHttpClientWinHTTP_HTTPAPI;
      txt := mmoInfo.Text+Format(#13#10'Assertion(s) failed: %d / %d'+
        #13#10'Number of clients connected at once: %d'+
        #13#10'Time to process: %s'#13#10'Operation per second: %d',
        [Test.AssertionsFailed,Test.Assertions,
         ThreadCount*ClientPerThread,Timer.Stop,Timer.PerSec(OperationCount*2)]);
      mmoInfo.Text := txt;
    finally
      Test.Free;
      Tests.Free;
    end;

Each thread of the thread pool will create its own HTTP connection, then loop to insert (Add ORM method) and retrieve (Retrieve ORM method) a fixed number of objects - checking that the retrieved object fields match the inserted values. Then all generated IDs of all threads are checked for consistency, to ensure no race condition did occur.

The input parameters are therefore the following:

•  Remote HTTP server IP (port is 888); 
•  Number of client threads; 
•  Number of client instances per thread; 
•  Number of TSQLRecordPeople objects added.

When running over the following hardware configuration:

•  Server is a Core i7 Notebook, with SSD, under Windows 7; 
•  Client is a Core 2 Duo Workstation, with regular hard-drive (not used), under Windows 7; 
•  Communicating over a somewhat slow 100 Mb network with a low priced Ethernet HUB.

Typical results are the following:

During all tests, no assertion failed, meaning that no concurrency problem did occur, nor any remote command lost.
It is worth noting that when run several times in a row, the same set of input parameters give the very same speed results: it indicates that the architecture is pretty stable and could be considered as safe.
The system is even able to serve 50000 connected clients at once, with no data loss - in this case, performance is lower (2152 insert/second in the above table), but we clearly reached the CPU and network limit of our client hardware configuration; in the meanwhile, server resources on the Notebook have still some potential.

Average performance is pretty good, even more if we consider that we are inserting one object per request, with no transaction.
In fact, it sounds like if our little SQLite3 server is faster than most database servers, even when accessed in highly concurrent mode! In batch mode we may achieve amazing results.

Feel free to send your own benchmark results and feedback, e.g. with concurrent clients on several workstations, or long-running tests, on our forums.