SELECT, UPDATE, and DELETE locks to be applied for to handle deadlock congestion problems (three DELETE operations)

Select, update, delete
The lock to be applied for the operation (three delete operations)

We have previously tested the lock applied for during the select and update operations.
Next, let's test the delete and insert operations.
Applied lock

The default isolation level for database connection in this test: Read committed

Use adventureworks
Go

-- View the isolation level of the current connection:

DBCC useroptions

The returned results are shown in Figure 1:

Figure 1

The last line in Figure 1 shows that the isolation level of the current connection is read committed.

Now let's look at a delete operation:

Set statistics profile on
Go

Begin tran
Delete [HumanResources]. [employee_btree]
Where loginid = 'Adventure-works \ kim1'

The execution plan is shown in Figure 2 ):

Figure 2

Next, let's look at the result of code execution after connecting B (Figure 3 ):

-- Connection B code:
Use adventureworks
Go
Select
Request_session_id,
Resource_type,
Request_status,
Request_mode,
Resource_description,
Object_name (P. object_id) as object_name,
P. index_id
From
SYS. dm_tran_locks t
Left join
SYS. partitions P
On
T. resource_associated_entity_id = P. hobt_id
Order
Request_session_id, resource_type

Figure 3

My table [employee_btree] only creates one clustered index.

From the above results, we can analyze the following:

The execution plan shows the following parts:

Clustered index scan for the first few rows deleted

The delete statement applies for an X lock (4) on the clustered index (index_id = 1)

Figure 4

Apply for an iX lock on its page (5)

Figure 5

These are the locks applied for by the delete statement at the Read committed isolation level.

So what will happen when there is a clustered index at the Repeatable read level?

Next we will perform the same test on table [HumanResources]. [employee_test] As above:

-- Change the isolation level of the connection:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO

-- View the isolation level of the current connection:

DBCC USEROPTIONS
GO

Remember to roll back the previous transaction before executing the following Delete statement:

ROLLBACK TRAN
GO

-- Test the table [HumanResources]. [Employee_Test:

BEGIN TRAN
DELETE [HumanResources]. [Employee_Test]
WHERE LoginID = 'Adventure-works \ kim1'

-- Execution Plan (for example ):

Figure 6

From figure 6, we can see that the execution plan has changed:

The previous clustered index deletion is changed to table deletion.
The previous rows remain unchanged.
The previous clustered index scan turns into an index search.

So does the lock they applied for change?

Next, view the result returned after the code of Connection B is executed (for example ):

Figure 7

See Figure 7:

The DELETE operation applies an X lock for each non-clustered index (index_id = 2, 4) (Figure 8)

Figure 8

Apply for an IX lock on their page (figure 9)

Figure 9

Apply for an IX lock on the modified heap page (figure 10)

Figure 10

Apply for an X lock on the corresponding RID (figure 11)

Figure 11

We can see the following rules from the above:

In the DELETE process, you can find the matching records and then DELETE them. Therefore, if the index is appropriate
The lock applied for in the first step will be less

DELETE not only deletes the data row but also deletes the related index key. Therefore, a table
The more the index, the more locks the number, the more blocking the index.

To prevent blocking

We cannot create any index without creating any index. Instead, we need to create a pair of queries.
It is best to Remove unused indexes for finding favorable indexes.

The INSERT operation is relatively simple. SQL Server will apply for an X lock for the newly inserted data.

Apply for an IX lock on the changed page

Because the inserted data is newly added and used by other connections with a low probability, blocking occurs.
The chances are very low. Here we will not perform a detailed test. If you are interested, try it yourself.