What Do you Understand by Blocking in SQL Server

What Do you Understand by Blocking in SQL Server

Answer:

SQL Server Blocking refers to the situation where one connection blocks another, even though they come from the same application or user is called blocking. Every live connection in SQL Server will have a logged-on session with the database and will have an associated Session ID a.k.a. (SPID). For Each SPIDs will have a reference to as a process which consists of the SQL server resources and the data structures necessary to service a query at the request of a single connection from a given client. Client applications can spawn one or more connections.

As you can see from the above example, SQL Server sees all these connections separately and it does not matter whether they come from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. If SQL Server is not configured correctly, or due to inefficient application code, or due to high transnational systems, One client connection can block another connection, regardless of whether they come from the same application or separate applications on two different client computers or the sample client computer with different applications.

They way SQL Server works, Blocking is inevitable and an unavoidable characteristic of any relational database management system (RDBMS) where the concurrency model is lock-based. Simply put, a blocking occurs when one SPID holds a lock on a specific resource in SQL Server and a second SPID attempts to acquire a conflicting lock type on the same resource. Now locking happens continuously on SQL Server and the time frames are small for which the first SPID locks the resource. When it releases the lock, the second connection is free to acquire its own unique lock on the resource and continue with query execution and processing. While this is an acceptable normal behavior and happens many times throughout the day with no noticeable effect on system performance, it is when the time was taken is much higher and repeatable, it can be a cause for concern.

See my post on Analyzing System Performance using DMV’s.

Locks are implemented for maintaining Database consistency and the duration and the context of the transaction for a query determine how long its locks are held and, thereby, their impact on other queries. If the query is not executed within a transaction (and no lock hints are used), the locks for SELECT statements will only be held on a resource at the time it is actually being read, not for the duration of the query unlike INSERT, UPDATE, and DELETE statements, where the locks are held for the duration of the query, both for data consistency and to allow the query to be rolled back if necessary.

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.