数据库死锁

数据库死锁

1:查找锁表进程

select request_session_id spid,
 OBJECT_NAME(resource_associated_entity_id) tableName
  from sys.dm_tran_locks
 where resource_type = 'OBJECT'

---

---

2.解锁语句，需要将锁表进程 @spid换成查询出来的锁表进程；

declare @spid  int 
Set @spid  = 58 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid  as varchar)
exec(@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

参考资料：https://blog.csdn.net/yyytttCSDN/article/details/117016474

https://blog.csdn.net/wysmh520/article/details/123047229

https://blog.csdn.net/tmaczt/article/details/82800234

SQLSERVER 数据库死锁的分析，排查（重点）：https://www.cnblogs.com/zmmboy/p/15998932.html