https://teguhth.blogspot.com/2023/09/how-to-sample-simulation-blocking.html
1. SIMULASI LOCKING
Session 1 - root - session_id 3
select connection_id() as session_id, user() as user;
START TRANSACTION;
UPDATE pembelian
SET JUMLAH_PEMBELIAN = 10
WHERE KODE_PEMBELIAN = 'BEL-E001';
select connection_id() as session_id, user() as user; START TRANSACTION; UPDATE pembelian SET JUMLAH_PEMBELIAN = 10 WHERE KODE_PEMBELIAN = 'BEL-E001';
Session 2 - admin - session_id 4
select connection_id() as session_id, user() as user;
SELECT *
FROM pembelian
WHERE KODE_PEMBELIAN = 'BEL-E001'
FOR UPDATE;
select connection_id() as session_id, user() as user; SELECT * FROM pembelian WHERE KODE_PEMBELIAN = 'BEL-E001' FOR UPDATE;
Session 3 - aisyah - session_id 6
select connection_id() as session_id, user() as user;
UPDATE pembelian
SET JUMLAH_PEMBELIAN = 20
WHERE KODE_PEMBELIAN = 'BEL-E001';
select connection_id() as session_id, user() as user; UPDATE pembelian SET JUMLAH_PEMBELIAN = 20 WHERE KODE_PEMBELIAN = 'BEL-E001';
2. CAPTURE LOCKING PROCESSLIST
## procelist 1
SELECT 'procelist 1',@@hostname,id,User,host,db,command,time,time_ms,state,info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command <> 'Sleep';
SELECT 'procelist
1',@@hostname,id,User,host,db,command,time,time_ms,state,info FROM
INFORMATION_SCHEMA.PROCESSLIST ;
## procelist 2
SELECT 'procelist 2',
r.trx_id AS waiting_trx_id,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx_id,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx AS r
ON r.trx_id = w.requesting_trx_id;
## procelist 3
SELECT 'procelist 3',
@@hostname AS Hostname,
pl.id AS thread_id,
pl.USER,
pl.HOST,
pl.DB,
pl.COMMAND,
pl.TIME,
pl.TIME_MS,
pl.STATE,
pl.INFO,
-- Blocking Info
rw.trx_id AS waiting_trx_id,
rw.trx_query AS waiting_query,
bw.trx_id AS blocking_trx_id,
bw.trx_query AS blocking_query,
bw.trx_mysql_thread_id AS blocking_thread
FROM
INFORMATION_SCHEMA.PROCESSLIST pl
LEFT JOIN information_schema.innodb_trx rw
ON pl.id = rw.trx_mysql_thread_id
LEFT JOIN information_schema.innodb_lock_waits lw
ON rw.trx_id = lw.requesting_trx_id
LEFT JOIN information_schema.innodb_trx bw
ON lw.blocking_trx_id = bw.trx_id
WHERE command <> 'Sleep'
ORDER BY
pl.TIME_MS DESC;
## procelist 4
SELECT 'procelist 4',
@@hostname AS hostname,
pl.id AS thread_id,
pl.user,
pl.host,
pl.db,
pl.command,
pl.time AS thread_time,
pl.time_ms,
pl.state,
pl.info AS current_query,
trx.trx_id,
trx.trx_state,
-- status logic
CASE
WHEN lw.requesting_trx_id IS NOT NULL THEN 'WAITING'
WHEN lw.blocking_trx_id IS NOT NULL THEN 'BLOCKED'
WHEN trx.trx_id IS NOT NULL THEN 'IN TRANSACTION'
ELSE 'IDLE'
END AS trx_status,
btrx.trx_mysql_thread_id AS blocking_thread,
btrx.trx_query AS blocking_query,
trx.trx_started,
TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS trx_duration,
trx.trx_rows_locked,
trx.trx_rows_modified,
lw.requesting_trx_id AS waiting_trx_id,
lw.blocking_trx_id
FROM information_schema.processlist pl
LEFT JOIN information_schema.innodb_trx trx
ON pl.id = trx.trx_mysql_thread_id
LEFT JOIN information_schema.innodb_lock_waits lw
ON trx.trx_id = lw.requesting_trx_id
LEFT JOIN information_schema.innodb_trx btrx
ON lw.blocking_trx_id = btrx.trx_id
WHERE command <> 'Sleep'
ORDER BY trx_duration DESC, pl.time DESC;
## procelist 5
SELECT 'procelist 5',
@@hostname AS hostname,
pl.id AS thread_id,
pl.user,
pl.host,
pl.db,
pl.command,
pl.time AS thread_time,
pl.time_ms,
pl.state,
pl.info AS current_query,
trx.trx_id,
trx.trx_state,
trx.trx_started,
TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS trx_duration,
trx.trx_rows_locked,
trx.trx_rows_modified,
l.lock_table,
l.lock_index,
l.lock_type,
l.lock_mode,
lw.requesting_trx_id AS waiting_trx_id,
lw.blocking_trx_id,
btrx.trx_mysql_thread_id AS blocking_thread,
btrx.trx_query AS blocking_query,
TIMESTAMPDIFF(SECOND, btrx.trx_started, NOW()) AS blocking_duration,
-- 🔥 STATUS SUPER JELAS
CASE
WHEN lw.requesting_trx_id IS NOT NULL THEN 'WAITING'
WHEN lw.blocking_trx_id IS NOT NULL THEN 'BLOCKED'
WHEN trx.trx_id IS NOT NULL AND pl.command = 'Sleep' THEN 'IDLE IN TRANSACTION 🔥'
WHEN trx.trx_id IS NOT NULL THEN 'ACTIVE TRANSACTION'
ELSE 'IDLE'
END AS trx_status,
-- 🔥 ROOT BLOCKER DETECTION
CASE
WHEN trx.trx_id IN (
SELECT blocking_trx_id
FROM information_schema.innodb_lock_waits
WHERE blocking_trx_id NOT IN (
SELECT requesting_trx_id FROM information_schema.innodb_lock_waits
)
) THEN '🔥 ROOT BLOCKER'
ELSE ''
END AS root_blocker,
-- 🔥 AUTO RECOMMEND ACTION
CASE
WHEN trx.trx_id IN (
SELECT blocking_trx_id
FROM information_schema.innodb_lock_waits
WHERE blocking_trx_id NOT IN (
SELECT requesting_trx_id FROM information_schema.innodb_lock_waits
)
) THEN CONCAT('KILL ', pl.id)
ELSE ''
END AS recommended_kill
FROM information_schema.processlist pl
LEFT JOIN information_schema.innodb_trx trx
ON pl.id = trx.trx_mysql_thread_id
LEFT JOIN information_schema.innodb_lock_waits lw
ON trx.trx_id = lw.requesting_trx_id
LEFT JOIN information_schema.innodb_trx btrx
ON lw.blocking_trx_id = btrx.trx_id
LEFT JOIN information_schema.innodb_locks l
ON trx.trx_id = l.lock_trx_id
WHERE
trx.trx_id IS NOT NULL
OR lw.requesting_trx_id IS NOT NULL
ORDER BY
root_blocker DESC,
trx_duration DESC,
pl.time DESC;
## procelist 6
SELECT 'procelist 6',
pl.id AS session_id,
-- STATUS mirip SQL Server
CASE
WHEN lw.requesting_trx_id IS NOT NULL THEN 'SUSPENDED'
WHEN trx.trx_id IS NOT NULL AND pl.command = 'Sleep' THEN 'SLEEPING (IN TX)'
WHEN trx.trx_id IS NOT NULL THEN 'RUNNING'
ELSE 'SLEEPING'
END AS status,
-- WAIT TYPE (simulasi ala SQL Server)
CASE
WHEN lw.requesting_trx_id IS NOT NULL AND l.lock_mode = 'X' THEN 'LCK_M_X'
WHEN lw.requesting_trx_id IS NOT NULL AND l.lock_mode = 'S' THEN 'LCK_M_S'
WHEN lw.requesting_trx_id IS NOT NULL THEN 'LOCK_WAIT'
ELSE NULL
END AS wait_type,
-- BLOCKING SESSION
btrx.trx_mysql_thread_id AS blocking_session,
-- INFO TAMBAHAN
trx.trx_state,
TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS trx_duration,
pl.state,
pl.info AS query
FROM information_schema.processlist pl
LEFT JOIN information_schema.innodb_trx trx
ON pl.id = trx.trx_mysql_thread_id
LEFT JOIN information_schema.innodb_lock_waits lw
ON trx.trx_id = lw.requesting_trx_id
LEFT JOIN information_schema.innodb_trx btrx
ON lw.blocking_trx_id = btrx.trx_id
LEFT JOIN information_schema.innodb_locks l
ON trx.trx_id = l.lock_trx_id
WHERE
trx.trx_id IS NOT NULL
OR lw.requesting_trx_id IS NOT NULL
ORDER BY
trx_duration DESC,
pl.time DESC;
## procelist 7
SELECT 'procelist 7',
@@hostname AS hostname,
pl.id AS thread_id,
pl.user,
pl.host,
pl.db,
pl.command,
pl.time AS thread_time,
pl.time_ms,
pl.state,
pl.info AS current_query,
trx.trx_id,
trx.trx_state,
trx.trx_started,
TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS trx_duration,
trx.trx_rows_locked,
trx.trx_rows_modified,
l.lock_table,
l.lock_index,
l.lock_type,
l.lock_mode,
lw.requesting_trx_id AS waiting_trx_id,
lw.blocking_trx_id,
btrx.trx_mysql_thread_id AS blocking_thread,
btrx.trx_query AS blocking_query,
TIMESTAMPDIFF(SECOND, btrx.trx_started, NOW()) AS blocking_duration,
-- 🔥 WAIT TYPE ala SQL Server
CASE
WHEN lw.requesting_trx_id IS NOT NULL
AND COALESCE(l.lock_mode, '') LIKE '%X%' THEN 'LCK_M_X'
WHEN lw.requesting_trx_id IS NOT NULL
AND COALESCE(l.lock_mode, '') LIKE '%S%' THEN 'LCK_M_S'
WHEN lw.requesting_trx_id IS NOT NULL THEN 'LOCK_WAIT'
ELSE NULL
END AS wait_type,
-- 🔥 STATUS
CASE
WHEN lw.requesting_trx_id IS NOT NULL THEN 'WAITING'
WHEN lw.blocking_trx_id IS NOT NULL THEN 'BLOCKED'
WHEN trx.trx_id IS NOT NULL AND pl.command = 'Sleep' THEN 'IDLE IN TRANSACTION 🔥'
WHEN trx.trx_id IS NOT NULL THEN 'ACTIVE TRANSACTION'
ELSE 'IDLE'
END AS trx_status,
-- 🔥 ROOT BLOCKER
CASE
WHEN trx.trx_id IN (
SELECT blocking_trx_id
FROM information_schema.innodb_lock_waits
WHERE blocking_trx_id NOT IN (
SELECT requesting_trx_id FROM information_schema.innodb_lock_waits
)
) THEN '🔥 ROOT BLOCKER'
ELSE ''
END AS root_blocker,
-- 🔥 RECOMMENDED ACTION
CASE
WHEN trx.trx_id IN (
SELECT blocking_trx_id
FROM information_schema.innodb_lock_waits
WHERE blocking_trx_id NOT IN (
SELECT requesting_trx_id FROM information_schema.innodb_lock_waits
)
) THEN CONCAT('KILL ', pl.id)
ELSE ''
END AS recommended_kill
FROM information_schema.processlist pl
LEFT JOIN information_schema.innodb_trx trx
ON pl.id = trx.trx_mysql_thread_id
LEFT JOIN information_schema.innodb_lock_waits lw
ON trx.trx_id = lw.requesting_trx_id
LEFT JOIN information_schema.innodb_trx btrx
ON lw.blocking_trx_id = btrx.trx_id
LEFT JOIN information_schema.innodb_locks l
ON trx.trx_id = l.lock_trx_id
WHERE
trx.trx_id IS NOT NULL
OR lw.requesting_trx_id IS NOT NULL
ORDER BY
root_blocker DESC,
trx_duration DESC,
pl.time DESC;
## procelist 8
SELECT 'procelist 8',
pl.id AS thread_id,
pl.user,
pl.host,
pl.db,
pl.command,
pl.time AS thread_time,
pl.state,
pl.info AS current_query,
trx.trx_id,
trx.trx_started,
TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS trx_duration,
lw.requesting_trx_id,
lw.blocking_trx_id,
btrx.trx_mysql_thread_id AS blocking_thread,
btrx.trx_query AS blocking_query,
-- ?? DETEKSI ROOT BLOCKER (UTAMA)
CASE
WHEN pl.id IN (
SELECT btrx.trx_mysql_thread_id
FROM information_schema.innodb_lock_waits lw
JOIN information_schema.innodb_trx btrx
ON lw.blocking_trx_id = btrx.trx_id
)
AND pl.id NOT IN (
SELECT trx.trx_mysql_thread_id
FROM information_schema.innodb_trx trx
JOIN information_schema.innodb_lock_waits lw
ON trx.trx_id = lw.requesting_trx_id
)
THEN '?? ROOT BLOCKER'
-- ?? fallback: idle transaction (sering banget jadi penyebab)
WHEN trx.trx_id IS NOT NULL
AND pl.command = 'Sleep'
AND trx.trx_started < NOW() - INTERVAL 20 SECOND
THEN '?? ROOT (IDLE TRX)'
ELSE ''
END AS root_blocker,
-- ?? PENYEBAB (biar jelas)
CASE
WHEN pl.command = 'Sleep' AND trx.trx_id IS NOT NULL
THEN 'TRANSACTION TIDAK DI-COMMIT (IDLE)'
WHEN pl.state LIKE '%metadata lock%'
THEN 'METADATA LOCK (DDL)'
WHEN pl.info LIKE 'ALTER%'
THEN 'ALTER TABLE'
WHEN pl.info LIKE 'UPDATE%' OR pl.info LIKE 'DELETE%'
THEN 'ROW LOCK (UPDATE/DELETE)'
ELSE 'LAINNYA'
END AS penyebab,
-- ?? ACTION
CASE
WHEN pl.id IN (
SELECT btrx.trx_mysql_thread_id
FROM information_schema.innodb_lock_waits lw
JOIN information_schema.innodb_trx btrx
ON lw.blocking_trx_id = btrx.trx_id
)
OR (trx.trx_id IS NOT NULL AND pl.command = 'Sleep')
THEN CONCAT('KILL ', pl.id)
ELSE ''
END AS kill_cmd
FROM information_schema.processlist pl
LEFT JOIN information_schema.innodb_trx trx
ON pl.id = trx.trx_mysql_thread_id
LEFT JOIN information_schema.innodb_lock_waits lw
ON trx.trx_id = lw.requesting_trx_id
LEFT JOIN information_schema.innodb_trx btrx
ON lw.blocking_trx_id = btrx.trx_id
WHERE
trx.trx_id IS NOT NULL
OR pl.command = 'Sleep'
OR pl.state LIKE '%lock%'
ORDER BY
root_blocker DESC,
trx_duration DESC,
pl.time DESC;
No comments:
Post a Comment