SQL Server – Concurrency Control – Optimistic and Pessimistic

Concurrency control  manages conflicts in data modification. Optimistic Locking and Pessimistic Locking have different consequences for data concurrency levels. Optimistic locking assumes the relevant data won’t  change while the application reads and Pessimistic locking assumes changes may occur and locking is applied. There a two categories of concurrency control 1) Pessimistic   2) Optimistic

Pessimistic is normally used in high contention environments. An example, is a financial transaction system where there are many writes and reads on the same data. When USER A applies a lock , no other user can commit a conflicting action until USER A releases the lock. From a resource usage perspective, the cost of lock management is less than rolling back transactions.

An application design  consequence of  Pessimistic Locking is deadlock and blocking management. If the resource is at a lock level such as Exclusive Lock (X)  - (used for data modification ) - another resource may request and be blocked . Consideration is given to the impact of the application.

An example of pessimistic concurrency in SQL Server is the REPEATABLE READ transaction isolation level. REPEATABLE READ ensures a statement can’t read modified data that hasn’t yet been commited by another transaction. Also, no other transaction can modify the data being read by the current transaction.

Optimistic is used in low contention environments. USER A does not lock the data when reading. If an update occurred while USER A was reading , a error is returned to USER A. The cost of the occasional transaction rollback is less than managing a lock system

Typically in Optimistic locking , the application may not be using the same connection for the whole session. This means locks cannot be maintained . The data state is managed with techniques such as  timestamps and row state.

Examples of optimistic concurrency in SQL Server are : SNAPSHOT Isolation level. SNAPSHOT isolation recognizes only data modifications commited before the transaction start. Any other changes made by another transaction will not be recognized by the current transaction.

Read More

SQL Server ISOLATION LEVELs ,UDF and table hints - SQL Server ...

SQL Server - How to detect and troubleshoot blocking and deadlocks

Blocked processes - SQL Server DBA