Informix技术问询：如何通过SQL获取当前连接锁模式的等待值

锁等待的相关信息确实存储在数据库的系统表或动态视图中，不同主流数据库的查询方式和存储位置如下：

MySQL #

• 查询当前连接的锁等待详情：

SELECT 
  r.trx_id AS 等待事务ID,
  r.trx_mysql_thread_id AS 等待线程ID,
  l.lock_mode AS 等待锁模式,
  b.trx_id AS 阻塞事务ID,
  b.trx_mysql_thread_id AS 阻塞线程ID,
  l.lock_type AS 锁类型,
  l.lock_table AS 锁定表
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS w
JOIN INFORMATION_SCHEMA.INNODB_LOCKS l ON w.requested_lock_id = l.lock_id
JOIN INFORMATION_SCHEMA.INNODB_TRX b ON w.blocking_trx_id = b.trx_id
JOIN INFORMATION_SCHEMA.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

• 存储锁等待数据的系统表：
  • INNODB_LOCK_WAITS：记录锁等待的关联关系
  • INNODB_LOCKS：存储当前所有持有或等待的锁的详细属性
  • INNODB_TRX：记录当前活跃的事务信息，用于关联锁的所属事务

PostgreSQL #

• 查询当前连接的锁等待详情：

SELECT 
  a.pid AS 等待进程ID,
  a.usename AS 等待用户,
  l.mode AS 等待锁模式,
  bl.pid AS 阻塞进程ID,
  bl.usename AS 阻塞用户,
  l.relation::regclass AS 锁定表名
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
LEFT JOIN pg_locks bl ON l.locktype = bl.locktype
  AND l.database = bl.database
  AND l.relation = bl.relation
  AND l.page = bl.page
  AND l.tuple = bl.tuple
  AND l.virtualxid = bl.virtualxid
  AND l.transactionid = bl.transactionid
  AND l.classid = bl.classid
  AND l.objid = bl.objid
  AND l.objsubid = bl.objsubid
  AND bl.pid != a.pid
  AND bl.granted = true
WHERE l.granted = false;

• 存储锁等待数据的系统视图：
  • pg_locks：记录数据库中所有锁的状态（包括已持有和等待中的锁）
  • pg_stat_activity：记录当前所有连接会话的活动状态，用于关联锁所属的进程

SQL Server #

• 查询当前连接的锁等待详情：

SELECT 
  s.session_id AS 等待会话ID,
  s.login_name AS 等待登录账户,
  l.request_mode AS 等待锁模式,
  bs.session_id AS 阻塞会话ID,
  bs.login_name AS 阻塞登录账户,
  OBJECT_NAME(l.resource_associated_entity_id) AS 锁定对象名
FROM sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN sys.dm_tran_locks bl ON l.resource_type = bl.resource_type
  AND l.resource_database_id = bl.resource_database_id
  AND l.resource_associated_entity_id = bl.resource_associated_entity_id
  AND bl.request_status = 'GRANT'
  AND bl.request_session_id != l.request_session_id
LEFT JOIN sys.dm_exec_sessions bs ON bl.request_session_id = bs.session_id
WHERE l.request_status = 'WAIT';

• 存储锁等待数据的动态管理视图：
  • sys.dm_tran_locks：记录当前所有锁的请求和持有状态
  • sys.dm_exec_sessions：记录当前所有会话的基础信息，用于关联锁所属的会话

总结：锁等待的相关值（如锁模式、阻塞方信息、等待状态等）都存储在数据库内置的系统对象中，通过上述SQL可以直接查询到当前连接的锁等待情况。

内容的提问来源于stack exchange，提问作者user3737906