SQL临时锁检测方法_SQL实时获取锁信息示例-SQL-PHP中文网

SQL临时锁检测方法_SQL实时获取锁信息示例

冷漠man

发布： 2025-12-17 17:34:03

原创

243人浏览过

SQL中临时锁检测的核心是实时查询数据库锁状态，MySQL用performance_schema.data_locks等视图，PostgreSQL用pg_locks与pg_stat_activity联查，SQL Server用sys.dm_tran_locks等DMV。

sql临时锁检测方法_sql实时获取锁信息示例

SQL 中临时锁检测的核心是实时查询数据库当前的锁状态，不同数据库系统提供的视图或函数略有差异。以下以主流数据库（MySQL、PostgreSQL、SQL Server）为例，给出可直接执行的实时锁信息获取方法，适用于排查阻塞、死锁或长事务问题。

MySQL：通过 performance_schema 和 information_schema 查锁

MySQL 8.0+ 推荐使用 performance_schema.data_locks 和 performance_schema.data_lock_waits 获取精确的行级锁信息：

查看当前所有数据锁（含表锁、行锁、意向锁）：

SELECT ENGINE_TRANSACTION_ID, INDEX_NAME, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA FROM performance_schema.data_locks;

查看锁等待关系（谁在等谁）：

SELECT * FROM performance_schema.data_lock_waits;

配合 INFORMATION_SCHEMA.INNODB_TRX 查看活跃事务：

SELECT trx_id, trx_mysql_thread_id, trx_state, trx_started, trx_query FROM INFORMATION_SCHEMA.INNODB_TRX ORDER BY trx_started;

注意：需确保 performance_schema 已启用，且相关消费者（如 events_transactions_current、data_locks）已打开。

PostgreSQL：用 pg_locks + pg_stat_activity 定位阻塞

PostgreSQL 中锁信息集中在 pg_locks，结合 pg_stat_activity 可快速识别被阻塞会话：

图酷AI

下载即用！可以免费使用的AI图像处理工具，致力于为用户提供最先进的AI图像处理技术，让图像编辑变得简单高效。

106

查看详情

查所有锁及对应进程和对象：

SELECT l.locktype, l.database, l.relation::regclass, l.mode, l.GRANTED, a.pid, a.usename, a.application_name, a.state, a.query FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid ORDER BY l.GRANTED, l.mode;

查正在被阻塞的活跃查询（GRANTED = false）：

SELECT blocked_activity.pid AS blocked_pid, blocked_activity.query AS blocked_query, blocking_activity.pid AS blocking_pid, blocking_activity.query AS blocking_query FROM pg_stat_activity blocked_activity JOIN pg_locks blocked_locks ON blocked_activity.pid = blocked_locks.pid JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_activity.pid JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;

SQL Server：使用 sys.dm_tran_locks 和 sys.dm_exec_requests

SQL Server 提供动态管理视图（DMV）实时反映锁与会话关系：

查看当前所有锁（含资源类型、请求模式、是否授予）：

SELECT request_session_id, resource_type, resource_database_id, resource_description, resource_associated_entity_id, request_mode, request_status, request_owner_type FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID();

查阻塞链（谁在等、谁在占）：

SELECT r.session_id, r.blocking_session_id, r.status, r.command, r.wait_type, r.wait_time, r.last_wait_type, t.text AS sql_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.blocking_session_id 0 OR r.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id 0);

可进一步关联 sys.dm_exec_sessions 获取登录名、主机等上下文信息。

基本上就这些。实际排查时建议先运行“查锁”语句定位资源争用点，再结合“查事务/会话”确认源头 SQL 和用户行为。临时锁通常持续时间短，务必在问题发生时立即执行，避免错过窗口。

以上就是SQL临时锁检测方法_SQL实时获取锁信息示例的详细内容，更多请关注php中文网其它相关文章！