Transaction inside tranaction

[ralfiii]

Hello!

I am just bugfixing in an app that uses IBDAC to access firebird 2.5.
The app starts with

Code: Select all

  Database : TIBCConnection;
begin
  Database.AutoCommit:=False;
  Database.Open;
  Database.StartTransaction;
  ...
  // From time to time - typically after relevant database actions - the app calls
  Database.CommitRetaining;

Everything worked neatly so far and the app is rather big, so I'd like to avoid changing "everything". However, if I am violating some good practice rules the way I handle transaction then please let me know, so at least I know for future projects.

Now I have an error case where CommitRetaining is called but a bit later an error occurs where I'd need to remove the changes that have been made in the database. The flow of events is as follows:

Code: Select all

  Here the "wrapping transaction" should start
  try
     Some Object writes to the database
       and (unfortunately) calls CommitRetaining
     Here an error occurs. As a result the changes in the database should be discarded
  except
        Here I'd like to rollback the "wrapping transaction"
  end;

Any chance to "wrap around" an extra transaction around the critical code section?
Thanks,
Ralf

[AndreyZ]

Hello,

If you perform Commit or CommitRetaining of a transaction, there is no way to roll back changes made in this transaction.

[ralfiii]

So I can't have a construction like that:

Code: Select all

  Start Transaction 1
  try
     start transaction 2
     write to db
     commit (transaction 2)
     write 
     possible bug here
     commit (transaction 2)
  except
    roll back transaction 1  // also rolls back stuff assiciated with tranaction 1
  end; 

This doesn't work?

[AndreyZ]

You can roll back changes made in a transaction, but you can do this only before committing them. You can use the code like this:

Code: Select all

begin
  IBCConnection1.Open;

  IBCQuery1.Connection := IBCConnection1;  
  IBCQuery1.AutoCommit := False;
  IBCQuery1.Transaction := IBCTransaction1; // the first transaction

  IBCQuery2.Connection := IBCConnection1;
  IBCQuery2.AutoCommit := False;
  IBCQuery2.Transaction := IBCTransaction2; // the second transaction
  
  IBCQuery1.Transaction.StartTransaction; // start the first transaction
  try
    IBCQuery1.SQL.Text := 'some changes';
    IBCQuery1.Execute; // changes are sent to the server but are not committed

    IBCQuery2.Transaction.StartTransaction; // start the second transaction
    try
      IBCQuery2.SQL.Text := 'some changes';
      IBCQuery2.Execute; // changes are sent to the server but are not committed
      IBCQuery2.Transaction.Commit; // changes are committed
      // here changes made in the second transaction cannot be roll backed
    except
      IBCQuery2.Transaction.Rollback; // if there were errors, changes are roll backed
      raise; // re-raise the error to prevent committing of the first transaction
    end;

    IBCQuery2.Transaction.StartTransaction; // start the second transaction
    try
      IBCQuery2.SQL.Text := 'some changes';
      IBCQuery2.Execute; // changes are sent to the server but are not committed
      IBCQuery2.Transaction.Commit; // changes are committed
      // here changes made in the second transaction cannot be roll backed
    except
      IBCQuery2.Transaction.Rollback; // if there were errors, changes are roll backed
      raise; // re-raise the error to prevent committing of the first transaction
    end;

    // if there were no errors, commit the changes made in the context of the first transaction
    IBCQuery1.Transaction.Commit;
  except
    IBCQuery1.Transaction.Rollback; // if there were errors, changes are roll backed
  end;
end;

As you can see, a transaction can roll back only those changes that were made in the transaction's context. If you want to make several changes and want to be sure that all of them will be roll backed in case of an error, you should make all of these changes in a context of one transaction. For example:

Code: Select all

begin
  IBCConnection1.Open;

  IBCQuery1.Connection := IBCConnection1;  
  IBCQuery1.AutoCommit := False;
  IBCQuery1.Transaction := IBCTransaction1;
  IBCQuery1.Transaction.StartTransaction;
  try
    IBCQuery1.SQL.Text := 'some changes';
    IBCQuery1.Execute; 

    IBCQuery1.SQL.Text := 'some changes';
    IBCQuery1.Execute; 

    IBCQuery1.SQL.Text := 'some changes';
    IBCQuery1.Execute; 
        
    IBCQuery1.Transaction.Commit; // here all changes are sent to the server
  except
    IBCQuery1.Transaction.Rollback; // if there were errors, all changes are roll backed
  end;
end;