日期:2014-05-18  浏览次数:20736 次

死锁
SET NOCOUNT ON
DECLARE @s_scddh varchar(12),@s_sxh int
DECLARE sk_temp CURSOR FOR 
SELECT scddh,sxh
FROM #temp_sk ORDER BY sxh
OPEN sk_temp

FETCH NEXT FROM sk_temp 
INTO @s_scddh,@s_sxh

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT scddh
  FROM erp_pp_a
  WHERE scddh = @s_ddh and (gxmc = 'AA') 
  IF @@ROWCOUNT = 0
BEGIN Tran myTran  
  begin transaction
insert into erp_pp_bg_pr_e(scddh,gxmc,sxh,user_name)
values ( @s_scddh,'AA',@s_sxh+5,'补数')
IF(@@error=0)  
  COMMIT Tran myTran  
  ELSE  
  ROLLBACK Tran myTran  
  FETCH NEXT FROM sk_temp 
  INTO @s_scddh,@s_sxh
END

CLOSE sk_temp
DEALLOCATE sk_temp
GO

不知为何执行该语句就会死锁!!!请高手赐教 !谢谢!!!


------解决方案--------------------
BEGIN Tran myTran
begin transaction 

LZ这个地方是什么意思,下面的COMMIT和ROLLBACK都是针对的BEGIN Tran myTran

自己多写了一个 begin transaction
------解决方案--------------------
不是死锁:
事务不正确:标记事务
BEGIN TRAN T1
UPDATE table1 ...
BEGIN TRAN M2 WITH MARK
UPDATE table2 ...
SELECT * from table1
COMMIT TRAN M2
UPDATE table3 ...
COMMIT TRAN T1
------解决方案--------------------
SQL code
--用@@error判断错误,如果是严重错误,根本不会执行rollback tran语句,系统会自动中止执行。
--建议用格式类似如下方式使用事务:
set xact_abort on

begin tran

....


commit tran

------解决方案--------------------
SQL code

SET NOCOUNT  ON 
SET XACT_ABORT ON
DECLARE @s_scddh varchar(12),@s_sxh int 

DECLARE sk_temp CURSOR FOR   
   SELECT scddh,sxh 
     FROM  #temp_sk ORDER BY sxh 

OPEN sk_temp 

FETCH NEXT FROM sk_temp INTO @s_scddh,@s_sxh 

WHILE @@FETCH_STATUS   =   0 
BEGIN 
      SELECT scddh 
         FROM erp_pp_a 
            WHERE scddh = @s_scddh and (gxmc = 'AA')   
      IF @@ROWCOUNT = 0 
       BEGIN
         BEGIN Tran myTran           
            insert into erp_pp_bg_pr_e(scddh,gxmc,sxh,user_name) 
              values (@s_scddh,'AA',@s_sxh+5, '补数') 
         COMMIT Tran        
       END      
      FETCH  NEXT FROM sk_temp INTO @s_scddh,@s_sxh 
END 

CLOSE   sk_temp 
DEALLOCATE sk_temp 
GO

------解决方案--------------------
SET XACT_ABORT {ON ¦OFF} 
当 SET XACT_ABORT 为 ON 时,如果 Transact-SQL 语句产生运行时错误,整个事务将终止并回滚。 
为 OFF 时,只回滚产生错误的 Transact-SQL 语句,而事务将继续进行处理。 

如果没有对 SET XACT_ABORT {ON ¦OFF} 进行设置,默认情况是ON还是OFF?? 

OFF!