This Article about Locking and Blocking in SQL Server is from my eBook “Tuning SQL Server (Second Edition)”
The majority of modern relational database management systems (RDBMSs) make use of lock-based concurrency. Lock-based concurrency is the approach based on which the Database Engine of a RDBMS ensures that no actions of committed transactions are lost. This is actually what Locking is.
SQL Server Lock Modes
SQL Server locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions. SQL Server uses the following lock modes:
- Shared (S)
- Update (U)
- Exclusive (X)
- Bulk Update (BU)
When Does Blocking Occur?
In RDBMSs with lock-based concurrency, such as SQL Server, there are many cases where blocking can occur. Blocking takes place when one server process id (SPID) holds a lock on one resource and a second SPID tries to place a lock of a conflicting type on the same resource. Most of the times, each blocking incident does not take much time and it is a natural behavior of RDBMs that use lock-based concurrency in order to help ensure data integrity.
There are sometimes however undesirable “blocking” incidents. Such incidents are:
- Starvation: It is the case where a transaction does not release a lock on a table/page thus forcing another transaction to wait indefinitely. SQL Server handles such issues with timeouts.
- Deadlock: It is the case where two transactions wait for each other in order to release their respective locks. SQL Server handles this by automatically selecting one of the two transactions and aborting the other along with rolling it back and sending an error message.
Check our Master Class “Essential SQL Server Administration Tips”
If you really want to learn sophisticated SQL Server administration techniques, then you should check our on-demand online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- The Philosophy and Fundamentals of Computer Programming
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Administration Articles:
- Essential SQL Sever Administration Tips
- How to Patch a Standalone SQL Server Instance
- The SQL Server Browser Service and UDP Port 1434
- The Maximum Number of Concurrent Connections Setting in SQL Server
- Top 10 SQL Server DBA Daily Tasks List
- There is no SQL Server Failover Cluster Available to Join
- Encrypting a SQL Server Database Backup
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).