# 查詢所有正在運行的事務及運行時間
select t.\*,to\_seconds(now())-to\_seconds(t.trx\_started) idle\_time from INFORMATION\_SCHEMA.INNODB\_TRX t
# 查詢事務詳細信息及執行的SQL
select now(),(UNIX\_TIMESTAMP(now()) - UNIX\_TIMESTAMP(a.trx\_started)) diff\_sec,b.id,b.user,b.host,b.db,d.SQL\_TEXT from information\_schema.innodb\_trx a inner join information\_schema.PROCESSLIST b
on a.TRX\_MYSQL\_THREAD\_ID=b.id and b.command = 'Sleep'
inner join performance\_schema.threads c ON b.id = c.PROCESSLIST\_ID
inner join performance\_schema.events\_statements\_current d ON d.THREAD\_ID = c.THREAD\_ID;
# 查詢事務執行過的所有歷史SQL記錄
SELECT
ps.id 'PROCESS ID',
ps.USER,
ps.HOST,
esh.EVENT\_ID,
trx.trx\_started,
esh.event\_name 'EVENT NAME',
esh.sql\_text 'SQL',
ps.time
FROM
PERFORMANCE\_SCHEMA.events\_statements\_history esh
JOIN PERFORMANCE\_SCHEMA.threads th ON esh.thread\_id = th.thread\_id
JOIN information\_schema.PROCESSLIST ps ON ps.id = th.processlist\_id
LEFT JOIN information\_schema.innodb\_trx trx ON trx.trx\_mysql\_thread\_id = ps.id
WHERE
trx.trx\_id IS NOT NULL
AND ps.USER != 'SYSTEM\_USER'
ORDER BY
esh.EVENT\_ID;
# 簡單查詢事務鎖
select \* from sys.innodb\_lock\_waits
# 查詢事務鎖詳細信息
SELECT
tmp.\*,
c.SQL\_Text blocking\_sql\_text,
p.HOST blocking\_host
FROM
(
SELECT
r.trx\_state wating\_trx\_state,
r.trx\_id waiting\_trx\_id,
r.trx\_mysql\_thread\_Id waiting\_thread,
r.trx\_query waiting\_query,
b.trx\_state blocking\_trx\_state,
b.trx\_id blocking\_trx\_id,
b.trx\_mysql\_thread\_id blocking\_thread,
b.trx\_query 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 r ON r.trx\_id = w.requesting\_trx\_id
) tmp,
information\_schema.PROCESSLIST p,
PERFORMANCE\_SCHEMA.events\_statements\_current c,
PERFORMANCE\_SCHEMA.threads t
WHERE
tmp.blocking\_thread = p.id
AND t.thread\_id = c.THREAD\_ID
AND t.PROCESSLIST\_ID = p.id
