Systems Engineering and RDBMS

Simulating autonomous transaction behavior in SQL Server

Posted by decipherinfosys on February 1, 2007

For the projects that are converting from Oracle to SQL Server, at times you will face an issue wherein the code that was written in Oracle is using pragma autonomous transactions.  What this means is that even though the transaction is a nested transaction, it’s commit/rollback scope is totally separate.  This is particularly helpful in scenarios where auditing is required or if one needs to log error messages despite the transaction doing a rollback.

In SQL Server, there is no direct equivalent for this.  Moreover, in SQL Server, if you have a nested transaction, one needs to be aware of the fact that the outermost commit is what controls the inner commits as well and any of the inner rollback will rollback all the nested transactions as well.  In order to simulate the same autonomous transaction behavior in SQL Server, one can make use of the table variables.  Here is such an example:

CREATE TABLE DEMO_AT (COL1 INT NOT NULL PRIMARY KEY, COL2 VARCHAR(10))
GO
CREATE TABLE MSG_LOG (MSG_LOG_ID INT IDENTITY NOT NULL PRIMARY KEY, MSG VARCHAR(100), VALUE VARCHAR(10))
GO

INSERT INTO DEMO_AT VALUES (1, ‘TEST’)
GO

BEGIN TRAN
DECLARE @TEST TABLE (COL1 INT, COL2 VARCHAR(10))
INSERT INTO @TEST SELECT * FROM DEMO_AT

–Now insert the value into the DEMO_AT table
–it will fail because of the PK violation
INSERT INTO DEMO_AT SELECT * FROM @TEST

–just use the @@error for demos
–in real life, use Try/Catch block
if @@error <> 0
begin
ROLLBACK TRAN
end
else
begin
COMMIT TRAN
end

–log it after the rollback occurs
insert into msg_log (msg, VALUE) select ‘INSERT FAILED’, COL2 FROM @TEST

–Now, see the value
SELECT * FROM MSG_LOG

Here is the output of that run:

Server: Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint ‘PK__DEMO_AT__483BA0F8′. Cannot insert duplicate key in object ‘DEMO_AT’.
The statement has been terminated.

MSG_LOG_ID  MSG                                                                                                  VALUE
———– —————————————————————————————————- ———-
1           INSERT FAILED                                                                                        TEST

So, how/why did this work even after the rollback was done?  Since we are using a table variable over here which is memory resident and it’s scope is not for that transaction, one can still access it’s contents after the rollback is done.  This technique can be used in auditing scenarios or for error handling in situations where a rollback occurs and you still want to log the messages into some table for further troubleshooting.

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: