Standard cursors control, through several options, concurrent access (when more than one user accesses and updates the same data at the same time). With concurrent access, data would soon become unreliable without some type of control. To activate the particular concurrency control desired, specify one of the options in the following table in a SET CONCURRENCY statement.
Option | Result |
READONLY | Updates are not permitted. |
LOCKCC | The rows are locked when they are fetched inside a user-initiated transaction. No other user can update these rows. Updates that are issued by the cursor owner are guaranteed to succeed. |
Note that with Microsoft SQL Server, locks that are placed by LOCKCC prevent other users from reading and updating the locked data. Use the BEGIN TRANSACTION and COMMIT TRANSACTION statement to hold the locks. For more information about locking, see "Holding Locks," later in this chapter. | |
OPTCC and OPTCCVAL | Fetched rows are not locked; other users can update or read them. |
To detect collisions between updates that are issued by the cursor owner and those that are issued by other users, standard cursors save and compare timestamps or column values. Therefore, if you specify either of the optimistic concurrency control options (OPTCC or OPTCCVAL), you may want to design the application to retry updates that fail because of collisions with other updates.
The two optimistic concurrency control options differ in the way they detect collisions, as described in the following table.
Option | Method of detection |
OPTCC | Compares timestamps if available; otherwise, saves and then compares the value of all non-text, non-image columns in the tables with their previous values. |
OPTCCVAL | Compares all non-text, non-image values whether or not a timestamp is available. |