Improve concurrency by avoiding lock waits

The concurrent access resolution, when applicable, can have one of the following values:

• Wait for outcome (default). This value directs the database manager to wait for the commit or rollback when encountering locked data in the process of being updated or deleted. Locked rows that are in the process of being inserted are not skipped. This option does not apply for read-only queries running under isolation level None or Uncommitted Read.
• Use currently committed. This value allows the database manager to use the currently committed version of the data for read-only queries when encountering locked data in the process of being updated or deleted. Locked rows in the process of being inserted can be skipped. This option applies if possible when the isolation level in effect is Cursor Stability and is ignored otherwise.
• Skip locked data. This value directs the database manager to skip rows in the case of record lock conflicts. This option is applicable only when the query is running under an isolation level of Cursor Stability or Read Stability and additionally for UPDATE and DELETE queries when the isolation level is None or Uncommitted Read.

The concurrent access resolution values of USE CURRENTLY COMMITTED and SKIP LOCKED DATA can be used to improve concurrency by avoiding lock waits. However, care must be used when using these options because they might affect application functionality. For more information on the USE CURRENTLY COMMITTED option, see Concurrency.

WAIT FOR OUTCOME, USE CURRENTLY COMMITTED, and SKIP LOCKED DATA can be specified as the concurrent-access-resolution-clause in the attribute-string of a PREPARE statement.

Additionally, they can be specified as the concurrent-access-resolution-clause at the statement level on a select-statement, SELECT INTO, searched UPDATE, or searched DELETE statement.

Concurrent access resolution is also specifiable as a precompiler option by using the CONACC parameter on the CRTSQLxxx. The CONACC parameter accepts one of the following values:

• *DFT - specifies that the concurrent access option is not explicitly set for this program. The value that is in effect when the program is invoked is used. The value can be set using the SQL_CONCURRENT_ACCESS_RESOLUTION option in the query options file QAQQINI.
• *CURCMT - use currently committed.
• *WAIT - wait for outcome.

These same options can be set on the RUNSQLSTM and RUNSQL CL commands and by using the SET OPTION SQL statement. Concurrent access resolution can be specified for SQL triggers, functions, and procedures by using the SET OPTION statement.

When the concurrent access resolution option is not directly set by the application, it is set to the value of the SQL_CONCURRENT_ACCESS_RESOLUTION option in the query options file QAQQINI. This option accepts one of the following values:

• *DEFAULT - the default value is set to *WAIT.
• *CURCMT - use currently committed.
• *WAIT - wait for outcome.