SQLServer之创建显式事务

显式事务定义

显式事务以 begin transaction 语句开始，并以 commit 或 rollback 语句结束。

备注

begin transaction 使 @@trancount 按 1 递增。

begin transaction 代表一点，由连接引用的数据在该点逻辑和物理上都一致的。 如果遇上错误，在 begin transaction 之后的所有数据改动都能进行回滚，以将数据返回到已知的一致状态。 每个事务继续执行直到它无误地完成并且用 commit transaction 对数据库作永久的改动，或者遇上错误并且用 rollback transaction 语句擦除所有改动。

begin transaction 为发出本语句的连接启动一个本地事务。 根据当前事务隔离级别的设置，为支持该连接所发出的 transact-sql 语句而获取的许多资源被该事务锁定，直到使用 commit transaction 或 rollback transaction 语句完成该事务为止。 长时间处于未完成状态的事务会阻止其他用户访问这些锁定的资源，也会阻止日志截断。

虽然 begin transaction 启动一个本地事务，但是在应用程序接下来执行一个必须记录的操作（如执行 insert、update 或 delete 语句）之前，它并不被记录在事务日志中。 应用程序能执行一些操作，例如为了保护 select 语句的事务隔离级别而获取锁，但是直到应用程序执行一个修改操作后日志中才有记录。

在一系列嵌套的事务中用一个事务名给多个事务命名对该事务没有什么影响。 系统仅登记第一个（最外部的）事务名。 回滚到其他任何名称（有效的保存点名除外）都会产生错误。 事实上，回滚之前执行的任何语句都不会在错误发生时回滚。 这些语句仅当外层的事务回滚时才会进行回滚。

如果在语句提交或回滚之前执行了如下操作，由 begin transaction 语句启动的本地事务将升级为分布式事务：

• 执行一个引用链接服务器上的远程表的 insert、delete 或 update 语句。 如果用于访问链接服务器的 ole db 访问接口不支持 itransactionjoin 接口，则 insert、update 或 delete 语句会失败。

• 当启用了 remote_proc_transactions 选项时，将调用远程存储过程。

  sql server 的本地副本成为事务控制器并且使用 microsoft 分布式事务处理协调器 (ms dtc) 来管理分布式事务。

  使用 begin distributed transaction 可以将事务作为分布式事务显式执行。 有关详细信息，请参阅 begin distributed transaction (transact-sql)。

  set implicit_transactions 设置为 on 时，begin transaction 语句创建两个嵌套的事务。 有关详细信息，请参阅 set implicit_transactions (transact-sql)

标记的事务

with mark 选项使事务名被置于事务日志中。 将数据库还原到早期状态时，可使用标记事务代替日期和时间。 有关详细信息，请参阅 使用标记的事务一致地恢复相关的数据库的事务（完全恢复模式）和 restore (transact-sql)。

另外，若要将一组相关数据库恢复到逻辑上一致的状态，必须使用事务日志标记。 标记可由分布式事务置于相关数据库的事务日志中。 将这组相关数据库恢复到这些标记将产生一组在事务上一致的数据库。 在相关数据库中放置标记需要特殊的过程。

只有当数据库由标记事务更新时，才在事务日志中放置标记。 不修改数据的事务不被标记。

使用t-sql脚本创建显式事务

语法：

--声明数据库引用
use 数据库名称;
go

begin { tran| transaction } [ { transaction_name | @tran_name_variable } [ with mark [ 'description' ] ]
begin
业务代码1;

save  { tran | transaction } { savepoint_name | @savepoint_variable };

rollback { tran | transaction } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ];

commit { tran | transaction }  { transaction_name | @tran_name_variable } with(delayed_durability=on);

end
go

语法解析：

--begin transaction transaction_name
--适用范围：sql server（从 2008 版开始）和 azure sql database
--分配给事务的名称。 transaction_name 必须符合标识符规则，但标识符所包含的字符数不能大于 32。 仅在最外面的 begin...commit 或 begin...rollback 嵌套语句对中使用事务名。
--transaction_name 始终区分大小写，即使 sql server 实例不区分大小写也是如此。

--begin transaction @tran_name_variable 
--适用范围：sql server（从 2008 版开始）和 azure sql database
--用户定义的、含有有效事务名称的变量的名称。 必须使用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。 如果传递给该变量的字符多于 32 个，则仅使用前面的 32 个字符；其余的字符将被截断。

--with mark [ 'description' ]
--适用范围：sql server（从 2008 版开始）和 azure sql database
--指定在日志中标记事务。 description 是描述该标记的字符串。 在将长于 128 个字符的 description 存储到 msdb.dbo.logmarkhistory 表中之前，先将其截断为 128 个字符。
--如果使用了 with mark，则必须指定事务名。 with mark 允许将事务日志还原到命名标记。

--save transaction savepoint_name
--分配给保存点的名称。 保存点名称必须符合标识符的规则，但长度不能超过 32 个字符。 savepoint_name 始终区分大小写，即使 sql server 实例不区分大小写也是如此。

--save transaction @savepoint_variable 
--包含有效保存点名称的用户定义变量的名称。 必须使用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。 如果长度超过 32 个字符，也可以传递到变量，但只使用前 32 个字符。

--rollback { tran | transaction } [ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ];
--将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。 可以使用 rollback transaction 清除自事务的起点或到某个保存点所做的所有数据修改。 它还释放由事务控制的资源。

--commit { tran | transaction } { transaction_name | @tran_name_variable };
--标志一个成功的隐性事务或显式事务的结束。 如果 @@trancount 为 1，commit transaction 使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放事务所占用的资源，并将 @@trancount 减少到 0。
--如果 @@trancount 大于 1，则 commit transaction 使 @@trancount 按 1 递减并且事务将保持活动状态。

--delayed_durability
--适用范围：sql server 和 azure sql 数据库
--请求将此事务与延迟持续性一起提交的选项。 如果已用 delayed_durability = disabled 或 delayed_durability = forced 更改了数据库，则忽略该请求。 有关详细信息，请参阅主题控制事务持续性。

示例：

--声明数据库引用
use testss;
go

begin transaction explicittran
with mark '开启一个显式事务'
begin
declare @counts int =0;

insert into test1(name,sex,age,classid,height) values('事务测试','男','21','20','178');
set @counts=@counts+(select @@rowcount);
save tran inserttran1;--回滚点一，事务已经插入了一条数据

insert into test2(name) values('事务测试');
set @counts=@counts+(select @@rowcount);
save tran inserttran2;--回滚点二，事务已经插入了二条数据

insert into test3(name,sex,age,classid) values('事务班','男','asd','23');
set @counts=@counts+(select @@rowcount);
save tran inserttran3;--回滚点三，事务已经插入了三条数据

--出错回滚到回滚点
if @counts=3
rollback transaction inserttran3;
else if @counts=2
rollback transaction inserttran2;
else if @counts=1
rollback transaction inserttran1;

--提交事务或者回滚事务
if @counts<>0
commit transaction explicittran
--with(delayed_durability=on)
;
else
rollback transaction explicittran;
end
go

示例结果：