Managing Transactions
Go Up to Connecting to Databases Index
A transaction is a group of actions that must all be carried out successfully on one or more tables in a database before they are committed (made permanent). If one of the actions in the group fails, then all actions are rolled back (undone). By using transactions, you ensure that the database is not left in an inconsistent state when a problem occurs completing one of the actions that make up the transaction.
For example, in a banking application, transferring funds from one account to another is an operation you would want to protect with a transaction. If, after decrementing the balance in one account, an error occurred incrementing the balance in the other, you want to roll back the transaction so that the database still reflects the correct total balance.
It is always possible to manage transactions by sending SQL commands directly to the database. Most databases provide their own transaction management model, although some have no transaction support at all. For servers that support it, you may want to code your own transaction management directly, taking advantage of advanced transaction management capabilities on a particular database server, such as schema caching.
If you do not need to use any advanced transaction management capabilities, connection components provide a set of methods and properties you can use to manage transactions without explicitly sending any SQL commands. Using these properties and methods has the advantage that you do not need to customize your application for each type of database server you use, as long as the server supports transactions. (The BDE also provides limited transaction support for local tables with no server transaction support. When not using the BDE, trying to start transactions on a database that does not support them causes connection components to raise an exception.)
Warning: When a dataset provider component applies updates, it implicitly generates transactions for any updates. Be careful that any transactions you explicitly start do not conflict with those generated by the provider.
Starting a transaction
When you start a transaction, all subsequent statements that read from or write to the database occur in the context of that transaction, until the transaction is explicitly terminated or (in the case of overlapping transactions) until another transaction is started. Each statement is considered part of a group. Changes must be successfully committed to the database, or every change made in the group must be undone.
While the transaction is in process, your view of the data in database tables is determined by your transaction isolation level.
For TADOConnection, start a transaction by calling the BeginTrans method:
Level := ADOConnection1.BeginTrans;
BeginTrans returns the level of nesting for the transaction that started. A nested transaction is one that is nested within another, parent, transaction. After the server starts the transaction, the ADO connection receives an OnBeginTransComplete event.
For TDatabase, use the StartTransaction method instead. TDataBase does not support nested or overlapped transactions: If you call a TDatabase component's StartTransaction method while another transaction is underway, it raises an exception. To avoid calling StartTransaction, you can check the InTransaction property:
if not Database1.InTransaction then
Database1.StartTransaction;
TSQLConnection also uses the StartTransaction method, but it uses a version that gives you a lot more control. Specifically, StartTransaction takes a transaction descriptor, which lets you manage multiple simultaneous transactions and specify the transaction isolation level on a per-transaction basis. In order to manage multiple simultaneous transactions, set the TransactionID field of the transaction descriptor to a unique value. TransactionID can be any value you choose, as long as it is unique (does not conflict with any other transaction currently underway). Depending on the server, transactions started by TSQLConnection can be nested (as they can be when using ADO) or they can be overlapped.
var
TD: TTransactionDesc;
begin
TD.TransactionID := 1;
TD.IsolationLevel := xilREADCOMMITTED;
SQLConnection1.StartTransaction(TD);
By default, with overlapped transactions, the first transaction becomes inactive when the second transaction starts, although you can postpone committing or rolling back the first transaction until later. If you are using TSQLConnection with an InterBase database, you can identify each dataset in your application with a particular active transaction, by setting its IsolationLevel property. That is, after starting a second transaction, you can continue to work with both transactions simultaneously, simply by associating a dataset with the transaction you want.
Note: Unlike TADOConnection, TSQLConnection and TDatabase do not receive any events when the transactions starts.
InterBase express offers you even more control than TSQLConnection by using a separate transaction component rather than starting transactions using the connection component. You can, however, use TIBDatabase to start a default transaction:
if not IBDatabase1.DefaultTransaction.InTransaction then
IBDatabase1.DefaultTransaction.StartTransaction;
You can have overlapped transactions by using two separate transaction components. Each transaction component has a set of parameters that let you configure the transaction. These let you specify the transaction isolation level, as well as other properties of the transaction.
Ending a transaction
Ideally, a transaction should only last as long as necessary. The longer a transaction is active, the more simultaneous users that access the database, and the more concurrent, simultaneous transactions that start and end during the lifetime of your transaction, the greater the likelihood that your transaction will conflict with another when you attempt to commit any changes.
When the actions that make up the transaction have all succeeded, you can make the database changes permanent by committing the transaction. For TDatabase, you commit a transaction using the Commit method:
MyOracleConnection.Commit;
For TSQLConnection, you also use the Commit method, but you must specify which transaction you are committing by supplying the transaction descriptor you gave to the StartTransaction method:
MyOracleConnection.Commit(TD);
For TIBDatabase, you commit a transaction object using its Commit method:
IBDatabase1.DefaultTransaction.Commit;
For TADOConnection, you commit a transaction using the CommitTrans method:
ADOConnection1.CommitTrans;
Note: It is possible for a nested transaction to be committed, only to have the changes rolled back later if the parent transaction is rolled back.
After the transaction is successfully committed, an ADO connection component receives an OnCommitTransComplete event. Other connection components do not receive any similar events.
A call to commit the current transaction is usually attempted in a try...except statement. That way, if the transaction cannot commit successfully, you can use the except block to handle the error and retry the operation or to roll back the transaction.
If an error occurs when making the changes that are part of the transaction or when trying to commit the transaction, you will want to discard all changes that make up the transaction. Discarding these changes is called rolling back the transaction.
For TDatabase, you roll back a transaction by calling the Rollback method:
MyOracleConnection.Rollback;
For TSQLConnection, you also use the Rollback method, but you must specify which transaction you are rolling back by supplying the transaction descriptor you gave to the StartTransaction method:
MyOracleConnection.Rollback(TD);
For TIBDatabase, you roll back a transaction object by calling its Rollback method:
IBDatabase1.DefaultTransaction.Rollback;
For TADOConnection, you roll back a transaction by calling the RollbackTrans method:
ADOConnection1.RollbackTrans;
After the transaction is successfully rolled back, an ADO connection component receives an OnRollbackTransComplete event. Other connection components do not receive any similar events.
A call to roll back the current transaction usually occurs in
- Exception handling code when you can not recover from a database error.
- Button or menu event code, such as when a user clicks a Cancel button.