[转]sql server transaction

本文转自：

http://www.2cto.com/database/201208/146734.html

sql事务（Transaction）用法介绍及回滚实例

 

事务（Transaction）是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，SQL Server能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性

 

当对多个表进行更新的时候，某条执行失败。为了保持数据的完整性，需要使用事务回滚。 

显示设置事务

 代码如下

begin try   www.2cto.com  

begin transaction 

insert into shiwu (asd) values ('aasdasda'); 

commit transaction 

end try 

begin catch 

select ERROR_NUMBER() as errornumber 

rollback transaction 

end catch

隐式设置事务

 代码如下

set implicit_transactions on; -- 启动隐式事务 

go 

begin try 

insert into shiwu (asd) values ('aasdasda'); 

insert into shiwu (asd) values ('aasdasda'); 

commit transaction; 

end try 

begin catch 

select ERROR_NUMBER() as errornumber 

rollback transaction; --回滚事务 

end catch 

set implicit_transactions off; --关闭隐式事务 

go

显示事务以下语句不能使用，隐式事务可以

 代码如下

alter database; 

backup;   www.2cto.com  

create database; 

drop database; 

reconfigure; 

restore; 

update statistics;

显示事务可以嵌套使用

 代码如下

--创建存储过程 

create procedure qiantaoProc 

@asd nchar(10) 

as 

begin 

begin try 

begin transaction innerTrans 

save transaction savepoint --创建事务保存点 

insert into shiwu (asd) values (@asd); 

commit transaction innerTrans 

end try 

begin catch 

rollback transaction savepoint --回滚到保存点 

commit transaction innerTrans 

end catch 

end 

go 

begin transaction outrans 

exec qiantaoProc 'asdasd'; 

rollback transaction outrans

事务嵌套，回滚外层事务时，如果嵌套内的事务已经回滚过则会有异常。此时需要使用事务保存点。如下实例

SQL事务回滚

指定当   Transact-SQL   语句产生运行时错误时，Microsoft®   SQL   Server™   是否自动回滚当前事务

方案一：

 代码如下

SET   XACT_ABORT   ON--如果产生错误自动回滚

GO

BEGIN   TRAN

INSERT   INTO   A   VALUES   (4)

INSERT   INTO   B   VALUES   (5)

COMMIT   TRAN  www.2cto.com  

也可以使用_ConnectionPtr 对象的方法： BeginTrans、CommitTrans、RollbackTrans，使用该系列函数判断并回滚。一旦调用了 BeginTrans 方法, 在调用 CommitTrans 或 RollbackTrans 结束事务之前, 数据库将不再立即提交所作的任何更改。

方案二

 代码如下

BEGIN TRANSACTION

INSERT INTO A   values  (4)   ----- 该表含有触发器，UPDATE其他表

IF @@error <> 0  --发生错误

   BEGIN

     ROLLBACK TRANSACTION

         

   END

ELSE

   BEGIN

     COMMIT TRANSACTION

       

   END

 

sql事务结合asp.net两种用法

在sql server+ .net 开发环境下，有两种方法能够完成事务的操作，保持数据库的数据完整性；一个就是用sqlserver/42850.htm target=_blank >sql存储过程，另一个就是在ADO.NET中一种简单的事务处理；现在通过一个典型的银行转账的例子来说明一下这两个例子的用法我们先来看看sql存储过程是如何来完成事务的操作的：首先创建一个表：

 代码如下

create database aaaa --创建一个表，包含用户的帐号和钱数gouse aaaacreate table bb( ID int not null primary key,  --帐号 moneys money    --转账金额）insert into bb values ('1','2000') --插入两条数据insert into bb values ('2','3000')用这个表创建一个存储过程：

create procedure mon --创建存储过程，定义几个变量

@toID int,    --接收转账的账户

@fromID int ,  --转出自己的账户

@momeys money --转账的金额

as

begin tran --开始执行事务

 

update bb set moneys=moneys-@momeys where ID=@fromID -执行的第一个操作，转账出钱，减去转出的金额

update bb set moneys=moneys+@momeys where ID=@toID --执行第二个操作，接受转账的金额，增加  www.2cto.com  

 

if @@error<>0 --判断如果两条语句有任何一条出现错误

begin rollback tran –开始执行事务的回滚，恢复的转账开始之前状态

return 0

end

go

 

else   --如何两条都执行成功

begin commit tran 执行这个事务的操作

return 1

end

go

 

接下来看看C#.net 是如何调用这个存储过程的：

   

 代码如下

protected void Button1_Click(object sender, EventArgs e)

    {

        SqlConnection con =new SqlConnection(@"Data Source=.SQLEXPRESS;database=aaaa;uid=sa;pwd=jcx"); //连接字符串

        SqlCommand cmd = new SqlCommand("mon",con); //调用存储过程

        cmd.CommandType = CommandType.StoredProcedure;

        con.Open();

        SqlParameter prar = new SqlParameter();//传递参数

        cmd.Parameters.AddWithValue("@fromID", 1);

        cmd.Parameters.AddWithValue("@toID", 2);

        cmd.Parameters.AddWithValue("@momeys",Convert.ToInt32( TextBox1.Text) );

   www.2cto.com  

        cmd.Parameters.Add("@return", "").Direction = ParameterDirection.ReturnValue;//获取存储过程的返回值

        cmd.ExecuteNonQuery();

        string value = cmd.Parameters["@return"].Value.ToString();//把返回值赋值给value

        if (value == "1")

        {

            Label1.Text = "添加成功";

        }

        else

        {

            Label1.Text = "添加失败";

       }

}

这个也就是在存储过程里添加事务，再来看看不在数据库写sql存储过程，ADO.NET是如何处理事务的：

 代码如下

protected void Button2_Click(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection(@"Data Source=.SQLEXPRESS;database=aaaa;uid=sa;pwd=jcx");

        con.Open();

        SqlTransaction tran = con.BeginTransaction();//先实例SqlTransaction类，使用这个事务使用的是con 这个连接，使用BeginTransaction这个方法来开始执行这个事务

        SqlCommand cmd = new SqlCommand();

        cmd.Connection = con;

        cmd.Transaction = tran;

        try

        {

             //在try{} 块里执行sqlcommand命令，

            cmd.CommandText = "update bb set moneys=moneys-'" + Convert.ToInt32(TextBox1.Text) + "' where ID='1'";

            cmd.ExecuteNonQuery();

            cmd.CommandText = "update bb set moneys=moneys+' aa ' where ID='2'";

            cmd.ExecuteNonQuery();

            tran.Commit();//如果两个sql命令都执行成功，则执行commit这个方法，执行这些操作

   www.2cto.com  

            Label1.Text = "添加成功";

        }

        catch

        {

            Label1.Text = "添加失败";

            tran.Rollback();//如何执行不成功，发生异常，则执行rollback方法，回滚到事务操作开始之前；

        }

 

    }

这就是两个事务不同用法的简单例子，ADO.NET 事务处理的方法看起来比较简单，但是他要使用同一个连接来执行这些操作，要是同时使用几个数据库来用一个事务执行，这样就比较繁琐，但是要是用sql存储过程，这样就相对比较简单

 

 

http://www.cnblogs.com/fgynew/archive/2011/12/16/2290525.html

仔细研究了下，发现sql server里面的explicit transaction还是有点复杂的。以下是有些总结：

·         Commit transaction 会提交所有嵌套的transaction修改。但是如果嵌套的transaction里面有rollback tran to save point, 那么save point之后的部分会revert掉。

delete from dbo.numbertable

begin tran out1

     insert into dbo.numbertable values(1)

     insert into dbo.numbertable values(2)

    

     begin tran inn1

          insert into dbo.numbertable values(3)

          insert into dbo.numbertable values(4)

     save tran inn1SavePoint

          insert into dbo.numbertable values(5)

     rollback tran inn1SavePoint

     commit tran inn1

commit tran out1

·         @@TRANCOUNT可以用来记录当前session transaction的个数，对于嵌套的transaction来讲，每次begin transaction都让它加一，每次commit tran都会让它减一。所以在语句里面可以通过select @@TRANCOUNT 来检查当前是否在一个transaction里面。如果当前@@TRANCOUNT为0，那调用commit还是rollback都会出现语句错误。在嵌套的transaction里面，rollback是很特殊的，它会直接把@@TRANCOUNT设置为0。

begin tran

begin tran

begin tran

print @@trancount

rollback tran

print @@trancount

·         对于嵌套的transaction来讲，rollback的写法是很特殊。如果嵌套，rollback transaction后面是不能带transaction的name的，要带也只能是最外面的transaction的name。Rollback只会抛弃所有嵌套transaction在rollback语句之前的修改。Rollback之后的更新依然提交就去了，原因在于：rollback之后，@@trancount为0，那么rollback之后的语句就不属于explicit transaction, 属于autocmmit transaction了，自动提交。

delete from dbo.numbertable

begin tran t1

     insert into dbo.numbertable values(1)

    

     begin tran t2

          insert into dbo.numbertable values(2)

     rollback tran

     print 'after rollback in innert transaction, the transaction count is: '+cast(@@trancount, varchar(5))

     insert into dbo.numbertable values(3)

--commit tran

select * from dbo.numbertable

·         存储过程里面也可以begin transaction，如果调用的地方也begin transaction,那么这种情况也属于嵌套transaction，如果在存储过程里面rollback，得到的结果和上面一样。但是有一点特殊的地方在与，执行存储过程结束的时候会比较开始执行sp的@@trancount和结束时候@@trancount的值，如果不一样，它会给出一个消息像“Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.”这个给出的消息并不会影响其后的执行。

CREATE PROCEDURE [dbo].[AddNumber]       

AS

BEGIN

     begin tran

          insert into dbo.numbertable values(1)

          insert into dbo.numbertable values(2)

          insert into dbo.numbertable values(3)

     rollback tran

END

delete from dbo.numbertable

begin tran out1

exec dbo.addnumber

print @@trancount

insert into dbo.numbertable values(3)

select * from dbo.numbertable

·         如果在sp里面rollback了，那到外满做commit， 或者rollback都是没有效果并且出错了，因为嵌套的transaction内部transaction一旦调用了rollback，@@trancount就为0了，在外满commit，rollback直接出错。比如如下sp，我想像在最外面rollback，那就出错了，因为sp里面语句rollback了。表里面始终会插入值3。

delete from dbo.numbertable

begin tran out1

exec dbo.addnumber

print @@trancount

insert into dbo.numbertable values(3)

rollback tran out1

select * from dbo.numbertable

·         所有对于嵌套的transaction来讲，如果内部transaction一旦rollback，就会给外部的transaction留下一个大坑。为了解决这个为题，有两种解决方案：

1.       在外部的transaction里面检查@@trancount，如果这个值跟你代码begin tran的可以一致，那说明内部transaction没有rollback，那可以继续commit或者rollback。

delete from dbo.numbertable

begin tran t1

     insert into dbo.numbertable values(1)

    

     begin transaction t2

          insert into dbo.numbertable values(2)

     rollback tran

 

     if @@trancount = 1

     begin

          insert into dbo.numbertable values(3)

          commit tran

     end

2.       在所有的内部transaction里面，只能commit，不能rollback。如果必须rollback，那怎么办？save point就可以派上用场了。比如sp改成这样子：

ALTER PROCEDURE [dbo].[AddNumber]        

AS

BEGIN

     begin tran

     save tran pp

          insert into dbo.numbertable values(1)

          insert into dbo.numbertable values(2)

          insert into dbo.numbertable values(3)

     rollback tran pp

     commit tran

END

 

begin tran out1

exec dbo.addnumber

print @@trancount

insert into dbo.numbertable values(3)

commit tran out1