Accessing currently committed data to avoid lock contention

You can reduce lock contention that results from uncommitted insert and delete operations by enabling transactions that read data to access the currently committed data rather than waiting for the uncommitted changes to be resolved.

About this task

You can control whether a transaction that reads data must wait for locks that are held on that data for uncommitted insert and delete operations. If the transaction does not wait for insert or delete operations to commit and resolve the lock contention, the read operation can complete more quickly, and concurrency improves.

Transactions that read data can avoid waiting for the operations to commit by reading the currently committed data. Currently committed data means the data as it was last committed, before the uncommitted change that holds the lock on the row. For uncommitted insert operations, a transaction that reads currently committed data does not read newly inserted rows that are uncommitted.For uncommitted delete operations, a transaction reads the uncommitted deleted rows, as if the delete operation did not happen. Transactions must always wait for uncommitted update operations.

Transactions that read currently committed data must be able to accept data that might be out of date. For applications, procedures, and functions that can tolerate only the most current data, do not enable read transactions to access the currently committed data.

Access to currently committed data is only available on universal table spaces, and applies only to row-level and page-level locks.

To control how access to uncommitted data is resolved, use the following approaches:

• For improved concurrency, specify that most transactions can read currently committed data.
• Specify that transactions wait for committed inserts in the following situations:
  • If your application or stored procedure can tolerate only the most current data.
  • When one transaction creates another. If the initial transaction passes information to the second transaction by inserting data into a table that the second transaction reads, then the second transaction must wait for the uncommitted inserts.
• Control how transactions from bound application programs react to uncommitted insert and delete operations by specifying the value of the CONCURRENTACCESSRESOLUTION bind option. When you specify USECURRENTLYCOMMITTED for this bind option, the application program can access currently committed data rows that are locked by uncommitted insert or delete operations without waiting for lock contention to resolve. When you specify WAITFOROUTCOME, the transaction waits for the locks that are held by uncommitted operations to be released. You can specify the CONCURRENTACCESSRESOLUTION option on the following commands:
  • BIND PACKAGE
  • BIND PLAN
  • REBIND PACKAGE
  • REBIND PLAN
  • REBIND TRIGGER PACKAGE
• Control how stored procedures and functions react to uncommitted data by specifying the CONCURRENT ACCESS RESOLUTION option. When you specify USE CURRENTLY COMMITTED, the procedure or function can access currently committed data rows that are locked by uncommitted insert or delete operations without waiting for lock contention to resolve. When you specify WAIT FOR OUTCOME, the transaction waits for the locks that are held by all uncommitted operations to be released. You can specify the CONCURRENT ACCESS RESOLUTION option on the following SQL statements
  • CREATE PROCEDURE
  • ALTER PROCEDURE
  • CREATE FUNCTION
  • ALTER FUNCTION
• Control how prepared SQL statements react to uncommitted insert and delete operations by specifying USE CURRENTLY COMMITTED in the attribute-string of a PREPARE statement. This option enables the prepared SQL statement to skip data from rows that are locked by uncommitted insert operations and obtain rows that are locked by uncommitted delete operations, instead of waiting for lock contention to resolve. When you specify WAIT FOR OUTCOME, the transaction waits for the locks that are held by all uncommitted operations to be released.
• Control whether applications skip rows that are locked for uncommitted inserts by specifying the value of the SKIPUNCI subsystem parameter. The value of the SKIPUNCI subsystem parameter applies at the subsystem level, and it applies only to insert operations. It applies only when row-level locking is used. However, it is not limited to universal tables spaces. When the value is YES, applications ignore uncommitted inserts as if the insert did not happen. When the value is NO, the application must wait for the insert operations to commit and the locks contention to be resolved. Specify NO if data is frequently modified by delete and insert operations, such that a new image of the data is inserted without the use of update operations. Otherwise, the data might be missed entirely when the uncommitted inserts are skipped.

Results

When different currently committed data options are specified at different levels, the most specific option applies to the transaction. The option that is specified at the statement level applies before the option specified at the package level. The package-level options apply before the value of the SKIPUNCI subsystem parameter value.

The isolation level of a transaction also controls whether it can read currently committed data. Transactions can skip uncommitted insert operations under the ISOLATION(RS) or ISOLATION(CS) options. However, only transactions that use the ISOLATION(CS) and CURRENTDATA(NO) options can read currently committed data when they encounter uncommitted delete operations.