SQLServer数据库定位以及解决锁表问题

• 问题背景
• 解决方法
• • 1.查询锁表记录
  • 2.定位锁表脚本
  • 3.原因分析

问题背景

系统中个别功能操作时，出现偶发卡顿的情况。经查询数据库锁表记录，发现与该业务相关的一张数据表频繁被锁表。如何根据锁表记录，来定位具体sql或者存储过程？

解决方法

1.查询锁表记录

首先通过以下脚本来获取所有的锁表进程id以及被锁表名，根据卡顿业务涉及到的数据表，来筛选相关的锁表进程id。

SELECT request_session_id AS 锁表进程, OBJECT_NAME(resource_associated_entity_id) AS 被锁表名
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

2.定位锁表脚本

根据锁表进程id，通过查询以下脚本，可以定位具体sql以及存储过程名称。分析脚本，找出引起锁表的原因，进而解决。
另，根据存储过程名称，在代码中进行全局搜索，可反推出具体的功能模块。

--查询进程id = 71的锁表信息
DECLARE @spid bigint = 71                     --锁表进程id
SELECT
	SPID = er.session_id                      --进程id
	,Status = ses.status
	,CommandType = er.command
	,SQLStatement = st.text                   --导致锁表的sql语句
	,StartTime = er.start_time
	,ObjectName = OBJECT_NAME(st.objectid)    --导致锁表的存储过程名称
	,ElapsedMS = er.total_elapsed_time
	,CPUTime = er.cpu_time
	,IOReads = er.logical_reads + er.reads
	,IOWrites = er.writes
	,LastWaitType = er.last_wait_type
	,Protocol = con.net_transport
	,ConnectionWrites = con.num_writes
	,ConnectionReads = con.num_reads
	,ClientAddress = con.client_net_address
	,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
	OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
	LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
	LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE er.session_id  = @spid                  --锁表进程id
ORDER BY er.blocking_session_id DESC,er.session_id

3.原因分析

针对脚本进行分析，发现引起该问题的原因是，在UNION ALL子查询时，只对部分子查询进行了WHERE过滤，导致查询大量冗余数据引起的。经过对其他子查询增加过滤后，问题得以解决。