SET CONCURRENCY (E-SQL)

The SET CONCURRENCY statement sets the concurrency option for standard cursors.

Syntax

SET CONCURRENCY {LOCKCC | OPTCC | OPTCCVAL | READONLY}

Arguments

LOCKCC (default if SET ANSI_DEFAULTS is ON)

Specifies intent to update locking. If a FETCH statement is issued within a user-defined transaction, an exclusive lock is placed on the data before it is fetched. The exclusive lock prevents others from viewing or changing the data until the lock is released when the transaction closes.

OPTCC (default if SET ANSI_DEFAULTS is not ON)

Specifies optimistic concurrency control based on a timestamp column (if available) or all nontext, nonimage columns.

OPTCCVAL

Specifies optimistic concurrency control based on all nontext, nonimage columns.

READONLY

Specifies read-only cursors. Data retrieved by a FETCH statement cannot be modified.

Remarks

After the SET CONCURRENCY statement is issued, it affects all subsequent OPEN statements. Using the DECLARE CURSOR FOR UPDATE statement has the same effect as SET CONCURRENCY LOCKCC and any reference to the SET CONCURRENCY statement is ignored. The SET CONCURRENCY statement is also ignored if you are using browse cursors.

If the LOCKCC option is used, you can choose to hold open the user-defined transaction only around each fetch. This requires that a SET FETCHBUFFER statement be issued before opening the cursor. Or you can choose to hold open the user-defined transaction for the life of the cursor. Note that holding open a transaction during LOCKCC cursor operations can significantly reduce concurrency and degrade performance.

If the OPTCC or OPTCCVAL option is used, an UPDATE WHERE CURRENT OF statement can fail if the row has been changed since the last FETCH statement. The application must be able to handle this situation.

Examples

EXEC SQL SET CONCURRENCY READONLY;

See Also