Experience in resolving SQL Server2005 database deadlock

"SQL Server 2005 deadlock resolution Exploration" mentioned earlier, deadlock severity, an average of one deadlock per day, and a lot of data and ideas in resolving and handling SQL server2005 deadlocks, and then we used the following methods:

1, change the database isolation level to the version control isolation level. (No shared lock deadlock)

2, rebuilding and optimizing indexes, optimizing SQL statements and using partitioned views, and other methods. Improve access speed. (Reduced lockout time)

3, horizontally split the table (partition) and when the program reads and writes as far as possible partition elimination, reduce the number of read and write, reduce the frequency and time of lock escalation. (Reduction of lock escalation)
There is a further understanding of why deadlocks and how few deadlocks are present, and here we share with you:



SQL Server Lock type
There are two main types of locks in the database: S (Shared Lock) and X (exclusive lock)

S (Shared Lock): When the query data is executed, SQL Server locks the row, at which point only the data can be queried, deleted, changed to be blocked,

X (Exclusive lock): when inserting and deleting data, the row is locked, then added, deleted, and changed to be blocked

Both of these locks can cause deadlocks:

Deadlock definition: In two or more tasks, if each task locks a resource that another task is trying to lock, it causes these tasks to become permanently blocked, resulting in a deadlock



Here, simulate the deadlock environment:

Setting up the Environment:
----Deadlock example, set up table data
CREATE TABLE [dbo]. [[Zping.com1]] (
A varchar (2)
, B varchar (2)
, C varchar (2))

--Inserting data
INSERT INTO [dbo]. [[Zping.com1]]
Select ' A1 ', ' B1 ', ' C1 '
UNION ALL SELECT ' A2 ', ' B2 ', ' C2 '
UNION ALL SELECT ' A3 ', ' B3 ', ' C3 '

--Create TABLE data
CREATE TABLE [dbo]. [[Zping.com2]]
(D varchar (2)
, E varchar (2))

--Inserting data
INSERT INTO [dbo]. [[Zping.com2]]
Select ' D1 ', ' E1 '
UNION ALL SELECT ' D2 ', ' E2 '



1.1 deadlock caused by exclusive lock
Execute statement:

BEGIN Tran
Update [dbo]. [[Zping.com2]]
Set d= ' D5 '
Where e= ' E1 '
WAITFOR DELAY ' 00:00:05 '

Update [dbo]. [[Zping.com1]]
Set a= ' AA '
where b= ' B2 '


BEGIN Tran
Update [dbo]. [[Zping.com1]]
Set a= ' AA '
where b= ' B2 '
WAITFOR DELAY ' 00:00:05 '

Update [dbo]. [[Zping.com2]]
Set d= ' D5 '
Where e= ' E1 '


Create a new two window, execute the above statement within 5 seconds, and soon a deadlock prompt will appear. (Remember to roll back the transaction after the end)

1.2 Deadlock caused by shared lock
BEGIN Tran
Update [dbo]. [[Zping.com2]]
Set d= ' D5 '
Where e= ' E1 '
WAITFOR DELAY ' 00:00:05 '

SELECT * FROM [dbo]. [[Zping.com1]]
where b= ' B2 '


BEGIN Tran
Update [dbo]. [[Zping.com1]]
Set a= ' AA '
where b= ' B2 '
WAITFOR DELAY ' 00:00:05 '

SELECT * FROM [dbo]. [[Zping.com2]]
Where e= ' E1 '


Create a new two window and execute the above statement within 5 seconds. A deadlock prompt will soon appear. (Remember to roll back the transaction after the end)



Knowing the cause of the deadlock, the deadlock generated in the production environment is similar in both cases.



A lot of information was later accessed online, including the Help documentation for SQL Server 2005. The following are the main points to be summarized:

1, reduce the isolation level or use row versioning to control the isolation level

2, improve the speed of data access

3, reduce transaction length

4, the Hotspot table will be accessed sequentially (e.g. by placing the frequently accessed table in the last access)



The difficulties encountered
But in our optimization, some are not very good to deal with such as:

1, reduce the transaction length, the size of the transaction is not our decision, it is determined by the business logic (from Tom's "Oracle 9i/10g in-depth internal system organization")

2, in order to access the Hotspot table, we found that the code in the method calls very frequently, often a table called multiple times, to modify the table access order is difficult.

Experience in resolving SQL Server2005 database deadlock