If you have ever dealt with a transaction being blocked, but didn’t quite know how to fix the problem, this is for you. We will go over how to find blocks using SP_WHO2.
First, we will create a database called BlockTest. Then, I used the following to create an Employees table.
CREATE TABLE [dbo].[Employees]( [EmployeeNo] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](20) NOT NULL, [LastName] [varchar](20) NOT NULL ) ON [PRIMARY] GO
Next, we want to run a transaction that will create a lock on the database. This transaction will create a transaction that will lock the DB for 5 minutes.
USE BLOCKTEST GO BEGIN TRANSACTION SELECT * FROM dbo.employees WITH (TABLOCKX,HOLDLOCK) WAITFOR DELAY '00:05:00' ROLLBACK TRANSACTION
Now, run the following query to try and insert a row into dbo.employees.
USE BLOCKTEST INSERT INTO dbo.employees values ('Test','User');
You will see that this query is taking forever to run. This query won’t actually start until the previous transaction is complete. To see what’s blocking this query, open another query window and use SP_WHO2.
You might have to scroll down a few times, but you will eventually see an entry that has a value in the BLKBY column. This is the transaction that is blocked and the value in the BLKBY column is the blocking transaction.