Page 1 of 1
Transaction inside tranaction
Posted: Fri 12 Apr 2013 08:42
by 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
Re: Transaction inside tranaction
Posted: Fri 12 Apr 2013 13:27
by AndreyZ
Hello,
If you perform Commit or CommitRetaining of a transaction, there is no way to roll back changes made in this transaction.
Re: Transaction inside tranaction
Posted: Fri 12 Apr 2013 13:47
by 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?
Re: Transaction inside tranaction
Posted: Mon 15 Apr 2013 09:02
by 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;