none
SqlServer2005 多个事务的问题 盼高手。。。。。。。。。。。。。。。。 RRS feed

  • 问题

  •  示例如下:
              declare @sql nvarchar(4000);
              select @sql='insert into dbo.A(ID,Age) values(1,18);........';--长度接近4000
              exec(@sql);--位置1
              select @sql='insert into dbo.B(ID,Age) values(2,48);........';--长度接近4000
              exec(@sql);--位置2
    现有这样一个问题,如何保证 位置1 和位置2 要么都执行,要么都不执行?
    2009年10月14日 8:07

答案

  • 用显式事务结合保存点可以满足你的需要。以下是我的测试过程:

    --设置环境
    SET QUOTED_IDENTIFIER OFF;
    GO
    SET NOCOUNT OFF;
    GO
    --创建测试表
    CREATE TABLE A(ID INT PRIMARY KEY,Age int NOT NULL);
    GO
    CREATE TABLE B(ID INT PRIMARY KEY,Age int NOT NULL);
    GO
    
    --故意在B表插入一条数据,导致后面事务违反PRIMARY KEY 约束,达到测试效果
    insert into B(ID,Age) values(2,20)
    
    --A表无记录,B表有一条记录
    select * from A
    select * from B
    /*
    	ID          Age
    	----------- -----------
    
    	(0 行受影响)
    
    	ID          Age
    	----------- -----------
    	2           20
    
    	(1 行受影响)
    */
    
    
    --事务操作
    declare @sql nvarchar(4000);
    BEGIN TRANSACTION myTran
    	SAVE TRANSACTION  pos1    
    	select @sql='INSERT INTO A VALUES (1, 10);INSERT INTO A VALUES (2, 20)'
    	-- 此句应该可以成功执行
    	exec(@sql)
    	if (@@ERROR <> 0)
    	begin
    		print 'SQL1 is error'
    		ROLLBACK TRANSACTION pos1
    		COMMIT TRANSACTION myTran
    		return
    	end
    
    	select @sql='INSERT INTO B VALUES (1, 10);INSERT INTO B VALUES (2, 20)'
    	-- 此句的后半部分会执行失败,导致整个事务回滚到保存点pos1,包括insert到A表的操作
    	exec(@sql)
    	if (@@ERROR <> 0)
    	begin
    		print 'SQL2 is error'
    		ROLLBACK TRANSACTION pos1
    		COMMIT TRANSACTION myTran;
    		return
    	end
    print 'both SQL1 and SQL2 ok'
    COMMIT TRANSACTION myTran;
    GO
    
    --操作前面成功,后面部分违法PRIMARY KEY 约束而失败
    /*
    	(1 行受影响)
    
    	(1 行受影响)
    
    	(1 行受影响)
    	消息 2627,级别 14,状态 1,第 1 行
    	违反了 PRIMARY KEY 约束 'PK__B__0EA330E9'。不能在对象 'dbo.B' 中插入重复键。
    	语句已终止。
    	SQL2 is error
    
    */
    
    
    --检查表中的数据情况,还是和原来的一模一样:A表和B表都没有插入记录
    select * from A
    select * from B
    /*
    	ID          Age
    	----------- -----------
    
    	(0 行受影响)
    
    	ID          Age
    	----------- -----------
    	2           20
    
    	(1 行受影响)
    */
    
    --删除掉B表中的数据
    truncate table B
    
    
    --再次执行上面的事务操作得到如下提示:两个语句都成功执行
    /*
    	(1 行受影响)
    
    	(1 行受影响)
    
    	(1 行受影响)
    
    	(1 行受影响)
    	both SQL1 and SQL2 ok
    */
    
    
    --再次检查表中的数据情况,两个表中都有想要的数据了
    select * from A
    select * from B
    /*
    	ID          Age
    	----------- -----------
    	1           10
    	2           20
    
    	(2 行受影响)
    
    	ID          Age
    	----------- -----------
    	1           10
    	2           20
    
    	(2 行受影响)
    */
    • 已标记为答案 steven_lsy 2009年10月15日 1:27
    2009年10月14日 16:02
    版主

全部回复

  • 加个 begin tran不就行了

    2009年10月14日 8:58
  • 。。。
    exec 执行时,默认作为一个事务。
    位置1的exec执行成功后,外层的tran回滚不了它
    2009年10月14日 9:12
  • 能不能把2个@sql合成1个?

    2009年10月14日 9:21
  • 或者你把exec(@sql)换成

    BEGIN TRAN

    EXECUTE sp_executesql @sql
    IF @@ERROR <> 0 OR @@ROWCOUNT = 0
     ROLLBACK
    else
     commit tran

    应该就可以了吧

    2009年10月14日 9:25
  • 已经说明了 长度接近4000
    若合并,则长度超过nvarchar(4000)的上限
    2009年10月14日 9:41
  • 或者你把exec(@sql)换成

    BEGIN TRAN


    IF @@ERROR <> 0 OR @@ROWCOUNT = 0
     ROLLBACK
    else
     commit tran

    应该就可以了吧

    EXECUTE sp_executesql @sql 本身就是个隐式事务。
    我的目标是让这2个隐式事务要么都执行,要么都不执行,你的做法只会保证一个隐式事务。。
    2009年10月14日 9:52
  • BEGIN TRAN

    EXECUTE sp_executesql @sql1
    IF @@ERROR <> 0 OR @@ROWCOUNT = 0
     ROLLBACK
    else

    EXECUTE sp_executesql @sql2
    IF @@ERROR <> 0 OR @@ROWCOUNT = 0
     ROLLBACK
    else

    commit tran

    应该就可以了吧

    2009年10月14日 10:16
  • BEGIN TRAN

    EXECUTE sp_executesql @sql1
    IF @@ERROR <> 0 OR @@ROWCOUNT = 0
     ROLLBACK
    else

    EXECUTE sp_executesql @sql2
    IF @@ERROR <> 0 OR @@ROWCOUNT = 0
     ROLLBACK
    else

    commit tran

    应该就可以了吧

    不可以。。。
    你可以测试下
    2009年10月14日 11:40
  • 用显式事务结合保存点可以满足你的需要。以下是我的测试过程:

    --设置环境
    SET QUOTED_IDENTIFIER OFF;
    GO
    SET NOCOUNT OFF;
    GO
    --创建测试表
    CREATE TABLE A(ID INT PRIMARY KEY,Age int NOT NULL);
    GO
    CREATE TABLE B(ID INT PRIMARY KEY,Age int NOT NULL);
    GO
    
    --故意在B表插入一条数据,导致后面事务违反PRIMARY KEY 约束,达到测试效果
    insert into B(ID,Age) values(2,20)
    
    --A表无记录,B表有一条记录
    select * from A
    select * from B
    /*
    	ID          Age
    	----------- -----------
    
    	(0 行受影响)
    
    	ID          Age
    	----------- -----------
    	2           20
    
    	(1 行受影响)
    */
    
    
    --事务操作
    declare @sql nvarchar(4000);
    BEGIN TRANSACTION myTran
    	SAVE TRANSACTION  pos1    
    	select @sql='INSERT INTO A VALUES (1, 10);INSERT INTO A VALUES (2, 20)'
    	-- 此句应该可以成功执行
    	exec(@sql)
    	if (@@ERROR <> 0)
    	begin
    		print 'SQL1 is error'
    		ROLLBACK TRANSACTION pos1
    		COMMIT TRANSACTION myTran
    		return
    	end
    
    	select @sql='INSERT INTO B VALUES (1, 10);INSERT INTO B VALUES (2, 20)'
    	-- 此句的后半部分会执行失败,导致整个事务回滚到保存点pos1,包括insert到A表的操作
    	exec(@sql)
    	if (@@ERROR <> 0)
    	begin
    		print 'SQL2 is error'
    		ROLLBACK TRANSACTION pos1
    		COMMIT TRANSACTION myTran;
    		return
    	end
    print 'both SQL1 and SQL2 ok'
    COMMIT TRANSACTION myTran;
    GO
    
    --操作前面成功,后面部分违法PRIMARY KEY 约束而失败
    /*
    	(1 行受影响)
    
    	(1 行受影响)
    
    	(1 行受影响)
    	消息 2627,级别 14,状态 1,第 1 行
    	违反了 PRIMARY KEY 约束 'PK__B__0EA330E9'。不能在对象 'dbo.B' 中插入重复键。
    	语句已终止。
    	SQL2 is error
    
    */
    
    
    --检查表中的数据情况,还是和原来的一模一样:A表和B表都没有插入记录
    select * from A
    select * from B
    /*
    	ID          Age
    	----------- -----------
    
    	(0 行受影响)
    
    	ID          Age
    	----------- -----------
    	2           20
    
    	(1 行受影响)
    */
    
    --删除掉B表中的数据
    truncate table B
    
    
    --再次执行上面的事务操作得到如下提示:两个语句都成功执行
    /*
    	(1 行受影响)
    
    	(1 行受影响)
    
    	(1 行受影响)
    
    	(1 行受影响)
    	both SQL1 and SQL2 ok
    */
    
    
    --再次检查表中的数据情况,两个表中都有想要的数据了
    select * from A
    select * from B
    /*
    	ID          Age
    	----------- -----------
    	1           10
    	2           20
    
    	(2 行受影响)
    
    	ID          Age
    	----------- -----------
    	1           10
    	2           20
    
    	(2 行受影响)
    */
    • 已标记为答案 steven_lsy 2009年10月15日 1:27
    2009年10月14日 16:02
    版主
  • 谢谢老师帮助
    2009年10月15日 1:28
  • 别客气,“老师”就不敢当了,大家相互学习嘛。

    2009年10月15日 4:48
    版主
  • 但是放在try块中时,貌似事务变不再起作用。。
    2009年10月15日 6:12
  • 用显式事务结合保存点可以满足你的需要。以下是我的测试过程:

    --设置环境
    
    SET
     QUOTED_IDENTIFIER OFF
    ;
    GO
    SET
     NOCOUNT
     OFF
    ;
    GO
    --创建测试表
    
    CREATE
     TABLE
     A(ID INT
     PRIMARY
     KEY
    ,Age int
     NOT
     NULL
    );
    GO
    CREATE
     TABLE
     B(ID INT
     PRIMARY
     KEY
    ,Age int
     NOT
     NULL
    );
    GO
    
    --故意在B表插入一条数据,导致后面事务违反PRIMARY KEY 约束,达到测试效果
    
    insert
     into
     B(ID,Age) values
    (2,20)
    
    --A表无记录,B表有一条记录
    
    select
     * from
     A
    select
     * from
     B
    /*
    	ID          Age
    	----------- -----------
    
    	(0 行受影响)
    
    	ID          Age
    	----------- -----------
    	2           20
    
    	(1 行受影响)
    */
    
    
    
    --事务操作
    
    begin try
    declare @sql nvarchar (4000); BEGIN TRANSACTION myTran SAVE TRANSACTION pos1 select @sql='INSERT INTO A VALUES (1, 10);INSERT INTO A VALUES (2, 20)' -- 此句应该可以成功执行 exec (@sql) if (@@ERROR <> 0) begin print 'SQL1 is error' ROLLBACK TRANSACTION pos1 COMMIT TRANSACTION myTran return end select @sql='INSERT INTO B VALUES (1, 10);INSERT INTO B VALUES (2, 20)' -- 此句的后半部分会执行失败,导致整个事务回滚到保存点pos1,包括insert到A表的操作 exec (@sql) if (@@ERROR <> 0) begin print 'SQL2 is error' ROLLBACK TRANSACTION pos1 COMMIT TRANSACTION myTran; return end print 'both SQL1 and SQL2 ok' COMMIT TRANSACTION myTran; GO
    end try
    begin catch
    end catch

    2009年10月15日 6:14
  • begin try 
       --原来的代码
    end try
    begin catch
       print 'catch'
       ROLLBACK TRANSACTION pos1
    end catch
    
    2009年10月15日 12:18
    版主
  • 用try结构了,@@ERROR就没什么意思了
    2009年10月15日 12:19
    版主
  • 谢谢哈,有点开窍了
    2009年10月15日 12:45
  • begin try
       --原来的代码
    end try
    begin catch print 'catch'
       ROLLBACK TRANSACTION pos1
       COMMIT TRANSACTION myTran;
    end catch
    2009年10月15日 14:40
    版主
  • 你试过了么?我这里是可以的
    2009年10月22日 3:38
  • 很好过多次报道是否还能
    2009年10月23日 5:47