In this section:
DB2 ensures transaction integrity by locking data source rows when they are read. The behavior of a lock depends on the isolation level of a transaction. The techniques suggested here for WebFOCUS Maintain applications all use an isolation level of repeatable read. Repeatable read involves a trade-off: it ensures absolute transaction integrity, but it can prevent other users from accessing a row for long periods of time, creating performance bottlenecks.
Under repeatable read, a row is locked when it is retrieved from the data source, and is released when the transaction that retrieved the row is either committed to the data source or rolled back. A Maintain DB2 transaction is committed or rolled back each time a WebFOCUS Maintain application issues a COMMIT or ROLLBACK command. You explicitly code COMMIT and ROLLBACK commands in your WebFOCUS Maintain application. In some circumstances the application may also issue these commands implicitly, as described in Designing Transactions That Span Procedures, and in When an Application Ends With an Open Transaction.
We recommend two strategies for writing transactions to DB2 data sources:
While these strategies are described for use with DB2 data sources, you can also apply them to transactions against other kinds of data sources, changing DBMS-specific details when necessary.
If you are familiar with using the DB2 Data Adapter with Information Builders products other than WebFOCUS Maintain, note that WebFOCUS Maintain works with DB2 a bit differently:
You can use the transaction locking strategy to manage DB2 row locks in WebFOCUS Maintain applications. While this strategy is described for use with DB2 data sources, you can also apply it to transactions against other kinds of data sources, changing DBMS-specific details when necessary. When using transaction locking, your application locks each row with an isolation level of repeatable read for the duration of the transaction, from the time it retrieves the row, until the time it commits or rolls back the transaction.
The following illustration shows the duration of connections, threads, and logical transactions (also known as logical units of work) when you use this strategy:
If your applications are small in scope, comprising only a single procedure, the duration of connections, threads, and logical transactions would look like this:
Compared to change verification, transaction locking is simpler to code, but keeps rows locked for a longer period of time. This may cause other users to experience time outs, in which case DB2 will return a -911 or -904 SQL code. You can mitigate the effect of row locking by:
To implement the transaction locking strategy for managing DB2 row locks in WebFOCUS Maintain applications, bind the DB2 Data Adapter plan with an isolation level of repeatable read. The isolation level is a DB2 Data Adapter installation BIND PLAN parameter. In your WebFOCUS Maintain application:
Caution: If any called procedure within the scope of a transaction returns control without GOTO END KEEP, Maintain issues an implied COMMIT command, releasing all row locks and making the application vulnerable to updates by other users. Be sure to return control using GOTO END KEEP; otherwise, code each transaction within a single procedure, so that the scope of each transaction does not extend beyond one procedure, or use the change verification strategy described in Using Change Verification to Manage DB2 Row Locks.
You can use the change verification strategy to manage DB2 row locks in WebFOCUS Maintain applications. While this strategy is described for use with DB2 data sources, you can also apply it to transactions against other kinds of data sources by changing DBMS-specific details when necessary.
When using change verification, your application retrieves all needed rows into a stack, locking them in the process; releases the locks after retrieval; and then performs all updates against the stack (not against the data source). This enables you to work with the data in the stack as long as necessary without preventing other users from accessing the data source. When you are ready to close the transaction, retrieve the original rows from the data source again, relocking them in the process. Then, compare their current values in the data source to their original values when you first retrieved them, and write the transaction to the data source if the values are the same, that is, if the rows have not been changed by other users in the interim.
Change verification enables the maximum number of users to access the same data concurrently, and makes it possible to write the maximum number of transactions in the shortest time. It is able to do this because it is an optimistic locking protocol, that is, it is optimized for the most common situation, in which at any moment, at most one user will attempt to update a given row. Compared to transaction locking, this is more complex to code, but locks rows for less time, increasing data availability.
The following illustration shows the duration of connections, threads, and logical transactions when you use this strategy for DB2 data sources:
To implement the change verification strategy for managing DB2 row locks in WebFOCUS Maintain applications, bind the DB2 Data Adapter plan with an isolation level of repeatable read. The isolation level is a DB2 Data Adapter installation BIND PLAN parameter. In your WebFOCUS Maintain application:
The COMMIT or ROLLBACK command releases all row locks.