表现二: 用户A读一条纪录,然后修改该条纪录 这是用户B修改该条纪录 这里用户A的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B里的独占锁由于A有共享锁存在所以必须等A释 放掉共享锁,而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现了死锁。 这种死锁比较隐蔽,但其实在稍大点的项目中经常发生。 解决方法: 让用户A的事务(即先读后写类型的操作),在select 时就是用Update lock 语法如下: select * from table1 with(updlock) where ....==========================在联机事务处理(OLTP)的数据库应用系统中,多用户、多任务的并发性是系统最重要的技术指标之一。为了提高并发性,目前大部分RDBMS都采用加锁技术。然而由于现实环境的复杂性,使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术,最小化死锁是开发联机事务处理系统的关键。 死锁产生的原因 在联机事务处理系统中,造成死机主要有两方面原因。一方面,由于多用户、多任务的并发性和事务的完整性要求,当多个事务处理对多个资源同时访问时,若双方已锁定一部分资源但也都需要对方已锁定的资源时,无法在有限的时间内完全获得所需的资源,就会处于无限的等待状态,从而造成其对资源需求的死锁。 另一方面,数据库本身加锁机制的实现方法不同,各数据库系统也会产生其特殊的死锁情况。如在Sybase SQL Server 11中,最小锁为2K一页的加锁方法,而非行级锁。如果某张表的记录数少且记录的长度较短(即记录密度高,如应用系统中的系统配置表或系统参数表就属于此类表),被访问的频率高,就容易在该页上产生死锁。 几种死锁情况及解决方法 清算应用系统中,容易发生死锁的几种情况如下: ● 不同的存储过程、触发器、动态SQL语句段按照不同的顺序同时访问多张表; ● 在交换期间添加记录频繁的表,但在该表上使用了非群集索引(non-clustered); ● 表中的记录少,且单条记录较短,被访问的频率较高; ● 整张表被访问的频率高(如代码对照表的查询等)。 以上死锁情况的对应处理方法如下: ● 在系统实现时应规定所有存储过程、触发器、动态SQL语句段中,对多张表的操作总是使用同一顺序。如:有两个存储过程proc1、proc2,都需要访问三张表zltab、z2tab和z3tab,如果proc1按照zltab、z2tab和z3tab的顺序进行访问,那么,proc2也应该按照以上顺序访问这三张表。 ● 对在交换期间添加记录频繁的表,使用群集索引(clustered),以减少多个用户添加记录到该表的最后一页上,在表尾产生热点,造成死锁。这类表多为往来账的流水表,其特点是在交换期间需要在表尾追加大量的记录,并且对已添加的记录不做或较少做删除操作。 ● 对单张表中记录数不太多,且在交换期间select或updata较频繁的表可使用设置每页最大行的办法,减少数据在表中存放的密度,模拟行级锁,减少在该表上死锁情况的发生。这类表多为信息繁杂且记录条数少的表。 如:系统配置表或系统参数表。在定义该表时添加如下语句: with max_rows_per_page=1 ● 在存储过程、触发器、动态SQL语句段中,若对某些整张表select操作较频繁,则可能在该表上与其他访问该表的用户产生死锁。对于检查账号是否存在,但被检查的字段在检查期间不会被更新等非关键语句,可以采用在select命令中使用at isolation read uncommitted子句的方法解决。该方法实际上降低了select语句对整张表的锁级别,提高了其他用户对该表操作的并发性。在系统高负荷运行时,该方法的效果尤为显著。 例如: select*from titles at isolation read uncommitted ● 对流水号一类的顺序数生成器字段,可以先执行updata流水号字段+1,然后再执行select获取流水号的方法进行操作。 小结 笔者对同城清算系统进行压力测试时,分别对采用上述优化方法和不采用优化方法的两套系统进行测试。在其他条件相同的情况下,相同业务笔数、相同时间内,死锁发生的情况如下: 采用优化方法的系统: 0次/万笔业务; 不采用优化方法的系统:50~200次/万笔业务。 所以,使用上述优化方法后,特别是在系统高负荷运行时效果尤为显著。总之,在设计、开发数据库应用系统,尤其是OLTP系统时,应该根据应用系统的具体情况,依据上述原则对系统分别优化,为开发一套高效、可靠的应用系统打下良好的基础。 ============ --转 /******************************************************** // 创建 : // 日期 : // 修改 : // // 说明 : 查看数据库里阻塞和死锁情况 ********************************************************/ use master go CREATE procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF @@ERROR<>0 RETURN @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end GO ========================== 呵呵,解决死锁,光查出来没有多大用处,我原来也是用这个存储过程来清理死锁的 我解决死锁的方式主要用了: 1 优化索引 2 对所有的报表,非事务性的select 语句 在from 后都加了 with (nolock) 语句 3 对所有的事务性更新尽量使用相同的更新顺序来执行 现在已解决了死锁的问题,希望能对你有帮助with (nolock)的用法很灵活 可以说只要有 from的地方都可以加 with (nolock) 标记来取消产生意象锁,这里 可以用在 delete update,select 以及 inner join 后面的from里,对整个系统的性能提高都很有帮助========================== use master --必须在master数据库中创建 goif exists (select * from dbo.sysobjects where id = object_id(N [dbo].[p_lockinfo] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1) drop procedure [dbo].[p_lockinfo] GO/*--处理死锁查看当前进程,或死锁进程,并能自动杀掉死进程因为是针对死的,所以如果有死锁进程,只能查看死锁进程 当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程--邹建 2004.4--*//*--调用示例exec p_lockinfo --*/ create proc p_lockinfo @kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示 @show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示 as declare @count int,@s nvarchar(1000),@i int select id=identity(int,1,1),标志, 进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid, 数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu, 登陆时间=login_time,打开事务数=open_tran, 进程状态=status, 工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess, 域名=nt_domain,网卡地址=net_address into #t from( select 标志='死锁的进程', spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=a.spid,s2=0 from master..sysprocesses a join ( select blocked from master..sysprocesses group by blocked )b on a.spid=b.blocked where a.blocked=0 union all select '|_牺牲品_>', spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=blocked,s2=1 from master..sysprocesses a where blocked<>0 )a order by s1,s2select @count=@@rowcount,@i=1if @count=0 and @show_spid_if_nolock=1 begin insert #t select 标志='正常的进程', spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time, open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master..sysprocesses set @count=@@rowcount endif @count>0 begin create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255)) if @kill_lock_spid=1 begin declare @spid varchar(10),@标志 varchar(10) while @i<=@count begin select @spid=进程ID,@标志=标志 from #t where id=@i insert #t1 exec('dbcc inputbuffer('+@spid+')') if @标志='死锁的进程' exec('kill '+@spid) set @i=@i+1 end end else while @i<=@count begin select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i insert #t1 exec(@s) set @i=@i+1 end select a.*,进程的SQL语句=b.EventInfo from #t a join #t1 b on a.id=b.id endGO本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zwkandy/archive/2010/02/02/5281581.aspx
--tryset nocount on ; if object_id('T1') is not null drop table T1 go create table T1(ID int primary key,Col1 int,Col2 nvarchar(20)) insert T1 select 1,101,'A' insert T1 select 2,102,'B' insert T1 select 3,103,'C' goif object_id('T2') is not null drop table T2 go create table T2(ID int primary key,Col1 int,Col2 nvarchar(20)) insert T2 select 1,201,'X' insert T2 select 2,202,'Y' insert T2 select 3,203,'Z' go 生成表數據: /* T1: ID Col1 Col2 ----------- ----------- -------------------- 1 101 A 2 101 B 3 101 CT2: ID Col1 Col2 ----------- ----------- -------------------- 1 201 X 2 201 Y 3 201 Z */防止死鎖: 1、 最少化阻塞。阻塞越少,發生死鎖機會越少 2、 在事務中按順序訪問表(以上例子:死鎖2) 3、 在錯誤處理程式中檢查錯誤1205並在錯誤發生時重新提交事務 4、 在錯誤處理程式中加一個過程將錯誤的詳細寫入日誌 5、 索引的合理使用(以上例子:死鎖1、死鎖3) 當發生死鎖時,事務自動提交,可通過日誌來監視死鎖 死鎖1(索引): --連接窗口1 --1步: begin tran update t1 set col2=col2+'A' where col1=101--3步: select * from t2 where col1=201 commit tran --連接窗口2--2步: begin tran update t2 set col2=col2+'B' where col1=203--4步: select * from t1 where col1=103 commit tran--連接窗口1:收到死鎖錯誤,連接窗口2得到結果:/* 訊息 1205,層級 13,狀態 51,行 3 交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 */--連接窗口2:得到結果/* ----------- ----------- -------------------- 3 103 C */處理方法: --在t1、t2表的col1條件列建索引 create index IX_t1_col1 on t1(col1) create index IX_t2_col1 on t2(col1) go--連接窗口1 --1步: begin tran update t1 set col2=col2+'A' where col1=101--3步: select * from t2 with(index=IX_t2_col1)where col1=201 --因表數據少,只能指定索引提示才能確保SQL Server使用索引 commit tran--連接窗口2--2步: begin tran update t2 set col2=col2+'B' where col1=203 --4步: select * from t1 with(index=IX_t1_col1) where col1=103 --因表數據少,只能指定索引提示才能確保SQL Server使用索引 commit tran--連接窗口1: /* ID Col1 Col2 ----------- ----------- -------------------- 1 201 X(1 個資料列受到影響)*/ --連接窗口2 /* ID Col1 Col2 ----------- ----------- -------------------- 3 103 C(1 個資料列受到影響) */ 死鎖2(訪問表順序):--連接窗口1: --1步: begin tran update t1 set col1=col1+1 where ID=1--3步: select col1 from t2 where ID=1 commit tran--連接窗口2: --2步: begin tran update t2 set col1=col1+1 where ID=1--4步 select col1 from t1 where ID=1 commit tran --連接窗口1:/* col1 ----------- 201(1 個資料列受到影響) */--連接窗口2:/* col1 ----------- 訊息 1205,層級 13,狀態 51,行 1 交易 (處理序識別碼 54) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 */處理方法:--改變訪問表的順序--連接窗口1: --1步: begin tran update t1 set col1=col1+1 where ID=1--3步: select col1 from t2 where ID=1 commit tran--連接窗口2: --2步: begin tran select col1 from t1 where ID=1--會等待連接窗口1提交 --4步 update t2 set col1=col1+1 where ID=1 commit tran死鎖3(單表):--連接窗口1:while 1=1 update T1 set col1=203-col1 where ID=2--連接窗口2: declare @i nvarchar(20) while 1=1 set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);--因表數據少,只能指定索引提示才能確保SQL Server使用索引--連接窗口1 /* 訊息 1205,層級 13,狀態 51,行 4 交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。 */ 處理方法: 1、刪除col1上的非聚集索引,這樣影響SELECT速度,不可取. drop index IX_t1_col1 on t1 2、建一個覆蓋索引 A、drop index IX_t1_col1 on t1 B、create index IX_t1_col1_col2 on t1(col1,col2) 通過SQL Server Profiler查死鎖信息:啟動SQL Server Profiler——連接實例——事件選取範圍——顯示所有事件 選擇項: TSQL——SQL:StmtStarting Locks——Deadlock graph(這是SQL2005新增事件,生成包含死鎖信息的xml值) ——Lock:DeadlockChain 死鎖鏈中的進程產生該事件,可標識死鎖進程的ID並跟蹤操作 ——Lock:Deadlock 該事件發生了死鎖
一个用户A 访问表A(锁住了表A),然后又访问表B
另一个用户B 访问表B(锁住了表B),然后企图访问表A 这时用户A由于用户B已经锁住表B,它必须等待用户B释放表B,才能继续,好了他老人家就只好老老实实在这等了
同样用户B要等用户A释放表A才能继续这就死锁了
解决方法:
这种死锁是由于你的程序的BUG产生的,除了调整你的程序的逻辑别无他法
仔细分析你程序的逻辑,
1:尽量避免同时锁定两个资源
2: 必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源.
表现二:
用户A读一条纪录,然后修改该条纪录
这是用户B修改该条纪录
这里用户A的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B里的独占锁由于A有共享锁存在所以必须等A释
放掉共享锁,而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁,于是出现了死锁。
这种死锁比较隐蔽,但其实在稍大点的项目中经常发生。
解决方法:
让用户A的事务(即先读后写类型的操作),在select 时就是用Update lock
语法如下:
select * from table1 with(updlock) where ....==========================在联机事务处理(OLTP)的数据库应用系统中,多用户、多任务的并发性是系统最重要的技术指标之一。为了提高并发性,目前大部分RDBMS都采用加锁技术。然而由于现实环境的复杂性,使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术,最小化死锁是开发联机事务处理系统的关键。
死锁产生的原因
在联机事务处理系统中,造成死机主要有两方面原因。一方面,由于多用户、多任务的并发性和事务的完整性要求,当多个事务处理对多个资源同时访问时,若双方已锁定一部分资源但也都需要对方已锁定的资源时,无法在有限的时间内完全获得所需的资源,就会处于无限的等待状态,从而造成其对资源需求的死锁。
另一方面,数据库本身加锁机制的实现方法不同,各数据库系统也会产生其特殊的死锁情况。如在Sybase SQL Server 11中,最小锁为2K一页的加锁方法,而非行级锁。如果某张表的记录数少且记录的长度较短(即记录密度高,如应用系统中的系统配置表或系统参数表就属于此类表),被访问的频率高,就容易在该页上产生死锁。
几种死锁情况及解决方法
清算应用系统中,容易发生死锁的几种情况如下:
● 不同的存储过程、触发器、动态SQL语句段按照不同的顺序同时访问多张表;
● 在交换期间添加记录频繁的表,但在该表上使用了非群集索引(non-clustered);
● 表中的记录少,且单条记录较短,被访问的频率较高;
● 整张表被访问的频率高(如代码对照表的查询等)。
以上死锁情况的对应处理方法如下:
● 在系统实现时应规定所有存储过程、触发器、动态SQL语句段中,对多张表的操作总是使用同一顺序。如:有两个存储过程proc1、proc2,都需要访问三张表zltab、z2tab和z3tab,如果proc1按照zltab、z2tab和z3tab的顺序进行访问,那么,proc2也应该按照以上顺序访问这三张表。
● 对在交换期间添加记录频繁的表,使用群集索引(clustered),以减少多个用户添加记录到该表的最后一页上,在表尾产生热点,造成死锁。这类表多为往来账的流水表,其特点是在交换期间需要在表尾追加大量的记录,并且对已添加的记录不做或较少做删除操作。
● 对单张表中记录数不太多,且在交换期间select或updata较频繁的表可使用设置每页最大行的办法,减少数据在表中存放的密度,模拟行级锁,减少在该表上死锁情况的发生。这类表多为信息繁杂且记录条数少的表。
如:系统配置表或系统参数表。在定义该表时添加如下语句:
with max_rows_per_page=1
● 在存储过程、触发器、动态SQL语句段中,若对某些整张表select操作较频繁,则可能在该表上与其他访问该表的用户产生死锁。对于检查账号是否存在,但被检查的字段在检查期间不会被更新等非关键语句,可以采用在select命令中使用at isolation read uncommitted子句的方法解决。该方法实际上降低了select语句对整张表的锁级别,提高了其他用户对该表操作的并发性。在系统高负荷运行时,该方法的效果尤为显著。
例如:
select*from titles at isolation read uncommitted
● 对流水号一类的顺序数生成器字段,可以先执行updata流水号字段+1,然后再执行select获取流水号的方法进行操作。
小结
笔者对同城清算系统进行压力测试时,分别对采用上述优化方法和不采用优化方法的两套系统进行测试。在其他条件相同的情况下,相同业务笔数、相同时间内,死锁发生的情况如下:
采用优化方法的系统: 0次/万笔业务;
不采用优化方法的系统:50~200次/万笔业务。
所以,使用上述优化方法后,特别是在系统高负荷运行时效果尤为显著。总之,在设计、开发数据库应用系统,尤其是OLTP系统时,应该根据应用系统的具体情况,依据上述原则对系统分别优化,为开发一套高效、可靠的应用系统打下良好的基础。 ============
--转
/********************************************************
// 创建 :
// 日期 :
// 修改 :
//
// 说明 : 查看数据库里阻塞和死锁情况
********************************************************/ use master go
CREATE procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses
where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
GO
==========================
呵呵,解决死锁,光查出来没有多大用处,我原来也是用这个存储过程来清理死锁的
我解决死锁的方式主要用了:
1 优化索引
2 对所有的报表,非事务性的select 语句 在from 后都加了 with (nolock) 语句
3 对所有的事务性更新尽量使用相同的更新顺序来执行
现在已解决了死锁的问题,希望能对你有帮助with (nolock)的用法很灵活 可以说只要有 from的地方都可以加 with (nolock) 标记来取消产生意象锁,这里 可以用在 delete update,select 以及 inner join 后面的from里,对整个系统的性能提高都很有帮助==========================
use master --必须在master数据库中创建
goif exists (select * from dbo.sysobjects where id = object_id(N [dbo].[p_lockinfo] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
drop procedure [dbo].[p_lockinfo]
GO/*--处理死锁查看当前进程,或死锁进程,并能自动杀掉死进程因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程--邹建 2004.4--*//*--调用示例exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
into #t from(
select 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2select @count=@@rowcount,@i=1if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select 标志='正常的进程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
endif @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
endGO本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zwkandy/archive/2010/02/02/5281581.aspx
if object_id('T1') is not null
drop table T1
go
create table T1(ID int primary key,Col1 int,Col2 nvarchar(20))
insert T1 select 1,101,'A'
insert T1 select 2,102,'B'
insert T1 select 3,103,'C'
goif object_id('T2') is not null
drop table T2
go
create table T2(ID int primary key,Col1 int,Col2 nvarchar(20))
insert T2 select 1,201,'X'
insert T2 select 2,202,'Y'
insert T2 select 3,203,'Z'
go
生成表數據:
/*
T1:
ID Col1 Col2
----------- ----------- --------------------
1 101 A
2 101 B
3 101 CT2:
ID Col1 Col2
----------- ----------- --------------------
1 201 X
2 201 Y
3 201 Z
*/防止死鎖:
1、 最少化阻塞。阻塞越少,發生死鎖機會越少
2、 在事務中按順序訪問表(以上例子:死鎖2)
3、 在錯誤處理程式中檢查錯誤1205並在錯誤發生時重新提交事務
4、 在錯誤處理程式中加一個過程將錯誤的詳細寫入日誌
5、 索引的合理使用(以上例子:死鎖1、死鎖3)
當發生死鎖時,事務自動提交,可通過日誌來監視死鎖
死鎖1(索引):
--連接窗口1
--1步:
begin tran
update t1 set col2=col2+'A' where col1=101--3步:
select * from t2 where col1=201
commit tran
--連接窗口2--2步:
begin tran
update t2 set col2=col2+'B' where col1=203--4步:
select * from t1 where col1=103
commit tran--連接窗口1:收到死鎖錯誤,連接窗口2得到結果:/*
訊息 1205,層級 13,狀態 51,行 3
交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/--連接窗口2:得到結果/*
----------- ----------- --------------------
3 103 C
*/處理方法:
--在t1、t2表的col1條件列建索引
create index IX_t1_col1 on t1(col1)
create index IX_t2_col1 on t2(col1)
go--連接窗口1
--1步:
begin tran
update t1 set col2=col2+'A' where col1=101--3步:
select * from t2 with(index=IX_t2_col1)where col1=201 --因表數據少,只能指定索引提示才能確保SQL Server使用索引
commit tran--連接窗口2--2步:
begin tran
update t2 set col2=col2+'B' where col1=203
--4步:
select * from t1 with(index=IX_t1_col1) where col1=103 --因表數據少,只能指定索引提示才能確保SQL Server使用索引
commit tran--連接窗口1:
/*
ID Col1 Col2
----------- ----------- --------------------
1 201 X(1 個資料列受到影響)*/
--連接窗口2
/*
ID Col1 Col2
----------- ----------- --------------------
3 103 C(1 個資料列受到影響)
*/
死鎖2(訪問表順序):--連接窗口1:
--1步:
begin tran
update t1 set col1=col1+1 where ID=1--3步:
select col1 from t2 where ID=1
commit tran--連接窗口2:
--2步:
begin tran
update t2 set col1=col1+1 where ID=1--4步
select col1 from t1 where ID=1
commit tran
--連接窗口1:/*
col1
-----------
201(1 個資料列受到影響)
*/--連接窗口2:/*
col1
-----------
訊息 1205,層級 13,狀態 51,行 1
交易 (處理序識別碼 54) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/處理方法:--改變訪問表的順序--連接窗口1:
--1步:
begin tran
update t1 set col1=col1+1 where ID=1--3步:
select col1 from t2 where ID=1
commit tran--連接窗口2:
--2步:
begin tran
select col1 from t1 where ID=1--會等待連接窗口1提交
--4步
update t2 set col1=col1+1 where ID=1
commit tran死鎖3(單表):--連接窗口1:while 1=1
update T1 set col1=203-col1 where ID=2--連接窗口2:
declare @i nvarchar(20)
while 1=1
set @i=(select col2 from T1 with(index=IX_t1_col1)where Col1=102);--因表數據少,只能指定索引提示才能確保SQL Server使用索引--連接窗口1
/*
訊息 1205,層級 13,狀態 51,行 4
交易 (處理序識別碼 53) 在 鎖定 資源上被另一個處理序鎖死並已被選擇作為死結的犧牲者。請重新執行該交易。
*/
處理方法:
1、刪除col1上的非聚集索引,這樣影響SELECT速度,不可取.
drop index IX_t1_col1 on t1
2、建一個覆蓋索引
A、drop index IX_t1_col1 on t1
B、create index IX_t1_col1_col2 on t1(col1,col2)
通過SQL Server Profiler查死鎖信息:啟動SQL Server Profiler——連接實例——事件選取範圍——顯示所有事件
選擇項:
TSQL——SQL:StmtStarting
Locks——Deadlock graph(這是SQL2005新增事件,生成包含死鎖信息的xml值)
——Lock:DeadlockChain 死鎖鏈中的進程產生該事件,可標識死鎖進程的ID並跟蹤操作
——Lock:Deadlock 該事件發生了死鎖