Cannot disconnect database with open transactions

[sandeep_c24]

Hi

I have a Firebird database and I am getting an exception when creating a unique index on a table. The table is populated with data first and then the index is created. Index is created using TZQuery component. Everything works fine if there are no duplicate records in the table, but if there are duplicate records then I get an exception, and if I try to close the database connection I also get following error...


Project Project1.exe raised exception class EZSQLException with message 'SQL Error: cannot disconnect database with open transactions (1 active). Error Code: -901. Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements'.


This results in database file being locked and I cannot delete the file. The only way to delete the database file is to close the app.

It looks like there is a bug in Zeos(Firebird part) which doesn't allow the connection to be closed if there are active transactions. I think all active transaction(s) should be rolled back if the connection is closed.

I am not using the latest SVN version.

Regards

Sandeep

[sandeep_c24]

In following method

Code: Select all

procedure TZConnection.Commit;
var
  ExplicitTran: Boolean;
begin
  CheckConnected;
  CheckNonAutoCommitMode;

  ExplicitTran := FExplicitTransactionCounter > 0;
  if FExplicitTransactionCounter < 2 then
  //when 0 then AutoCommit was turned off, when 1 StartTransaction was used
  begin
    ShowSQLHourGlass;
    try
      try
        FConnection.Commit;
      finally
        FExplicitTransactionCounter := 0;
        if ExplicitTran then
          AutoCommit := True;
      end;
    finally
      HideSQLHourGlass;
    end;
    DoCommit;
  end
  else
    Dec(FExplicitTransactionCounter);
end;

if exception is raised in

Code: Select all

        FConnection.Commit;

then I think the finally part

Code: Select all

      finally
        FExplicitTransactionCounter := 0;
        if ExplicitTran then
          AutoCommit := True;
      end;

should not be done and it should be left to the application to handle it. Doing this will make sure following code works perfectly

Code: Select all

    con.StartTransaction;
    try
      qry.ExecSQL;
      con.Commit;
    except
      con.Rollback;
      raise;
    end;

Should this change be done to this method?

Regards

Sandeep

[sandeep_c24]

Any comments?

Regards

Sandeep

[JefersonOliveira]

then I think the finally part

Code: Select all

      finally
        FExplicitTransactionCounter := 0;
        if ExplicitTran then
          AutoCommit := True;
      end;

should not be done and it should be left to the application to handle it.

+1

In addition to your suggestion, I would like to point that, in StartTransaction method FExplicitTransactionCounter is incremented without any test:

Code: Select all

procedure TZConnection.StartTransaction;
begin
  CheckAutoCommitMode;

  if FExplicitTransactionCounter = 0 then
    AutoCommit := False;
  DoStartTransaction;
  Inc(FExplicitTransactionCounter);  //No test before increment
end;

and then, in the Commit method, if the counter is less than 2 then the transaction is committed, but if greater than 2 the counter is just decremented, but the transaction is not committed:

Code: Select all

procedure TZConnection.Commit;
var
  ExplicitTran: Boolean;
begin
  CheckConnected;
  CheckNonAutoCommitMode;

  ExplicitTran := FExplicitTransactionCounter > 0;
  if FExplicitTransactionCounter < 2 then
  //when 0 then AutoCommit was turned off, when 1 StartTransaction was used
  begin
    ...
  end
  else
    Dec(FExplicitTransactionCounter);
end;

I think that testing the counter value before increment, and only increment if equal zero could avoid non-commitment when StartTransaction is called more than one time before Commit is called. If the test would be made, there would be no need of decrement FExplicitTransactionCounter in Commit method.

If I'm missing something please someone let me know.


P.S.: Thanks to Campus to analyse this problem and share his thoughts on Brazilian's Firebird Mailing List: http://mail.firebase.com.br/pipermail/l ... 42364.html


Regards,

Jeferson Oliveira
Brazil