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.

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

  For more information, see CONCURRENTACCESSRESOLUTION bind option.

• 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 in CREATE and ALTER statements for the following objects:

  • Functions
  • Procedures
  • Advanced triggers
• 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 UTS. 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. For more information, see Improving concurrency for applications that tolerate incomplete results and SKIP UNCOMM INSERTS field (SKIPUNCI subsystem parameter).