An autonomous transaction is a feature of Oracle databases which enables to leave the context of the calling transaction and perform an independent transaction. An autonomous transaction is a nested transaction which 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 where auditing is required logging error messages despite the transaction is doing a rollback. In Microsoft SQL Server, there is no direct equivalent for this. Moreover, if you have a nested transaction, one needs to be aware of the fact that the outermost commit is what controls the inner commits and any of inner rollback will rollback all the whole transaction. In the article, I will cover how to use table variable techniques in order to simulate the autonomous transaction behavior of Oracle databases in Microsoft SQL Server.
Autonomous transactions in Oracle
Let's create two tables table1 and table2 in Oracle database (see Listing 1).
create table table1
(nId int primary key,
vcValue varchar2(20)
);
create table table2
(nId int primary key,
vcValue varchar2(20)
); |
Listing 1
Now let's create two procedures: one procedure that initiates a parent transaction which in turn calls for a nested autonomous transaction (see 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;
/ |
Listing 2
Let's test the behavior of the autonomous transaction. Run the following P-SQL statements (see Listing 3).
begin
insert into table2
values(1,'value1');
commit;
insert_into_table2(1,'value1');
commit;
end; |
Listing 3
The result is displayed as shown in 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 |
Listing 4
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
NID VCVALUE
---------- --------------------
1 value1
1 row selected. |
Listing 6
Now let's simulate the same behavior in Microsoft SQL Server using table variables.
First of all, let's create the two tables (see Listing 7).
create table table1
(nId int primary key,
vcValue varchar(20)
);
create table table2
(nId int primary key,
vcValue varchar(20)
); |
Listing 7
Now it's the time to code a stored procedure which implements the logic of autonomous transactions (see 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 8
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';
end; |
Listing 9
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 10
Now if you select the rows of the table1 using the SQL statements in Listing 11, you will result the results shown in Listing 12.
Listing 11
nId vcValue
----------- --------------------
1 value1
(1 row(s) affected) |
Listing 12
Conclusion
In this article, I covered the techniques to simulate the autonomous transaction behavior of Oracle databases in Microsoft SQL Server.