Ensuring Transaction Integrity for DB2 Data Sources

In this section:

Using Transaction Locking to Manage DB2 Row Locks
Using Change Verification to Manage DB2 Row Locks

Reference:

How Maintain Data DB2 Logic Differs From Other Information Builders Products

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 Maintain Data 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 Data DB2 transaction is committed or rolled back each time a Maintain Data application issues a COMMIT or ROLLBACK command. You explicitly code COMMIT and ROLLBACK commands in your Maintain Data 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:

Using transaction locking to manage DB2 row locks. This locks each row for the duration of the transaction, from the time a row is retrieved, until the transaction is committed. In effect, it relies on DB2 to ensure transaction integrity. This is simpler to code, but keeps rows locked for a longer period of time. This is the preferred strategy, unless the duration of its locks interferes excessively with your data source concurrency requirements.
Using change verification to manage DB2 row locks. This locks each row while it is being retrieved, releases the lock, and then relocks the row shortly before writing it to the data source. This technique ensures transaction integrity by verifying, before writing each row, that the row has not been changed by other users in the interim. This is more complex to code, but locks rows for a shorter period of time, increasing data availability.

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.

Reference: How Maintain Data DB2 Logic Differs From Other Information Builders Products

If you are familiar with using the Data Adapter for DB2 with Information Builders products other than Maintain Data, note that Maintain Data works with DB2 a bit differently:

Maintain Data enables you to issue COMMIT and ROLLBACK commands explicitly. It also issues them implicitly in certain situations, as described in Designing Transactions That Span Procedures, and in When an Application Ends With an Open Transaction.
Maintain Data does not support the SQL DB2 SET AUTOCOMMIT command to control automatic commits.
Because Maintain Data works on sets of rows, the Data Adapter for DB2 does not automatically generate change verification logic.

Using Transaction Locking to Manage DB2 Row Locks

How to:

Implement Transaction Locking for DB2

You can use the transaction locking strategy to manage DB2 row locks in Maintain Data 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 the following illustration:

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:

Keeping the size of the transaction small, making it less likely that another user will encounter a row locked by your transaction.
Implementing the change verification strategy described in Using Change Verification to Manage DB2 Row Locks.
Having user applications check for a locked condition when retrieving rows, and upon encountering a lock, re-issuing the retrieval request a specified number of times in a loop. If the user application exceeds the specified number of attempts, have it display a message to the user indicating that the row is in use, and suggesting that the user try again later.
Using standard database administration techniques, such as report scheduling, tablespace management, and data warehousing.

Procedure: How to Implement Transaction Locking for DB2

To implement the transaction locking strategy for managing DB2 row locks in Maintain Data applications, bind the Data Adapter for DB2 plan with an isolation level of repeatable read. The isolation level is a Data Adapter for DB2 installation BIND PLAN parameter. In your Maintain Data application:

Read the rows. Retrieve all required rows. Retrieval locks the rows with an isolation level of repeatable read.
Write the transaction to the data source. Apply the updates of the transaction to the data source.
Be sure to terminate called procedures correctly. If a Maintain Data procedure calls another Maintain Data procedure within the scope of a transaction, the called procedure must return control using the GOTO END KEEP command. For more information about GOTO END KEEP, see Designing Transactions That Span Procedures.

Caution: If any called procedure within the scope of a transaction returns control without GOTO END KEEP, Maintain Data 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.
Close the transaction. When the transaction is complete, close it by issuing a COMMIT or ROLLBACK command. The COMMIT or ROLLBACK command releases all row locks.

Using Change Verification to Manage DB2 Row Locks

How to:

Implement Change Verification for DB2

You can use the change verification strategy to manage DB2 row locks in Maintain Data 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.

Procedure: How to Implement Change Verification for DB2

To implement the change verification strategy for managing DB2 row locks in Maintain Data applications, bind the Data Adapter for DB2 plan with an isolation level of repeatable read. The isolation level is a Data Adapter for DB2 installation BIND PLAN parameter. In your Maintain Data application:

Read the rows. Retrieve all required rows into a stack (for example, Stack1). Retrieval locks the rows with an isolation level of repeatable read.
Free the row locks. Issue a ROLLBACK command immediately following retrieval in order to release all row locks.
Copy the stack. Make a copy of the stack (for example, Stack2). You will use this copy later when checking for changes.
Write the transaction to the stack. Apply the updates of the transaction to the rows in the original stack (Stack1).
Read the rows again. Retrieve the rows of the transaction from the data source into a new stack (for example, Stack3). Retrieval relocks the rows with an isolation level of repeatable read.
Verify changes. Compare the original data source values in the copy of the original stack (Stack2) to the current data source values (Stack3) to verify that other users have not changed these rows in the interim.
Write the transaction to the data source. If any of these rows have been changed in the data source by another user, you can roll back the transaction or take some other action, as your application logic requires. If none of the rows in the transaction have been changed by other users in the interim, your application can apply the transaction updates to the data source, and issue a COMMIT command to commit the transaction.

The COMMIT or ROLLBACK command releases all row locks.