mORMot Open Source

mattsbg

Member

Registered: 2017-05-23

Posts: 20

HTTP remote - mixing transactions in threadmode tmMainConnection

Hi,

using the http api server in single threaded mode (tmMainConnection) it allows to use transactions. If a transaction of one client is going on, it corretly rejects transaction starts of other clients (after some internal retries).

But if another client does an insert or update out of any transaction this one is executed in the currently active transaction of the other client.

Is this a known or the intended behaviour?

Best regards,
Matthias

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: HTTP remote - mixing transactions in threadmode tmMainConnection

With which remote DB and SynDB unit?

mattsbg

Member

Registered: 2017-05-23

Posts: 20

Re: HTTP remote - mixing transactions in threadmode tmMainConnection

I am using TSQLDBServerHttpApi on the server side. On the server side it is handled in SynDb.TSQLDBConnection.RemoteProcessMessage. The database connection on the server is a Oracle Connection (SynDbOracle). As far as I understood, the problem is in the RemoteProcessMessage. On cTryStartTransaction it correctly checks whether a concurrent transaction exists or not. On cExecute it just executes the statement on the connection, regardless if a transaction from another session is active or not.

mattsbg

Member

Registered: 2017-05-23

Posts: 20

Re: HTTP remote - mixing transactions in threadmode tmMainConnection

@ab can I provide you more details?

mattsbg

Member

Registered: 2017-05-23

Posts: 20

Re: HTTP remote - mixing transactions in threadmode tmMainConnection

Just wanted to share how I am securing commands getting executed in a concurrently opened transaction. Of course this is no solution, but at least its preventing the worst. ;-)

1) Implemented a new procedure TransactionCheck
2) Calling it from RemoteProcessMessage for commandExecs

(syndb.pas)
procedure TSQLDBProxyConnectionProtocol.TransactionCheck(AConnection: TSQLDBConnection; ASessionId: Integer);
begin
  //prevent executing command in someone others t ransaction
  if (ASessionID<>0) and (fTransactionSessionID <> 0) and (ASessionID <> fTransactionSessionID) and
    (AConnection.Properties.InheritsFrom(TSQLDBConnectionPropertiesThreadSafe)) and
    (TSQLDBConnectionPropertiesThreadSafe(AConnection.Properties).ThreadingMode=tmMainConnection) then
  begin
    raise ESQLDBRemote.Create('Remote transaction active');
  end;
end;


procedure TSQLDBConnection.RemoteProcessMessage(const Input: RawByteString;
  out Output: RawByteString; Protocol: TSQLDBProxyConnectionProtocol);
...
 cExecute, cExecuteToBinary, cExecuteToJSON, cExecuteToExpandedJSON: begin
      RecordLoad(InputExecute,O,TypeInfo(TSQLDBProxyConnectionCommandExecute));
      ExecuteWithResults := header.Command<>cExecute;
>>>>      Protocol.TransactionCheck(self,header.SessionID);
      Stmt := NewStatementPrepared(InputExecute.SQL,ExecuteWithResults,true);
      if fBlobAsNull in InputExecute.Force then
        Stmt.ForceBlobAsNull := true;