select * from master .dbo.sysprocesses where blocked>0---查看阻塞---blocked 指被阻塞或锁定的SPID
go
dbcc inputbuffer(5)--查看具体是那个SQL阻塞5指SPID
go
Sp_lock--- 找出spid=5 --objid 被锁定或阻塞的对象--dbid 被阻塞或锁定的对象所在的DB
go
Select db_name(@dbid)---找到数据库
go
select object_name(@objid)---找到表
go
select @@LOCK_TIMEOUT -- -1 无限期等待 0 发生锁定或阻塞直接抛出错误。
--查看那个进程ID阻塞了哪几个进程ID --session_id被阻塞者、blocking_session_id阻塞者
SELECT blocking_session_id, wait_duration_ms, session_id,* FROM sys.dm_os_waiting_tasks where blocking_session_id is not null
--查看整个数据库的锁定和阻塞详细信息:
SELECT * FROM sys.dm_tran_locks
EXEC sp_lock
解决方法:
如果是更新,则改为不再实时对tabA表进行update操作,而是每次访问都先insert一条记录到一个中间表中,然后再用一个作业,
每隔几分钟定时更新书目标表中的数据。
---检测数据阻塞语句
create procedure pr_check_blocked_SQL
---ZHAOWENZHONG
---检测数据阻塞语句
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM MASTER..SYSPROCESSES WHERE BLOCKED>0)
BEGIN
SELECT '以下SQL语句是引起阻塞的源'
select distinct sp.spid '进程ID',sp.status '进程ID的状态',
sp.blocked '分块进程的进程ID',sp.hostname '工作站名',
sp.uid '执行者',sp.dbid 'DB',
sp.program_name'应用程序名',
sp.cmd '目前正在执行的命令',sp.cpu 'CPU时间',
sp.physical_io 'IO',
sp.loginame '登录名',
st.text '执行SQL语句'
from master..sysprocesses as sp
cross apply sys.dm_exec_sql_text(sp.sql_handle)as st
where sp.blocked=0 and sp.spid in(select blocked from master..sysprocesses )
--被阻塞的SQL
SELECT '以下是被阻塞的SQL语句'
select distinct sp.spid '进程ID',sp.status '进程ID的状态',
sp.blocked '分块进程的进程ID',sp.hostname '工作站名',
sp.uid '执行者',sp.dbid 'DB',
sp.program_name'应用程序名',
sp.cmd '目前正在执行的命令',sp.cpu 'CPU时间',
sp.physical_io 'IO',
sp.loginame '登录名',
st.text '执行SQL语句'
from master..sysprocesses as sp
cross apply sys.dm_exec_sql_text(sp.sql_handle)as st
where sp.blocked<>0
END