mORMot Open Source

AntonE

Member

Registered: 2012-02-03

Posts: 74

TransactionBegin problem

I have an app that run 2 threads in background that record and edit data.
They run relatively tight loops (<50ms each time) and I have at beginning and end of each:

   while not Server.DB.TransactionBegin(TSQLWISPSite) do
    Sleep(50);
...
   Server.DB.Commit;

With even no client connected, from time to time, I get  "SQLITE ERROR (1) -cannot rollback transaction - no transaction is active."
When it get that error, I never get a 'True' result from TransactionBegin and the server loops indefinately until I restart it.

Am I handling transactions wrong? Where are no errors between TransactionBegin and Commit.
In line 25117 in mORMot.pas , fTransactionActiveSession stays =1, so after initial error, TransactionBegin just always returns False without error.

Regards

PS: Am I right in assuming that SessionID is not relevant as only one transaction can be active at a time in anycase? Or should each call have a unique SessionID?

Last edited by AntonE (2015-01-21 07:50:56)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: TransactionBegin problem

Transactions should be kept as small as possible.
Doing a sleep within a transaction is not a good idea!

In fact, the transactions are blocking.
So any not closed transactions are blocking the other attempts to run TransactionBegin().

And the transactions are blocked per session.
On the server side, there is no session, so you should be aware of it.

The right way to run fast ACID process is not to use Transactions, but BATCH process.
You should create a TSQLRestBatch instance per ACID scope, then execute it with a parameter to create automatic transactions.
You will gain a lot of performance, by the way, since the ORM is able to generate much better SQL statement in Batch mode than with regular individual mode.
Note that Batch mode works also for NoSQL database backend - so it is preferred than regular transactions.

AntonE

Member

Registered: 2012-02-03

Posts: 74

Re: TransactionBegin problem

Thanks. I'll definitely use that for some processes, but my problem is here that I need either:
1) Increment a value
2) Check if a record exists, if so, increment some fields, if not, create.
So I don't think a batch can work in this scenario?

I removed all but 1 usage of transactions so there can be no conflicting transactions and still I get the error after some random time.

I might be doing something else wrong...
e.g.:

   (*Update sites traffic*)
 while not Server.DB.TransactionBegin(TSQLWISPSite) do Sleep(50);
   Site:=TSQLWispSite.Create;
   for ID in SitesTouched.Keys do
    begin
     I64:=SitesTouched[ID];
     Server.DB.Retrieve(ID,Site);
     Site.CapUsed:=Site.CapUsed+I64;
     Server.DB.Update(Site,'CapUsed');
    end;
   Site.Free;
  Server.DB.Commit;

 (*Now flush results to DB*)
 while not Server.DB.TransactionBegin(TSQLWISPTraffic_IP) do Sleep(50);
 SQL_IP    :=TSQLWISPTraffic_IP.Create;
 for TrafficIP in ListIP do
  begin
   SQL_IP.FillPrepare(Server.DB,'Year = ? and Month = ? and Day = ? and Hour = ? and IP = ?',[Y,M,D,H,TrafficIP.IP]);
   if SQL_IP.FillOne
      then begin
            SQL_IP.UL:=SQL_IP.UL+TrafficIP.UL;
            SQL_IP.DL:=SQL_IP.DL+TrafficIP.DL;
            Server.DB.Update(SQL_IP);
           end
      else begin
            SQL_IP.ClearProperties;
            SQL_IP.Year    :=Y;
            SQL_IP.Month   :=M;
            SQL_IP.Day     :=D;
            SQL_IP.Hour    :=H;
            SQL_IP.CalcDate:=RecordDate;
            SQL_IP.IP      :=TrafficIP.IP;
            SQL_IP.UL      :=TrafficIP.UL;
            SQL_IP.DL      :=TrafficIP.DL;
            Server.DB.Add(SQL_IP,True);
           end;
  end;
 Server.DB.Commit;
 SQL_IP.Free;

I use it without transaction and performance is still very good with no problems:) but I'll have to see when app goes live and real data flows.

AntonE

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: TransactionBegin problem

On server side, such use sounds pretty safe.

If your code is re-entrant and multi-threaded, I would ensure that all ORM writes would be locked.
This is the default but the aServer.AcquireExecutionMode[execORMWrite] parameter should not be set to amUnlocked.
See the documentation about this, in http://synopse.info/files/html/Synopse% … #TITLE_184

AntonE

Member

Registered: 2012-02-03

Posts: 74

Re: TransactionBegin problem

I looked at that.
One thing that caught my eye:

Most TSQLDBConnectionProperties will inherit from TSQLDBConnectionPropertiesThreadSafe, so will create one connection per thread. This is efficient, but some providers may have issues with it.

Do I need to create another connection to local SQLite db for each internal server thread?

Thank you for your reply.

BTW: aServer.AcquireExecutionMode[execORMWrite]=amLocked

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: TransactionBegin problem

No, our sqlite wrapper is thread safe and you should better use a single connection to a single file.