hive查死锁sql

在 Hive 中查找死锁可以使用以下 SQL 查询：

SELECT * FROM (
  SELECT t1.*, t2.requesting_session_id
  FROM (
    SELECT hl1.*, hl2.blocking_session_id
    FROM (
      SELECT blocking_locks.txn_id AS blocked_txn_id, blocked_locks.txn_id AS blocking_txn_id
      FROM (SELECT * FROM TXN_COMPONENTS WHERE LOCKED = true) blocked_locks
      JOIN (SELECT * FROM TXN_COMPONENTS WHERE LOCKED = true) blocking_locks
      ON (blocked_locks.ext_id = blocking_locks.ext_id)
    ) hl1
    JOIN HIVE_LOCKS t1 ON (hl1.blocked_txn_id = t1.txn_id)
    JOIN HIVE_LOCKS t2 ON (hl1.blocking_txn_id = t2.txn_id)
  ) hl2
  JOIN HS2_LOCKS l1 ON (hl2.lockid = l1.lockid)
  JOIN HS2_LOCKS l2 ON (hl2.blocking_lock_id = l2.lockid)
) deadlock
JOIN HS2_SESSIONS s1 ON (deadlock.sessionid = s1.sessionid)
JOIN HS2_SESSIONS s2 ON (deadlock.requesting_session_id = s2.sessionid);

这个查询会返回所有死锁情况的详细信息，包括被阻塞的事务 ID、阻塞的事务 ID、锁 ID、会话 ID 等。你可以根据需要调整查询条件和显示的列。请注意，查询结果可能会很大，所以在执行之前请确保你有足够的资源和权限。

另外，你也可以使用 Hive 的 Web UI 或命令行工具来查找死锁。使用 `SHOW LOCKS` 命令可以列出当前的锁信息。如果发现存在死锁，你可以使用 `KILL <session_id>` 命令来终止阻塞的会话，解决死锁问题。