Using Database Transactions in Delphi VCL Applications
Transaction support is a powerful feature available to database developers, but not used by everyone. That's a shame, since the concept is easy, and once you know how to use transactions in your database interactions, the result is often a more robust and professional application.
In this article, I will first introduce transactions and briefly explain what benefit they can offer.We'll then examine transaction support in the different Win32 data access technologies available in Delphi, from BDE / SQL Links (deprecated!) via dbGo for ADO to dbExpress and MIDAS / DataSnap.Some other time, we'll cover.NET specific transaction support in Delphi.

Transactions?
In real-life, a transaction can be seen as an interchange between two parties.The crux of a transaction is that it either takes place (completely) or not.If we take that concept to the IT world, then a transaction can be completed which means that all parts of the transaction have succeeded, or a transaction can not be completed, in which case everything belonging to that transaction has to be undone, or rolled back.A transaction is often called an atomic operation (in the sense that it cannot be split – or I guess bad things would be the result if it would be split).In database terminology, one of more database operations, executed in the context of the transaction, can end either with a COMMIT or a ROLLBACK command.So either everything happened, or nothing at all.

ACID Transactions
In the database world, transaction features are often summarised in the ACID properties: Atomic, Consistent, Isolated and Durable.Atomic means the transaction should succeed as a whole, or fail.Consistent means that the data in the database must be in a consistent state again after the transaction commits (or rollbacks again).Isolated means that the effects of a transaction should not be visible to other transactions until the transaction had committed, and finally Durable means that once a transaction has committed, the changes should be permanent (if the server crashes or shuts down).
The use of a transaction is especially beneficial in cases where two or more updates (or inserts) depend upon each other, and one should not be done without the other.The deletion of a master record with associated details comes to mind.We should not delete the master without deleting the details, and vice versa.As long as we perform these actions in a single transaction, we will be ensured that everything contained in the transaction either succeeds (with a COMMIT) or is not done at all (using a ROLLBACK).
Using a transaction means that we can perform X-1 steps of a transaction containing X steps, and when the last step fails (step X), a simple ROLLBACK will automatically ensure that all previous steps are undone as well.So it's either all or nothing, which is a great help (especially if you imagine the amount of code you'd have to write yourself to implement this without the support of transactions).

Transaction Example
Perhaps the best way to explain the use of database transactions is to give a real-world example and implement it.Say I want to transfer some money from my bank account into my wife's account.In order for the transaction to succeed, two distinct operations should be performed as one: the balance on my account should be decremented by the specified amount, and the balance on my wife's account should be incremented by the same specified amount.If something unexpected or bad happens along the way (like my account would have insufficient funds, of I made a typing mistake and the account number I specified as my wife's account doesn't exist), then the transaction should be rolled back as if nothing happened.The transaction either commits or rolls back, but no money is lost along the way (even if you have to pay for transferring money from one account to another, the fee would go to the bank account, so still no money is lost or "created" out of thin air, ensuring the consistency of the database).

Transaction Support in Delphi
To implement this example, we only need a single table with two fields: the account number and the balance field, and I'll use one in both Paradox and SQL Server to demonstrate how to use transactions in Delphi.The Paradox table is designed as shown below:

Note that the BALANCE field of type Money has a minimum value of 0, which means that this bank doesn't allow people to get a negative balance in their account. This is an easy way to simulate a failure in an update statement. The table contains a number of records, including record with ACCOUNTNR 42 and 41.In order to transfer an amount of 100 (Euro in my case) from account number 42 to 41, we should execute the two SQL commands shown below:

  UPDATE ACCOUNT SET BALANCE = BALANCE + 100 WHERE ACCOUNTNR = 41
  UPDATE ACCOUNT SET BALANCE = BALANCE - 100 WHERE ACCOUNTNR = 42

BDE / SQL Links
I'm sure all readers are aware of the fact that the BDE has been frozen for a while now, and SQL Links is even deprecated (in fact, SQL Links is no longer shipped with Delphi 2005 or 2006, although these versions of Delphi can use the existing SQL Links installation from Delphi 7 for example).
Transaction support for BDE / SQL Links is available in the TDatabase component, with the StartTransaction, Commit and Rollback methods.We can also use the InTransaction property to determine if we are inside a transaction already.
The TDatabase component has an Execute method that we can use to execute SQL commands (either with or without a transaction), so the two SQL commands from Listing 1 can be placed inside a BDE TDatabase transaction and executed as shown below:

  procedure TFormMain.btnTransferClick(Sender: TObject);
  begin
    Database1.StartTransaction;
    try
      Database1.Execute('UPDATE ACCOUNT SET BALANCE = BALANCE + 100 ' +
                        'WHERE ACCOUNTNR = 41');
      Database1.Execute('UPDATE ACCOUNT SET BALANCE = BALANCE - 100 ' +
                        'WHERE ACCOUNTNR = 42');
      Database1.Commit
    except
      on E: Exception do
      begin
        ShowMessage(E.Message);
        Database1.Rollback
      end
    end;
    tblAccount.Refresh // to refresh display
  end;

BDE Transaction Isolation Level
Note that if you compile and execute this code for the first time, you will get an exception that tells you that "the transaction level must be first read for local database".This is something we have to set using the TransIsolation property of the TDatabase component.This property specifies the way the current transaction interacts with other transactions that operate on the same tables.
There are three possible isolation level values: tiRepeatableRead, tiReadCommitted, and tiDirtyRead.The tiReadCommitted is the default choice, and ensures that we only read committed values from other transactions (so there's no chance we read something which might later be rolled back to its original value).Using tiDirtyRead we allow the transaction to read values set by other transactions which have not committed yet.While this may offer a more up-to-date view of the data, it's also a slightly more dangerous approach, and the data may be rolled back to its original state later.Finally, tiRepeatableRead can be seen as taking a snapshot of the database: once you've read the data, the current transaction will not see any changes made by other transactions (committed or not).This is the highest isolation level for transactions, although the transaction can now fail if it tries to modify a record which has been changed by another transaction.You have to make sure to be able to handle the consequences if you decide to use another transaction isolation level than the default tiReadCommitted.
Regardless of the availability of the BDE TTransIsolation options, using local Paradox, dBASE, Access or FoxPro files with the BDE, only tiDirtyRead is supported.In other words: there's no transaction isolation available for local BDE data access.This means that local BDE does not support full ACID transactions (since the Isolated part is not supported), but that's hardly surprising from a table-based approach.

Transaction in Action!
But even without real transaction isolation, we can demonstrate the usefulness of transactions by running the code in the btnTransferClick event multiple times.As long as the resulting balance of the account with nr 42 remains positive, there is no problem, and the transaction will commit.But as soon as we reach a negative value, an exception is raised (see also Figure 2), and the transaction rollback is executed, so the changes are all undone.
Note that if there was an open dataset (table or query) pointing to the data, we need to Refresh this dataset in order to view the changes.Otherwise, the transaction will be committed, but the dataset is still showing the old values.This is a behaviour we'll see again in the ADO and dbExpress examples, and common to the use of transactions, even in a "connected" approach.

dbGo for ADO
Where BDE can be used for local tables only, and the use of SQL Links is advised against, dbGo for ADO offers support for all kinds of databases that can be connected to using Microsoft Win32's ADO.This includes SQL Server, but also all other databases with an ADO or OLE DB connection available.To continue with the example, I've created a similar ACCOUNT table in SQL Server. Using dbGo for ADO, we can use the TADOConnection component to connect to the SQL Server database, and use the Execute method (similar to the TDatabase's Execute method), as shown in Listing 3.Note again that I had to set the Active property of the ADODataSet (pointing to the ACCOUNT table) to False and back to True again to force a Refresh of the data in the ADODataSet.

  procedure TFormMain.btnADOClick(Sender: TObject);
  var
    NestingLevel,Rows: Integer;
  begin
    NestingLevel := ADOConnection1.BeginTrans;
    try
      ADOConnection1.Execute('UPDATE ACCOUNT SET BALANCE = BALANCE + 100 ' +
                             'WHERE ACCOUNTNR = 41', Rows);
      ADOConnection1.Execute('UPDATE ACCOUNT SET BALANCE = BALANCE - 100 ' +
                             'WHERE ACCOUNTNR = 42', Rows);
      ADOConnection1.CommitTrans
    except
      on E: Exception do
      begin
        ShowMessage(E.Message);
        ADOConnection1.RollbackTrans
      end
    end;
    ADODataSet1.Active := False;
    ADODataSet1.Active := True // Refresh
  end;

Apart from just trying (in a try-except block), I have found no way to determine if nested transactions (or even simple transactions) are supported for the actual DBMS you are using with dbGo for ADO.

dbGo for ADO Transaction Isolation Level
In order to specify the dbGo for ADO transaction isolation level, we must use the IsolationLevel property (before we call the BeginTrans method).Since the dbGo for ADO code doesn't know which database we are using on beforehand, the actual supported isolation level depends on the DBMS that we use.After the transaction has started with the BeginTrans method, we can read the value of the IsolationLevel property again to see the actual isolation level of the current transaction.
There are no less than nine different values for this property, although three are mere aliases, and one is an "unknown" indicator, so there are "only" five real different options that we can use:
ilIsolated is equal to ilSerializable, and means that all transactions are fully isloated from other transactions.
ilRepeatableRead means that we see the snapshot of the data, and won't see any changes until we re-query the database, a bit similar to the BDE's tiRepeatableRead option.
ilReadCommitted is equal to ilCursorStability (the default dbGo for ADO isolation level), which means that we can only see changes from other transactions after they have been committed – this is equal to the BDE tiReadCommitted option.
ilBrowse is equal to ilReadUncommitted which means that we can also see changes that have not yet been committed (and can potentially rollback) – similar to the tiDirtyRead option of the BDE.
ilChaos means that we cannot overwrite changes from higher-isloated transactions.
ilUnspecified finally just indicates that Delphi was unable to determine the exact isolation level which is currently being used.Fortunately, this result doesn't occur often (it never happened to me in real-world projects).
The TADOConnection component also contains a number of transaction supporting events - OnBeginTransComplete, OnCommitTransComplete, OnRollbackTransComplete - that we can use to write event handlers for when transactions are started, committed or rolledback.

dbExpress
Where the BDE and dbGo for ADO are so-called "connected" data access techniques, that operate directly on the table or database, the dbExpress architecture was the first disconnected data access implementation available in Delphi.Instead of directly peeking and poking inside the database table, the dbExpress architecture requires the use of a TDataSetProvider to feed a TClientDataSet before we can connect to a TDataSource and data-aware controls (note that we can use the dbExpress TSQLDataSet fr read-only unidirectional access when that's all that we require).
This means that we need more components to actually view the data, as can be seen below:

Apart from the disconnected approach, dbExpress also offers a database independent implementation (being the recommended replacement for SQL Links), and as such had to deal with lots of different databases and their SQL and transaction capabilities.
Since not all databases that we can connect to using dbExpress may actually support transactions (an old version of mySQL comes to mind) or multiple/nested transactions, we should use the dbExpress TSQLConnection component to verify if transaction support is available, checking the TransactionSupported property after the connection has been opened.If transactions are indeed supported, the TSQLConnection components offers three methods, with the unsurprising names StartTransaction, Commit and Rollback.However, unlike the BDE and dbGo for ADO counterparts, the dbExpress methods all take a single parameter of type TTransactionDesc.The TTransactionDesc is a packed record, with four fields, as shown in the snippet from DBXpress.pas below:

  TTransIsolationLevel = (xilREADCOMMITTED, xilREPEATABLEREAD, xilDIRTYREAD, xilCUSTOM);

    TTransactionDesc = packed record
      TransactionID    : LongWord;             { Transaction id }
      GlobalID         : LongWord;             { Global transaction id }
      IsolationLevel   : TTransIsolationLevel; {Transaction Isolation level}
      CustomIsolation  : LongWord;             { DB specific custom isolation }
    end;

dbExpress Transaction Isolation Level
As we can see in Listing 4, there are four different transaction isolation levels.I'm sure the xilREADCOMMITTED, xilREPEATABLEREAD and xilDIRTYREAD options are clear by now, and the last option – xilCUSTOM – only means that we use a different (or unknown) isolation level, which values is then specified in the CustomIsolation field of the TTransactionDesc record.At this time, no known dbExpress driver uses this option, however.
Using dbExpress, this means that we can write a bit more code to verify that transactions are supported and perform the two updates in one transaction if supported, or without transactions when not supported, as shown in Listing 5.

  procedure TFormMain.btnDBXClick(Sender: TObject);
  var
    Trans: TTransactionDesc;
  begin
    MSSQLCONNECTION.Open;
    if MSSQLCONNECTION.TransactionsSupported then
    begin
      Trans.IsolationLevel := xilREADCOMMITTED; // default
      MSSQLCONNECTION.StartTransaction(Trans)
    end;
    try
      MSSQLCONNECTION.Execute('UPDATE ACCOUNT SET BALANCE = BALANCE + 100 ' +
                              'WHERE ACCOUNTNR = 41', nil);
      MSSQLCONNECTION.Execute('UPDATE ACCOUNT SET BALANCE = BALANCE - 100 ' +
                              'WHERE ACCOUNTNR = 42', nil);
      if MSSQLCONNECTION.TransactionsSupported then
        MSSQLCONNECTION.Commit(Trans)
    except
      if MSSQLCONNECTION.TransactionsSupported then
        MSSQLCONNECTION.Rollback(Trans)
    end;
    cdsAccount.Active := False;
    cdsAccount.Active := True // Refresh
  end;

  if MSSQLCONNECTION.MultipleTransactionsSupported then
    ShowMessage('Multiple Transactions Supported')
  else ShowMessage('Multiple Transactions NOT Supported');

Changes in DBX4
Prior to DBX4, we had to call the StartTransaction, Commit, and RollBack methods of TSQConnection. However, these methods are now deprecated. That doesn't mean that transaction support itself is deprecated (as some people incorrectly thought), but that the way to deal with transactions has changed. We should now use the new methods called BeginTransaction, CommitFreeAndNil, and RollBackFreeAndNil of the TSQLConnection component instead of the deprecated methods:

  DBXTransaction := SQLConnection1.BeginTransaction(TDBXIsolations.ReadCommitted);
  try
    // do your work...
    SQLConnection1.CommitFreeAndNil(DBXTransaction);
  except
    SQLConnection1.RollBackFreeAndNil(DBXTransaction);
    raise
  end;

DataSnap
Where dbExpress offers a disconnected approach to data access, using a TDataSetProvider and TClientDataSet to receive the results of a TSQLDataSet, the DataSnap architecture takes this a step further by splitting the components in two tiers: with the TClientDataSet at the client side, and the TDataSetProvider with the actual data access components (dbExpress, dbGo for ADO or BDE or even a third-party component) at the middle-ware server side.Here, we cannot explicitly start a transaction from the client, since we only have the TClientDataSet and a connection component.But we can call the ApplyUpdates method from the TClientDataSet to start an implicit transaction.
The ApplyUpdate methods takes a single integer parameter.If we pass -1 as value to that parameter, we tell DataSnap to apply all updates in the TClientDataSet's changelog in a single transaction, and to never rollback, regardless of the number of errors encountered along the way.The updates that failed will still be returned, but all updates that succeeded will have succeeded.This is in fact not a true transaction, since it's not 100% all or nothing, but merely "as much as possible".We still have to deal with the updates that failed (by responding to the OnReconcileError event handler of the TClientDataSet).
If we pass a number bigger than or equal to zero, then we tell DataSnap to rollback the transaction only if the number of errors encountered during the update is greater than or equal to the parameter value we passed in.However, if we pass a value bigger than 1, like 2, and encounter one update error, then the transaction will commit (not rolled back), but we still get that single error to handle later.So it's still not all or nothing.The only way we can guarantee that the ApplyUpdates method will use a single transaction that will do all or nothing, is when we pass 0 as argument.No errors allows; zero tolerance, and either all updates will happen, or none at all.
Note that using DataSnap, we cannot use the TClientDataSet to control the actual transaction isolation level – this must be configured at the middle-ware server side, based on the data access technology used at that end (which has been covered in the first parts of this article).
In all cases, any updates that failed are still left in the TClientDataSet's ChangeLog, and updates that succeeded were removed from the ChangeLog, and the place to detect and handle update errors is the OnReconcileError event handler (but that's a story for another day).

  procedure TFormMain.btnApplyClick(Sender: TObject);
  begin
    cdsAccount.ApplyUpdates(0)
  end;

  procedure TFormMain.cdsAccountReconcileError(DataSet: TCustomClientDataSet;
    E: EReconcileError; UpdateKind: TUpdateKind; var Action: TReconcileAction);
  begin
    ShowMessage(E.Message)
  end;

Summary
Transactions offer a powerful way to ensure and safeguard the consistency of your data in databases.ACID transactions offer Atomic, Consistent, Isolated and Durable features. The Local BDE does not implement full ACID transaction support.dbGo for ADO and dbExpress offer transaction isolation options to support ACID transactions, depending on the underlying DBMS used.When using DataSnap for multi-tier applications, the TClientDataSet component offers an implicit transaction, while the actual isolation level should be configured at the middle-ware server tier.
One final advise: make sure the transaction – once started - calls the Commit or Rollback as soon as possible – at least within the same event handler where the transaction was started, and not in different event handlers, to minimise the amount of time (and chance) some other transaction has made conflicting changes.