Professional SQL Server® 2005 Performance Tuning

4.5. Locking and Blocking

No chapter on waiting is complete without a discussion on locking and blocking. To understand why, you need to understand a few core concepts: Concurrency, transactions, isolation levels, blocking locks, and deadlocks.

4.5.1. Concurrency

Concurrency is the ability for multiple processes to access the same piece of data at the same time. There are two approaches to managing this effectively: Pessimistically and optimistically. The pessimistic approach takes the view that different processes will try to simultaneously read and write to the same data and acquires locks to prevent conflicts from occurring. When a process reads data, a lock is placed to prevent another process from writing to it, and when a write occurs a lock is placed to prevent another process reading it. Thus, readers block writers and writers block readers. The optimistic approach takes the view that it's unlikely that readers and writers will cross paths and doesn't lock the data. This means that readers don't block writers and writers don't block readers.

The default in SQL Server is pessimistic concurrency, so a system of locking and un-locking resources is used to enable applications to have high concurrency. However, with the release of SQL Server 2005, you have the ability to adopt an optimistic strategy, which is possible because SQL Server is now able to use row versioning. This allows a reader to read the data as it was before the start of a modification from a writer. We will ...