AutoCommit not working

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Tsagoth
Posts: 33
Joined: Wed 15 Jul 2009 01:25

AutoCommit not working

Post by Tsagoth » Sat 04 Jul 2020 02:44

So I have the following in a multithreaded application. Each thread has it's own connection, query and transaction:

zuser.IBCTransaction1.DefaultConnection := zuser.IBCConnection1;
zuser.IBCTransaction1.IsolationLevel := iblCustom;
zuser.IBCTransaction1.Params.Add('read');
zuser.IBCTransaction1.Params.Add('write');
zuser.IBCTransaction1.Params.Add('wait');
zuser.IBCTransaction1.Params.Add('concurrency');

and then I try to perform:

zdbu.IBCQuery1.Close;
zdbu.IBCQuery1.SQL.Clear;
zdbu.IBCQuery1.SQL.Add('UPDATE DEVICE SET LCALLDATE = :LCALLDATE ');
zdbu.IBCQuery1.SQL.Add('WHERE DEVID = :DEVID;');

zdbu.IBCQuery1.ParamByName('LCALLDATE').AsDateTime := Now;
zdbu.IBCQuery1.ParamByName('DEVID').AsString := string(zdev.deviceid);

zdbu.IBCQuery1.Execute;
zdbu.IBCQuery1.Close;


There are three threads. Each thread executes the update about two seconds after the last thread, so there is ample time for the commit to occur. Threads 1 and 2 execute fine. Thread 3 gets an exception "deadlock update conflicts with concurrent update'.

Why is this ? Why is IBDAC not committing the transaction when I close the TIBCQuery ? If I add a line after the update saying "COMMIT;" then I get an error saying COMMIT not a valid keyword. There would be no conflict if IBDAC was actually doing a commit, so I have to think IBDAC is broken.

Tsagoth
Posts: 33
Joined: Wed 15 Jul 2009 01:25

Re: AutoCommit not working

Post by Tsagoth » Tue 07 Jul 2020 15:14

As a workaround to AutoCommit being broken, I changed the Execut above to :

zdbu.IBCTransaction1.StartTransaction;
zdbu.IBCQuery1.Execute;
zdbu.IBCTransaction1.Commit;

and this works. However, I ran into yet another bug with IBDAC:

zdbu.IBCTransaction1.StartTransaction;
zdbu.IBCQuery1.Execute;
zdbu.IBCTransaction1.Commit;

zdbu.IBCQuery1.Close;
zdbu.IBCQuery1.SQL.Clear;
zdbu.IBCQuery1.SQL.Add('INSERT INTO DEVAPP(IDXNO,DEVID,UPDSTATUS,UPDPKGID,VERSION,FILEVERSION,FILEDATE,ISBANNED,ISACTIVE,APPDATE,APPID) ');
zdbu.IBCQuery1.SQL.Add('VALUES(GEN_ID(IDXGEN,1),:DEVID,:UPDSTATUS,:UPDPKGID,:VERSION,:FILEVERSION,:FILEDATE,:ISBANNED,:ISACTIVE,:APPDATE,:APPID);');

zdbu.IBCQuery1.Prepare;

>> Exception: "can not perform operation on an active transaction"

What gives ? The previous transaction was committed, the query was closed. Why is Prepare starting a transaction ?

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: AutoCommit not working

Post by oleg0k » Wed 08 Jul 2020 10:34

Hello,

Please send us an example demonstrating the incorrect behavior through the contact form on our website:
https://devart.com/company/contactform.html

wbr, Oleg
Devart Team

Tsagoth
Posts: 33
Joined: Wed 15 Jul 2009 01:25

Re: AutoCommit not working

Post by Tsagoth » Wed 08 Jul 2020 13:13

I'm afraid that's impossible, I can't send you my Interbase installation. If you guys lack the skill to read your own code in .Prepare to see why it's creating and then trying to modify an active transaction, then there really is no chance you'll be able to fix it.

Thanks anyway.

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: AutoCommit not working

Post by oleg0k » Fri 24 Jul 2020 12:36

Hello,
We've reproduced the issue conflicting transactions and are working on it.
We'll notify you once we resolve it.

wbr, Oleg
Devart Team

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: AutoCommit not working

Post by oleg0k » Wed 24 Feb 2021 18:22

Hello!
Tsagoth wrote: Sat 04 Jul 2020 02:44 So I have the following in a multithreaded application. Each thread has it's own connection, query and transaction:

zuser.IBCTransaction1.DefaultConnection := zuser.IBCConnection1;
zuser.IBCTransaction1.IsolationLevel := iblCustom;
zuser.IBCTransaction1.Params.Add('read');
zuser.IBCTransaction1.Params.Add('write');
zuser.IBCTransaction1.Params.Add('wait');
zuser.IBCTransaction1.Params.Add('concurrency');

and then I try to perform:

zdbu.IBCQuery1.Close;
zdbu.IBCQuery1.SQL.Clear;
zdbu.IBCQuery1.SQL.Add('UPDATE DEVICE SET LCALLDATE = :LCALLDATE ');
zdbu.IBCQuery1.SQL.Add('WHERE DEVID = :DEVID;');

zdbu.IBCQuery1.ParamByName('LCALLDATE').AsDateTime := Now;
zdbu.IBCQuery1.ParamByName('DEVID').AsString := string(zdev.deviceid);

zdbu.IBCQuery1.Execute;
zdbu.IBCQuery1.Close;


There are three threads. Each thread executes the update about two seconds after the last thread, so there is ample time for the commit to occur. Threads 1 and 2 execute fine. Thread 3 gets an exception "deadlock update conflicts with concurrent update'.

Why is this ? Why is IBDAC not committing the transaction when I close the TIBCQuery ? If I add a line after the update saying "COMMIT;" then I get an error saying COMMIT not a valid keyword. There would be no conflict if IBDAC was actually doing a commit, so I have to think IBDAC is broken.
When a dataset is closed, associated transactions are not closed automatically. The AutoCommit property is True by default, and the Commit (CommitRetaining) or Rollback (RollbackRetaining) methods are executed automatically whenever data is modified. In your project, these method are executed after invoking Execute. You can run your code without using multy-threading, i.e. in a single thread, to observe it.
Tsagoth wrote: Tue 07 Jul 2020 15:14 As a workaround to AutoCommit being broken, I changed the Execut above to :

zdbu.IBCTransaction1.StartTransaction;
zdbu.IBCQuery1.Execute;
zdbu.IBCTransaction1.Commit;

and this works. However, I ran into yet another bug with IBDAC:

zdbu.IBCTransaction1.StartTransaction;
zdbu.IBCQuery1.Execute;
zdbu.IBCTransaction1.Commit;

zdbu.IBCQuery1.Close;
zdbu.IBCQuery1.SQL.Clear;
zdbu.IBCQuery1.SQL.Add('INSERT INTO DEVAPP(IDXNO,DEVID,UPDSTATUS,UPDPKGID,VERSION,FILEVERSION,FILEDATE,ISBANNED,ISACTIVE,APPDATE,APPID) ');
zdbu.IBCQuery1.SQL.Add('VALUES(GEN_ID(IDXGEN,1),:DEVID,:UPDSTATUS,:UPDPKGID,:VERSION,:FILEVERSION,:FILEDATE,:ISBANNED,:ISACTIVE,:APPDATE,:APPID);');

zdbu.IBCQuery1.Prepare;

>> Exception: "can not perform operation on an active transaction"

What gives ? The previous transaction was committed, the query was closed. Why is Prepare starting a transaction ?
This behavior is specific to Firebird: any operation in InterBase/Firebird, even preparing a dataset, is peformed in a transaction. Therefore, TUniQuery.Prepare starts a transaction automatically if no associated transction is active.
Unfortunately, we couldn't reproduce the issue in our test environment. The following code was executed without any errors:

Code: Select all

zdbu.IBCTransaction1.StartTransaction;
zdbu.IBCQuery1.Execute;
zdbu.IBCTransaction1.Commit;
zdbu.IBCQuery1.Close;
zdbu.IBCQuery1.SQL.Clear;
zdbu.IBCQuery1.SQL.Add('INSERT INTO DEVAPP(IDXNO,DEVID,UPDSTATUS,UPDPKGID,VERSION,FILEVERSION,FILEDATE,ISBANNED,ISACTIVE,APPDATE,APPID) ');
zdbu.IBCQuery1.SQL.Add('VALUES(GEN_ID(IDXGEN,1),:DEVID,:UPDSTATUS,:UPDPKGID,:VERSION,:FILEVERSION,:FILEDATE,:ISBANNED,:ISACTIVE,:APPDATE,:APPID);');
zdbu.IBCQuery1.Prepare;
An error is generated when you run this code again, this is expected behavior: StartTransaction tries to start a transaction while a transaction is already active.
In your code, replace the lines:

Code: Select all

zdbu.IBCTransaction1.StartTransaction;
with

Code: Select all

if not zdbu.IBCTransaction1.Active then
  zdbu.IBCTransaction1.StartTransaction;


If this doesn't help, please create a sample project demonstrating the issue, and send it to us along with scripts for creating and populating database objects used in your project: https://devart.com/company/contactform.html
We don't need your InterBase installation, only a sample project with scripts.

Post Reply