分类： WINDOWS
2012-04-16 22:46:02
何为自治事务（ 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 sql, SqlString dbname, SqlInt16 iso)
{
// 创建不加入环境事务的事务作用域
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Suppress))
{
using (SqlConnection conn = new SqlConnection("context connection=true" ))
{
conn.Open();
// 获取过程调用者的安全上下文
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select suser_name()" ;
string loginame = (string )cmd.ExecuteScalar();
// 创建 LOOPBACK 连接（禁止使用连接池）
using (SqlConnection connAT =
new SqlConnection("server=.;integrated security=true;database=master;pooling=false" ))
{
connAT.Open();
connAT.ChangeDatabase((string )dbname);
SqlCommand cmdAT = connAT.CreateCommand();
// 定义自治事务的事务隔离等级
string level;
switch ((Int16 )iso)
{
case 1:
level = "read uncommitted" ;
break ;
case 3:
level = "repeatable read" ;
break ;
case 4:
level = "serializable" ;
break ;
case 5:
level = "snapshot" ;
break ;
default :
level = "read committed" ;
break ;
}
cmdAT.CommandText = "set transaction isolation level " + level;
cmdAT.ExecuteNonQuery();
/ / 将 CLR 存储过程运行的安全上下文切换到调用者的，从而避免 CLR 过程运行在 SQL Server 服务进程的安全上下文中
cmdAT.CommandText = "execute as login = '" + loginame + "' with no revert" ;
cmdAT.ExecuteNonQuery();
// 执行 SQL 语句
cmdAT.CommandText = (string )sql;
cmdAT.ExecuteNonQuery();
}
}
trans.Complete();
}
}
}
CLR 存储过程代码：
use master
go
exec sp_configure 'show advanced options' , 1;
go
reconfigure
go
exec sp_configure 'clr enabled' , 1;
go
reconfigure
go
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:/Devs/Projects/clrAutoTran/clrAutoTran/bin/Release/clrAutoTran.dll'
with permission_set= external_access;
go
create procedure dbo. ExecuteATStmt
@stmt nvarchar ( max ), @dbname nvarchar ( 128)= 'master' , @iso smallint = 2
as external name AutoTran. AutoTran. ExecuteATStmt;
go
演示：
use tempdb
go
if object_id ( 'emps' ) is not null
drop table emps;
go
create table emps ( id int identity , fname varchar ( 20), salary numeric ( 8, 2));
go
insert into emps ( fname, salary) values ( 'Tom' , 4000);
insert into emps ( fname, salary) values ( 'Jerry' , 6000);
go
if object_id ( 'emp_logs' ) is not null
drop table emp_logs;
go
create table emp_logs ( id int identity , loginame sysname , spid int , stmt nvarchar ( 4000));
go
-- 触发器用于捕获 / 记录所用对 EMPS 表 salary 列的更新操作
if object_id ( 'trg_emps_update' ) is not null
drop trigger trg_emps_update;
go
create trigger trg_emps_update on emps
with execute as owner 、
for update as
set nocount on ;
if update (salary)
begin
declare @event table ( EventType nvarchar ( 30), Parameters int , EventInfo nvarchar ( 4000));
insert into @event exec ( 'dbcc inputbuffer(@@spid) with no_infomsgs' );
declare @stmt nvarchar ( max );
set @stmt= N'insert into emp_logs(loginame,spid,stmt) ' +
N'select ''' + original_login()+ ''',' + ltrim ( @@spid )+ ',' ;
select @stmt= @stmt+ '''' + replace ( EventInfo, '''' , '''''' )+ '''' from @event;
exec master. dbo. ExecuteATStmt @stmt, 'tempdb' ;
end
go
-- 测试，即使 UPDATE 动作被回滚，也会产生记录
begin tran
declare @salary varchar ( 8);
set @salary= '10000' ;
update emps set salary= salary;
rollback tran
select * from emp_logs;
