问个问题,我在存储过程中加入了了事务回滚,发现每天总能出现sqlserver死锁,这个是什么原因呀
CREATE PROCEDURE Sn_ComposeAccounts
@FInHandNo char(5),@FOutHandNo char(5),@FInName char(10)
AS
SET XACT_ABORT ON --出现错误整个事务将终止并回滚
BEGIN TRAN
Declare @FClasses char(2)
Declare @FHandNoSum int
Select @FClasses=ClassesID from ChangeClasses where YNClasses='N'
Declare @InDocNo char(8),@OutDocNo char(8)
Select @InDocNo=Doc_No from Sn_Hand where Hand_No=@FInHandNo
Select @OutDocNo=Doc_No from Sn_Hand where Hand_No=@FOutHandNo --明细合帐
Update Sn_itemizeBill Set
Doc_No=@InDocNo
from Sn_itemizeBill where Doc_No=@OutDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
Update Sn_Bill Set
AccountDateTime=GETDATE ( ),
AccountName=@FInName,
Classes=@FClasses,
PayMode='12',
RealFEE=0,
DueRMB=0,
RealRMB=0,
YNAccount='Y'
from Sn_Bill where Doc_No=@OutDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
--Sn_Hand 改变手牌帐单号
Update Sn_Hand Set
Doc_No=@InDocNo
from Sn_Hand where Doc_No=@OutDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
Select @FHandNoSum=count(*) from Sn_Hand where Doc_No=@OutDocNo
Update Sn_Bill Set
PersonQty=PersonQty+@FHandNoSum
from Sn_Bill where Doc_No=@InDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
COMMIT TRAN
GO
CREATE PROCEDURE Sn_ComposeAccounts
@FInHandNo char(5),@FOutHandNo char(5),@FInName char(10)
AS
SET XACT_ABORT ON --出现错误整个事务将终止并回滚
BEGIN TRAN
Declare @FClasses char(2)
Declare @FHandNoSum int
Select @FClasses=ClassesID from ChangeClasses where YNClasses='N'
Declare @InDocNo char(8),@OutDocNo char(8)
Select @InDocNo=Doc_No from Sn_Hand where Hand_No=@FInHandNo
Select @OutDocNo=Doc_No from Sn_Hand where Hand_No=@FOutHandNo --明细合帐
Update Sn_itemizeBill Set
Doc_No=@InDocNo
from Sn_itemizeBill where Doc_No=@OutDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
Update Sn_Bill Set
AccountDateTime=GETDATE ( ),
AccountName=@FInName,
Classes=@FClasses,
PayMode='12',
RealFEE=0,
DueRMB=0,
RealRMB=0,
YNAccount='Y'
from Sn_Bill where Doc_No=@OutDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
--Sn_Hand 改变手牌帐单号
Update Sn_Hand Set
Doc_No=@InDocNo
from Sn_Hand where Doc_No=@OutDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
Select @FHandNoSum=count(*) from Sn_Hand where Doc_No=@OutDocNo
Update Sn_Bill Set
PersonQty=PersonQty+@FHandNoSum
from Sn_Bill where Doc_No=@InDocNo
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END
COMMIT TRAN
GO
应该用SET XACT_ABORT OFF
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN
RETURN
END
这个造成的,原来没有这个,但是发现会出现写数据错误的问题,但是加了,就有出现死锁的现象
只是我想如果出现错误,将回滚全部的修改,为了保证所有修改的完整性
和
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN
RETURN
END
有冲突吗,是不能同时用吗
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END 结束
COMMIT TRAN
如果中间出错了,最后的COMMIT TRAN 还有什么意义呢? 不停的回滚不停的回滚,最后还要提交,有点不符合逻辑的感觉SET XACT_ABORT ONBEGIN TRAN……
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
ELSE
COMMIT TRAN直接这样写不行吗?