SET CONCURRENCY
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;