查找未提交 / 长时间运行的事务

Oracle

SELECT s.sid, s.serial#, s.username,
       t.start_time, t.used_ublk AS undo_blocks
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY t.start_time;

used_ublk 较高表示大事务正在消耗大量 undo；它可能正在等待 commit/rollback。

PostgreSQL

SELECT pid, usename, state,
       now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

处于 'idle in transaction' 状态的长事务会阻碍 VACUUM 和元组清理；需加以监控。

MySQL

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started;

使用 trx_mysql_thread_id 与 PROCESSLIST 关联，必要时将其 KILL。

SQL Server

DBCC OPENTRAN;
-- Detailed:
SELECT st.session_id, dt.database_transaction_begin_time
FROM sys.dm_tran_database_transactions dt
JOIN sys.dm_tran_session_transactions st ON st.transaction_id = dt.transaction_id
WHERE dt.database_id = DB_ID()
ORDER BY dt.database_transaction_begin_time;

长时间未提交的事务会阻止日志截断并使日志文件膨胀。

MongoDB

db.adminCommand({
  currentOp: 1,
  "transaction": { $exists: true }
})

长时间运行的多文档事务在超过 transactionLifetimeLimitSeconds（默认 60 s）后会被自动中止。

Redis

# Not applicable: MULTI/EXEC transactions are short and atomic; they do not stay open.

Redis 事务不持有锁；长时间运行的单个命令请检查 SLOWLOG。