Nested transactions

hnb · 2015-08-28 13:28:33

Hi,

I really need to mix ORM with SQL for existing database (performance and DB logic) and I have small problem with batching.

Is any simple way to omit transaction inside batch system? I can't use plain ORM. It's big existing project, I don't have any chance to do it in pure correct "mORMot style".

  aProps.MainConnection.StartTransaction;
  try
      b := TSQLRestBatch.Create(aRestServer, TSQLsome, MaxInt);

      with TAutoFree.One(r, TSQLsome.CreateAndFillPrepare(Ctxt.Call.InBody)) do
        while r.FillOne do
          b.Add(r, true);

      aRestServer.BatchSend(b); // here is called aProps.MainConnection.Commit, in my code I need to omit this 

      aProps.Execute('SELECT pr_tr_some trigger(' + IntToKey(LBillID) + ',0)', []);
      aProps.Execute('SELECT pr_tr_some_trigger2(' + IntToKey(LBillID) + ')', []);

     { more FPC operations and ORM operations and SQLs }
     {...}

     aProps.MainConnection.Commit;
  except
    aProps.MainConnection.Rollback; // don't work because "aRestServer.BatchSend"
  end;

only working solution for me is using:

      with TAutoFree.One(r, TSQLsome.CreateAndFillPrepare(Ctxt.Call.InBody)) do
        while r.FillOne do
          aRestServer.Add(r, true);

instead of:

      with TAutoFree.One(r, TSQLsome.CreateAndFillPrepare(Ctxt.Call.InBody)) do
        while r.FillOne do
          b.Add(r, true);

      aRestServer.BatchSend(b); // here is called aProps.MainConnection.Commit, in my code I need to omit this

Last edited by hnb (2015-08-28 13:29:48)

ab · 2015-08-28 13:34:53

Nested transactions are not supported, since it is almost impossible to find a consistent way of using transactions, at SQL level, among all databases.
There is a world between FireBird and Oracle transactions...

As documented, there is no transaction created in BatchSend(), if you leave AutomaticTransactionPerRow=0 in TSQLRestBatch.Create.

hnb · 2015-08-28 13:58:28

Sorry, somehow I overlooked it :\ . You need to write book about mORMot: "mORMot in nutshell"

Is any other potential "critical" place where can be executed aProps.MainConnection.Commit ?

ab · 2015-08-28 14:38:53

For safety, I guess mixed ORM/direct DB access should only occur on the server side, and in a single continuous thread if possible, within the main ORM DB lock.

hnb · 2015-08-29 11:03:00

To be sure:

I need to create 2 "ConnectionProperties" for different PostgreSQL DB users (one normal user where is used ORM and second user where some triggers are off - for performance, and for this user is mixed ORM with SQL). User without triggers is used less often and I'am also limited by number of allowed connections to PostgreSQL:

  aProps := TSQLDBZEOSConnectionProperties.Create(
    TSQLDBZEOSConnectionProperties.URI(dPostgreSQL, 'localhost:xxxx', 'libpq.dll', False), 'xxxxx', 'postgres', '********');
  aPropsDisabledTriggers := TSQLDBZEOSConnectionProperties.Create(
    TSQLDBZEOSConnectionProperties.URI(dPostgreSQL, 'localhost:xxxx', 'libpq.dll', False), 'xxxxx', 'noTriggers', '********');
  // to get only single connection for aPropsDisabledTriggers
  aPropsDisabledTriggers.ThreadingMode := tmMainConnection;

Now I need to create 2 models. Each of the model is the same model, but if I understand correctly model is correlated to ConnectionProperties and I must to do this in this way:

    aModelForHttp := CreateModel; // model is also used for http server
    aModelForDisabledTriggers := CreateModel;
    // use PostgreSQL database for all tables
    VirtualTableExternalRegisterAll(aModelForHttp, aProps);
    VirtualTableExternalRegisterAll(aModelForDisabledTriggers, aPropsDisabledTriggers);

Now I need to create 2 rest servers. One is for http, and second is only for my special "no trigger" postgreSQL user.
If I understand correctly the safest and the only settings for PostgreSQL for execORM* is amBackgroundORMSharedThread:

      aRestServer := TSQLMyDBServerWithPublishedMethods.Create(aModelForHttp,':memory:',false);
      aRestServerDisabledTriggers := TSQLRestServerDB.Create(aModelForDisabledTriggers,':memory:',false);

      aRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
      aRestServer.AcquireWriteMode := amBackgroundORMSharedThread;
      // create tables or fields if missing
      aRestServer.CreateMissingTables;

      aRestServerDisabledTriggers.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
      aRestServerDisabledTriggers.AcquireWriteMode := amBackgroundORMSharedThread; 
      aRestServerDisabledTriggers.CreateMissingTables();

after execution of aRestServer.CreateMissingTables (above) I don't need connection for DB for aProps in main thread so now I can call:

      aProps.MainConnection.Disconnect;

to limit http server threads (and connections to PostgreSQL):

     aHttpServer := TSQLHttpServer.Create('888',[aRestServer],'+',useHttpApiRegisteringURI, 7);
     aHttpServer.AccessControlAllowOrigin := '*';

So after all of above operations the max DB connections = 8 (7 for aProps and 1 for aPropsDisabledTriggers).

I've added fLocker in TSQLMyDBServerWithPublishedMethods.

And main question

Am I on right track?
For safety my published method in TSQLMyDBServerWithPublishedMethods should looks like this (SendReceiptPosition is also for non Delphi/FPC clients):

procedure TSQLMyDBServerWithPublishedMethods.SendReceiptPosition(Ctxt: TSQLRestServerURIContext);
var
  r: TSQLsomeRecord;
  LConnection: TSQLDBConnection;
  LBillID: Integer;
  b: TSQLRestBatch;
  IDs: TIDDynArray;
begin
  LConnection := aPropsDisabledTriggers.MainConnection;

  // already connected by aRestServerDisabledTriggers.CreateMissingTables();
  //if not LConnection.IsConnected then
  //  LConnection.Connect;

  LConnection.StartTransaction;
  with
    fLocker.ProtectMethod,
    TAutoFree.Several([
      @b, TSQLRestBatch.Create(aRestServerDisabledTriggers, TSQLsomeRecord),
      @r, TSQLsomeRecord.CreateAndFillPrepare(Ctxt.Call.InBody)])
  do try
    while r.FillOne do
      b.Add(r, true);

    if aRestServerDisabledTriggers.BatchSend(b, IDs) = HTML_SUCCESS then
      {...}

    LBillID := ifthen(r.BillID <> 0, r.BillID, -1);

    {...}

    aPropsDisabledTriggers.Execute('SELECT some_Trigger1', []);
    aPropsDisabledTriggers.Execute('SELECT some_Trigger2', []);

    {...}

    LConnection.Commit;
  except
    LConnection.Rollback;
  end;

  // ...
  if LBillID <> -1 then
    Ctxt.Returns('')
  else
    Ctxt.Error('');
end;

thanks in advance!

ab · 2015-08-30 07:32:28

AFAIK AcquireExecutionMode[] and AcquireWriteMode only affect ORM remote access via URI, not direct BatchSend().

If you want the SendReceiptPosition method-based service to be run in the same thread, you would need to set AcquireExecutionMode[execSOAByMethod] := amBackgroundORMSharedThread.
But in fact,
- it would use its own thread, not the same thread as execORMGet/execORMWrite
- and this would affect all method-based services
So it won't be perfect, either.

What you may do is simply not directly calling TSQLRestServer/TSQLMyDBServerWithPublishedMethods, but a TSQLRestClientDB.
Use TSQLRestClientDB.Create(aRunningServer: TSQLRestServerDB) constructor.
Thanks to this, all ORM process would pass through TSQLRestServer.URI, so threading would be consistent, and would use a single amBackgroundORMSharedThread connection.

hnb · 2015-08-31 06:54:31

ab wrote:

But in fact,
- it would use its own thread, not the same thread as execORMGet/execORMWrite
- and this would affect all method-based services
So it won't be perfect, either.

So probably fLocker.ProtectMethod is the best option

ab wrote:

AFAIK AcquireExecutionMode[] and AcquireWriteMode only affect ORM remote access via URI, not direct BatchSend().
What you may do is simply not directly calling TSQLRestServer/TSQLMyDBServerWithPublishedMethods, but a TSQLRestClientDB.
Use TSQLRestClientDB.Create(aRunningServer: TSQLRestServerDB) constructor.
Thanks to this, all ORM process would pass through TSQLRestServer.URI, so threading would be consistent, and would use a single amBackgroundORMSharedThread connection.

for batching I will use aRestClient*.BatchSend instead of aRestServer*.BatchSend.

Thanks for suggestions.

ab · 2015-08-31 09:16:42

fLocker would not share the same thread, just lock the process.

So it would not share the same PostgreSQL connection, which sounds like a good idea.
No need to do any locking if you define the ORM execution via amBackgroundORMSharedThread.

mORMot Open Source

#1 2015-08-28 13:28:33

Nested transactions

#2 2015-08-28 13:34:53

Re: Nested transactions

#3 2015-08-28 13:58:28

Re: Nested transactions

#4 2015-08-28 14:38:53

Re: Nested transactions

#5 2015-08-29 11:03:00

Re: Nested transactions

#6 2015-08-30 07:32:28

Re: Nested transactions

#7 2015-08-31 06:54:31

Re: Nested transactions

#8 2015-08-31 09:16:42

Re: Nested transactions

Board footer