Delphi, Firebird, IBObjects and Transaction Management

I recently had reason to revisit transaction management in our application. The application makes extensive use of data aware grids (TDBGrid) and, as I am sure everyone is aware, it is impossible to explicitly manage transactions when you are displaying data in a grid.

Fortunately, IBObjects provides the means to automatically clean up transactions left hanging by TDBGrid. Unfortunately, this isn’t as well documented or as straightforward as I would like. So, after much research using the programmer’s secret weapon (Google) and a few questions posted to the IBObjects support group (thanks Jason) I think I finally understand how it works.

I started out by trying to figure out exactly what state the various transactions were in after I opened the dataset and displayed the first batch of rows in the grid. To do this, I looped through the TIB_Connection.Transactions list and displayed the status of all of the TIB_Transactions that I found there. There are a number of properties of TIB_Transaction that are of interest here. These are:

1. Started
   If this property is True it means that a transaction has been started with Firebird. That is to say that Firebird would consider this transaction to be active and ultimately in need of being committed or rolled back.
2. TransactionIsActive
   If this property is True it means that some kind of update has been performed using this transaction. That is, at least one row has been updated, inserted or deleted, some DDL was executed or a Firebird POST_EVENT statement was executed since the transaction was started. It should be noted that IBObjects will not always realize that data has been updated if the update is done in a stored procedure. In some cases it is necessary to set StoredProcHasDML to True to force the transaction to be marked as active. Also, if you use ExecuteImmediate to execute an SQL statement, IBObjects does not check to see if the statement updates any data. In this case you would have to mark the transaction active manually, using TIB_Transaction.Activate.
3. InTransaction
   If this property is True it means that an explicit transaction has been started. That is, your application has started a transaction using TIB_Transaction.StartTransaction.
4. LastStarted
   The date and time that the transaction was started with Firebird. This may or may not have anything to do with the time that your application called StartTransaction.

What this showed to me was that, even though I only have a single transaction declared in my application, there are 2 transactions present for my database connection. The second one is a hidden transaction that is used internally by IBObjects when it needs to query the database to get the metadata for the various SQL statements used in your application. In any event, after my application opened the dataset and displayed the first batch of rows in the grid, both of these transactions would have the Started property set to True for some period of time. Eventually both of the transactions would be closed but it would take a couple of minutes for this to happen. In a busy database environment, a couple of minutes is an eternity.

As I mentioned earlier, there is a way to help this process along. This is all controlled by TIB_Transaction.TimeoutProps property. This property has several sub-properties. These are:

1. AllowCheckOAT
   This needs to be set to the number of seconds from the time the transaction was started, after which you want IBObjects to attempt to end (commit) the transaction. The transaction can only be ended if all datasets have had all of their rows fetched and none of the datasets have updates pending.
2. Attempt
   This needs to be set to the number of seconds from the time the transaction was started, after which you want IBObjects to start fetching rows from open cursors. The rows are fetched in small increments during the application’s idle processing.
3. AttemptMaxRows
   This needs to be set to the maximum number of rows that IBObjects will fetch automatically. This needs to be a reasonable value. After all, you wouldn’t want to automatically fetch all rows for a dataset containing millions of rows. When IBObjects fetches this many rows, it will stop fetching further rows. This means that the transaction cannot be automatically closed because the dataset will still have rows pending but they will never be fetched. There is a way to force the transaction closed. More on this later.
4. AttemptRetry
   IBObjects automatically fetches rows in small increments. It does this by fetching rows for AttemptTicks milliseconds every AttemptRetry seconds. So if AttempTicks were 500 and AttemptRetry were 5, IBObjects would fetch records for 500 milliseconds every 5 seconds.
5. AttempTicks
   See AttemptRetry.
6. ForceClosed
   This needs to be set to the number of seconds from the time the transaction was started, after which IBObjects will force the transaction to be closed, whether or not all rows have been fetched or whether or not any datasets are active. A value of zero means that the transaction will never be forced closed. The user will be prompted before the transaction is forced closed.
7. PropmtUser
   This needs to be set to the number of seconds from the time the transaction was started, after which IBObjects will prompt the user, asking whether or not it is OK to force the transaction closed. A value of zero means that the user will never be prompted.
8. PromptUserDuration
   This needs to be set to the number of seconds that the prompt dialogue will be displayed.
9. PromptUserRetry
   This needs to be set to the number of seconds between user prompts.

网址：http://www.lnssoftware.ca/blog/?p=141