TransactionScope + Audit Table + Commit Transaction In Stored Procedure

Question

My company is using SQL Server 2008. I am facing a Audit Table with transaction problems.

I have a stored procedure.

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

I have a block of VB.net code

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

However there is no record inserted into AUDIT_TABLE. I found the reason in MSDN http://msdn.microsoft.com/en-us/library/ms189336.aspx

My question is how can I insert records AUDIT_TABLE with stored procedure.

Thanks!

Sorry for my VB.net code. I missed `tx.Complete()` statement. However, it never goes to that line. Because I always throw exception immediately after `cmd.ExecuteNonQuery()` method. What I want is rolling back records inserted in MY_TABLE but **NOT** rolling back records inserted in AUDIT_TABLE. — Alex Yeung, Mar 04 '11 at 00:39

score 1 · Answer 1 · edited Jan 04 '12 at 08:43

1

Basically, what you can do is to have one async audit/log system. So your audit will be running on a different thread, and it does not matter whether you main transaction scope fails or not.

use enterprise library+MSMQ
build you own light-weight async log system ( use a sync queue)

edited Jan 04 '12 at 08:43

musefan

47,875
21
135
185

answered Jan 04 '12 at 06:16

huyq2002

11
1

Oded · Answer 2 · 2011-03-04T08:49:07.983

0

When you use TransactionScope, you need to call the Complete method before going out of its scope if you don't want the transaction to rollback:

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

edited Mar 04 '11 at 08:49

answered Mar 03 '11 at 23:05

Oded

489,969
99
883
1,009

I just want MY_TABLE rollback but not AUDIT_TABLE. I know `tx.Complete()` method. However since I have `Throw New ApplicationException("Always throw exception.")` line, there is no way to do `tx.Complete()`. – Alex Yeung Mar 04 '11 at 00:34
@Alex - if you enrol both calls in a transaction, both will rollback or both will be committed. If you don't want that behaviour, don't enrol them both calls in one transaction. – Oded Mar 04 '11 at 08:48

Remus Rusanu · Answer 3 · 2011-03-04T06:04:53.083

Any operation inside a transaction that rolled back rolls back as well. Not doing so would break transaction atomicity. Given that the activity you're auditing is being rolled back, is very likely that you actually want the audit to be rolled back anyway.

None the less, there are legitimate cases when one needs to have operations recorded outside the scope of the current transaction, like certain debug cases. There are known workarounds, like using event notifications for the user configurable event class, then using sp_trace_generateevent to cause the event notification activated procedure to run and record the audit. Because the profiler events are generated outside the transaction scope, the audit record does not get rolled back.

: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

TransactionScope + Audit Table + Commit Transaction In Stored Procedure

3 Answers3