Simulating Autonomous Transactions in Microsoft SQL Server

Introduction

In this article, we will learn about Autonomous transactions in Oracle Database. An autonomous transaction is a feature of Oracle databases that enables one to leave the context of the calling transaction and perform an independent transaction.

Autonomous transaction in Oracle

An autonomous transaction is a nested transaction that can modify data and commit or rollback independent of the state of the parent transaction. There is no link with the calling transaction, so only committed data can be shared by both transactions. This is a common scenario in which auditing is required to log error messages despite the marketing being a rollback. In Microsoft SQL Server, there is no direct equivalent for this. Moreover, if you have a nested transaction, you need to be aware that the outermost commit controls the inner commits, and any inner rollback will roll back the whole transaction. In the article, I will cover how to use table variable techniques to simulate the autonomous transaction behavior of Oracle databases in Microsoft SQL Server.

How to use Autonomous transactions in Oracle?

Let's create tables table1 and table2 in the Oracle database (see Listing 1).

Listing 1

create table table1
(nId int primary key,
vcValue varchar2(20)
);
create table table2
(nId int primary key,
vcValue varchar2(20)
);

Now let's create two procedures: one that initiates a parent transaction, which calls for a nested autonomous transaction (see Listing 2).

Listing 2

create or replace procedure insert_into_table1 (nId int, vcValue varchar2)
as
pragma autonomous_transaction;
begin
insert into table1
values(nId, vcValue);
commit;
end;
/
create or replace procedure insert_into_table2 (nId int, vcValue varchar2)
as
begin
insert_into_table1 (nId,vcValue);

insert into table2
values(nId, vcValue);
commit;
end;
/

Let's test the behavior of the autonomous transaction. Run the following P-SQL statements (see Listing 3).

Listing 3

begin
insert into table2
values(1,'value1');
commit;

insert_into_table2(1,'value1');
commit;
end;

The result is displayed as shown in Listing 4.

Listing 4

Begin
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005744) violated
ORA-06512: at "SCOTT.INSERT_INTO_TABLE2", line 6
ORA-06512: at line 6

When you select the rows of the table1 by running the SQL statement in Listing 5, you will see that the autonomous transaction commits its changes and enable the insertion of a row even though the parent transaction rollbacks its changes because there is a primary key constraint violation (see Listing 6).

Listing 5

select *
from table1;

Listing 6

NID VCVALUE

---------- --------------------
1 value1
1 row selected.

Now let's simulate the same behavior in Microsoft SQL Server using table variables.

First, let's create the two tables (see Listing 7).

Listing 7

create table table1
(nId int primary key,
vcValue varchar(20)
);

create table table2
(nId int primary key,
vcValue varchar(20)
);

Now it's time to code a stored procedure that implements the logic of autonomous transactions (see Listing 8).

Listing 8

create procedure autonomous_transactions_example @nId int, 
@vcValue varchar(20) as begin declare @temporary_table table(
  nId int, 
  vcValue varchar(20)
);
begin tran begin tran insert into @temporary_table 
values 
  (@nId, @vcValue);
if @@error > 0 begin rollback;
end else begin commit;
end;
insert into table2 
values 
  (@nId, @vcValue);
if @@error > 0 begin rollback;
end else begin commit;
end;
insert into table1 
select 
  * 
from 
  @temporary_table;
end;

Listing 9

Now let's test the behavior of the simulated autonomous transaction. Run the following T-SQL statements (see Listing 9).

begin begin tran insert into table2 
values 
  (1, 'value1');
commit;
exec autonomous_transactions_example 1, 
'value1';

Listing 10

The output of this operation is shown in Listing 10.(
  1 row(s) affected
) (
  1 row(s) affected
) Msg 2627, 
Level 14, 
State 1, 
Procedure autonomous_transactions_example, 
Line 22 Violation of PRIMARY KEY constraint 'PK__table2__7E6CC920'.Cannot insert duplicate key in object 'dbo.table2'.The statement has been terminated.(
  1 row(s) affected
)

Listing 11

Now, if you select the rows of the table1 using the SQL statements in Listing 11, you will result in the results shown in Listing 12.

select *
from table1;

Listing 12

nId vcValue
----------- --------------------
1 value1

(1 row(s) affected)

Conclusion

In this article, I covered the techniques to simulate the autonomous transaction behavior of Oracle databases in Microsoft SQL Server.