The logical unit of work feature gives you the ability to group a number of database transaction actions within a logical unit of work and the ability to commit the changes to the database or discard changes using a rollback operation. The database transaction actions are linked together using a logical unit of work identifier (LUWId) that is obtained by using the Logical Unit of Work Begin action. The Logical Unit of Work Commit and Logical Unit of Work Rollback actions are used to close out the logical unit of work.
Assumptions
The following is assumed:
- You are familiar with the concept of transactions and the demarcation of a logical unit of work using the SQL Begin, Commit and Rollback operations.
- You are familiar with database locking features and concurrent table access.
Process
This section describes the tasks you would perform in order to use the Logical Unit of Work feature.
- Create a database transport and specify values for
Logical Unit of Work parameters such
as open timeouts, transaction count limits, and default
resolution (Commit or Rollback). See Creating
the database transport.
- When creating the database transport and working with the logical unit of work feature, you are required to specify a connection pool size greater than 1. When the Logical Unit of Work Begin action executes, the Transaction Server reserves a connection out of the pool to process transactions associated with this logical unit of work. In order to allow database transactions outside of a logical unit of work to be processed, the Transaction Server does not allow the last available connection to be reserved.
- Create a trigger and use the Logical Unit
of Work Begin action to indicate that you want
to start a logical unit of work. If the action is
successful, you will have a 36 character globally
unique identifier (GUID) in the output parameter. This
is the logical unit of work identifier (LUWId).
- In order to indicate that a database transaction action needs to be executed within the logical unit of work, map the identifier to the LUWId input parameter of the database transaction action. This LUWId can be shared between triggers and sub-triggers.
- Use the Logical Unit of Work Commit action to indicate that you want to commit the SQL operations performed by database transaction actions associated with the LUWId.
- Use the Logical Unit of Work Rollback action to indicate that you want to rollback SQL operations performed by database transaction actions associated with the LUWId.
The following shows a partial trigger with actions that use the logical unit of work feature:
MySQL
If you are using the logical unit of work feature with MySQL, the tables associated with the database transaction actions need to use the InnoDB storage engine. The myISAM storage engine, which is selected by default, does not support transactions. This means that any changes made to myISAM tables are committed immediately and cannot be rolled back. For more information, refer to the MySQL Server database documentation.
LOCALDB
The Logical Unit of Work feature cannot be used with the LOCALDB transports including LOCALDB through LOCALDB5.
Database level locking
There are other database level locking issues that will be enumerated per database type as follows:
Record locking
The Transaction Server uses JDBC to access supported databases. All database operations performed by the database transport handler execute with the TRANSACTION_READ_COMMITTED isolation level, with the exception of the Select With Update transport map with Pessimistic Locking where the isolation level is escalated to TRANSACTION_SERIALIZABLE. When a table is accessed within a logical unit of work, the database server might choose to lock individual rows, associated index files, or the entire table. The impact is that other transport maps that are using the same table might experience timeouts waiting on the current logical unit of work to close (Commit and Rollback). JDBC drivers and database servers interpret and implement declared isolation levels in different ways and also use several lock escalation techniques. This can result in the failure of other transactions that are accessing the locked tables. A detailed description of these scenarios is beyond the scope of this document. Work with your database administrator to obtain a better understanding of the impact of logical unit of work transactions on table locking.
MySQL
When inserting data into a table defined with a primary key or unique index from multiple logical units of work, one or more of the logical unit of work transaction actions can still experience a lock wait condition while other logical units of work are open. This is due to MySQL using InnoDB Record, Gap and Nex-Key locks. Thus, when an index range is locked by another logical unit of work, then an attempt to insert a row with a primary key column value that falls in that range will result in the transaction waiting on the index range lock to be released. If the value specified for the primary key is not in the lock range, then the Insert operation will not be blocked. This occurs even though the logical unit of work operates with an isolation level of TRANSACTION_READ_COMMITTED. Refer to the MySQL Server database documentation for a detailed explanation.
When using a Select with Update or Select with Delete operation within a logical unit of work, the underlying table should have a primary key defined; otherwise, the transaction will fail. This is a limitation imposed by the JDBC specification. Refer to the JDBC specification section on Result Set Enhancements and updateable result sets.
Timeout considerations
When configuring the timeout values on your database transport definition, use a value that is longer than the lock timeout specified by your database server. If the transaction timeout is lower than this value and a lock wait condition is in process due to an logical unit of work, the transport will timeout and shutdown the connection to the database invalidating the currently open logical unit of work. When the transaction timeout is longer than the configured database lock timeout, the transport handler will report a failure back to the database transaction action; however, the connection to the database will still remain established and currently open logical unit of work can be used.
You can configure a lock timeout value on the following databases using the Extended Attributes feature on the database transport. See Creating the database transport.
| Database | Extended Attribute |
|---|---|
| DB2 | currentLockTimeout specified in seconds. |
| SQL Server | lockTimeout specified in milliseconds. |
Exception conditions
Conditions that will cause a failure of a Logical Unit of
Work Begin action
- The database transport handler is in Store and Forward mode.
- The database transport handler is unable to connect to the target database.
Conditions that will cause invalidation of all LUWIds that
are currently open on a transport
Any open LUWIds will become invalid due to the following conditions:
- The database transport handler loses connectivity to the database server.
- The database transport handler transitions to Store
and Forward due to the failure of a non-logical unit of
work database transaction action causing the transport
to go to Store and Forward.
When this happens any use of the LUWId in a database transaction action or a Commit or Rollback action will cause the action to fail.
Conditions that will cause forcible termination of the
logical unit of work
- If the number of transactions executed within an logical unit of work exceeds the Transaction Count Threshold as specified in the transport definition.
- If the period of time for which a logical unit of
work is left open exceeds the Idle
Timeout specified in the transport
definition.
When this condition occurs an exception message will be logged.
System variables information about the open logical unit of work
The Transaction Server will publish logical unit of work
information in the System Variables table.
These variables can viewed using the Workbench >
Administration > System
Variables tab.
| System Variable | Description |
|---|---|
| trans.luw.count | This is a count of all of the active logical unit of work for all transport. |
| trans.luw.[0..30] |
Every logical unit of work will have an entry in the system variable table.This entry shows the transport name and the logical unit of work Identifier along with a count of the number of requests that are waiting to be executed (Request Waiting) and the number of transactions that are have executed (Executed). The system variables will not show information for more than 31 concurrent active logical unit of work. |