More of this Feature Printer friendly version

Join the Discussion "Post your views and comments to this chapter of the free Delphi database Programming Course" Discuss!

Related Resources • free DB Course.TOC • more Database articles

The general idea behind a transaction is that several steps can be performed in series, with the capability to undo all of the steps at once if needed. In addition, the transaction should happen inside an isolated world where other transactions cannot change data while your transaction is running.

For example, to transfer money between two bank accounts, you subtract an amount from one account and add the same amount to the other. If either update fails, the accounts no longer balance. Treating these changes as a single event ensures either all or none of the changes apply.

In this chapter of the free Delphi ADO database course we'll see how to enable transaction processing in Delphi ADO development.

   BeginTrans, CommitTrans, RollBackTrans
Database transactions are a means to allow a user to do many operations on a recordset or not to do any of them. There is no such thing in a transaction that one task is done and other is not. Transactions are always executed as a *whole*. 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.

In Delphi ADO's transaction processing, 3 methods are used with the TADOConnection object to save or cancel changes made to the data source.

Once you call the BeginTrans method, the provider will no longer instantaneously commit any changes you make until you call CommitTrans or RollbackTrans to end the transaction.

   Transaction Level
The IsolationLevel property is the level of transaction isolation for a TADOConnection object. The purpose of the isolation level is to define how other transactions can interact with your transactions, when they work with the same tables. For example, can you see changes in other transactions before or after they are committed? This property only goes into effect after you make a BeginTrans method call.

   Transaction processing
To start a transaction call the BeginTrans method of the TADOConnection object. BeginTrans returns the nesting level of the new transaction. A return value of "1" indicates you have opened a top-level transaction (that is, the transaction is not nested within another transaction), "2" indicates that you have opened a second-level transaction (a transaction nested within a top-level transaction), and so forth. Once the BeginTrans is executed, the OnBeginTransComplete event is triggered and the InTransaction property to True.

Note: Since transactions can be nested, all lower-level transactions must be resolved before you can resolve higher-level transactions.

Once you have started a transaction, a call to commit the transaction is usually attempted in a try...except block. 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.

var Level: integer;
begin
  Level:=ADOConnection1.BeginTrans;
  try
    //do some database
    //updating, deleting or inserting
    ADOConnection1.CommitTrans;
  except
    on E:Exception do ADOConnection1.RollbackTrans;
  end;//try
end;

As you can see, using transactions in Delphi ADO is rather simple. When you call CommitTrans, the current transaction ends and, if possible, all changes are saved. However, if the database is unable to save any one of the changes, then none of them are saved. In this latter case when a saving problem occurs, the CommitTrans method throws an exception, we catch it in the except part and call the RollbackTrans to cancel any changes made during the current transaction.

Even though it is possible, it is NOT advisable to start, commit or rollback a transaction in different event handlers (button clicks, for example). Windows are event driven, if a user starts a transaction within a button click procedure, you must be sure he will try to commit it. Thus, the longer a transaction is active, the higher is the probability that transaction will conflict with another when you attempt to commit any changes.

   To the next chapter
If you need any kind of help at this point, please post to the Delphi Programming Forum where all the questions are answered and beginners are treated as experts.

DB Course Next Chapter >>
>> Deploying Delphi ADO database applications - DB/23