SQL Server Concurrency: Locking, Blocking and Row Versioning

Kalen Delaney wrote:In general, database systems can take two approaches to managing concurrent data access: pessimistic or optimistic. Prior to SQL Server 2005, pessimistic concurrency was the only available model. SQL Server 2005 and later versions support both models, but pessimistic concurrency is still the default and is the recommended model until you have thoroughly tested optimistic concurrency and verified that the extra costs are worthwhile. 

This book will examine the details of both concurrency models and explain what factors we must consider when comparing the relative costs on our systems. We’ll also discuss concurrency concepts, such as transactions and isolation levels, that we must understand, no matter which concurrency model we’re using.

Intended Audience

This book is for anyone using SQL Server as a programmer, an administrator, or even a user, who wants to understand how SQL Server manages multiple sessions, and what causes excessive blocking. It is also for those SQL Server professionals who need to know how to troubleshoot and solve blocking and deadlocking problems, and those who need to be able to compare the costs of SQL Server’s two concurrency models to make the best choice for their systems and applications.