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 onbegin tran....commit tran
------解决方案--------------------
- SQL code
SET NOCOUNT ON SET XACT_ABORT ONDECLARE @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!