Can I exclude a stored procedure from a transaction in MS SQL?

Question

To my problem: I call a stored procedure from my business code. This call is in a explicit transaction. The stored procedure sometimes calls another one to write something into the database. This data should stay in the database even when the transaction is rolled back. A similar scenario is when you want to write something in a log table and the log message should be kept (this is not my case, it is just a similar requirement).

How could I exclude the second stored procedure from the outer transaction? I think that I am looking for something like "autonomous transactions" in Oracle. I looked for a possible emulation but all the solutions didn't look very "nice" (create a loopback server, add some .NET methods, ...)

Any ideas? Thank you!

Answer 1

There is no elegant solution to this type of problem although it seems to be common. Everything between begin transaction and either commit or rollback is done as a whole. You cannot just insert a line into a log table for instance and keep that one after an eventual rollback.

But you can do some tricks.

1) Call your procedure with xp_cmdshell to call OSQL.exe. Performance would be bad, but external commands do not participate in the transaction and nothing keeps you from executing SQL statements externally.

2) in the stored procedure you could add the records into a table-variable instead of a real table. Table-variables do not participate in the transaction, as they do not alter the database. Afterwards append the content of the variable to your table when you closed the transaction in either way.

3) If you cannot change the inner procedure, you can fetch the record(s) that it possibly did create into a table variable after the call but from within the still open transaction. Rollback the transaction and append the fetched records to the table again.

Answer 2

Yes you can! Use a linked server to yourself and set the 'remote proc transaction promotion' to false. Here is an example:

EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK', @srvproduct=N'Microsoft', @provider=N'SQLNCLI', @datasrc=N'MYMACHINE\INSTANCE', @catalog=N'DB_NAME_HERE'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'remote proc transaction promotion', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'rpc out', @optvalue=N'true'
--I think most below are defaults
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'use remote collation', @optvalue=N'true'

CREATE PROCEDURE dbo.ap_deleteme_outsideTransaction  AS
    --codes is just some random table I have
    exec ('insert into codes values (1, ''TEST'', ''TEST_ED'', ''DELTE_ME'', 0, ''1234'', ''myId'', getDate())') at LOOPBACK
GO

CREATE PROCEDURE dbo.ap_deleteme_test_transaction  AS
    begin transaction
        insert into codes values (10, 'TEST', 'TEST_ED', 'DELTE_ME_1', 0, '1234', 'myId', getDate())
        --exec ('generic query you may want to execute') at LOOPBACK
        exec dbo.ap_deleteme_outsideTransaction
        insert into codes values (20, 'TEST', 'TEST_ED', 'DELTE_ME_2', 0, '1234', 'myId', getDate())
    --rolling back like this makes no sense, but here you should be able to see 3 records 
    --inserted and then two rolled back.  The record inserted in the second proc call
    --will still remain.
    rollback transaction
GO

I would not call this optimal, but once you setup this link, you should be able to direct any call you do not want to participate in the transaction to this link and it will not participate. My initial tests do not show a significant performance hit, but be aware that making calls in a loop may prove to be costly.

-Update: Performance tests show it is 1-2 millisecond hit, but this is about 60x slower than a "direct" call. 500 or so hits is not noticeable, but when you get thousands, you can start to see seconds add up - not that this would be a common practice. We see that it far outweighs other side effects such as blocking, which is what we put this in place for.