Applies to
TIB_Transaction

Declaration
Property Isolation : TIB_Isolation;

Description
Determines the isolation level of the transaction.

These are the supported modes of isolation:


tiConcurrency - Read-Repeatable or Snapshot
tiConsistency - Forced-Repeatable or Forced Snapshot (Exclusive access)
tiCommitted - Read-Committed

I'll summarize each one's behavior.

tiConcurrency - Read-Repeatable or Snapshot

This transaction isolation is ideal for reports or exports that need to be able to work with a consistant view of the data in a way that will not block others from continuing to update the data. As long as the transaction is open you are guaranteed to get the same exact view of the data over and over again. Essentially, you are given a true SNAPSHOT of the data.

It is possible to update data from this transaction and the updates made within your own transaction context are visible. But, if another user or transaction context has any posted OR COMMITTED changes (on a per individual record basis) then this isolation level will always confront a deadlock resolution for whatever records have been altered when attempting to perform an update or delete on those records. This is because the snapshot baseline for the transaction has record versions of other transactions after it. Since these other changes are not visible to this transaction it will not allow new changes based on an older view of the database to be posted over them.

Thus, as far as locking for this isolation is concerned, any limbo or committed changes on records from another transaction context will result in a deadlock if a change is attempted on any of these records. The only way to be able to avoid the deadlocks is to close and reopen the transaction in order to establish a more recent baseline to work from.

This isolation level is not ideal for user interfaces that involve browsing of data and "pot-shot" editing of records. As users work on the data they will slowly build up locks and nobody can see each other's work once it has committed. How is the user to know when they should do a rollback or commit in order to resolve these matters? This is a bit dangerous to give the user this level of control. Plus, all of the buffered data for the datasets is closed and the user starts all over again.

This is the default isolation for explicitly defined transactions when you use an IB_TransactionXXXX component yourself from the component pallette. Otherwise, an individual internal transaction is generated for each IB_DSQL, IB_Cursor and IB_Query component that does not assign a transaction object to the IB_Transaction property .

tiConsistency - Forced-Repeatable or Forced Snapshot

This isolation is the same as above except that it also imposes the same of other transactions. It keeps a consistant view of the data and also does not allow any other transaction to alter the data. This is why I say "Forced" because it also forces that its view of the data is going to stay the same.

This isolation should ONLY be used when you know exactly what you are doing. It will cause deadlocks galore since it is a blocking transaction. This could lock up a database and keep others from making changes. Of course, if this is what you want to accomplish then go for it.

Keep in mind that there are also ways of using these isolation levels for individual tables as well. So this could become a powerful tool to put together some specialized functionality on a per table basis.

tiCommitted - Read-Committed

This is the ideal isolation for a browsing user interface because it keeps up with all committed changes in the database as they occur. Thus, the interface can be refreshed to reflect changes in the database without having to start a new transaction.

If using an IB_Query component it is even possible to refresh individual rows of a dataset or, based on a refresh of the keys of the dataset, refresh the order or inclusion (in case of deletes and inserts by other users or a change in the ORDER BY criteria) of records without having to refetch the individual records of the dataset again. When this type of refresh is performed all of the row buffers are stored aside so that when their keys are fetched from the server they are reassociated with the record buffer in memory.

This isolation should not be used to run a report or an export because the data may change in a manner that will cause the report to be skewed or inconsistent. When designing an application it is a good idea to have the users browsing on an IB_Query based dataset and then if they want to run a report take the SQL of the IB_Query and assign it to an IB_Cursor that has a different transaction isolation (tiConcurrency) and run the report. Be sure to start with a fresh transaction too. There is an excellent way to move the SQL from an IB_Query to an IB_Cursor that will even keep all of the search criteria of the IB_Query. See the method AssignSQLWithSearch() for more information.

This isolation level is the default isolation for internally defined transactions when the IB_Transaction property of an IB_DSQL, IB_Cursor or IB_Query is left undefined. This is because AutoCommit is also changed to default to true in this circumstance and when AutoCommit is true the isolation of tiCommitted is more sensible. AutoCommit does not demand the isolation be set to tiCommitted.

In InterBase it is not possible to perform a "dirty-read" in which posted and non-committed data from another transaction's context is read. Each transaction defines how it will be able to read data from the database.