::: Simalation Locking, Blocking & Deadlock in SQl Server MSSQL, LCK_M_S(locking), LCK_M_X(Blocking Deadlock) :::.

 

Mantap ini contoh tabel kamu sudah pas untuk bikin simulasi.
Saya buatkan 3 skenario nyata di SQL Server: locking ? blocking ? deadlock (step-by-step, tinggal kamu jalankan di 2 query window / session).

1. SIMULASI LOCKING

Session 1 (Window 1) (session 64 sa)

SELECT @@SPID as session_id;
BEGIN TRAN;
UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 10 
WHERE KODE_PEMBELIAN = 'BEL-E001';
 

Session 2 (session 60 sa)
 
SELECT @@SPID as session_id;
SELECT * 
FROM pembelian 
WHERE KODE_PEMBELIAN = 'BEL-E001';
 

Session 3 (session 53 aisyah)

SELECT @@SPID as session_id;
SELECT * 
FROM pembelian 
WHERE KODE_PEMBELIAN = 'BEL-E001';
 

Session 4 (session 87 adminsql)

SELECT @@SPID as session_id;
SELECT * 
FROM pembelian 
WHERE KODE_PEMBELIAN = 'BEL-E001';
 

DBCC INPUTBUFFER(64);
 

 

2. SIMULASI BLOCKING

Session 1 (session 91 sa)

BEGIN TRAN;
UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 99 
WHERE KODE_PEMBELIAN = 'BEL-E002';
-- JANGAN COMMIT dulu
 

Session 2 (session 60 sa)

SELECT @@SPID;
UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 50 
WHERE KODE_PEMBELIAN = 'BEL-E002';
 

Session 3 (session 74 aisyah)

SELECT @@SPID;
UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 51 
WHERE KODE_PEMBELIAN = 'BEL-E002';
 

Session 4 (session 83 adminsql)

SELECT @@SPID;
UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 52 
WHERE KODE_PEMBELIAN = 'BEL-E002';
 

91 = ROOT BLOCKER (biang utama)
60 kena lock dari 91, lalu dia nge-block yang lain

 

3. SIMULASI DEADLOCK (INI YANG SERU ??)

Kita bikin 2 transaksi saling kunci

Session 1 (session 52 sa)
BEGIN TRAN;

UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 100 
WHERE KODE_PEMBELIAN = 'BEL-E003';

WAITFOR DELAY '00:00:05';

UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 200 
WHERE KODE_PEMBELIAN = 'BEL-E004';

COMMIT;
 

Session 2 (jalankan cepat setelah Session 1 start) (session 90 adminsql)
BEGIN TRAN;

UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 300 
WHERE KODE_PEMBELIAN = 'BEL-E004';

WAITFOR DELAY '00:00:05';

UPDATE pembelian 
SET JUMLAH_PEMBELIAN = 400 
WHERE KODE_PEMBELIAN = 'BEL-E003';

COMMIT;
 

Hasilnya:

SQL Server akan detect:

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 

deadvisual

 

 

 

dead solusi

 

Deadlock event

 

  

Query Processlist SQL Server

 
SELECT @@SERVERNAME,concat('kill ',s.session_id),s.session_id,
s.login_name,
r.status,
r.blocking_session_id 'Blk_by',
r.wait_type,
wait_resource,
r.wait_time,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time 'Elaps_M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc

or simple

SELECT 
--@@SERVERNAME,concat('kill ',s.session_id),
s.session_id,
s.login_name,
r.status,
r.blocking_session_id 'Blk_by',
--r.wait_type,
--wait_resource,
--r.wait_time,
--r.cpu_time,
--r.logical_reads,
--r.reads,
--r.writes,
--r.total_elapsed_time 'Elaps_M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Query wait event deadlock SQL Server

 check deadlock gmt+0

 
SELECT @@servername as ServerName,xdr.value('@timestamp', 'datetime') AS [Date],
    xdr.query('.') AS [Event_Data]
FROM (SELECT CAST([target_data] AS XML) AS Target_Data
            FROM sys.dm_xe_session_targets AS xt
            INNER JOIN sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
            WHERE xs.name = N'system_health'
              AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]')
AS XEventData(xdr)
ORDER BY [Date] DESC;

running query with root blocker

WITH blocking_tree AS (
    SELECT
        r.session_id,
        r.blocking_session_id,
        CAST(r.session_id AS VARCHAR(MAX)) AS blocking_path
    FROM sys.dm_exec_requests r
    WHERE r.blocking_session_id = 0

    UNION ALL

    SELECT
        r.session_id,
        r.blocking_session_id,
        CAST(bt.blocking_path + ' -> ' + CAST(r.session_id AS VARCHAR) AS VARCHAR(MAX))
    FROM sys.dm_exec_requests r
    JOIN blocking_tree bt 
        ON r.blocking_session_id = bt.session_id
)
SELECT 
    @@SERVERNAME AS server_name,
    s.session_id,
    r.blocking_session_id,
    
    -- ini ROOT BLOCKER
    CASE 
        WHEN r.blocking_session_id = 0 
             AND EXISTS (SELECT 1 FROM sys.dm_exec_requests x WHERE x.blocking_session_id = r.session_id)
        THEN 'ROOT BLOCKER'
        ELSE ''
    END AS blocker_type,

    bt.blocking_path,

    r.status,
    r.wait_type,
    r.wait_time,
    r.wait_resource,

    s.login_name,
    s.host_name,
    s.program_name,

    r.cpu_time,
    r.total_elapsed_time,

    r.open_transaction_count,

    SUBSTRING(st.text,
        (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE r.statement_end_offset END
        - r.statement_start_offset)/2)+1
    ) AS running_query

FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r 
    ON s.session_id = r.session_id
LEFT JOIN blocking_tree bt 
    ON r.session_id = bt.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE s.session_id <> @@SPID
ORDER BY 
    bt.blocking_path,
    r.cpu_time DESC;

check deadlock gmt+7

SELECT 
    @@servername AS ServerName,
    DATEADD(HOUR, 7, xdr.value('@timestamp', 'datetime')) AS [Date_Jakarta],
    xdr.query('.') AS [Event_Data]
FROM (
    SELECT CAST([target_data] AS XML) AS Target_Data
    FROM sys.dm_xe_session_targets AS xt
    INNER JOIN sys.dm_xe_sessions AS xs 
        ON xs.address = xt.event_session_address
    WHERE xs.name = N'system_health'
      AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY [Date_Jakarta] DESC;

proceslist perfect 

WITH sessions AS (
    SELECT 
        s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        s.status AS session_status,

        r.status AS request_status,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        r.cpu_time,
        r.total_elapsed_time,
        r.open_transaction_count,
        r.sql_handle,
        r.statement_start_offset,
        r.statement_end_offset

    FROM sys.dm_exec_sessions s
    LEFT JOIN sys.dm_exec_requests r
        ON s.session_id = r.session_id
    WHERE 
        s.session_id <> @@SPID
        AND s.is_user_process = 1   -- 🔥 FILTER SYSTEM SESSION
),

blocking_tree AS (

    -- ROOT BLOCKER
    SELECT 
        s.session_id,
        s.blocking_session_id,
        CAST(s.session_id AS VARCHAR(MAX)) AS blocking_path
    FROM sessions s
    WHERE 
        (s.blocking_session_id IS NULL OR s.blocking_session_id = 0)
        AND EXISTS (
            SELECT 1 
            FROM sessions x 
            WHERE x.blocking_session_id = s.session_id
        )

    UNION ALL

    -- CHAIN BLOCKING
    SELECT 
        s.session_id,
        s.blocking_session_id,
        CAST(bt.blocking_path + ' -> ' + CAST(s.session_id AS VARCHAR) AS VARCHAR(MAX))
    FROM sessions s
    JOIN blocking_tree bt
        ON s.blocking_session_id = bt.session_id
)

SELECT 
    @@SERVERNAME AS server_name,

    s.session_id,
    s.blocking_session_id,

    CASE 
        WHEN (s.blocking_session_id IS NULL OR s.blocking_session_id = 0)
             AND EXISTS (
                 SELECT 1 FROM sessions x 
                 WHERE x.blocking_session_id = s.session_id
             )
        THEN 'ROOT BLOCKER'
        ELSE ''
    END AS blocker_type,

    bt.blocking_path,

    s.session_status,
    s.request_status,

    s.wait_type,
    s.wait_time,
    s.wait_resource,

    s.login_name,
    s.host_name,
    s.program_name,

    s.cpu_time,
    s.total_elapsed_time,
    s.open_transaction_count,

    SUBSTRING(st.text,
        (s.statement_start_offset/2)+1,
        ((CASE s.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE s.statement_end_offset END
        - s.statement_start_offset)/2)+1
    ) AS running_query

FROM sessions s
LEFT JOIN blocking_tree bt
    ON s.session_id = bt.session_id
OUTER APPLY sys.dm_exec_sql_text(s.sql_handle) st

-- 🔥 hanya session yang terlibat blocking
WHERE 
    s.session_id IN (
        SELECT session_id FROM sessions WHERE blocking_session_id IS NOT NULL
        UNION
        SELECT blocking_session_id FROM sessions WHERE blocking_session_id IS NOT NULL
    )

ORDER BY 
    bt.blocking_path,
    s.cpu_time DESC;

---habis itu 

DBCC INPUTBUFFER(63);
==

 

gabungan

 

WITH sessions AS (
    SELECT 
        s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        s.status AS session_status,

        r.status AS request_status,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time,
        r.wait_resource,
        r.cpu_time,
        r.total_elapsed_time,
        r.open_transaction_count,
        r.sql_handle,
        r.statement_start_offset,
        r.statement_end_offset

    FROM sys.dm_exec_sessions s
    LEFT JOIN sys.dm_exec_requests r
        ON s.session_id = r.session_id
    WHERE 
        s.session_id <> @@SPID
        AND s.is_user_process = 1   -- 🔥 FILTER SYSTEM SESSION
),

blocking_tree AS (

    -- ROOT BLOCKER
    SELECT 
        s.session_id,
        s.blocking_session_id,
        CAST(s.session_id AS VARCHAR(MAX)) AS blocking_path
    FROM sessions s
    WHERE 
        (s.blocking_session_id IS NULL OR s.blocking_session_id = 0)
        AND EXISTS (
            SELECT 1 
            FROM sessions x 
            WHERE x.blocking_session_id = s.session_id
        )

    UNION ALL

    -- CHAIN BLOCKING
    SELECT 
        s.session_id,
        s.blocking_session_id,
        CAST(bt.blocking_path + ' -> ' + CAST(s.session_id AS VARCHAR) AS VARCHAR(MAX))
    FROM sessions s
    JOIN blocking_tree bt
        ON s.blocking_session_id = bt.session_id
)

SELECT 
    @@SERVERNAME AS server_name,

    s.session_id,
    s.blocking_session_id,

    CASE 
        WHEN (s.blocking_session_id IS NULL OR s.blocking_session_id = 0)
             AND EXISTS (
                 SELECT 1 FROM sessions x 
                 WHERE x.blocking_session_id = s.session_id
             )
        THEN 'ROOT BLOCKER'
        ELSE ''
    END AS blocker_type,

    bt.blocking_path,

    s.session_status,
    s.request_status,

    s.wait_type,
    s.wait_time,
    s.wait_resource,

    s.login_name,
    s.host_name,
    s.program_name,

    s.cpu_time,
    s.total_elapsed_time,
    s.open_transaction_count,

    SUBSTRING(st.text,
        (s.statement_start_offset/2)+1,
        ((CASE s.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE s.statement_end_offset END
        - s.statement_start_offset)/2)+1
    ) AS running_query

FROM sessions s
LEFT JOIN blocking_tree bt
    ON s.session_id = bt.session_id
OUTER APPLY sys.dm_exec_sql_text(s.sql_handle) st

-- 🔥 hanya session yang terlibat blocking
WHERE 
    s.session_id IN (
        SELECT session_id FROM sessions WHERE blocking_session_id IS NOT NULL
        UNION
        SELECT blocking_session_id FROM sessions WHERE blocking_session_id IS NOT NULL
    )

ORDER BY 
    bt.blocking_path,
    s.cpu_time DESC;
 

-- automatic dbccinput buffer

SELECT 
    s.session_id,
    s.status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_start_time,
    s.last_request_end_time,
    ib.event_info AS input_buffer_text
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) ib
WHERE s.session_id IN (
    SELECT DISTINCT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0   -- blocker yang sedang memblokir request lain
)
ORDER BY s.session_id;