Hi Team,
Given this construct..
{code}
var
  Tables: TEDBStringsArray;
begin
  with MyDatabase do
     begin
     SetLength(Tables,2);
     Tables[0]:='Customer';
     Tables[1]:='Orders';
     StartTransaction(Tables);
     try
        { Perform some updates to the table(s) in the transaction }
        Commit;
     except
        Rollback;
        raise;
     end;
     end;
end;
{code}
What is the best way to handle if say 'Orders' is locked resulting in a "ElevateDB Error #300 Cannot lock the table Orders in the schema Default for transaction access."  error?

Do I put the "StartTransaction(Tables);" into a Try-Except-end of it's own, or is there another way?

Regards,
Ian

Ian

Your code looks good.
If one of the table is locked by another process then an error is raised and the rollback is fired.

I have used a lot of restricted transactions and the code is something like that. The only difference is that I handle any errors using Application.OnException but the idea is the same.

Eduardo

Ian

As Eduardo says your code looks good, however, if you look in edbcomps you'll see that StartTransaction can raise an exception IF the database is already involved in a transaction in the current session. Its not something I've ever bothered about but probably it should have been. You'll only need to handle it if you want to customise the error message.

On another note, if you want an automatic retry you'll need to wrap the lot in a try..except block (probably with a counter).


Roy Lambert

On 1/29/2021 5:12 AM, Jose Eduardo Helminsky wrote:
> Your code looks good.
> If one of the table is locked by another process then an error is raised and the rollback is fired.
>
> I have used a lot of restricted transactions and the code is something like that. The only difference is that I handle any errors using Application.OnException but the idea is the same.


I'm with Eduardo on this in general though i would suggest to actually
check for EDB exception error code - either here or calling function.

If it it is 300 locking then what you do depends on your app logic - if
locking is transient you could for example not re-raise and retry few
times with some delay or ask user if they want to retry (assuming it's
running in UI context).

Raul

Ian,

<< What is the best way to handle if say 'Orders' is locked resulting in a "ElevateDB Error #300 Cannot lock the table Orders in the schema Default for transaction access."  error? >>

You don't have to worry about that - if EDB encounters a lock error (or any error while it is trying to lock the involved tables), then it will unlock any tables that have already been locked before the problematic table.  So, keep your StartTransaction/Commit/Rollback blocks exactly as you have them.

Tim Young
Elevate Software
www.elevatesoft.com