Using SQL Server Profiler to detect deadlocks (GO)

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:

[SQL]View PlainCopyprint?

1. Use AdventureWorks
2. GO
3. SET TRANSACTION isolation level repeatable READ
4. GO
5. BEGIN TRANSACTION
6. SELECT *
7. From Sales.SalesOrderDetail
8. WHERE Salesorderdetailid = 121316

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

[SQL]View PlainCopyprint?

1. Use AdventureWorks
2. GO
3. SET TRANSACTION isolation level repeatable READ
4. BEGIN TRANSACTION
5. SELECT *
6. From Sales.SalesOrderDetail
7. WHERE Salesorderdetailid = 121317

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

[SQL]View PlainCopyprint?

1. UPDATE Sales.SalesOrderDetail
2. SET orderqty=2
3. WHERE salesorderdetailid=121317

13. Enter the following statement in the second window:

[SQL]View PlainCopyprint?

1. UPDATE Sales.SalesOrderDetail
2. SET orderqty=2
3. WHERE 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.

Using SQL Server Profiler to detect deadlocks (GO)