关于事务的代码编写注意事项

最新推荐文章于 2022-10-18 08:21:45 发布

原创最新推荐文章于 2022-10-18 08:21:45 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #output #sql server #存储 #数据库 #table

以下内容为自己总结，望讨论更正

一、基础概念

SQL Server 数据库引擎将忽略内部事务的提交。根据最外部事务结束时采取的操作，将提交或者回滚内部事务。如果提交外部事务，也将提交内部嵌套事务。如果回滚外部事务，也将回滚所有内部事务，不管是否单独提交过内部事务。

嵌套事务的程序执行过程应该是：随着事务的执行，嵌套层数由一递增，之后逐渐递减到第一层。整个事务是否提交取决于最后一层是运行Commit（提交）还是Rollback（回滚）。

1、对 COMMIT TRANSACTION的每个调用都应用于最后执行的 BEGIN TRANSACTION。如果嵌套 BEGIN TRANSACTION 语句，那么 COMMIT 语句只应用于最后一个嵌套的事务，也就是在最内部的事务。即使嵌套事务内部的 COMMIT TRANSACTION transaction_name 语句引用外部事务的事务名称，该提交也只应用于最内部的事务。

2、ROLLBACK TRANSACTION 语句的 transaction_name 参数引用一组命名嵌套事务的内部事务是非法的，transaction_name 只能引用最外部事务的事务名称。如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句，那么所有嵌套事务都将回滚。如果在一组嵌套事务的任意级别执行没有 transaction_name 参数的ROLLBACK TRANSACTION 语句，那么所有嵌套事务都将回滚，包括最外部事务。（此段意思表示：只允许嵌套层第一层时使用ROLLBACK回滚）

3、@@TRANCOUNT 函数记录当前事务的嵌套级别。每个 BEGIN TRANSACTION 语句使 @@TRANCOUNT 增加 1。每个 COMMIT TRANSACTION语句使 @@TRANCOUNT 减去 1。

A、没有事务名称的ROLLBACK TRANSACTION 语句将回滚所有嵌套事务，并使 @@TRANCOUNT 减小到 0。

B、使用一组嵌套事务中最外部事务的事务名称的 ROLLBACK TRANSACTION 将回滚所有嵌套事务，并使 @@TRANCOUNT 减小到 0。

C、在无法确定是否已经在事务中时，可以用 SELECT @@TRANCOUNT 确定 @@TRANCOUNT 是等于 1 还是大于 1。如果 @@TRANCOUNT 等于 0，则表明不在事务中。

注：以上内容摘自-- http://msdn.microsoft.com/zh-cn/library/ms189336.aspx

二、示例说明：

1、创建一个表格，用于插入数据的实验

create table TranTestTable(

T datetime

)

2、没有使用事务，程序运行会出错。但第一条语句还是插入了一条记录，第二条没有插入记录。

alter PROC TranTest2(@errorTag int output)

BEGIN

insert into TranTestTable values(getdate())

insert into TranTestTable values('....')

END

3、见下面代码，存在两层事务的嵌套使用，TranTest1嵌套调用TranTest2，根据“一”中的1、2可以得出以下代码。调用TranTest1运行正常，没有插入任何记录。

alter PROC TranTest1(@errorTag int output)

BEGIN

set @errorTag = 0

Begin transaction

declare @errorTag2 int

exec TranTest2 @errorTag2 output ----调用TranTest2存储过程

if @errorTag2<0

BEGIN

set @errorTag = -1

ROLLBACK TRANSACTION

RETURN

END

IF @@TRANCOUNT > 0

COMMIT TRANSACTION

END

--------- TranTest2代码------------

alter PROC TranTest2(@errorTag int output)

BEGIN

set @errorTag = 0

Begin transaction

insert into TranTestTable values(getdate())

insert into TranTestTable values('....')

if @@error <> 0

BEGIN

set @errorTag = -1

COMMIT TRANSACTION

RETURN

END

IF @@TRANCOUNT > 0

COMMIT TRANSACTION

END

4、但TranTest2是一个独立的存储过程，也可能被直接嗲用（外层不嵌套事务），TranTest2被TranTest1调用和直接被调用的区别在于事务嵌套级别不同。直接被调用（不存在嵌套调用）时，此时TranTest2中就是事务的第一层，碰到异常情况应该运行ROLLBACK TRANSACTION使得整个事务回滚；被TranTest1调用（存在嵌套调用）时，TranTest2的层级（@@TRANCOUNT的值）为2，此时出现异常情况应该运行COMMIT TRANSACTION，将嵌套层数减1，并将事务交给上一层处理，最后这个事务是否提交取决于第一层最后是提交还是回滚；

为了使得TranTest2存储过程具有通用性，可以根据嵌套层数决定是调用COMMIT还是ROLLBACK。如果是大于1，说明上面还有事务包着，此时事务应该交给上一层处理。如果是1，则需要回滚整个事务，因为没有其他地方可以将事务回滚了。

alter PROC TranTest2(@errorTag int output)

BEGIN

set @errorTag = 0

Begin transaction

insert into TranTestTable values(getdate())

insert into TranTestTable values('....')

if @@error <> 0

BEGIN

set @errorTag = -1

IF @@TRANCOUNT > 1

COMMIT TRANSACTION