Recently I had a deadlock or blocking of my Microsoft SQL database while using Entity Framework, of course, it was a bad piece of code that was causing that block. After the blocking occurred I started getting timeout exception for every database action that I wanted to do, even for the basic most simple one queries.
This specific issue will not be the topic of this article but something more general like what are and why blocks appear, how to detect which is the process that is causing the blocking and what you need to do to stop that process on a database level.
Blocks are actually a good thing
Sure nobody want’s their precious piece of code to get in this situation but this is not a bad thing. Because of these programmers headaches we can rely on our RDBMS and be sure that we have data integrity within our system.
In order for this to be accomplished every transaction in our code must pass the ACID Test, each of these for letter is a criteria that must be met. I’ll briefly describe this criteria without going deeper into this:
- Atomic – this means everything in the transaction will be executed according to the principle “All or Nothing”
- Consistent – transactions are performed in uniform manner
- Isolated – this means that transactions are isolated until they are finished
- Durable – maintain a record of uncompleted transactions in the event of recovery during a failure
The locking of the objects occurs in order to meet the Isolation criteria, this means that a certain object will be locked for all other processes that wants to change that object until the lock is removed.
What are Deadlock, Lock, Block and Timeout
All of these part of the same set and connected, so let’s go briefly through each one of them.
When we have a situation when a client application is waiting for a resource more than the maximum waiting time then a Timeout occurs. Timeouts can occur for all different kind of reasons, like transaction blocking, server is not responding to our request, blocked CPU due to high utilization etc.
For solving the database Timeout issue, besides our code logic, we need to check if there are any blocking transactions (we’ll describe this later in this text), OS response, network response, database timeout threshold, number of database connections etc.
This is one of the main mechanisms for providing data integrity. If our process is accessing some resource with a lock we can ensure that no other process is accessing it before the lock is removed.
We can require for Exclusive Lock or Non-Exclusive Lock. With Exclusive Lock the resource is not available to other processes for reading and writing. If we have Non-Exclusive Lock then the resource is available for read but not for write action.
This is occurring when a process is waiting for another process to release the resource they both want to occupy. This action is called Blocking because one transaction is blocked by another transaction and all processes that want to access the certain process are kept in the waiting queue.
In case when the blocking transaction is waiting for a long time it may cause a Database Timeout to occur.
If the blocking transaction is requiring a lock it may cause a occurrence of a Deadlock.
Let’s say that we have two processes A and B that are requiring a lock to some resource. If we have a situation where the process A is waiting for the transaction in the process B to complete, and in the same time the process B is waiting for the transaction in process A to complete then we have a Deadlock situation.
In this situation we must find what are the processes that are causing this Deadlock, and if you are using MS SQL Server I’ll explain how to detect and stop/kill the blocking process.
How to use SP_WHO2 to discover blocking process
There is a special stored procedure in MS SQL Server which we can use to get overview of the processes that are currently running on our database, see their current status, CPU and Disk utilization and what is important for this article, the ID of the process that is blocking our process (if any).
So, how to run SP_WHO2 stored procedure… If you are using Microsoft SQL Management Studio, after you log into you need to create new query.
In the newly opened query window you need to write the execution command “EXEC SP_WHO2” and click on the “Execute” button.
After this you should see the results table with all the running processes, we want to analyse these two columns, SPID and BlkBy.
The SPID column gives us the ID of each process, and if there is a process that is blocking it the ID of the blocking process would be displayed in the BlkBy column.
So now we now the ID of the process that is not letting our transaction to execute. If the blocking transaction is taking too long we can easy stop it by using the KILL command.
I hope this will be helpful when you run into deadlocks and blocking transactions.