事务嵌套


SQL Server中的嵌套事务与@@TranCount
<wbr></wbr>
<wbr>在处理事务的时候,一般都用RollBack Transaction来回滚,但是如果在嵌套事务中这样使用的话,就会出现错误。</wbr>
<wbr></wbr>
在SqlServer里,嵌套事务的层次是由@@TranCount全局变量反映出来的。每一次Begin Transaction都会引起@@TranCount加1。而每一次Commit Transaction都会使@@TranCount减1,而RollBack Transaction会回滚所有的嵌套事务包括已经提交的事务和未提交的事务,而使@@TranCount置0。例如: <wbr>www.2cto.com <wbr></wbr></wbr>
<wbr></wbr>
Begin Transaction -- @@TranCount = 1
<wbr><wbr><wbr><wbr><wbr>BeginTransaction -- @@TranCount = 2 <wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> BeginTransaction -- @@TranCount = 3</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> Commit Transaction -- @@TranCount = 2</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>Commit Transaction -- @@TranCount = 1</wbr></wbr></wbr></wbr></wbr>
Commit Transaction -- @@TranCount = 0
<wbr></wbr>
如果出现错误ROLLBACK TRANSACTION<wbr></wbr>
则: <wbr>www.2cto.com <wbr></wbr></wbr>
<wbr></wbr>
Begin Transaction -- @@TranCount = 1
<wbr><wbr><wbr><wbr><wbr>BeginTransaction -- @@TranCount = 2 <wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> BeginTransaction -- @@TranCount = 3</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> ROLLBACK TRANSACTION <wbr>-- @@TranCount = 0</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>Commit Transaction -- @@TranCount = 0---出现错误</wbr></wbr></wbr></wbr></wbr>
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
<wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>如果被嵌套的事务中发生错误,最简单的方法应该是无论如何都先将它提交,同时返回错误码(一个正常情况不可能出现的代码 如 -1)让上一层事务来处理这个错误,从而使@@TranCount 减1。 这样外层事务在回滚或者提交的时候能够保证外层事务在开始的时候和结束的时候保持一致。由于里层事务返回了错误码,因此外层事务(最外层)可以回滚事务,这样里面已经提交的事务也可以被回滚而不会出现错误。</wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>在项目中应该会常常出现这样的情况,一个存储过程里面用了事务,但是不能保证它会被别的带有事务的存储过程调用,如果单独调用的话,出现错误可以直接回滚,但是如果是被别的带事务的存储过程调用的话,RollBack 就会出错了。因此需要一种机制来区分,建立一个临时的变量来区分是否嵌套,和嵌套的层数,如下:</wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
DECLARE @TranCounter INT;
<wbr><wbr> SET @TranCounter = @@TRANCOUNT;</wbr></wbr>
<wbr><wbr> IF @TranCounter &gt; 0</wbr></wbr>
<wbr><wbr><wbr><wbr> SAVE TRANSACTION ProcedureSave;</wbr></wbr></wbr></wbr>
<wbr><wbr> ELSE</wbr></wbr>
<wbr><wbr><wbr><wbr> BEGIN TRANSACTION;</wbr></wbr></wbr></wbr>
…………

--事务内要执行的代码
…………
IF @@ERROR<>0
goto Error
<wbr>IF @TranCounter = 0</wbr>
Commit Transaction
--下面返回要返回的值0只是个例子
Return 0<wbr></wbr>
<wbr></wbr>
Error:
<wbr><wbr><wbr>IF @TranCounter = 0</wbr></wbr></wbr>
<wbr><wbr><wbr><wbr>ROLLBACK TRANSACTION;</wbr></wbr></wbr></wbr>
<wbr><wbr> Else <wbr></wbr></wbr></wbr>
<wbr></wbr>
<wbr><wbr><wbr>ROLLBACK TRANSACTION ProcedureSave;</wbr></wbr></wbr>
<wbr></wbr>
<wbr><wbr>Return @Error</wbr></wbr>


//=================================================================================================

对于带有事务的嵌套存储过程的处理网上的确有很多资料可参考。但大多是片面或过于字面化。对不清楚如何解决这种问题的同仁来说没有多大用处。最近正好工作不忙,就研究了下,发现sqlserver的嵌套事务存储过程在处理时确实有一定难度。原因在于微软的sqlserver在带有嵌套的事务方面存在以下几点特征:

<wbr></wbr>

1。 sqlserver 忽略提交内部事务。即无论内层事务是否提交,外层事务提交后,所有内层事务都会提交,反之外层事务回滚了,内层事务即使提交过,也会回滚。以外层事务的最后执行的操作(commit or rollback)为准。

<wbr></wbr>

2。每个commit的调用都只对应最后执行的begin transaction。意思就是每个commit对应与自己开始的begin transaction。打个比方:内层commit对应的是内层begin<wbr><wbr>transaction,而不会影响外层。即使有更多层的嵌套也是如此。</wbr></wbr>

<wbr></wbr>

3。rollback transaction 中的 transaction_name (事务名)只能引用最外层的transaction_name。引用内层或更多嵌套内层的事务名是不允许的。比如:内层begin transaction xx(事务名),内层rollback transaction 时不能引用xx,而只能引用最外层的事务名。

<wbr></wbr>

4。@@trancount记录事务的嵌套级数。每begin transaction一次 都会使@@trancount加1,每commit transaciton一次都会使@@trancount减1,而rollback transaction 则会直接使@@trancount为0,无论是嵌套事务还是单一事务。可通过检查@@trancount是否为0来确实是否还在事务中,大于0表明还在事务中。

<wbr></wbr>

由于以上几的特征的限制,使得嵌套的事务处理不那么容易。单一的事务存储过程是很好解决的。但如果需要调用已经存在的存储过程且自身已有事务处理了,那么问题就出现了(二层嵌套事务的存储过程执行结果):

原因在于:每个事务在开始与提交时@@trancount要对应,如果开始事务的@@trancount与commit时的@@trancount不对应,就会出现以上的问题。说白了就是开始事务时@@trancount为1 ,那么在commit时也要为1,commit之后才会使@@trancount减1。

要解决嵌套事务的存储过程,避免以上的问题,个人总结两个方式:

1。使用@@trancount<wbr> + 事务保存点 解决。 外层事务传递当前@@trancount值给内层事务,根据传递过来的@@trancount值是否大于0来选择是否保存事务点。大于0则保存,用于内层回滚到此事务点,等于0说明没开始事务,则直接开始内层的事务处理。内层事务处理中如果有错误,则根据传递过来的@@trancount值判断是回滚整个事务还是回滚到事务的保存点。</wbr>

<wbr></wbr>

2。使用事务保存点 + rollback transactin 事务保存点 + 强制内层事务提交 解决。 此方式与1差别不多大,只是不使用@@trancount全局函数。内层事务开始后立即保存事务点。出错后直接回滚到事务保存点,再交由外层的事务来决定最后的执行操作。

<wbr></wbr>

以下是第一种方式的sql脚本的详细过程:

<wbr></wbr>

--测试用的数据库test
use test;
GO

--删除测试用的表
if<wbr> EXISTS<br> (<br><wbr>SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'AgeValues')<br> )</wbr></wbr>

begin

<wbr>DROP TABLE AgeValues;</wbr>

end
GO

--内层存储过程已存在则先删除再创建
IF exists
(
<wbr>SELECT * from sys.objects WHERE object_id = object_id(N'InnerProcedure')<br> )</wbr>

begin

<wbr>drop PROCEDURE InnerProcedure;</wbr>

end

--外层存储过程已存在则先删除再创建
IF exists
(
<wbr>SELECT * from sys.objects where object_id = object_id(N'OuterProcedure')<br> )</wbr>

begin

<wbr>drop PROCEDURE OuterProcedure;</wbr>

end


--创建测试用的表
CREATE table AgeValues(age int not null check (age < 200));
GO

--设置不返回受影响的行数
SET NOCOUNT on;
GO

<wbr></wbr>

<wbr></wbr>

<wbr></wbr>

--内层事务存储过程,可直接执行,也可被其它带有事务的存储过程调用
create procedure InnerProcedure
(
<wbr>@TranCount int,<wbr><wbr> --当前@@trancount值<br><wbr><br><wbr>@in_AgeValue int, --年龄<br><wbr><br><wbr>@out_Result varchar(500) output --结果(OK正确,其它错误)<br> )</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

as

begin

<wbr>begin try<wbr>--开始捕获异常<br><wbr><br><wbr><wbr>print ('开始内层事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><br><wbr><wbr>begin transaction InnerTran --开始事务<br><wbr><br><wbr><wbr>save transaction InnerTranPoint --保存事务点,用于回滚<br><wbr><wbr><br><wbr><wbr>print ('开始内层事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><br><wbr><wbr>print ('开始执行InnerProcedure存储过程的数据处理......');<br><wbr><wbr><br><wbr><wbr>insert INTO AgeValues VALUES(@in_AgeValue);<br><wbr><wbr><br><wbr><wbr>IF @@error &lt;&gt; 0 --如果执行有错误,则@@error显示错误码,无错误时显示0<br><wbr><wbr><br><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr>if @TranCount &gt; 0<wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>set @out_Result = '执行出错,错误码为:' + cast(@@error AS VARCHAR);<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行回滚事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>rollback transaction InnerTranPoint; --回滚到事务的保存点<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行回滚事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行提交事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>commit transaction InnerTran;<wbr>--把当前的事务提交<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('被其它事务存储过程调用时,在执行提交事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>end<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr>else if @TranCount = 0<br><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>set @out_Result = '执行出错,错误码为:' + cast(@@error AS VARCHAR);<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('直接调用时,在执行回滚事务前,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>rollback transaction InnerTran;<br><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr>print ('直接调用时,在执行回滚事务后,@@trancount计数为:' + cast(@@trancount as varchar));<br><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr>end<br><wbr><wbr><wbr><br><wbr><wbr><wbr>end<br><wbr><wbr><wbr><br><wbr><wbr>else<wbr>--没有错误时则直接提交当前事务,并设置返回信息为1<br><wbr><wbr><br><wbr><wbr><wbr>begin<br><wbr><wbr><wbr><br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

### 事务嵌套的使用方法与行为 数据库中的 **nested transactions**(嵌套事务)是一种允许在一个事务内部启动另一个事务的功能。这种机制通常用于复杂的业务逻辑场景,其中某些操作可能需要独立提交或回滚而不影响外部事务的状态。 #### 嵌套事务的行为特点 1. 外部事务控制整体状态,而内部事务可以具有局部作用域[^2]。 2. 如果支持真正的嵌套事务,则只有当所有子事务都成功完成时,整个事务才会被标记为成功并最终提交[^3]。 3. 子事务的 `COMMIT` 并不会立即生效;它仅表示该部分工作已完成,但仍需等待父事务的整体提交才能永久化更改[^4]。 4. 若某个子事务失败并执行 `ROLLBACK`,则其修改会被撤销,但不影响其他已成功的兄弟子事务或父事务继续运行[^5]。 #### SQL Server 中的实现方式 在 Microsoft SQL Server 数据库管理系统里,通过设置 SAVEPOINT 来模拟嵌套事务的效果。SAVEPOINT 定义了一个恢复点,在此之后所做的任何变更都可以单独撤消,而无需终止整个大范围内的活动记录更新过程: ```sql BEGIN TRANSACTION OuterTran; -- Some operations here... BEGIN TRANSACTION InnerTran; INSERT INTO ExampleTable (ColumnA) VALUES ('Value'); IF @@ERROR <> 0 ROLLBACK TRANSACTION InnerTran; COMMIT TRANSACTION InnerTran; -- More actions... IF AllGood = 1 COMMIT TRANSACTION OuterTran; ELSE ROLLBACK TRANSACTION OuterTran; ``` 尽管如此,请注意并非所有的 DBMS 都完全支持标准定义下的多层次结构化的交易管理功能——比如 MySQL 默认情况下不提供严格意义上的 nested transaction 支持[^6]。 #### Oracle 的 Savepoint 方法 Oracle 提供 savepoints 作为处理复杂事务的一种手段。Savepoints 让程序员能够在长事务期间创建检查点,从而使得能够有选择性地回退到这些特定位置而不是放弃全部未保存的工作成果: ```plsql SET AUTOCOMMIT OFF; DECLARE v_error EXCEPTION; BEGIN INSERT INTO employees (...); SAVEPOINT before_bonus_update; UPDATE bonuses SET bonus=bonus*1.1 WHERE employee_id=... ; RAISE v_error; -- Simulate an error condition EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT before_bonus_update; END; / COMMIT; ``` 以上脚本展示了如何利用 PL/SQL 编程语言结合异常处理器来构建更加健壮的应用程序逻辑流控方案[^7]。 ### 注意事项 - 不同的关系型数据库产品对于嵌套事务的支持程度存在差异,开发人员应当查阅所使用的具体产品的官方文档获取最精确的信息。 - 使用不当可能导致死锁或者资源争用等问题,因此建议谨慎设计涉及多个并发访问路径的数据操纵流程[^8]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值