SQL Server Concurrency: Locking, Blocking and Row Versioning

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.

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!

Tags: , , , , , ,

  • 17564 views

  • Rate
    [Total: 4    Average: 2/5]
  • rlDawson

    When?
    Kalen,
    I have followed your work for more than a decade and have always found new things in your books and articles to help me.

    So, when will this one be available? All the page says is "coming soon".

    btw, indexes should be un-impeachable. :-)>

    Have a great day.
    Richard Dawson

  • yogirk

    Eager
    I am eagerly looking forward to this book!

  • saleyoun

    The definitive guide on locking & blocking
    I pick up my copy of this book during SQL in the City event in Austin, TX. This is a must read for anyone who needs to have a good understanding of locking & blocking. Not only DBAs of SQL servers with high concurrency but application developers will also benefit from understanding the material in this book.

    Kalen, you rock at explaining complex subjects and everything SQL.

  • T-SQL

    Concurrency_Code.Zip
    Ms. Delaney

    I purchased your SQL Server Concurrency book but the Concurrency_Code.Zip URL does not seem to work???

    Thanks,
    Roderick

  • DrMabuse

    ZIP files for book
    This is an elegant and excellent book. I’ve learned so much that, once again, I’m amazed at Kalen Delaney’s depth of SQL Server knowledge. She’s definitely my go to person when I want to "go deep". Sans the schizophrenia, there should be a book about her also titled "A Beautiful Mind".

    Just one thing: like the previous gentleman, I too can’t seem to access the Zip file for this book.

  • DrMabuse

    Please disregard my last comment
    I was able to download and extract the .zip file. All is fine.