ODBC offers various types of cursor concurrency to allow you to balance the needs of consistency against multi-user performance. The general goal is to provide for the maximum amount of concurrency (i.e. to maximize multi-user access) by restricting the use of locks to the minimum necessary for individual update operations.
The default type is read-only (SQL_CONCUR_READ_ONLY) which, as the name suggests, only supports reading (i.e. no updating or deleting) of records in the result set.
Locking (SQL_CONCUR_LOCK) uses the lowest level locking necessary to allow safe updating or deleting of rows in the result set. This is sometimes called “pessimistic concurrency” and has the highest adverse effect on concurrency (and thus should be used with restraint).
Between these two extremes are two versions of optimistic concurrency: using row versions (SQL_CONCUR_ROWVER) and using values (SQL_CONCUR_VALUES). The idea of optimistic concurrency is to assume that locking is not needed up front. The assumption is then confirmed prior to actually updating or deleting a record. If the chance of actually updating or deleting is small, or the chance of another user changing the records in the meantime is small, this method can greatly increase concurrency. To confirm the assumption prior to an update or delete, the server checks if the record has been changed since it was read by this user. If so, the update or deletion is aborted and an error returned (typically sqlstate 01001 – cursor operation conflict). The application can then retry the operation using SQL_CONCUR_LOCK. Note, however, that this would require using SQLOP_SET_STMATR to change the concurrency attribute and executing the query / fetch / update sequence again.)
The two methods of detection are to check the row version (typically a timestamp) or the row values. Checking the row version is more efficient, but is not supported by all data sources.
