mORMot Open Source

wxinix

Member

Registered: 2020-09-07

Posts: 121

How to correctly call TRestServerDB.TransactionBegin?

The second param of TRestServerDB.TransactionBegin is SessinID,

How can I get that SessionID?  (TRestClientDB provides SessionID, but I didn't find it from TRestServerDB).

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to correctly call TRestServerDB.TransactionBegin?

Do not use transactions on Server side. Use TRestBatch only, which will make the transaction and keep it as short as possible.

You could use TransactionBegin, with SessionID=0 if you really need to.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

I have the need for server-side transactions as well and am feeling lost... so, I will use this thread to ask instead of opening my own, I hope that is ok.

Batches seem to be the suggested method instead of transactions, but what about adding a record and a number of TSQLRecordMany aggregate records in one transaction, batch can't help with that unless if I am missing something? I need the ID of the new record which I cant have since the insert is part of batch? I suppose I should ask mormot for a fresh record ID (how?) and use the ForceID=true when adding the record?

btw I tried with transactions but I must have setup something incorrectly because they dont seem to have an effect, the moment I add a record for example I can see it in PGAdmin...

Last edited by Milos (2021-10-11 18:49:06)

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

This seems to work, but is it the correct/safe way to do it?

success := false;
DB.MainConnection.StartTransaction;
try
  success := do something with db and do something else etc...
  if success then DB.MainConnection.Commit;
finally
  if not Success
    then DB.MainConnection.RollBack;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to correctly call TRestServerDB.TransactionBegin?

At SynDB level, it should be enough.

But if you use a REST server over it, you should better acquire/release the global DB lock for write around the whole transaction.
If another thread tries to write to the DB, you will have some conflicts.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

Thank you for the response, I have a rest server with interface based services so I will definitely take a look at the global lock then!

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

My goal is to use this as a foolproof simple way of using transactions both for ORM and "direct" queries via DB.Execute.

I revised my code but would GREATLY appreciate if someone could ease my mind about this being correct, or point out if there is a problem, or tell me if I reinvented the wheel and should have used some already existing functionality.

  TfunRestServer = class (TSQLRestServerDB) 
// class extended for easier transactions
    DBConnectionProperties: TSQLDBConnectionPropertiesThreadSafe;
    procedure LockAndStartTransaction;
    procedure CommitAndUnlock;
    procedure RollbackAndUnlock;

procedure TfunRestServer.LockAndStartTransaction;
begin
  try
    fAcquireExecution[execORMWrite].Safe.Lock;
    DBConnectionProperties.MainConnection.StartTransaction;
  except
    halt; // I am paranoid about transaction problems leading to undetermined DB state at the moment...
  end;
end;

procedure TfunRestServer.CommitAndUnlock;
begin
  try
    DBConnectionProperties.MainConnection.Commit;
    fAcquireExecution[execORMWrite].Safe.UnLock;
  except
    halt; // I am paranoid about transaction problems leading to undetermined DB state at the moment...
  end;
end;

procedure TfunRestServer.RollbackAndUnlock;
begin
  try
    DBConnectionProperties.MainConnection.Rollback;
    fAcquireExecution[execORMWrite].Safe.UnLock;
  except
    halt; // I am paranoid about transaction problems leading to undetermined DB state at the moment...
  end;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to correctly call TRestServerDB.TransactionBegin?

An additional try...finally Unlock end in the *AndUnlock methods may be needed.

But I would not kill a server anyway by "halt".
Perhaps rather just close the connection.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

OK I agree that commit can legitimately raise an exception depending on what the database operations were, in which case I should catch the exception and do a rollback instead of halting.

But if an exception happens for transaction start or rollback that seems like something that should never happen? I am afraid of leaving the database is in some unexpected state such as having an open transaction that would never be commited so any later updates would "go into the wind".

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to correctly call TRestServerDB.TransactionBegin?

Making a rollback is the way to go IMHO.

It will be what most client DB drivers will do on halt/release anyway, I guess.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

Thank you for the responses, I am still unsure what is best to do if the start or rollback crash though, I suppose if the called code is written in a safe way then a crash in Start should mean there is no transaction but if Rollback crashes then I am not sure what the state is...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to correctly call TRestServerDB.TransactionBegin?

If RollBack crashes, then I guess you could close and recreate the DB connection.

It would flush all pending transactions in a clean way.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

OK, I will try that, thanks

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

Sorry for - again - ressurecting this topic but I am still confused and my code (which I thought I tested and seemed fine) doesn't work. The moment I call ORM .Add the record is in the database even if transaction is still not commited.

Can we please start over with an example? Here's a function, on the server, exposed via service interface

function TUserAdministration.Add (var User: TSqlFunUser; const WarehouseIDs: TIDDynArray): TOutcome;

The goal is to create a new user and immediately link it with N warehouses (many-many) and if any part of the process fails I don't want any trace of it in the database. In the past I always used transactions for such things but I think it is either not the Mormot way or I don't know how.

I would use batch instead of transaction but I (seemingly) can't do that because I can't know the ID of the newly created FunUser record to supply as SourceID for the subsequent many-many inserts. Is there a way to "reserve" an unique ID in advance and use it for such a batch operation?

Last edited by Milos (2022-04-03 13:50:37)

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: How to correctly call TRestServerDB.TransactionBegin?

Is there a way to "reserve" an unique ID in advance and use it for such a batch operation?

You can have a look at TSynUniqueIdentifierGenerator. And you can register it as follows:

Model.SetIDGenerator(TOrmObject, CustomerID);

With best regards
Thomas

pvn0

Member

From: Slovenia

Registered: 2018-02-12

Posts: 211

Re: How to correctly call TRestServerDB.TransactionBegin?

Sounds like he needs a pivot table instead.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

Thank you, that generator can help me solve the problem by using batch instead of transactions, but if that is the path to take I think I will instead read the max ID for the table at the server's start and use my own generator to simply autoincrement (within a critical section ofc) instead.

@pvn0 I don't need a pivot table, it is a simple matter of one master record in one table and multiple many-many records in another table, all of which I would like to insert in one, "atomic", operation.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

Welp... I have just realised that batches have separate transactions for each table involved.

So, if anything unexpected were to happen such as hardware failure at the wrong moment, the batch does not provide integrity safety for the entire operation.

Is there any way for me to add something like that? Why do the transactions have to be per-table and not for the entire database?

pvn0

Member

From: Slovenia

Registered: 2018-02-12

Posts: 211

Re: How to correctly call TRestServerDB.TransactionBegin?

manually call TransactionBegin,Commit,Rollback, etc... and for vanilla sqlite the transaction will apply to the entire database.

Last edited by pvn0 (2022-04-08 07:13:19)

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

  mu := TSqlMeasurementUnit.Create;
  mu.Name := 'TST';
  mu.Symbol := 'TST';
  RestServer.TransactionBegin(TSqlMeasurementUnit);
  RestServer.Add(mu);
  RestServer.Rollback;

if I execute this on server the transaction seems to have no effect, if I stop after the Add() line the record is commited to the (postgre) database. Rollback doesn't seem to have any effect.

Could it be I have misconfigured something?
(edit: I checked and TransactionBegin returns true. Also, I tried supplying SessionID=0 to the transaction calls, as suggested earlier in this thread, but there is no change in outcome)

Last edited by Milos (2022-04-09 13:05:07)

pvn0

Member

From: Slovenia

Registered: 2018-02-12

Posts: 211

Re: How to correctly call TRestServerDB.TransactionBegin?

Oh, didn't know you are using postgre, I have no experience with that database, maybe someone else will chime in, gl.

Milos

Member

Registered: 2021-01-04

Posts: 36

Re: How to correctly call TRestServerDB.TransactionBegin?

Maybe best to ignore me as I think I am going insane because now I am re-testing and the approach I wrote in post #7 seems to be working, except in one case where trying to start the transaction crashes with message "database should be connected" and I don't see whats different about that case... but I will try to figure it out.