SQL Server 自治事务

何为自治事务( 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 事务隔离级别下,如果有需要可以修改此默认的事务隔离级别。

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值