[SQL] Creating a Test Head Blocker

[Scenario]

A mission given to me killing SQL head blocker automatically by running the process every hour.

However, SQL head blocker randomly appears thus hard to test.

 

Test head blocker needs to be created.

 

[Objectives]

Creating a test head blocker

 

[Steps]

- Go and run SSMS(SQL Server Management Studio)

 

- Create four tabs and run below queries

<Tab 1>

-- 세션 A는 트랜잭션을 시작한 후 커밋하지 않음
use YTC_OPERATION

BEGIN TRAN;
UPDATE dbo.TestBlocker SET Name = 'Blocked by A' WHERE ID = 1;

-- 커밋하지 말고 열어두기 (Head Blocker 역할)
-- 여기서 멈춰 있어야 다른 세션이 블로킹 당함
-- COMMIT 또는 ROLLBACK 은 나중에 수동으로 실행

 

<Tab 2>

-- 이 세션은 세션 A가 락을 풀기 전까지 멈춰 있게 됨

use YTC_OPERATION
UPDATE dbo.TestBlocker SET Name = 'Blocked by B' WHERE ID = 1;

 

<Tab 3>

-- B 세션이 끝나길 기다리게 되는 블로커 체인
UPDATE dbo.TestBlocker SET Name = 'Blocked by C' WHERE ID = 1;

 

<Tab 4>

-- 헤드블로커 확인용 쿼리
SELECT 
    s.session_id,
    s.login_name,
    r.blocking_session_id,
    r.status,
    r.command,
    st.text AS running_sql
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE s.session_id > 50;

 

- Run the tabs in the order then head blockers will appear at tab 4

 

- Double check with Activity Monitor