Locking and Blocking in SQL Server

Locking and Blocking in SQL Server

Locking and Blocking in SQL Server

This article is from my book:

Tuning SQL Server (eBook)

Tuning SQL Server (Second Edition)

Table of Contents | Sample Chapter | Learn More

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 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)
  • Intent
  • Schema
  • Bulk Update (BU)
  • Key-range

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.

Learn more about SQL Server Performance Tuning in my latest eBook “Tuning SQL Server (second edition)”!

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)


What are your views on the subject? Have something to share? Feel free to leave your comment!

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

Recommended eBooks on SQL Server:

Developing with SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Developing SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics 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). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.