::: 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

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;

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;