Embedded SQL for C and SQL Server
Concurrency Control
Standard cursors control, through several options, concurrent access. With concurrent access, data soon becomes unreliable without some type of control. To activate the particular concurrency control desired, specify one of these options in a SET CONCURRENCY statement.
| Option | Result |
|---|---|
| READONLY | Updates are not permitted. |
| LOCKCC | Rows are locked when they are fetched inside a user-initiated transaction. No other user can update these rows. Updates issued by the cursor owner are guaranteed to succeed. |
| With Microsoft® SQL Server™ 2000, locks placed by LOCKCC prevent other users from reading and updating the locked data. Use the BEGIN TRANSACTION and COMMIT TRANSACTION statements to hold the locks. For more information about locking, see Holding Locks. | |
| OPTCC and OPTCCVAL | Fetched rows are not locked. Other users can update or read them. |
To detect collisions between updates issued by the cursor owner and those 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.
| Option | Method of detection |
|---|---|
| OPTCC | Compares timestamps if available; otherwise, saves and then compares the value of all nontext, nonimage columns in the tables with their previous values. |
| OPTCCVAL | Compares all nontext, nonimage values whether a timestamp is available. |