sql中临时锁检测的核心是实时查询数据库锁状态，mysql用performance_schema.data_locks等视图，postgresql用pg_locks与pg_stat_activity联查，SQL Server用sys.dm_tran_locks等DMV。
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 可快速识别被阻塞会话：
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 和用户行为。临时锁通常持续时间短，务必在问题发生时立即执行，避免错过窗口。