8 cách khác nhau để phát DeadLock trong truy vấn cơ sở dữ liệu SQL Server

DeadLock là hiện tượng tranh chấp tài nguyên giữa hai hay nhiều lệnh trong đó lệnh này giữ tài nguyên mà lệnh kia cần dẫn tới việc không lệnh nào có thể kết thúc để giải phóng tài nguyên.
Ví dụ:

• Giao dịch T1 giữ được tài nguyên R1, nhưng cần tài nguyên R2 để kết thúc và giải phóng R1
• R2 lại đang bị T2 giữ và T2 cần R1 để kết thúc và sau đó giải phóng R2.
• Trong trường hợp này cả T1 và T2 không thể kết thúc. Trường hợp này trong SQL Server sẽ phải chọn kill một trong hai giao dịch.

Xem thêm bài https://timoday.edu.vn/phan-tich-deadlock-trong-co-so-du-lieu-va-cach-phong-tranh/
Trong bài này chúng tôi giới thiệu các cách khác nhau để tìm ra các lỗi DeadLock trong truy vấn cơ sở dữ liệu của SQL Server

1. Sử dụng câu lệnh T-SQL SP_LOCK

Thực thi câu lệnh T-SQL SP_LOCK của SQL Server, bạn có thể tìm các status là WAIT cho các phiên đang bị chặn.


USE master;  
GO  
EXEC sp_lock;  
GO 


Xem thêm tại đây

2. Sử dụng câu lệnh truy vấn với sys.sysprocesses


SELECT * FROM sys.sysprocesses 
WHERE blocked > 0


3. Sử dụng trình DVM (Dynamic Management Views)


SELECT 	
	der.blocking_session_id AS BlockingSessionID
	,dest.text AS BlockingStatement
FROM sys.dm_exec_connections AS sdec
INNER JOIN sys.dm_exec_requests AS der
    ON sdec.session_id = der.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS dowt
    ON der.session_id = dowt.session_id
CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest


4. Sử dụng sys.dm_tran_locks


SELECT 
	request_session_id AS SPID 
	,DB_NAME(resource_database_id) AS DatabaseName
	,resource_type AS LockedResource
	,request_mode AS LockType
FROM sys.dm_tran_locks


5. Bật cờ theo dõi bắt buộc để ghi thông tin liên quan đến DeadLock trong Tracefile


DBCC TRACEON (1204, -1)
DBCC TRACEON (1222, -1)


6. Đếm tổng số lần xảy ra DeadLock


SELECT cntr_value AS TotalNumberOfDeadLocks
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Number of Deadlocks/sec'
   AND instance_name = '_Total'


7. Sử dụng Extended Events và đoạn script bên dưới để tạo một Extended Event cho theo dõi DeadLock


CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER 
ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,
ADD EVENT sqlserver.lock_deadlock,
ADD EVENT sqlserver.lock_deadlock_chain,
ADD EVENT sqlserver.xml_deadlock_report 
ADD TARGET package0.event_file(SET filename=N'C:\Temp\Deadlock_Monitor.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO


Bạn có thể sử dụng SSMS để tạo Extended Events cho DeadLock, và có thể theo dõi trạng thái trực tiếp của server.

Xem kết quả khi xảy ra DeadLock:

8. Sử dụng SQL Server Profiler

SQL Server Profiler có ba kiểu sự kiện khác nhau để bắt DeadLock:

• Deadlock graph
• Lock: Deadlock
• Lock: Deadlock Chain
  Bước 1: Chọn Template là Blank
  
  Bước 2: Chọn kiểu Events là Locks để bắt các sự kiện liên quan đến DeadLock