Show Table of Contents
1.2. Transaction Qualities of Service
When it comes to choosing the products that implement your transaction system, there is a great variety of database products and transaction managers available, some free of charge and some commercial. All of them have nominal support for transaction processing, but there are considerable variations in the qualities of service supported by these products. This section provides a brief guide to the kind of features that you need to consider when comparing the reliability and sophistication of different transaction products.
Qualities of service provided by resources
The following features determine the quality of service of a resource:
Transaction isolation levels
ANSI SQL defines four transaction isolation levels, as follows:
- Transactions are perfectly isolated from each other. That is, nothing that one transaction does can affect any other transaction until the transaction is committed. This isolation level is described as serializable, because the effect is as if all transactions were executed one after the other (although in practice, the resource can often optimize the algorithm, so that some transactions are allowed to proceed simultaneously).
- Every time a transaction reads or updates the database, a read or write lock is obtained and held until the end of the transaction. This provides almost perfect isolation. But there is one case where isolation is not perfect. Consider a SQL
SELECTstatement that reads a range of rows using a
WHEREclause. If another transaction adds a row to this range while the first transaction is running, the first transaction can see this new row, if it repeats the
SELECTcall (a phantom read).
- Read locks are not held until the end of a transaction. So, repeated reads can give different answers (updates committed by other transactions are visible to an ongoing transaction).
- Neither read locks nor write locks are held until the end of a transaction. Hence, dirty reads are possible (that is, a transaction can see uncommitted updates made by other transactions).
Databases generally do not support all of the different transaction isolation levels. For example, some free databases support only
READ_UNCOMMITTED. Also, some databases implement transaction isolation levels in ways that are subtly different from the ANSI standard. Isolation is a complicated issue, which involves trade offs with database performance (for example, see Isolation in Wikipedia).
Support for the XA standard
In order for a resource to participate in a transaction involving multiple resources, it needs to support the X/Open XA standard. You also need to check whether the resource's implementation of the XA standard is subject to any special restrictions. For example, some implementations of the XA standard are restricted to a single database connection (which implies that only one thread at a time can process a transaction involving that resource).
Qualities of service provided by transaction managers
The following features determine the quality of service of a transaction manager:
Support for multiple resources
A key differentiator for transaction managers is the ability to support multiple resources. This normally entails support for the XA standard, where the transaction manager provides a way for resources to register their XA switches.
Strictly speaking, the XA standard is not the only approach you can use to support multiple resources, but it is the most practical one. The alternative typically involves writing tedious (and critical) custom code to implement the algorithms normally provided by an XA switch.
Support for suspend/resume and attach/detach
Some transaction managers support advanced capabilities for manipulating the associations between a transaction context and application threads, as follows:
- Suspend/resume current transaction—enables you to suspend temporarily the current transaction context, while the application does some non-transactional work in the current thread.
- Attach/detach transaction context—enables you to move a transaction context from one thread to another or to extend a transaction scope to include multiple threads.
Some transaction managers have the capability to manage transactions whose scope includes multiple nodes in a distributed system (where the transaction context is propagated from node to node using special protocols such as WS-AtomicTransactions or CORBA OTS).
Advanced transaction managers typically provide visual tools to monitor the status of pending transactions. This kind of tool is particularly useful after a system failure, where it can help to identify and resolve transactions that were left in an uncertain state (heuristic exceptions).
Recovery from failure
There are significant variations amongst transaction managers with respect to their robustness in the event of a system failure (crash). The key strategy that transaction managers use is to write data to a persistent log before performing each step of a transaction. In the event of a failure, the data in the log can be used to recover the transaction. Some transaction managers implement this strategy more carefully than others. For example, a high-end transaction manager would typically duplicate the persistent transaction log and allow each of the logs to be stored on separate host machines.