Trace deadlock on SQL Server

SQL Server xử lý Deadlock như thế nào?

Dựa vào 2 tiêu chí:

1. Deadlock Priority
2. Rollback cost

Cách tạo Deadlock trong SQL Server

Bước 1 - Tạo những Table để Demo (SQL 2008 R2)

USE tempdb
IF OBJECT_ID ('TestTable1') IS NOT NULL DROP TABLE TestTable1
IF OBJECT_ID ('TestTable2') IS NOT NULL DROP TABLE TestTable2
CREATE TABLE TestTable1
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL
)
CREATE TABLE TestTable2
(
    Number INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(20) NOT NULL
)

Bước 2 - Tạo Data cho những table ở trên
DECLARE @I INT 
SET @I = 100
WHILE 1 = 1
BEGIN
    SET @I = @I + 1
    INSERT INTO TestTable1(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    INSERT INTO TestTable2(Name)
    VALUES (CONVERT(CHAR(6),@I)+'Test')
    IF @I=10000
    BREAK;
END

Bước 3 - Tạo Transaction A trong New Query Editior Window

USE tempdb
    BEGIN TRAN
    UPDATE TestTable1 SET Name = 'Updated Name'
    WHERE Number = 1000
    WAITFOR DELAY '00:00:15'
    UPDATE TestTable2 SET Name = 'Updated Name'
    WHERE Number = 1000

Bước 4 - Tạo Transaction B in New Query Editor Window
USE tempdb

    BEGIN TRAN
    UPDATE TestTable2 SET Name = 'Updated Name'
    WHERE Number = 1000
    WAITFOR DELAY '00:00:15'
    UPDATE TestTable1 SET Name = 'Updated Name'
   WHERE Number = 1000

Cách trace deadlock trong SQL Server dùng profiler

The following steps enable you to detect deadlocks with SQL Server Profiler:

• Start SQL Server Profiler.
• Select New Trace… from the File menu. In the Connect to Server dialog box, provide

the connection details for the SQL Server hosting the AdventureWorks2012
database and click on Connect.
3. In the General tab of Trace Properties, select the Blank template in the Use the
template: dropdown list.
Dealing with Locking, Blocking, and Deadlocking
390
4. Click on the Events Selection tab. On this screen, expand the Locks event category
and select the following events:
‰‰ Deadlock graph
‰‰ Lock:Deadlock
‰‰ Lock:Deadlock Chain
5. Expand the TSQL event category and select the following events:
‰‰ SQL:StmtCompleted
‰‰ SQL:StmtStarting
6. Click on the Column Filters… button in the Events Selection tab of the Trace
Properties dialog box. In the Edit Filter dialog box, select the DatabaseName data
column from the list of available data columns on the left-hand side. Expand the Like
option, enter the string value AdventureWorks2012, and click on the OK button.
7. Click the Organize Columns… button in the Events Selection tab of the Trace
Properties dialog box and organize the data columns in the order shown in
following screenshot. Click on the Ok button in the Organize Columns dialog box.
8. Click on the Run button to start the trace.
9. Now, open SQL Server Management Studio and establish a connection to the
SQL server.