2 methods for SQL Server blocking exclusions

Background knowledge:

What caused the blockage?

From the point of view of the lock, be sure to lock the object before you can access it whether you are reading or writing, if User A is to be held by the object, a to lock on the object, if B

You also want to access this object, and it also locks the object. The point is that if a user plus an exclusive lock, B users can do is obediently wait for a to run out. B, wait for this.

Is blocking because the two locks cannot coexist to cause a blockage.

Scene Reproduction:

In order to reproduce the scene we have to open three connections to the data. That means open three Managerment studio or open three sqlcmd. I use SSMS (SQL Server managerment Studio) as an example

Prepare the scene first, the code is as follows.

Use Studio;
Go

CREATE TABLE testtable (ID int not null,string nchar (10));
Go

DECLARE @i as int = 1;
While @i<10
Begin
Insert into TestTable (id,string) VALUES (@i,replicate (CAST (@i as nchar (1), 10));
Set @i = @i +1;
End
Go

Select T.id,t.string from TestTable as T;

Go

Connect a runs the following code:

BEGIN Tran
UPDATE dbo. TestTable
Set String = ' AAA '
where id=1;
Go--you can see that a will hold the exclusive lock on the first line until the transaction is commit or rollback!

Connection B runs the following code:

UPDATE dbo. TestTable
Set String = ' BBB '
where id=1;

Go-because a in id=1 this line has the row lock, so B can only obediently wait, which caused the blockage. I can see that B has been waiting for more than 2 minutes.

The C connection is used to unblock, and the running code is immediately spoken.

Method 1, start with the lock

The first step, see who is waiting for resources to determine who is blocked, run the following code

Select
tra.request_session_id as [SessionID],--session Name
Db_name (tra.resource_database_id) as [DBName],--database name
Tra.resource_type as [resourcetype],--resource type
tra.resource_associated_entity_id as [ResourceID],--resource ID
Tra.request_status as [status]--state
From sys.dm_tran_locks as Tra
where tra.request_session_id>50;
Go

--you can see that there is a wait stating that it is waiting for someone else to release the resources.

So 54 is waiting for someone else, who is it waiting for? As can be seen from the figure 51 holds 54 in the waiting object of the exclusive lock, stating that 54 is 51.

The second step, positioning, and so on which resources:

Run the following code

Select object_name (565577053);--565577053 from the penultimate line on the graph.
Go

2 methods for SQL Server blocking exclusions