Transaction isolation level determines how a transaction interacts with other simultaneous transactions when they work with the same tables. In particular, it affects how much a transaction "sees" of other transactions' changes to a table.
Each server type supports a different set of possible transaction isolation levels. There are three possible transaction isolation levels:
- DirtyRead: When the isolation level is DirtyRead, your transaction sees all changes made by other transactions, even if they have not been committed. Uncommitted changes are not permanent, and might be rolled back at any time. This value provides the least isolation, and is not available for many database servers (such as Oracle, Sybase, MS-SQL, and InterBase).
- ReadCommitted: When the isolation level is ReadCommitted, only committed changes made by other transactions are visible. Although this setting protects your transaction from seeing uncommitted changes that may be rolled back, you may still receive an inconsistent view of the database state if another transaction is committed while you are in the process of reading. This level is available for all transactions except local transactions managed by the BDE.
- RepeatableRead: When the isolation level is RepeatableRead, your transaction is guaranteed to see a consistent state of the database data. Your transaction sees a single snapshot of the data. It cannot see any subsequent changes to data by other simultaneous transactions, even if they are committed. This isolation level guarantees that once your transaction reads a record, its view of that record will not change. At this level your transaction is most isolated from changes made by other transactions. This level is not available on some servers, such as Sybase and MS-SQL and is unavailable on local transactions managed by the BDE.
In addition,
TSQLConnection lets you specify database-specific custom isolation levels. Custom isolation levels are defined by the
dbExpress driver. See your driver documentation for details.
Note: For a detailed description of how each isolation level is implemented, see your server documentation.
TDatabase and
TADOConnection let you specify the transaction isolation level by setting the
TransIsolation property. When you set
TransIsolation to a value that is not supported by the database server, you get the next highest level of isolation (if available). If there is no higher level available, the connection component raises an exception when you try to start a transaction.
When using TSQLConnection, transaction isolation level is controlled by the IsolationLevel field of the transaction descriptor.
When using InterBase express, transaction isolation level is controlled by a transaction parameter.
See Also