SQL application in SQL2008-deadlock (deadlocking) _mssql2008

Sessions 1 and Session 2 cannot continue until the other party frees resources. Therefore, SQL Server chooses a session in the deadlock as the "deadlock victim."

Note: The deadlock victim's session will be killed and the transaction will be rolled back.

Note: deadlocks and normal blocking are two concepts that are often confused.

Some of the causes of deadlocks occur:

1. The application accesses the table in a different order. For example, session 1 updates the customer and then updates the order, and session 2 updates the order and then updates the customer. This increases the likelihood of deadlocks.

2. The application uses a long transaction to update many rows or many tables in a transaction. This increases the "surface area" of the row, resulting in a deadlock conflict.

3, in some cases, SQL Server issued a number of row locks, and then it decided to upgrade them to table locks. If these rows are in the same data page, and two sessions want to upgrade the lock granularity simultaneously on the same page, a deadlock is generated.

One, using SQL Server Profiler to parse deadlocks  

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

Second, use the tracking sign to find the deadlock

This article mainly describes the use of the DBCC TRACEON, DBCC TRACEOFF, and DBCC TRACESTATUS commands to ensure that deadlocks are correctly logged to the SQL Server Management Studio SQL log. These commands are used to enable, close, and check the status of trace flag bits.

DBCC traceon, enable trace flag bits. Usage: DBCC traceon (trace# [,... n] [,-1]) [with NO_INFOMSGS]

See more Msdn:http://msdn.microsoft.com/zh-cn/library/ms187329.aspx

DBCC tracestatus, check trace flag bit status. Usage: DBCC tracestatus ([[trace# [,... N]] [,] [-1]]) [with NO_INFOMSGS]

See more Msdn:http://msdn.microsoft.com/zh-cn/library/ms187809.aspx

DBCC traceoff, turn off trace flag bits. Usage: DBCC traceoff (trace# [,... n] [,-1]) [with NO_INFOMSGS]

See more Msdn:http://msdn.microsoft.com/en-us/library/ms174401.aspx

Here we simulate a deadlock:

Execute in the first SQL query window:

Copy Code code 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 Code code 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 a few seconds, one of the query Windows prompts:

Copy Code code as follows:

/*
MSG 1205, level, state Wuyi, line 9
Transaction (Process ID) is deadlocked on lock and another Process and
has been chosen as the deadlock victim. Rerun the transaction.
*/

At this point, look at SQL Server Management Studio's SQL log and find that the deadlock event is not logged.
Open a third query window and execute:

Copy Code code as follows:

DBCC Traceon (1222,-1)
Go
DBCC Tracestatus

To simulate another deadlock, the "Win" Connection query (the one that was not killed) is restarted, and then the deadlock lost session is restarted, and a second deadlock occurs after a few seconds.
After a deadlock occurs, stop another query that executes. SQL Server Management Studio's SQL log now contains detailed error information for the deadlock event. Includes related databases and objects, locking mode, and SQL statements in deadlocks.

When the check is complete, turn off the trace flag bit:

Copy Code code as follows:

DBCC Traceon (1222,-1)
Go
DBCC Tracestatus

Analytical:  

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

Third, set the deadlock priority

We can also use the Set deadlock_priority command to increase the likelihood of a query session being chosen as a deadlock victim. The syntax for 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, the first query window would almost certainly be chosen as a deadlock victim if the following deadlock priority command was used. (Normally, SQL Server considers the connection with the lowest cost of cancellation or rollback as the default deadlock victim):

Copy Code code 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

Resolution: You can set the priority to high or normal, and highindicates that unless another session has the same priority, it will not be chosen as a victim. Normal is the default behavior and may be selected if another session is high. If the other is low, it can be safely unchecked. If the two sessions have the same priority, the transaction with the lowest rollback cost is selected.

Additional resources about deadlocks may be added:

The

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