16th-handling locks, blockages, and deadlocks (3)--Detecting deadlocks using SQL Server Profiler

Original: 16th--handling locks, blocks and deadlocks (3)--Detecting deadlocks using SQL Server Profiler

Objective:

As a DBA, it may be common for colleagues or customers to respond to frequent deadlocks that affect the use of the system. At this point, you need to detect and deal with such problems as quickly as possible.

Deadlocks are caused when two or more of the transactions are blocked from one another. In this case, two transactions will wait indefinitely for the other to release the resource for operation. Here is the deadlock:

This article uses SQL Server Profiler to track deadlocks.

Preparatory work:

In order to detect deadlocks, we need to simulate the deadlock first. This example creates two transactions using two different sessions.

Steps:

1. Open SQL Server Profiler

2. Select "New trace" to connect to the instance.

3. Then select "Blank" Template:

4. On the Event Selection page, expand the Locks event and select the following event:

1. Deadlock graph

2, Lock:deadlock

3, Lock:deadlock Chain

5. Then open the TSQL event and select the following event:

1, sql:stmtcompleted

2, Sql:stmtstarting

6, click on "Column Filter", in the tracking properties, select the database name is required to detect the database, here use AdventureWorks.

7. In the "organization column", adjust the order as follows:

8, click to run.

9. Then open SQL Server and open two connections.

10. Enter and execute the following script in the first window:

Use AdventureWorks Goset TRANSACTION isolation level repeatable readgobegin transactionselect  *from    Sales.salesorderdetailwhere   salesorderdetailid = 121316

11. Then enter and execute the following script in the second window:

Use Adventureworksgoset TRANSACTION isolation level repeatable readbegin transactionselect  *from    Sales.salesorderdetailwhere   salesorderdetailid = 121317

12. Now go back to the first form and run the following script:

UPDATE Sales.salesorderdetailset Orderqty=2where salesorderdetailid=121317

13. Enter the following statement in the second window:

UPDATE Sales.salesorderdetailset Orderqty=2where salesorderdetailid=121316

14, and then in the second window you will see the following message:

15, switch to SQL Server Profiler, you can see the following:

16, click "Deadlock graph" time, will show the image of the deadlock:

17. You can save the deadlock image, right-click and select Export event data and save as XDL file:

Here is its XML format:

Analysis:

In this article, you first create a profiler blank template, and then select the following events to monitor:

1. Deadlock graph

2, Lock:deadlock

3, Lock:deadlock Chain

4, sql:stmtcompleted

5, Sql:stmtstarting

Then limit the scope of the monitored objects by qualifying the database.

After the configuration is done, run the trace and run the script in SSMs. SQL Server automatically handles and detects this type of deadlock. You will then receive a 1205 error in the second form.

In SQL Server Profiler, you demonstrated how to collect deadlock events, and in the trace results you can see that two transactions attempt to add an exclusive lock on a key that has a shared lock. With the deadlock image, you can see the details of the deadlock occurring.

In order to avoid or minimize the occurrence of deadlocks, there are some suggestions for reference:

1. Make sure your business is as small as possible, and this refers to the scope.

2. Use a transaction with a lower isolation level.

3. For possible queries, use the NOLOCK query hint.

4, standardized database design.

5. Create an index on the required column so that the table does not need to be scanned frequently, reducing the lock problem.

6. The order in which the database objects are accessed is controlled in the same order.

16th-handling locks, blockages, and deadlocks (3)--Detecting deadlocks using SQL Server Profiler