死锁不知道为什么

liyiding23 2006-02-10 05:23:01
a事务 spid 58:Receive 'SN71333326','05CM14740','2015-1-1 0:00:00',1,'SH','SH037','SN-J-SH-2006-0064','2006-2-8 9:51:00','回收'

b处理 spid 53:select validdate from productdetail where prod_code='SN71420102' and prod_batc='05DM01240'

日志提示是
A事务:ResType:LockOwner Stype:'OR' Mode: X SPID:53 ECID:4 Ec:(0x322EE0C0) Value:0x2b354e20 Cost:(0/0)
B事务:ResType:LockOwner Stype:'OR' Mode: IS SPID:58 ECID:3 Ec:(0x3233E0C0) Value:0x1ebf27e0 Cost:(0/0)

-----------------------------------------------------------
事务A内容
alter proc yiding_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
begin tran t
declare @id bigint,@comp_code nvarchar(20)

select top 1 @id=id from productdetail with (updlock) where last_stat='送货' and last_code=@ord_no and prod_code=@pro_code and prod_batc=@lot_no and prod_qty=@qty

update productdetail with (xlock) set cust_code=NULL,last_code=pre_code,last_stat=pre_stat where id=@id
update officeproductstocklist with (xlock) set orde_qty=orde_qty-1 where offi_code=@office and prod_code=@pro_code

if @@error>0
rollback
commit tran t
SET XACT_ABORT OFF
...全文
171 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
liyiding23 2006-02-13
这个死锁是什么意思,为什么会发生???不理解.
回复
vovo2000 2006-02-11
提高隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- 参考级别
--     { READ COMMITTED
--         | READ UNCOMMITTED
--         | REPEATABLE READ
--         | SERIALIZABLE
--     }
回复
Yang_ 2006-02-11
不要指定锁

特别是表锁
回复
OracleRoob 2006-02-11
已经设置了SET XACT_ABORT ON
不要再指定对表的锁定
回复
pbsql 2006-02-10
把select与update合并为一句:
update productdetail with (xlock) set cust_code=NULL,last_code=pre_code,last_stat=pre_stat
where id in(select 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)
update officeproductstocklist with (xlock) set orde_qty=orde_qty-1 where offi_code=@office and prod_code=@pro_code
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-02-10 05:23
社区公告
暂无公告