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))
CREATE TABLE MSG_LOG (MSG_LOG_ID INT IDENTITY NOT NULL PRIMARY KEY, MSG VARCHAR(100), VALUE VARCHAR(10))
INSERT INTO DEMO_AT VALUES (1, ‘TEST’)
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
–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.
One Response to “Simulating autonomous transaction behavior in SQL Server”
Sorry, the comment form is closed at this time.