mORMot Open Source

leus

Member

Registered: 2012-09-05

Posts: 79

Waiting for transaction to complete

I'm using the following code to reserve a record for further processing (selecting available record, update to ensure it is not used by another thread).

function ReserveRecord(BranchOffice, Terminal: RawUTF8): Int64;
var
  MyRecord: TSomeRecord;
  log: TSynLog;
begin
  log := TSynLog.Add;
  Result := -1;
  if ServerDB.TransactionBegin(TSomeRecord) then
  begin
    try
      MyRecord := TSomeRecord.Create;
      try
        if ServerDB.Retrieve('Available = 1', MyRecord) then
        begin
          MyRecord.Available := false;
          MyRecord.TrackId := 0;
          MyRecord.BranchOffice := BranchOffice;
          MyRecord.Terminal := Terminal;
          if ServerDB.Update(MyRecord) then
          begin
            Result := MyRecord.PreviouslyAssignedNumber;
          end;
        end;
        ServerDB.Commit();
      finally
        MyRecord.Free;
      end;
    except
      ServerDB.RollBack();
    end;
  end
  else
  begin
    log.log(sllError, 'Error acquiring transaction');
  end;
end;

When many requests arrive at the same time I get 'Error acquiring transaction' for most of them (18 out of 20). I understand some locking is involved. What would be good ways to either minimize locking, or a good way to "retry" without hammering the server?

leus

Member

Registered: 2012-09-05

Posts: 79

Re: Waiting for transaction to complete

For the moment, I added a retry logic on the calling method:

    repeat
      if Result.Retries > 0 then
        TThread.Sleep(FPreferences.WaitBetweenRetries);
      Result.PreviouslyAssignedNumber := ReserveRecord(Branch, Terminal);
      Inc(Result.Retries);
    until (Result.PreviouslyAssignedNumber > 0) or (Result.Retries >= FPreferences.NumberOfRetries);

After some testing, I found out that the ReserveRecord() function takes about 50ms to complete. So I tried sending 5 requests at the same time, and have configured 0 retries.

Start
Thread: 1 - Error obtaining record (2 ms., 1 retries)
Thread: 1 - Error obtaining record (0 ms., 1 retries)
Thread: 1 - Error obtaining record (0 ms., 1 retries)
Thread: 1 - Error obtaining record (0 ms., 1 retries)
Thread: 1 - Assigned number: 2290 (52 ms., 1 retries)
End

Adding 10ms delay, 2 retries:

Start
Thread: 1 - Error obtaining record (53 ms., 2 retries)
Thread: 1 - Error obtaining record (59 ms., 2 retries)
Thread: 1 - Error obtaining record (64 ms., 2 retries)
Thread: 1 - Assigned number: 2292 (146 ms., 2 retries)
Thread: 1 - Assigned number: 2291 (173 ms., 1 retries)
End

Adding 50ms delay, 10 retries:

Start
Thread: 1 - Assigned number: 2296 (139 ms., 2 retries)
Thread: 1 - Assigned number: 2297 (195 ms., 3 retries)
Thread: 1 - Assigned number: 2295 (208 ms., 1 retries)
Thread: 1 - Assigned number: 2298 (295 ms., 5 retries)
Thread: 1 - Assigned number: 2299 (359 ms., 6 retries)
End

In my current case, I have about 20 terminals, so worst case scenario the server would receive 20 requests at the same time (and this is likely never going to happen), and a 100ms or so wait time for a ticket to complete is not big deal, so for my current needs this is OK. However, if I wanted to scale (for example, a server in the cloud attending thousands of terminals), what could be a good way to "reserve" a record? I imagine creating an in-memory concurrent cache (queue?) would do the trick, but I don't know how easy is to do that in Delphi and mORMot.

Last edited by leus (2020-12-27 15:35:02)

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: Waiting for transaction to complete

What if you protect this method with a lock ... unlock? Perhaps it is a better option.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: Waiting for transaction to complete

The easiest is to use TSQLRestServer.WriteLock/WriteUnlock and no transaction.