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

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
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过滤，导致查询大量冗余数据引起的。经过对其他子查询增加过滤后，问题得以解决。
                    SQLServer数据库定位以及解决锁表问题问题背景解决方法查询锁表记录定位锁表脚本原因分析问题背景系统中个别功能操作时，出现偶发卡顿的情况。经查询数据库锁表记录，发现与该业务相关的一张数据表频繁被锁表。如何根据锁表记录，来定位具体sql或者存储过程？解决方法查询锁表记录首先通过以下脚本来获取所有的锁表进程id以及被锁表名，根据卡顿业务涉及到的数据表，来筛选相关的锁表进程id。SELECT request_session_id AS 锁表进程, OBJECT_NAME(resource_asso
				查询被锁住的表
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'
查询结果，前面是锁的进程id，后面是被锁的表名
解除表锁定：将spid的值改为要杀死的进程...
				某些情况下，sqlserver的表会被锁住，比如某个会话窗口有数据一直没提交，窗口又没关闭，这时表就会被锁住
其他任何连接查询表数据时都不会返回
这时需要手工杀掉产生死锁的会话ID，才能恢复正常
查看被锁表：
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName
from   ...
https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-showcontig-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-s
查看被锁表：
select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
from   sys.dm_tran_locks where resource_type='OBJECT'
spid   锁表进程 
				在SqlServer中，项目中需要写到存储过程，但是有些刚接触的小伙伴，可能会出现写了begin，没有end的情况，这种SQL语句可以保存到存储过程中，但是在执行存储过程的时候会出现锁的情况，这个时候就需要我们去进行解锁；
解锁语句如下：
先运行下方的select语句，查询出 锁的进程；
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran..