SQL Server deadlock handling and tuning experience

"SQL Server 2005 Deadlock resolution Discovery" mentioned a while ago, serious deadlock, the average occurrence of a deadlock every day, in the resolution and processing of SQL server2005 deadlock in the search for a lot of information and think of a lot of ways, on why deadlocks and how less deadlock has a further understanding, Here to share with you:

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

S (Shared Lock): When the query data is executed, SQL Server locks the row, only querying data, deleting, blocking,

X (Exclusive lock): when inserting and deleting data, locks the row, then increases, deletes, and changes are blocked

Both of these locks can cause deadlocks:

Deadlock definition: In two or more tasks, if each task locks a resource that other tasks are attempting to lock, it can cause these tasks to block permanently, resulting in deadlocks



Here simulate the deadlock environment:

Setting up the Environment:
----Deadlock example, CREATE 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 locks
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 in the production environment is similar to both.



Later, a lot of information was available online, including the Help documentation for SQL Server 2005. The summary has the following main points:

1, reduce isolation levels or use row versioning to control isolation levels

2, improve the speed of data access

3, reduce transaction length

4, you will access the Hotspot table sequentially (for example, place the frequently accessed tables on the last access)



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

1, reduce transaction length, the size of the transaction is not decided by us, is determined by the business logic (from Tom's "Oracle 9i/10g deep Internal system body")

2, in order to access the Hotspot table, we found that the code in the method of calling each other very frequently, often a table called many times, to modify the table access order is more difficult.



The method used
Later we used the following methods:

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

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

3, the horizontal split table (partition) and in the program read and write as far as possible partition elimination, reduce the number of read and write lines, reduce the frequency of lock upgrades and time. (Reduce lock upgrades)



Through 4 months or so of operation, the system has been a deadlock, significantly lower than before.