SQL Server Concurrency: Locking, Blocking and Row Versioning - Redgate Software


SQL Server Concurrency: Locking, Blocking and Row Versioning

Download your free eBook.

About the eBook

by Kalen Delaney

Buy from Amazon.comKindle
Buy from Amazon.co.ukKindle

If you’ve designed your SQL code intelligently and implemented a sensible indexing strategy, there’s a good chance your queries will “fly”, when tested in isolation. In the real world, however, where multiple processes can access the same data at the same time, SQL Server often has to make one process wait, sacrificing concurrency and performance in order that all processes can succeed without destroying data integrity.

Transactions are at the heart of concurrency. I explain their ACID properties, the transaction isolation levels that dictate acceptable behaviors when multiple transactions access the same data simultaneously, and SQL Server’s optimistic and pessimistic models for mediating concurrent access.

Pessimistic concurrency, SQL Server’s default, uses locks to avoid concurrency problems. I explain all the different locks and their compatibility. I show how to control locking with hints and bound connections, and how to troubleshoot excessive blocking and deadlocking.

Optimistic concurrency uses row versions to support concurrency. I explain how row versioning works, cover SQL Server’s two snapshot-based isolation levels and offer troubleshooting tips for issues such as update conflicts.

Your application can have world-class indexes and queries, but they won’t help you if you can’t get your data because another application has it locked. That’s why every DBA and developer must understand SQL Server concurrency, and how to troubleshoot any issues. I hope my book helps!

About the author

Kalen Delaney has been working with SQL Server for 25 years, and is one of the world’s most respected authorities on SQL Server and a much sought after trainer and speaker. Her book on “SQL Server Internals” is consistently cited as a must-read for aspiring SQL Server professionals.


  • Twitter
  • Facebook
  • Google+
  • LinkedIn

You may also like

Blog post

Database provisioning from backups using SQL Clone

This article offers a brief, step-by-step guide to the alternative approach of creating a data image from a database full backup.


SQL Clone 60-second intro

New Redgate SQL Clone enables rapid SQL Server provisioning while making efficient use of disk space.


SQL Server Internals: In-Memory OLTP

Kalen Delaney explains how the new In-Memory OLTP engine works, how it stores and manipulates data, and how you can still guarantee the…