How to get exclusive access to the DB (sqlite) via TSQLRestServerDB?

edwinsn · 2017-05-24 16:45:10

Hello Ab,

This is the second followup to this post: "cannot rollback - no transaction is active" error

I found new info - by looking at the logs, the error might caused by mix-up of **server-side** DB transactions (executed in a service method) and DB transactions initiated by batch operations, and I'll illustrate the problem with simplified pseudo code below - though I might be wrong.

// This is a server-side method that will query and update the db.
function TServerApi.ServerFuncA(): String;
var
  isLocalTrans: Boolean;
begin
  ...

  isLocalTrans := fServer.TransactionActiveSession < 1;

  try
    if isLocalTrans  then
      fServer.TransactionBegin;

    fServer.Upate(TSQLTableA, ...);
    // I guess the DB TRANSACTION has been committed by a batch-mode operation 
at this point, thus the following Commit command will fail, thus the exception's 
caught below and Rollback is called, thus causing the 
"cannot rollback - no transaction is active" error.

    if isLocalTrans then
      fServer.Commit;
  except
    if isLocalTrans  then
      fServer.Rollback;
  end;

  ...
end;

The issue is causing my server program to hang. One idea occur to me is to **lock** the DB access in the server-side method above, but how? Thanks.

Last edited by edwinsn (2017-05-25 04:30:02)

edwinsn · 2017-05-25 09:15:19

I think I should call fServer.Db.Lock to get exclusive access to the database server on the server-side.

Correct me if I'm wrong. Thanks.

edwinsn · 2017-05-25 10:08:28

Hey Arnaud, I might be wrong, but since the rest db server is multi-threaded, will it possible for thread B to commit/rollback the transaction started by thread A? If yes, is it a design pitfall?

Sorry if I'm wrong.

mORMot Open Source

#1 2017-05-24 16:45:10

How to get exclusive access to the DB (sqlite) via TSQLRestServerDB?

#2 2017-05-25 09:15:19

Re: How to get exclusive access to the DB (sqlite) via TSQLRestServerDB?

#3 2017-05-25 10:08:28

Re: How to get exclusive access to the DB (sqlite) via TSQLRestServerDB?

Board footer