Deadlocking of SQL Application in SQL 2008

Time:2019-9-22

Neither session 1 nor session 2 can continue until the other party releases resources. So, SQL Server chooses a session in a deadlock as a “deadlock victim”.

Note: The session of the deadlock victim is killed and the transaction is rolled back.

Be careful:deadlockAnd normalblockThey are two often confused concepts.

Some reasons for deadlock:

1. Applications access tables in different order. For example, session 1 updates the customer first and then the order, while session 2 updates the order first and then the customer. This increases the possibility of deadlock.

2. The application uses a long transaction to update many rows or tables in a transaction. This increases the “surface area” of rows, leading to deadlock conflicts.

3. In some cases, SQL Server issues some row locks and then decides to upgrade them to table locks. If these rows are in the same data page and two sessions want to upgrade the lock granularity on the same page at the same time, deadlocks will occur.

First,Analysis of deadlocks using SQL Server Profiler 

http://msdn.microsoft.com/zh-cn/library/ms188246.aspx

2. Finding Deadlock by Using Tracking Flag Location

This paper mainly introduces the use of DBCC TRACEON, DBCC TRACEOFF and DBCC TRACESTATUS commands to ensure that deadlocks are correctly recorded in the SQL Server Management Studio SQL log. These commands are used to enable, close, and check the status of trace flags.

 DBCC TRACEON,Enable trace flags. Usage: DBCC TRACEON (trace [,… N][, -1]) [WITH NO_INFOMSGS]

See MSDN: http://msdn.microsoft.com/zh-cn/library/ms187329.aspx for details.

 DBCC TRACESTATUS,Check the status of the tracking flag. Usage: DBCC TRACESTATUS ([[trace [,… N]] [,] [- 1]]]) [WITH NO_INFOMSGS]

See MSDN: http://msdn.microsoft.com/zh-cn/library/ms187809.aspx for details.

 DBCC TRACEOFF,Close the trace flag. Usage: DBCC TRACEOFF (trace [,… N] [, -1]) [WITH NO_INFOMSGS]

See MSDN: http://msdn.microsoft.com/en-us/library/ms174401.aspx for details.

Let’s simulate a deadlock:

Execute in the first SQL query window:

Copy codeThe code is as follows:
use AdventureWorks
go
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 90
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 91
COMMIT TRAN
END

Execute in the second query window:

Copy codeThe code is as follows:
use AdventureWorks
go

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
WHILE 1=1
BEGIN
BEGIN TRAN
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 91
UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 90
COMMIT TRAN
END

After waiting for a few seconds, one of the query windows will prompt:

Copy codeThe code is as follows:
/*
Msg 1205, Level 13, State 51, Line 9
Transaction (Process ID 52) was deadlocked on lock resources with another process and
has been chosen as the deadlock victim. Rerun the transaction.
*/

At this point, look at the SQL log of SQL Server Management Studio and find that deadlock events are not recorded.
Open the third query window and execute:

Copy codeThe code is as follows:
DBCC TRACEON (1222, -1)
GO
DBCC TRACESTATUS

In order to simulate another deadlock, the connection query of “Victory” (the one not killed) will be restarted, then the session lost by the deadlock will be restarted, and a few seconds later another deadlock will appear.
When a deadlock occurs, stop another query that is executed. Now, the SQL log of SQL Server Management Studio contains detailed error information about deadlock events. Including related databases and objects, lock mode and SQL statements in deadlocks.
Deadlocking of SQL Application in SQL 2008
After checking, close the tracking flag:

Copy codeThe code is as follows:
DBCC TRACEON (1222, -1)
GO
DBCC TRACESTATUS

Analysis: 

In this case, we use the trace flag bit 1222. Trace flag 1222 can return detailed deadlock information to the SQL log. Flag-1 indicates that trace flag 1222 should be enabled globally for all SQL Server connections.

Setting deadlock priority

We can also use the SET DEADLOCK_PRIORITY command to increase the possibility of a query session being selected as a deadlock victim. The grammar of this command is as follows:
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

http://msdn.microsoft.com/en-us/library/ms186736.aspx

For example, in the previous example, if the first query window uses the following deadlock priority commands, it is almost certain that it will be chosen as a deadlock victim. (Normally, SQL Server will take what it considers to be the least expensive connection to cancel or roll back as the default deadlock victim):

Copy codeThe code is as follows:
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET DEADLOCK_PRIORITY LOW
BEGIN TRAN

UPDATE Purchasing.Vendor
SET CreditRating = 1
WHERE VendorID = 2
UPDATE Purchasing.Vendor
SET CreditRating = 2
WHERE VendorID = 1
COMMIT TRAN

Analysis:You can set the priority to High or Normal.HighRepresents that unless another session has the same priority, it will not be chosen as a victim.NormaL is the default behavior, and if another session is High, it may be selected. If the other is Low, it can be safely unchecked. If two sessions have the same priority, the transaction with the least rollback cost is selected.

Additional resources on deadlocks may be added:

HappyhippySQL Server deadlock summary, but also a good summary. http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

Recommended Today

Comparison and analysis of Py = > redis and python operation redis syntax

preface R: For redis cli P: Redis for Python get ready pip install redis pool = redis.ConnectionPool(host=’39.107.86.223′, port=6379, db=1) redis = redis.Redis(connection_pool=pool) Redis. All commands I have omitted all the following commands. If there are conflicts with Python built-in functions, I will add redis Global command Dbsize (number of returned keys) R: dbsize P: print(redis.dbsize()) […]