concurrent-access-resolution-clause

The optional concurrent-access-resolution-clause specifies the concurrent access resolution to use for select-statement.

Read syntax diagramSkip visual syntax diagramWAIT FOR OUTCOME

WAIT FOR OUTCOME specifies to wait for the commit or rollback when encountering data in the process of being updated or deleted. Rows encountered that are in the process of being inserted are not skipped. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED are ignored. This clause applies when the isolation level is CS or RS. It is ignored when an isolation level of UR or RR is in effect, or when the table is column-organized.

This clause causes the following behavior and settings to be overridden:
  • Any higher level setting such as bind options, CLI settings, JDBC settings, or lock modifications.
Read syntax diagramSkip visual syntax diagramSKIP LOCKED DATA

The SKIP LOCKED DATA clause specifies that rows are skipped when incompatible locks that would block the progress of the statement are held on the rows by other transactions. These rows can belong to any accessed table addressed in the statement, including tables accessed in a subquery. This clause applies when the isolation level is CS or RS and is ignored when an isolation level of UR or RR is in effect. It applies to row and block level locks.

This clause causes the following behavior and settings to be overridden:
  • Any higher level setting such as bind options, CLI settings, JDBC settings, or lock modifications still apply, with the modified behavior that if a lock request is made and there is a conflict, the corresponding row is skipped. SKIP LOCKED DATA specified on a statement does override a higher level WAIT FOR OUTCOME setting.

SKIP LOCKED DATA is ignored if it is specified when WITH RR or WITH UR. The default isolation level of the statement depends on the isolation of the package or plan with which the statement is bound, and whether the result table is read-only. If the default isolation level of the statement is Repeatable Read or Uncommitted Read, then SKIP LOCKED DATA is ignored.

SKIP LOCKED DATA clause is strictly SQL based. Also, it cannot be specified for the following:
  • Positioned updates and deletes
  • Subquery
  • Update/delete statements on column organized table
  • PREPARE command
  • BIND command
Attention: The following feature is available in Db2 11.5.6 and later versions.
Read syntax diagramSkip visual syntax diagramNOWAITWAIT <time sec>

The NOWAIT and WAIT clauses specify the number of seconds to wait for a lock before returning an error indicating that a lock cannot be obtained.

When using the WAIT clause, <time sec> is an integer between -1 and 32767.
Note: For NOWAIT and WAIT 0, locks are not waited for. If no lock is available at the time of the request, a -911 error is returned.
When a WAIT value of -1 is specified, lock timeout detection is turned off. In this situation a lock is waited for (if one is not available at the time of the request) until either of the following events occur:
  • The lock is granted.
  • A deadlock occurs.
Use of the NOWAIT and WAIT clauses overwrites the value of the LOCKTIMEOUT database configuration variable and the value of the CURRENT LOCK TIMEOUT special register for this select statement. This means that adding the NOWAIT/WAIT clause with a wait time value of t has the same effect as executing the select statement with a LOCKTIMEOUT value or CURRENT LOCK TIMEOUT value of t.