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

-- Select, update, and delete locks to be applied for to handle deadlock congestion problems (two update operations)

For the update statement, you can simply query the SQL Server first and find the data to be modified.

Then, modify the record. The S lock (shared lock) is required for the query action. After finding the record to be modified

The U Lock will be applied first, and then the U Lock (update lock) will be upgraded to the X lock (exclusive lock ).

[HumanResources]. [employee] and [HumanResources]. [employee _ test]

Test the two tables to see how SQL Server applied for a lock during update. Continue to use repeatable

To run an update statement:

-- Connect code:
Use adventureworks
Go
SET transaction isolation level Repeatable read
Set statistics profile on
Go
Begin tran
Update [HumanResources]. [employee_test]
Set Title = 'changedheap'
Where employeeid in (200)
-- Rollback tran
-- Result returned after connection A is executed (figure ):

-- Open a new connection (Connection B) and execute the following code:

-- Connection B:
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

-- Connect to the result returned by code B (Figure B ):

-- From Figure B, we can see that:

This update statement applies for three U locks (update locks) on Non-clustered indexes)

3 x locks applied on the RID (exclusive locks)

This statement uses the non-clustered index pk_employee_employee_test (index_id is 2)

Find the three records. Non-clustered index pk_employee_employee_test does not exist

The title column is used, so it does not need to be modified. However, the data RID has been repaired.

Therefore, the RID is applied with the X lock, and no lock is applied to other indexes.

From this example, we can see that if update uses an index, the key value of this index

There will be a U Lock, and no lock will be applied to unused indexes. There will be an X lock in the actual modification.

Apply the IU lock to the Page SQL Server involved in the query to modify the Page SQL Server

Apply the IX lock

The above case is that the modified column is not used by the index. If the modified column is indexed
What will happen when it is used?

Below I will use the [HumanResources]. [Employee] Table for testing:

First, I create a non-clustered index in the Title field of the [HumanResources]. [Employee] table.

Create nonclustered index Employee_Title_IDX ON

[HumanResources]. [Employee] ([Title] ASC) ON [PRIMARY]

GO

-- Next we will first roll back the previous transaction:
ROLLBACK TRAN

-- Then execute the following update statement:

BEGIN TRAN
UPDATE [HumanResources]. [Employee]
SET Title = 'changedheap'
WHERE EmployeeID IN (200)

-- The Execution Plan result of query A is (Figure C ):

-- Run the statement connecting to B:
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
Go

-- Result returned after the statement is executed (Figure D ):

We can see through the analysis of Figure D:

The statement uses the clustered index to find the three records that will be modified. But we can see that there are nine keys

X lock.

Pk_employee_employeeid (index_id = 1) clustered index, where data is stored

The update statement has not been changed to its index column. It only needs to change the value of the title column.

Therefore, on index_id = 1, it only needs to apply for three X locks, each record with one

However, the title contains a non-clustered index: employee_title_idx (index_id = 5), and

Title is the first column. It is modified later. The original index key value will be deleted and inserted

Apply for 6 x locks on the index_id = 5 index, and 3 new old key values.

Three key values

Because the title column is not used for other indexes, none of them apply for a lock.

The above is the source of the nine key locks.

The two examples show that:

(1) For each used index, SQL Server will apply the U lock to the preceding key value

(2) SQL Server only adds an X lock to the record or key value to be modified.

(3) The more indexes are used for the columns to be modified, the more locks are used.

(4) The more pages scanned, the more intention locks will be. During the scan
The record will also be locked, even if it is not modified above

Therefore, from the above rule, if you want to reduce the chance that an UPDATE statement will be blocked by others

In addition to paying attention to the query part, the database design also needs to do the following:

(1) modify as few records as possible. The more records you modify, the more locks you need.

(2) Minimize indifferent indexes. The more indexes, the more locks required.

(3) Avoid table scanning as much as possible. If only a small part of the table is modified, try to use
Index seek to avoid the emergence of full table scan such execution plans