何为自治事务( Autonomous Transaction )?
简单的说,如果在会话中调用一个过程,则此过程会运行在此会话当前的事务作用域中;而自治事务可以让这个过程运行在一个独立的事务作用域中,使用其 不受外部事务(也就是调用此过程的会话的当前事务)的影响,可以被独立的回滚 / 提交。
为了方便理解,举个例子:
use tempdb
go
if object_id ( 'emps' ) is not null
drop table emps;
go
create table emps ( id int identity , names varchar ( 20), salary numeric ( 8, 2));
go
insert into emps( names, salary) values ( 'Tom' , 3000.00);
insert into emps( names, salary) values ( 'Jerry' , 5000.00);
go
if object_id ( 'emp_logs' ) is not null
drop table emp_logs;
go
create table emp_logs ( id int identity , actions varchar ( 20), timestamps datetime , username varchar ( 128));
go
emps 表用于记录员工信息, emp_logs 表用于记录对 emps 的修改动作。
如果现在要记录所有对 emps 表上 salary 列的修改操作,即使此操作没有成功提交。为了实现此功能,可以创建一个触发器用于捕获 update 语句的执行,
create trigger trg_emps_update_salary
on emps for update
as
if update ( salary)
insert into emp_logs( actions, timestamps, username) values ( 'UPDATE SALARY' , getdate (), suser_name ());
go
正常情况下,这个触发器没有任何问题。但是,如果 update 语句被回滚或没有被成功提交,触发器将不会向 emp_logs 表中插入任何记录,这是因为触发器和激发此触发器的 update 语句都位于一个事务作用域中,总是被一起提交 / 回滚。
那么,在触发器中使用显式事务( begin tran insert into ... commit tran )怎么样?同样不行,因为如同 SQL Server 的嵌套事务,只有最外层的 commit tran 语句才会真正提交之前的所有修改,而在内层事务中的 commit tran (触发器中的)将不会起任何作用。
解决方法:
我们知道,在 ORACLE 中,可以在触发器中指定 PRAGMA AUTONOMOUS_TRANSACTION 选项让其支持自治事务。那么,在 SQL Server 下要如何处理?
这里提供了两种方法:
其一, 通过 loopback 服务器链接执行远过程调用, 让此过程拥有独立的事务作用域(适用于 SQL Server 2008 )。具体步骤,
建立 LOOPBACK 服务器链接
EXEC sp_addlinkedserver @server = N'loopback' , @srvproduct = N' ' , @provider = N'SQLNCLI' , @datasrc = @@SERVERNAME
设置服务器链接选项,阻止 SQL Server 由于远过程调用而将本地事务提升为分布事务 (重点)
EXEC sp_serveroption loopback, N'rpc out' , 'TRUE'
EXEC sp_serveroption loopback, N'remote proc transaction promotion' , 'FALSE'
将 insert 语句打包为一个过程
use tempdb
go
create procedure usp_insert_emplogs
as
insert into emp_logs( actions, timestamps, username) values ( 'UPDATE SALARY' , getdate (), suser_name ());
go
在触发器中通过 LOOPBACK 服务器链接调用此存储过程
create trigger trg_emps_update_salary
on emps for update
as
if update ( salary)
exec loopback. tempdb . dbo. usp_insert_emplogs;
go
现在,测试一下吧
begin tran
update emps set salary=salary*1.1 where id=1;
rollback tran
其二 , 通过 CLR 存储过程实现(适用于 SQL Server 2005 及以上版本),通过 loopback 连接让 CLR 过程运行在一个独立的事务作用域下。具体步骤,
创建 CLR 运行库(AutoTran.dll )
/*
编译命令(System.Transactions 需要添加对 System.Transactions.dll 库文件的引用)
csc.exe /r:c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/System.Transactions.dll /target:library AutoTran.cs
*/
using System;
using System. Data. SqlClient;
using System. Data. SqlTypes;
using Microsoft. SqlServer. Server;
using System. Transactions;
public class AutoTran
{
[SqlProcedure]
public static void ExecuteATStmt(SqlString stmt, SqlString dbname)
{
// 生成新的事务(重点)
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.RequiresNew ))
{
// 创建到数据库的 loopback 连接(重点)
using (SqlConnection conn = new SqlConnection("server=.;integrated security=true;database=master;enlist=false" ))
{
conn.Open();
conn.ChangeDatabase((string)dbname);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = (string) stmt;
cmd.ExecuteNonQuery();
}
trans.Complete();
}
}
}
在 SQL Server 上创建对应的存储过程
use master
go
-- 启用clr 服务器选项
exec sp_configure 'show advanced options' , 1;
go
reconfigure
go
exec sp_configure 'clr enabled' , 1;
go
reconfigure
go
-- 启用 master 数据库信任,以允许创建 external_access 权限的程序集
alter database master set trustworthy on ;
go
if object_id ( 'dbo.ExecuteATStmt' ) is not null
drop procedure dbo. ExecuteATStmt;
go
-- 创建程序集
if exists ( select * from sys . assemblies where name= 'AutoTran' )
drop assembly AutoTran;
go
create assembly AutoTran authorization dbo
from 'C:/Database/Assembly/AutoTran.dll'
with permission_set = external_access ;
go
-- 创建存储过程
create procedure dbo. ExecuteATStmt
@stmt nvarchar ( max ), @dbname nvarchar ( 128)= 'master'
as external name AutoTran. AutoTran. ExecuteATStmt;
go
在触发器中调用此存储过程
create trigger trg_emps_update_salary
on emps for update
as
declare @stmt nvarchar ( 4000);
set @stmt = N'insert into emp_logs(actions,timestamps,username)' +
N' values(''UPDATE SALARY'',' + quotename ( convert ( nvarchar ( 20), getdate (), 120), '''' )+
N',' + quotename ( suser_name (), '''' )+ N')' ;
if update ( salary)
exec master . dbo. ExecuteATStmt @stmt, N'tempdb' ;
go
此 CLR 过程主要用于演示 SQL Server 实现自治事务的方法,因此存在了几点 遗憾:
ExecuteATStmt 存储过程的安全性是个问题。其创建的 loopback 连接运行在 SQL Server 服务进程的安全上下文中,就是说用户只要具有此过程的执行权限,就可以为所欲为。因此,如果要在实践中使用,需要在 SQL Server 中创建一个特定的登陆帐户用于创建 LOOPBACK 连接。更安全的、更简单的解决方法已经有了,不过一个还有 bug ,一个有局限(要求 windows 身份验证),以后会放出。
这里我尽量将ExecuteATStmt 存储过程向 sp_executesql 系统过程靠近,让其可以执行任何 T-SQL 语句。但是ExecuteATStmt 不能向执行语句中传递参数,不能返回 SELECT 结果集。对于前一个问题,可以在拼接 SQL 语句时处理;对于后一个问题,个人认为没有什么太大的影响,毕竟事务中主要执行的是修改操作。
ExecuteATStmt 存储过程中创建的事务默认运行在 SERIALIZE 事务隔离级别下,如果有需要可以修改此默认的事务隔离级别。