2个事务老是死锁

to libin_ftsafe(子陌红尘) :只有在 SELECT 语句中才允许使用 NOLOCK、READUNCOMMITTED 和 READPAST 锁提示

to libin_ftsafe(子陌红尘) :不好意思, 看错了

楼主的语句是可能产生死锁的，举个简单的例子：
表1的记录如下：
id  code
--- -----
1   'aa'
2   'bb'调用“事务A”的时候， code参数='bb'
调用“事务B”的时候， code参数='aa'假设两个是同时调用的，假设他们的select时间都很长，假设按照优化原则，只能进行全表扫描
假设“事务A”，“事务B”都同时扫描数据，这样的话，由于共享锁不是互拆的，所以“事务A”，“事务B”都能顺利的对两条记录下共享锁
由于楼主设置的了 "updlock" , 因此，这两个共享锁都要到提交事务完成后才释放。
假设上面的两步都完成后，“事务A”，“事务B”都准备更新数据，由于更新数据要把共享锁变为排它锁才能更新
而共享锁与排它锁是冲突的，所以，“事务A”要更新记录2的话，则必须等待“事务B”对记录2下的共享锁的释放
而同样的，“事务B”要更新记录1的话，则必须等待“事务A”对记录1下的共享锁的释放很显然，这样的结果是“事务A”等待“事务B”，“事务B”等待“事务A”，这样就造成了标准的死锁

select top 1 @id=id from 表1 with (updlock) where code=@code
update 表1 with (updlock) set TTTcode='xxx' where id=@id
不可以直接改的，这里的事务我精减过了
原来可能是看得到ID的同时得到状态再确定UPDATE做不做
如
select @状态=状态 from 表1 with (updlock) where code=@code
if @状态='xxx'
update 表1 with (updlock) set TTTcode='xxx' where id=@id
这样怎么办？？？？难得就没有办法操作先SELECT后UPDATE ？？？？事务隔离级别是默认的read committed啊我吐血

S 共享锁
U 更新锁
IU 意向更新锁共享锁
共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。更新锁
更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。排它锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。锁模式描述
意向共享 (IS) 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。
意向排它 (IX) 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。
与意向排它共享 (SIX) 通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置一个 SIX 锁（允许并发 IS 锁），在当前所修改页上放置 IX 锁（在已修改行上放置 X 锁）。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。

那我是不是去掉updlock 在read committed级别下就不会出现死锁????和lock timeout有关系吗?

去掉updlock, 则锁在select 完成后即释放, 不存在事务A,B互相等待的情形, 自然也就没有死锁当然, 我说的是默认事务隔离级别,如果你把事务隔离级别设置为REPEATABLE READ或者SERIALIZABLE
那死锁仍然会存在, 因为这两种事务隔离级别都会导致select的锁在事务结束时才释放

和lock_time没有关系, lock_timeout 一般用于决定资源阻塞时, 等待阻塞消除的时间.例如, 事务A在更新表1, 事务B要检索表1的话, 必须等待事务A更新完成, 这时, 事务A就阻塞了事务B事务A更新表1可能会需要10分钟, 有时, 我们可能不想等待这么长时间, 我们只想等待1秒钟,如果还是没有等待事务A完成, 则我们不等待, 那么我们可以设置 lock_timeout 来实现这个目的

谢谢我试试看.hehe
那我想问问,是不是去掉updlock之后,设置为read committed级别,这种SELECT UPDATE就再也不会出现死锁,就是一次也不可能发生,不是说死锁是不可必面的吗？？？和lock timeout没有关系???

我原来当是一个事务A是U锁状态,另一个事务B要求IU，然后因为事务A是-1所以事务B认为事务A永远不可以释放要限制的等下去，所以才会死锁...

对楼主的这两个处理语句而言, 去掉 updlock 应该就没有问题了.

还是会死锁!邹老大帮忙再看一下

日志如下:
-------------------------------------------------------------
Input Buf:  事务A,'XXX'
SPID: 55 ECID: 0 Statement Type: SELECT Line #: 7(第7行是一个SELECT,全是操作表1）
Mode: S
Input Buf:事务B,'XXX2'
SPID: 69 ECID: 0 Statement Type: UPDATE Line #: 13（第13行是一个UPDATE,全是操作表1)
Mode: IX      事务A大体的内容，事务B基本和事务A相同
--------------------------------------------------------------
ALTER   proc 事务A,@code nvarchar(20)
as
set xact_abort on
set transaction isolation level read committed
begin tran
declare @id bigint,@type int
select top 1 @id=id,@type=type from 表1 where xxx='xxx' and code=@code
if @type='a'
update 表1 set code='dddd2' where id=@id
else
update 表1 set code='dddd' where id=@id
if @@error>0
rollback
commit tran
set xact_abort off

先贴两个事务，下一贴是日志，不过日志是精减的，不然贴不出来
--事务 sffitting
ALTER  proc sffitting @pro_code nvarchar(30),@lot_no nvarchar(30),@expiry_date smalldatetime,@qty float,@office nvarchar(20),@cust_code nvarchar(20),@ord_no nvarchar(20),@del_date smalldatetime,@gp nvarchar(20)
as
declare @id bigint,@comp_code nvarchar(20)
set xact_abort on
set transaction isolation level read committed
begin tran
if @lot_no='#LOT'
insert into salesdetail(ord_no,pro_code,lot_no,expiry_date,qty,goods_place,status) values(@ord_no,@pro_code,@lot_no,@expiry_date,@qty,@gp,'送货')
else
select top 1 @id=id from productdetail where (last_stat='已收货' or last_stat='初始入库' or last_stat='原厂入库') and hosp_code=@gp and rece_depo=@office and prod_code=@pro_code and prod_batc=@lot_no and prod_qty=@qty
if not @id is null
begin
update productdetail set cust_code=@cust_code,pre_code=last_code,pre_stat=last_stat,last_code=@ord_no,last_stat='送货' where id=@id
update officeproductstocklist  set orde_qty=orde_qty+@qty  where  offi_code=@office and  prod_code=@pro_code
insert into salesdetail(ord_no,pro_code,lot_no,expiry_date,qty,goods_place,status) values(@ord_no,@pro_code,@lot_no,@expiry_date,@qty,@gp,'送货')
end
if @@error>0
rollback
commit tran
set xact_abort off--事务Receive
ALTER   proc Receive @pro_code nvarchar(30),@lot_no nvarchar(30),@expiry_date smalldatetime,@qty float,@office nvarchar(20),@cust_code nvarchar(20),@ord_no nvarchar(20),@del_date smalldatetime,@status nvarchar(20)
as
set xact_abort on
set transaction isolation level read committed
begin tran
declare @id bigint,@comp_code nvarchar(20)
select top 1 @id=id from productdetail where last_stat='送货' and  last_code=@ord_no and prod_code=@pro_code and prod_batc=@lot_no and prod_qty=@qty
if @status='销售'
begin
update productdetail set last_stat='销售'  where id=@id
update officeproductstocklist set orde_qty=orde_qty-@qty,sale_qty=sale_qty+@qty  where  offi_code=@office and  prod_code=@pro_code
end
else
begin
if not @lot_no='#LOT'
begin
update productdetail set cust_code=NULL,last_code=pre_code,last_stat=pre_stat  where id=@id
update officeproductstocklist  set orde_qty=orde_qty-@qty  where  offi_code=@office and  prod_code=@pro_code
end
end
if @@error>0
rollback
commit tran
set xact_abort off

--日志
2006-03-23 12:30:09.43 spid1     Node:1
2006-03-23 12:30:09.43 spid1     PAG: 7:1:162558                CleanCnt:2 Mode: IX Flags: 0x2
2006-03-23 12:30:09.43 spid1      Grant List 3::
2006-03-23 12:30:09.43 spid1        SPID: 69 ECID: 0 Statement Type: UPDATE Line #: 13
2006-03-23 12:30:09.43 spid1        Input Buf: Language Event: sffitting 'SF806PC55T','W05J5748','2006-3-14',1,'SH','SH262','SF-S-SH-2006-0903','2006-3-22 10:30:00',''
2006-03-23 12:30:09.43 spid1      Requested By:
2006-03-23 12:30:09.43 spid1        ResType:LockOwner Stype:'OR' Mode: S SPID:68 ECID:0 Ec:(0x35B335A0) Value:0x199c3b40 Cost:(0/0)
2006-03-23 12:30:09.43 spid1
2006-03-23 12:30:09.43 spid1     Node:2
2006-03-23 12:30:09.43 spid1     PAG: 7:1:2889                  CleanCnt:2 Mode: S Flags: 0x2
2006-03-23 12:30:09.43 spid1      Grant List 2::
2006-03-23 12:30:09.43 spid1        SPID: 68 ECID: 0 Statement Type: SELECT Line #: 7
2006-03-23 12:30:09.43 spid1        Input Buf: Language Event: Receive 'SF806TS55L120T','W05G3212','2005-8-01',1,'XA','ZX006','SF-S-XA-2006-0270','2006-03-21 17:09:00','销售'
2006-03-23 12:30:09.43 spid1      Requested By:
2006-03-23 12:30:09.43 spid1        ResType:LockOwner Stype:'OR' Mode: IX SPID:69 ECID:0 Ec:(0x1A89D5A0) Value:0x3ff6a280 Cost:(0/228)
2006-03-23 12:30:09.43 spid1     Victim Resource Owner:
2006-03-23 12:30:09.43 spid1      ResType:LockOwner Stype:'OR' Mode: S SPID:68 ECID:0 Ec:(0x35B335A0) Value:0x199c3b40 Cost:(0/0)

楼主帖出的处理代码和死锁的记录是否对不上号吧?处理代码是一些insert和update处理语句.
而错误日志中, 则是一些具体的值, 似乎是insert的?

老大，对的上呀
日志里显示一个死锁是因为sffitting和receive两个存储过程中的某行,两个存储过程的第7行为select,第13行为update,
不知道为什么还会死唉。
实际上sffitting时应该是这句
select top 1 @id=id from productdetail where (last_stat='已收货' or last_stat='初始入库' or last_stat='原厂入库') and hosp_code=@gp and rece_depo=@office and prod_code=@pro_code and prod_batc=@lot_no and prod_qty=@qty
和receive里的
update productdetail set last_stat='销售'  where id=@id
发生了死锁.-------------------------------------------
  SPID: 68 ECID: 0 Statement Type: SELECT Line #: 7
2006-03-23 12:30:09.43 spid1        Input Buf: Language Event: Receive 'SF806TS55L120T','W05G3212','2005-8-01',1,'XA','ZX006','SF-S-XA-2006-0270','2006-03-21 17:09:00','销售'
SPID: 69 ECID: 0 Statement Type: UPDATE Line #: 13
2006-03-23 12:30:09.43 spid1        Input Buf: Language Event: sffitting 'SF806PC55T','W05J5748','2006-3-14',1,'SH','SH262','SF-S-SH-2006-0903','2006-3-22 10:30:00',''

调试易

2个事务老是死锁

解决方案 »