mORMot Open Source

mingda

Member

Registered: 2013-01-04

Posts: 121

question about TSQLRestClientURI's Transaction control

In document, transcation should do like this:

      if Client.TransactionBeginRetry(TSQLRecordPeopleObject,20) then
      try
        //.... modify the database content, raise exceptions on error
        Client.Commit;
      except
        Client.RollBack; // in case of error
      end; 

but when I do this, when i debug, found transcation start and commit is not match,
this will make have some pending conneciton, later some operation will lock by this
pending operation, i use a TOleDBMSSQLConnectionProperties register external Db,
since ThreadSafeConnection default will use tmThreadPool, when call TransactionBeginRetry,
the server will use a thread, but when call client.commit, perhaps will use another thread,
then commit will not commit the pending conneciton, is this true?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: question about TSQLRestClientURI's Transaction control

Yes, due to the HTTP thread pool, TransactionBeginRetry and Commit/Rollback will probably not be run in the same thread, so in the same connection.
For SQlite3, it is not an issue.
But for OleDB/MSSQL, it is a problem.

Solution is to run the transaction on the server side.
You have full access to the ORM RESTful methods on the server side also, via its TSQLRestServer instance.

You can use an interface-based service, then do the process on the server side:
- Either regroup all the transaction operations within one method;
- Or ensure that all methods are run in the same thread. See optExecInMainThread / optFreeInMainThread and paragraph "Server-side execution options (threading)" in the SAD 1.18 pdf.

This is even more SOA friendly, and will probably be more efficient, if the client is on a slow network.

We are working on creating a new SynDB class able to run all the process in one dedicated thread.
Or create a similar threading model, at least for the write process: in the current implementation, ORM writes are protected by a critical section - we may improve it to optionally regroup all this write process within a dedicated background thread. It won't be much slower, but safer to work with. ORM reads could still be executed within the thread pool.
Both solutions, will let transactions work on the client side. But I prefer the 2nd (more generic).

I've created a ticket for the 2nd solution.
See http://synopse.info/fossil/info/e370758677

mingda

Member

Registered: 2013-01-04

Posts: 121

Re: question about TSQLRestClientURI's Transaction control

Ab, thank you for your intelligent insights, for me this is a difficult problem.