Here are some hints for working with
IB Objects transactions:
With IBO three conceptual aspects of transaction need to be considered. They are the physical transaction, the logical transaction and whether the transaction is explicit. Each has its own behavior and in many cases the behaviors overlap. Other factors, such as isolation, also affect the behaviors.
Physical Transactions
· IBO automatically takes care of starting the physical transaction. It is not necessary to start the physical transaction explicitly yourself.
· It is the physical transaction which needs to be kept as short as possible so that the server is not prevented from doing its garbage collection.
· It is where the settings that provide the isolation, lockwait, recversion, and so on, take effect.
· The physical transaction forms the foundation for the logical and explicit transactions.
Long Transactions
A physical transaction of long duration can lead to severe performance degradation and, eventually, a server crash if it is left for more than a day or so in a moderately active database environment. A high-volume database could show signs of degradation within a day.
End of the Physical Transaction
A physical transaction is ended when Commit[Retaining], Rollback[Retaining], Refresh() or Close is called. The same methods also end a logical
unit of work and an explicit transaction.
Oldest Active Transaction (OAT) Management
OAT (Oldest Active Transaction) Management is IBO's set of automated features that close the physical transaction for you. Provided you avoid or carefully manage the cases where the OAT management is suspended, you will not have any problems with long physical transactions in your applications.
Here are the cases that prevent IBO from automatically advancing the OAT:
1. You are not using cached updates, AutoCommit is false and you post any change to the database.
This flags the transaction as active (tsActive). The OAT stuff is suspended whilst a transaction's state is tsActive. You must call Commit[Retaining], Rollback[Retaining], LosePoint, SavePoint or Refresh() to resolve the active state of the transaction and allow the OAT stuff to resume.
2. You are using PessimisticLocking and you have a row in dssEdit state.
In order to give duration to the lock, the transaction must be held open. As soon as the row is posted or cancelled, the lock ends, the former rules apply and OAT behavior resumes.
3. You use the tiConcurrency isolation level.
OAT will not advance, because tiConcurrency tells the server that you want a snapshot view of the database. If physical transactions were to able to come and go in order to advance the OAT, the snapshot view would be corrupted. Each time a new transaction is started with tiConcurrency you get a fresh view of the database because other user's committed changes become visible.
4. You have a SELECT statement that will return thousands of rows and you open it but don't fetch all the records.
This forces a cursor to be held open on the server until all the rows have been fetched. Eventually this situation would be overcome by the background fetching that begins taking place in order to free up the cursor. If you set the CommitAction to caInvalidateCursor then it will just get refreshed for you and there won't be a problem.
It is generally inadvisable to leave large datasets open where all records are not fetched in.
Be sure to get familiar with the TimeOutProps settings because it is possible there to ensure that the OAT advancement issues can be dealt with.
Logical Transactions
Consider a logical transaction to be the
unit of work that the user is performing. IBO will automatically keep track of your logical transaction for you.
Implicit Logical Transaction
Use the TransactionIsActive property to determine whether you are in an implicit logical transaction.
· If AutoCommit is true, the implicit logical transaction will be limited to the duration of time that any dataset is in an edit state. As soon as it is posted, the transaction is auto-committed and the implicit logical transaction ends.
· If AutoCommit is false then the implicit logical transaction will start from the moment at which a dataset is put into an edit state and will persist after a change is posted. It will not finish until the transaction is ended by explicitly calling one of the methods to end it.
· If a dataset is cancelled and no other changes had been posted then the implicit logical transaction will be ended automatically.
Explicit Transactions
· An explicit transaction is initiated by calling the StartTransaction method. It will persist until one of the methods to end the
unit of work is called.
· LosePoint and SavePoint does not end an explicit transaction.
· The InTransaction property is used exclusively to determine whether you are in an explicit transaction. It does not indicate the status of an implicit logical transaction or of the underlying physical transaction.
· AutoCommit=True behavior is suspended during an explicit transaction and all changes posted to the server remain in the transaction, i.e. they are not committed when a dataset's Post method is called.
AutoCommit
· Setting AutoCommit to True allows you to avoid taking explicit control of logical transactions. It will generate an immediate SavePoint when you activate the transaction by posting a change to a dataset, executing a DML statement, etc. Everything becomes committed on the server the moment it is executed. In these conditions, there is no logical transaction to worry about.
· If AutoCommit is false, you need to call LosePoint, SavePoint, CommitRetaining, Commit, RollbackRetaining, Rollback or Refresh() in order to process a
unit of work. These methods all end the logical transaction whether it is implicit or explicit.