Note: This is an update of a blog I posted in 2006 with a lot of additional information (I was less wordy in my 30s apparently). It is just as relevant today. I have a presentation I do occasionally on concurrency entitled “Let Me Finish” that covers many of the different types of concurrency behaviors … Read more
The optimistic concurrency model assumes that several concurrent transactions can usually complete without interfering with each other, and therefore do not require draconian locking on the resources they access. SQL Server 2005, and later, implements a form of this model called row versioning concurrency. It works by remembering the value of the data at the start of the transaction and checking that no other transaction has modified it before committing. If this optimism is justified for the pattern of activity within a database, it can improve performance by greatly reducing blocking. Kalen Delaney explains how it works in SQL Server.
… Read more
Your application can have impeachable indexes and queries, but they won't help you if you can't get to your data because another application has it locked. That's why every DBA and developer must understand SQL Server concurrency and how to troubleshoot excessive blocking or deadlocking. Kalen Delaney's book is your guide.… Read more