交易范围 +审核表 +在存储过程中提交事务

Question

我的公司正在使用 SQL Server 2008。我面临着一个存在事务问题的审计表。

我有一个存储过程。

create proc MySP
as begin
    insert into MY_TABLE values('Value1');

    begin transaction TX_MY_SP
    -- write to audit table permanently
    insert into AUDIT_TABLE values('Value1 is inserted.'); 
    commit transaction TX_MY_SP
end

我有一段 VB.net 代码

Using tx = New TransactionScope()
    Using conn = New SqlConnection(MY_CONN_STR)
        Using cmd = New SqlCommand("MySP", conn)
            conn.Open()
            cmd.ExecuteNonQuery()
            Throw New ApplicationException("Always throw exception.")
        End Using
    End Using
    tx.Complete()
End Using

，但是没有记录插入到 AUDIT_TABLE 中。我在MSDN http://msdn.microsoft.com/en-us 中找到了原因/library/ms189336.aspx

我的问题是如何使用存储过程插入记录 AUDIT_TABLE 。

谢谢！

Answer 1

基本上，您可以做的就是拥有一个异步审核/日志系统。
因此，您的审计将在不同的线程上运行，并且主事务范围是否失败并不重要。

1. 使用企业库+MSMQ
2. 构建自己的轻量级异步日志系统（使用同步队列）

Answer 2

事务内任何回滚的操作也会回滚。不这样做会破坏事务原子性。鉴于您正在审核的活动正在被回滚，您很可能实际上希望审核被回滚。

尽管如此，在某些合法情况下，需要在当前事务范围之外记录操作，例如某些调试情况。有一些已知的解决方法，例如对 用户可配置事件类，然后使用sp_trace_generateevent 引发事件通知 激活程序来运行和记录审核。由于探查器事件是在事务范围之外生成的，因此审计记录不会回滚。

:setvar dbname testdb
:on error exit

set nocount on;
use master;

if exists (
    select * from sys.server_event_notifications
    where name = N'audit')
begin
    drop event notification audit on server;
end 
go

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end
go

create database [$(dbname)];
go

alter authorization on database::[$(dbname)] to [sa];
go

use [$(dbname)];
go

create queue audit;
create service audit on queue audit (
    [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go  

create table audit_table (
    Time datetime not null,
    TextData nvarchar(256) not null);
go

create procedure usp_audit
as
begin
declare @h uniqueidentifier, @mt sysname, @mb varbinary(max), @mx xml;
begin transaction;
receive top(1) @h = conversation_handle,
    @mt = message_type_name,
    @mb = message_body
from audit;
if (@mt = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
begin
    select @mx = cast(@mb as xml);
    insert into audit_table (Time, TextData)
    values (
        @mx.value(N'(/EVENT_INSTANCE/PostTime)[1]', N'datetime'),
        @mx.value(N'(/EVENT_INSTANCE/TextData)[1]', N'nvarchar(256)'));
end
else if (@mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
    or @mt = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
begin
    end conversation @h;
end 
commit
end 
go

alter queue audit
    with activation (
        status = on,
        procedure_name = usp_audit,
        max_queue_readers = 1,
        execute as owner);
go      

create event notification audit
on server for USERCONFIGURABLE_0
to service N'audit', N'current database';
go

begin transaction;
exec sp_trace_generateevent 82, N'this was inserted from a rolled back';
rollback
go

waitfor delay '00:00:05';
select * from audit_table;
go

Answer 3

当您使用TransactionScope时，您需要调用Complete 方法：

Using tx = New TransactionScope()
    Using conn = New SqlConnection(MY_CONN_STR)
        Using cmd = New SqlCommand("MySP", conn)
            conn.Open()
            cmd.ExecuteNonQuery()
            'Throw New ApplicationException("Always throw exception.")
        End Using
    End Using

    tx.Complete() ' <---- Here

End Using