Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Concurrency: Locking, Blocking and Row Versioning

By Kalen Delaney,

DBA Handbooks

SQL Server Concurrency: Locking, Blocking and Row Versioning by Kalen DelaneySQL Server Concurrency: Locking, Blocking and Row Versioning by Kalen Delaney


Buy from Amazon.com
Buy from Amazon.co.uk
Download eBook (PDF)


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!

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.


Publisher: Red Gate Books

Total article views: 2230 | Views in the last 30 days: 54
 
Related Articles
FORUM

concurrent queries

concurrent queries

FORUM

Loading server with concurrent user

Loading server with concurrent sessions

FORUM

Application Server Unavailable error SSRS Report Manager

Application Server Unavailable error when accessing the Report Manager for SQL Server Reporting Serv...

BLOG

Practical SQL Server Performance Troubleshooting Day

Due to the huge demand of SQL Server Troubleshooting Know How and Techniques I'm running on January...

BLOG

Practical SQL Server Performance Troubleshooting Day

Due to the huge demand of SQL Server Troubleshooting Know How and Techniques I’m running on January ...

Tags
books    
concurrency    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones