sqlserver数据库发生死锁处理

SQLSERVER数据库锁表

1.?查看被锁的表
???select?request_session_id???spid,OBJECT_NAME(resource_associated_entity_id)?tableName???
???from?sys.dm_tran_locks?where?resource_type='OBJECT'

2.?解除表的锁定
???declare?@spid??int?
???Set?@spid??=?57?--锁表进程
???declare?@sql?varchar(1000)
???set?@sql='kill?'+cast(@spid??as?varchar)
???exec(@sql)

3.?查询数据库死锁的信息
??????CREATE?procedure?sp_who_lock???
	as?????
	begin?????
	???declare?@spid?int?????
	???declare?@blk?int?????
	???declare?@count?int?????
	???declare?@index?int?????
	???declare?@lock?tinyint??????
	???set?@lock=0??????
	???create?table?#temp_who_lock??????
	?(??????
	??id?int?identity(1,1),??????
	??spid?int,??????
	??blk?int?????
	?)??????
	?if?@@error<>0?return?@@error??????
	?insert?into?#temp_who_lock(spid,blk)??????
	?select?0?,blocked???????
	?from?(select?*?from?master..sysprocesses?where?blocked>0)a??????
	?where?not?exists(select?*?from??master..sysprocesses?where?a.blocked?=spid?and?blocked>0)??????
	?union?select?spid,blocked?from??master..sysprocesses?where?blocked>0??????
	?if?@@error<>0?return?@@error??????
	?select?@count=count(*),@index=1?from?#temp_who_lock??????
	?if?@@error<>0?return?@@error??????
	?if?@count=0??????
	?begin?????
	??select?'没有阻塞和死锁信息'?????
	??return?0??????
	?end?????
	?while?@index<=@count??????
	?begin?????
	??if?exists(select?1?from?#temp_who_lock?a?where?id>@index?and?exists(select?1?from?#temp_who_lock?where?id<=@index?and?a.blk=spid))??????
	??begin?????
	???set?@lock=1??????
	???select?@spid=spid,@blk=blk?from?#temp_who_lock?where?id=@index?????
	???select?'引起数据库死锁的是:?'+?CAST(@spid?AS?VARCHAR(10))?+?'进程号,其执行的SQL语法如下'?????
	???select??@spid,?@blk????
	???dbcc?inputbuffer(@spid)??????
	???dbcc?inputbuffer(@blk)??????
	??end?????
	??set?@index=@index+1??????
	?end?????
	?if?@lock=0???????
	?begin?????
	??set?@index=1??????
	??while?@index<=@count??????
	??begin?????
	???select?@spid=spid,@blk=blk?from?#temp_who_lock?where?id=@index?????
	???if?@spid=0??????
	????select?'引起阻塞的是:'+cast(@blk?as?varchar(10))+?'进程号,其执行的SQL语法如下'?????
	???else??????
	????select?'进程号SPID：'+?CAST(@spid?AS?VARCHAR(10))+?'被'?+?'进程号SPID：'+?CAST(@blk?AS?VARCHAR(10))?+'阻塞,其当前进程执行的SQL语法如下'?????
	???dbcc?inputbuffer(@spid)????
	???dbcc?inputbuffer(@blk)??????
	???set?@index=@index+1??????
	??end?????
	?end?????
	?drop?table?#temp_who_lock??????
	?return?0??????
	end???????????
	?
	?
	GO

4.??设置数据库锁的级别
????READ_COMMITTED_SNAPSHOT??ON
????
????使用基于行版本控制的隔离级别?(SQL?Server?2005?支持?)?：开启下面的选项后，?SELECT?不会对请求的资源加?S?锁，不加锁或者加?Sch-S?锁，
????从而将读与写操作之间发生的死锁几率降至最低；而且不会发生脏读

????ALTER?DATABASE?EBCMKS?SET?ALLOW_SNAPSHOT_ISOLATION?ON
????ALTER?DATABASE?EBCMKS?SET?READ_COMMITTED_SNAPSHOT?ON

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!