-- card_id: SQL-MY-04
-- title: MySQL 8.x 锁等待与候选阻塞链（只读、脱敏）
-- engine: MySQL
-- versions: 8.0, 8.4
-- risk: R2
-- purpose: 列出采集时刻最多 100 条锁等待关系，供负责人继续核验事务用途、持续时间和回滚影响。
-- input: 无；只读取当前 performance_schema 快照。
-- output: 等待/阻塞线程与事务不可逆指纹、请求锁和持有锁的类型与模式；不含 SQL、库表、索引、账号或地址。
-- permission: performance_schema.data_lock_waits 与 performance_schema.data_locks 的 SELECT 权限。
-- resource_limit: 当前快照、Top 100，建议客户端超时 5 秒。
-- validation: 已在 MySQL 8.0/8.4 隔离合成锁等待验证；5.7、生产及客户环境未验证。
-- failure: performance_schema 未启用时返回明确状态行，权限不足时报错，无等待时返回空结果。
-- prohibited: 不得 KILL、提交、回滚、修改参数、扩大权限或把候选阻塞线程写成应终止对象。

SELECT
    'ready' AS observation_status,
    SHA2(CONCAT('sql-my-04-v1:', w.REQUESTING_THREAD_ID), 256)
        AS waiting_thread_fingerprint,
    SHA2(CONCAT('sql-my-04-v1:', w.BLOCKING_THREAD_ID), 256)
        AS blocking_thread_fingerprint,
    SHA2(CONCAT('sql-my-04-v1:', w.REQUESTING_ENGINE_TRANSACTION_ID), 256)
        AS waiting_transaction_fingerprint,
    SHA2(CONCAT('sql-my-04-v1:', w.BLOCKING_ENGINE_TRANSACTION_ID), 256)
        AS blocking_transaction_fingerprint,
    requested.LOCK_TYPE AS requested_lock_type,
    requested.LOCK_MODE AS requested_lock_mode,
    blocking.LOCK_TYPE AS blocking_lock_type,
    blocking.LOCK_MODE AS blocking_lock_mode
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS requested
  ON requested.ENGINE = w.ENGINE
 AND requested.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS blocking
  ON blocking.ENGINE = w.ENGINE
 AND blocking.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
UNION ALL
SELECT
    'performance_schema_disabled' AS observation_status,
    NULL AS waiting_thread_fingerprint,
    NULL AS blocking_thread_fingerprint,
    NULL AS waiting_transaction_fingerprint,
    NULL AS blocking_transaction_fingerprint,
    NULL AS requested_lock_type,
    NULL AS requested_lock_mode,
    NULL AS blocking_lock_type,
    NULL AS blocking_lock_mode
WHERE @@performance_schema = 0
ORDER BY
    waiting_thread_fingerprint,
    blocking_thread_fingerprint
LIMIT 100;
