# 实时查询数据库活动会话状态
# MySQL
-- 查询所有会话
show full processlist;
-- 查询info不为空的
select * from information_schema.processlist where info is not null order by time desc;
-- 查询锁阻塞会话
-- mysql 8.0
SELECT
b.trx_mysql_thread_id AS `阻塞会话id`,
b.trx_query AS `阻塞sql`,
r.trx_mysql_thread_id AS `被阻塞会话id`,
r.trx_query AS `被阻塞sql`,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS `等待秒`
FROM
PERFORMANCE_SCHEMA.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
ORDER BY
`等待秒` DESC;
-- mysql 5.7
SELECT
b.trx_mysql_thread_id AS `阻塞会话id`,
b.trx_query AS `阻塞sql`,
r.trx_mysql_thread_id AS `被阻塞会话id`,
r.trx_query AS `被阻塞sql`,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS `等待秒`
FROM
information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
ORDER BY
`等待秒` DESC;
# SQl Server
WITH t_proc AS (SELECT spid, count(*) proc_count, max(loginame) loginame FROM sys.sysprocesses GROUP BY spid),
exec_sql_tab AS (SELECT * FROM sys.dm_exec_requests),
h_lock AS (
SELECT
a.blocking_session_id AS blocked
FROM
exec_sql_tab a
WHERE
NOT EXISTS (SELECT 1 FROM exec_sql_tab WHERE blocking_session_id > 0 AND session_id = a.blocking_session_id)
AND blocking_session_id > 0
GROUP BY
a.blocking_session_id
) SELECT
(CASE WHEN h_lock.blocked IS NOT NULL THEN 1 ELSE 0 END) AS 'headlockflag',
'"' + (
CASE
WHEN substring(dest.TEXT, 1, 16) = 'FETCH API_CURSOR' THEN
(SELECT t.TEXT FROM sys.dm_exec_cursors (der.session_id) c CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t)
ELSE
dest.TEXT
END
) + '"' AS 'SQL语句',
(CASE WHEN substring(dest.TEXT, 1, 16) = 'FETCH API_CURSOR' THEN 1 ELSE 0 END) AS 'fetch_cursor状态',
(
CASE
WHEN charindex ('<ParameterList>', dest_plan.query_plan) > 0 THEN
'"' + substring(
dest_plan.query_plan,
charindex ('<ParameterList>', dest_plan.query_plan),
charindex ('</ParameterList>', dest_plan.query_plan) - charindex ('<ParameterList>', dest_plan.query_plan) + 16
) + '"'
ELSE
''
END
) AS 'SQL变量绑定参数',
der.cpu_time AS '运行时间',
der.session_id AS '会话ID',
t_proc.proc_count AS '运行线程数',
der.command AS '命令',
der.blocking_session_id AS '阻塞其会话的会话ID',
DB_NAME (der.database_id) AS '数据库名称',
der.request_id AS '请求ID',
der.start_time AS '开始时间',
der.STATUS AS '状态',
der.READS AS '物理读次数',
der.writes AS '写次数',
der.logical_reads AS '逻辑读次数',
der.row_count AS '返回结果行数',
der.wait_type AS '等待资源类型',
der.wait_time AS '等待时间',
der.total_elapsed_time,
der.wait_resource AS '等待的资源',
conn.client_net_address AS '客户端地址',
tmpdb.user_objects_alloc_page_count AS 'tempdb使用页',
t_proc.loginame AS '登录用户名',
der.query_hash AS 'SQL的HASH',
der.transaction_id,
der.open_transaction_count,
der.open_resultset_count,
CONVERT(CHAR(24), getdate (), 121) AS '采集时刻'
FROM
exec_sql_tab der
LEFT JOIN sys.dm_exec_connections conn ON (conn.session_id = der.session_id)
LEFT JOIN h_lock ON (h_lock.blocked = der.session_id)
LEFT JOIN t_proc ON (t_proc.spid = der.session_id)
LEFT JOIN sys.dm_db_session_space_usage tmpdb ON (tmpdb.session_id = der.session_id) CROSS APPLY sys.dm_exec_sql_text (der.sql_handle) AS dest CROSS APPLY sys.dm_exec_text_query_plan (der.plan_handle, DEFAULT, DEFAULT) AS dest_plan
WHERE
der.session_id <> @@spid
ORDER BY
'headlockflag' DESC,
blocking_session_id,
cpu_time DESC
# Oracle
SELECT
SUBSTR(sys_connect_by_path (s.INST_ID || '#' || s.SID, '-->'), 4) AS tree_sid,
S.INST_ID AS inst_id, -- 对于RAC的节点
S.SID AS sid, -- 会话ID
S.SERIAL # AS serial,
S.STATUS AS status, -- 会话状态
S.MACHINE AS machine, -- 客户端机器名
S.PROGRAM AS program, -- 客户端运行程序
S.SQL_ID AS sql_id, -- 执行sql的id
B.SQL_TEXT AS sql_text, -- 执行sql的文本
-- (CASE WHEN LENGTHB(B.SQL_TEXT)<3990 THEN TO_CLOB('') ELSE B.SQL_FULLTEXT END) SQL_FULLTEXT, -- sql长度超过3990字节才取fulltext，否则返回空
S.WAIT_CLASS AS wait_class, -- 等待类型
S.EVENT AS event, -- 等待事件
S.SECONDS_IN_WAIT AS seconds_in_wait, --等待时间（秒)
TO_CHAR(S.SQL_EXEC_START, 'YYYY-MM-DD HH24:MI:SS') sql_exec_start, -- SQL执行开始时间
TO_CHAR(S.LOGON_TIME, 'YYYY-MM-DD HH24:MI:SS') login_time, -- 会话登录时间
(SELECT LISTAGG（B.object_name, ',' || CHR(13)) WITHIN GROUP (ORDER BY B.OWNER)
FROM
GV$LOCKED_OBJECT A,
DBA_OBJECTS B
WHERE
A.inst_id = S.inst_id
AND A.session_id = S.SID
AND B.owner = S.username
AND B.object_id = A.object_id
) lock_object_name -- 锁定对象名称
FROM
GV$ SESSION S
LEFT JOIN GV$ SQL B ON (B.INST_ID = S.INST_ID AND B.SQL_ID = S.SQL_ID AND B.CHILD_NUMBER = S.SQL_CHILD_NUMBER)
WHERE
S.TYPE = 'USER'
--and S.sid<>(select sys_context('userenv','sid') from dual) -- 不含本次查询连接会话
AND (
S.BLOCKING_SESSION IS NOT NULL
OR (S.INST_ID || '#' || S.SID) IN (SELECT DISTINCT BLOCKING_INSTANCE || '#' || BLOCKING_SESSION FROM GV$ SESSION)
OR S.status = 'ACTIVE'
OR (S.status = 'INACTIVE' AND S.TADDR IS NOT NULL)
) START WITH (s.BLOCKING_INSTANCE || '#' || s.BLOCKING_SESSION) = '#' CONNECT BY PRIOR (S.INST_ID || '#' || S.SID) = (s.BLOCKING_INSTANCE || '#' || s.BLOCKING_SESSION)
ORDER BY
TREE_SID
# Postgres
SELECT
a.pid,
a.usename,
a.datname,
a.client_addr,
a.STATE,
a.query,
now() - a.query_start AS query_duration,
blocked_count.COUNT AS blocked_count
FROM
pg_stat_activity a
JOIN (
SELECT
UNNEST(pg_blocking_pids (pid)) AS blocking_pid,
COUNT(*) AS COUNT
FROM
pg_stat_activity
WHERE
CARDINALITY (pg_blocking_pids (pid)) > 0
GROUP BY
blocking_pid
) blocked_count ON a.pid = blocked_count.blocking_pid
ORDER BY
blocked_count.COUNT DESC;
# DM
-- 查询所有活动会话及其完整SQL
SELECT SF_GET_SESSION_SQL(SESS_ID) AS FULL_SQL, STATE, CLNT_IP
FROM V$SESSIONS WHERE STATE = 'ACTIVE';
-- 查询存在未提交事务的会话完整SQL（常用于排查锁表问题）
SELECT SF_GET_SESSION_SQL(SESS_ID) AS FULLSQL, *
FROM V$SESSIONS
WHERE TRX_ID IN (SELECT ID FROM V$TRX WHERE INS_CNT + DEL_CNT + UPD_CNT + UPD_INS_CNT > 0);
-- 查询会话锁树列表
WITH SESS_TAB AS (
SELECT
S.SESS_ID, -- 会话ID
S.SESS_SEQ AS SERIAL, -- 会话序列号，用来唯一标识会话
S.STATE, -- 会话状态
nvl (
REPLACE(substr(S.CLNT_IP, 1, instr (S.CLNT_IP, ':',- 1) - 1), '::ffff:', ''),
''
) AS CLNT_IP, -- 客户端机器IP
S.CURR_SCH, -- 当前模式
S.SQL_ID, -- 执行sql的id
S.SQL_TEXT, -- 执行sql的文本,取sql的头1000个字符
(
CASE
WHEN (SELECT COUNT(*) FROM V$SQLTEXT WHERE SQL_ID = S.SQL_ID) > 1 THEN
(SELECT LISTAGG2 (SQL_TEXT, '') WITHIN GROUP (ORDER BY SQL_NTH) FROM V$SQLTEXT WHERE SQL_ID = S.SQL_ID)
ELSE
TO_CLOB ('')
END
) AS SQL_FULLTEXT,
S.TRX_ID, -- 执行事务ID
nvl (X.WAIT_FOR_ID,- 1) WAIT_FOR_ID, -- 等待事务ID
nvl (X.WAIT_SESS_ID,- 1) WAIT_SESS_ID, -- 等待事务所属会话ID
nvl (X.WAIT_TIME, 0) / 1000 WAIT_TIME, --当前等待时间（豪秒)
nvl ((SELECT R.NAME FROM SYSOBJECTS R WHERE R.ID = X.WAIT_TABLE_ID), '') WAIT_OBJ_NAME, -- 等待对象名
TO_CHAR(S.CREATE_TIME, 'YYYY-MM-DD HH24:MI:SS') LOGON_TIME, -- 会话登录时间
nvl (
(
SELECT LISTAGG
(NAME, ',') WITHIN GROUP (ORDER BY NAME)
FROM
SYSOBJECTS
WHERE
ID IN (SELECT DISTINCT W.TABLE_ID FROM V$ LOCK W WHERE W.TRX_ID = S.TRX_ID AND W.IGN_FLAG = 0)
AND SCHID > 0
),
''
) LOCK_OBJECT_NAME -- 操作锁定对象集合
FROM
V$SESSIONS S
LEFT JOIN (
SELECT
A.*,
(SELECT SESS_ID FROM V$TRX WHERE ID = A.WAIT_FOR_ID) AS WAIT_SESS_ID,
C.WAITING,
B.TABLE_ID AS WAIT_TABLE_ID
FROM
V$TRXWAIT A,
V$ LOCK B,
V$TRX C
WHERE
B.ADDR = C.WAITING
AND A.WAIT_FOR_ID = B.TID
AND A.ID = C.ID
) X ON (X.ID = S.TRX_ID)
WHERE
S.SESS_ID <> (SELECT sys_context ('userenv', 'sid') FROM dual)
AND TRX_ID > 0
) SELECT
1 AS TEST_ID,
SUBSTR(sys_connect_by_path (T.SESS_ID, '->'), 4) AS TREE_SESS_ID,
T.*
FROM
SESS_TAB T START WITH T.WAIT_FOR_ID = - 1 CONNECT BY PRIOR T.TRX_ID = T.WAIT_FOR_ID
ORDER BY
TREE_SESS_ID;
# KingBase
-- 查询锁阻塞
SELECT
pid,
sys_blocking_pids (pid),
wait_event_type,
wait_event,
query
FROM
sys_stat_activity
WHERE
STATE = 'active';
编撰人：wangyxyf
