SQL Server Concurrency: Locking, Blocking and Row Versioning

Comments 0

Share to social media

1569-SQL_Server_Concurrency_COVERx200.gi

Free PDF: From Red Gate
Buy the printed book: US $29.99 | UK £19.99
Kindle edition: US $10.26 | UK £6.79

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.

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!

Load comments

About the author

Kalen Delaney

See Profile

Kalen Delaney has been working with SQL Server since 1987, and provides advanced SQL Server training to clients worldwide. She has been a SQL Server MVP since 1993 and has been writing about SQL Server almost as long. Kalen has spoken at dozens of technical conferences, including almost every PASS conference in the US since the organization’s founding in 1999. Kalen is the author or co-author of many books on SQL Server, including SQL Server 2012 Internals, from Microsoft Press. She is one of the main editors for SQL Server Central’s SQL Server Stairways Series. Kalen blogs at www.sqlblog.com and her personal website and schedule can be found at www.SQLServerInternals.com.

Kalen Delaney's contributions