SQL Server 中锁表查询操作 

 1 --查询是否存在阻塞信息
 2 select bl.spid blocking_session,bl.blocked blocked_session,st.text blockedtext from (SELECT   spid ,blocked
 3    FROM (SELECT * FROM sys.sysprocesses WHERE   blocked>0 ) a 
 4    WHERE not exists(SELECT * 
 5                     FROM (SELECT * 
 6                           FROM sys.sysprocesses 
 7                           WHERE   blocked>0 ) b 
 8                     WHERE a.blocked=spid)
 9    union SELECT spid,blocked 
10          FROM sys.sysprocesses 
11          WHERE   blocked>0) bl,(SELECT t.text ,c.session_id 
12          FROM sys.dm_exec_connections c  
13          CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t) st
14  where bl.blocked = st.session_id
15  
16  --查询阻塞信息中主题
17  select bl.spid blocking_session,bl.blocked blocked_session,st.text blockedtext,sb.text blockingtext 
18 from 
19 (SELECT   spid ,blocked
20    FROM (SELECT * FROM sys.sysprocesses WHERE   blocked>0 ) a 
21    WHERE not exists(SELECT * 
22                     FROM (SELECT * 
23                           FROM sys.sysprocesses 
24                           WHERE   blocked>0 ) b 
25                     WHERE a.blocked=spid)
26    union 
27  SELECT spid,blocked 
28          FROM sys.sysprocesses 
29          WHERE   blocked>0) bl,
30 (SELECT t.text ,c.session_id 
31          FROM sys.dm_exec_connections c  
32          CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t) st,
33 (SELECT t.text ,c.session_id 
34          FROM sys.dm_exec_connections c  
35          CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t) sb
36  where bl.blocked = st.session_id and bl.spid = sb.session_id
37  
38  --查询表进程死锁情况
39  select *
40    from master..SysProcesses
41   where db_Name(dbID) = 'EFINTERFACE'
42     and spId <> @@SpId
43     and dbID <> 0
44     and blocked >0;

 

posted @ 2018-08-11 00:53  Jarman  阅读(2151)  评论(0)    收藏  举报