Pessimistic Concurrency Control
Pessimistic concurrency control works on the assumption that there are enough data modification operations in the system to make it likely that any given read operation will be affected by a data modification made by another user. The database engine uses share locks to prevent transactions from reading rows that have been modified but not yet committed by other transactions. The shared locks are held long enough to guarantee the behaviors of the current transaction isolation level setting. For example, in the Read Committed isolation level, shared locks are held only until the end of the current read operation; but in the Serializable isolation level, the share locks are held until the read transaction completes.
The shared locks taken by pessimistic transactions prevent other transactions from modifying data in any way that would break the rules for the isolation level of the pessimistic transaction. The transaction attempting the modification simply waits until the share locks are released. This contrasts to the optimistic model in which some modifications might require that a transaction be rolled back and run again.
Pessimistic concurrency control typically offers the best overall performance in systems with moderate to heavy updating.
Optimistic Concurrency Control
Optimistic transactions do not request shared locks on data during read operations. Optimistic concurrency support in the database engine is enabled by setting either or both the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options to ON. These options enable row versioning in the database, so that the database engine maintains versions of each row that is modified in the database. When a Snapshot transaction reads a row, it is given the version of the row that was last committed before the transaction started. If READ_COMMITTED_SNAPSHOT is ON, a Read Committed transaction is given the version of the row that was last committed before the read operation started.
When a Snapshot transaction attempts to modify data, the database engine first checks to see whether the data was modified by another transaction after the start of the Snapshot transaction. Data modification under Snapshot isolation is optimistic in the assumption that no other transaction is updating the same data. When the Snapshot transaction is alerted that the data it is modifying has been changed by another transaction, it raises an update conflict error, and the Snapshot transaction is aborted. The application can be coded to retry the transaction when update conflicts occur. Note that update conflicts do not occur in Read Committed transactions using row versioning.
Certain cursor options also support optimistic concurrency using a mechanism that is independent of the database engine. When the cursor is opened it is populated with a snapshot of the data as it currently exists.
Zieglers 