SQL查询数据库死锁

     SELECT   spid,  kpid,  blocked,  waittime AS 'waitms', 
  lastwaittype,   DB_NAME(dbid)AS DB,  
  waitresource,   open_tran,
 hostname,[program_name],
 hostprocess,loginame,
 [status]
 FROM sys.sysprocesses WITH(NOLOCK) 
 WHERE    kpid>0  AND  [status]<>'sleeping'  AND spid>50 and blocked>0






sys.sysprocesses  能显示会话进程有多少， 等待时间， open_tran有多少事务， 阻塞会话是多少. 整体内容更为详细。
  关键字段说明:

       spid 会话ID（进程ID），SQL内部对一个连接的编号，一般来讲小于50

　　kipid 线程ID
　　blocked: 阻塞的进程ID， 值大于0表示阻塞, 值为本身进程ID表示io操作
　　waittime:当前等待时间（以毫秒为单位）。
　　open_tran: 进程的打开事务数
　　hostname:建立连接的客户端工作站的名称
　　program_name 应用程序的名称。 
　　hostprocess 工作站进程 ID 号。
　　loginame 登录名。
　　[status]
　　　　running = 会话正在运行一个或多个批
　　　　background = 会话正在运行一个后台任务，例如死锁检测
　　　　rollback = 会话具有正在处理的事务回滚
　　　　pending = 会话正在等待工作线程变为可用
　　　　runnable = 会话中的任务在等待,由scheduler来运行的可执行队列中。(重要)
　　　　spinloop = 会话中的任务正在等待调节锁变为可用。
　　　　suspended = 会话正在等待事件（如 I/O）完成。(重要)
　　　　sleeping = 连接空闲

              wait resource 格式为 fileid:pagenumber:rid 如(5:1:8235440)

              kpid=0, waittime=0 空闲连接

              kpid>0, waittime=0 运行状态
              kpid>0, waittime>0 需要等待某个资源，才能继续执行,一般会是suspended（等待io）
              kpid=0, waittime=0 但它还是阻塞的源头，查看open_tran>0 事务没有及时提交。

              如果blocked>0，但waittime时间很短，说明阻塞时间不长，不严重
              如果status 上有好几个runnable状态任务，需要认真对待。 cpu负荷过重没有及时处理用户的并发请求

exec sp_lock;---查询所有进程锁

 exec sp_lock 55 -- 查询指定进程55的锁




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



--输出引起死锁的操作
DBCC INPUTBUFFER (@spid)

 

 declare @spid int
Set @spid = 53 --要Kill掉的锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)

 新开两个查询窗口 

BEGIN TRANSACTION--开始事务 创造锁表的操作

update [dbo].[Archer]     set [NAME] ='00000'  where id='1'

WAITFOR DELAY '02:00'; 

select * from [Archer] where id='1'

SELECT
    SPID                = er.session_id 
    ,Status             = ses.status 
    ,[Login]            = ses.login_name 
    ,Host               = ses.host_name 
    ,BlkBy              = er.blocking_session_id 
    ,DBName             = DB_Name(er.database_id) 
    ,CommandType        = er.command 
    ,SQLStatement       = st.text 
    ,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 
    ,StartTime          = er.start_time 
    ,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 > 50 
  ---  AND @SessionID IS NULL OR er.session_id = @SessionID 
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id 

-----参考https://www.cnblogs.com/ls11736/p/13231574.html

 

转自  https://blog.csdn.net/weixin_34001430/article/details/86006022