How to handle PRAGMA AUTONOMOUS TRANSACTION mode within SQL Server?
The question on the subject was asked by one of the Data Integration project developers, which is initiated to migrate a database from Oracle to SQL Server.
The process of transactions should be handled from Application and when it comes to the data migration you have the tools such as SQL Server Migration Assistant (SSMA) for all other data sources. Just to the topic of data migration I would like to refer the resources to see how SSMA automates, streamlines, and simplifies data migration and helps bring data management, rich data analysis, and data-mining capabilities to your business.
So the doubt from the Developers was within Oracle if the application begin any particular transaction and run a particular update statement this particular update would not be available to any other session until the transaction get committed. Is there a way out to define autonomous transactions in SQL Server in using a nested transactions that cannot commit independently but only within the scope of outermost transactions as in Oracle's PRAGMA AUTONOMOUS_TRANSACTION mode.
The simple answer to this is you can make use of SAVEPOINT and ROLLBACK and it is not an ideal solution as you need to consider a careful coding of DML statements and ROLLBACK with a conditions. Rather another solution is to use TABLE variables or make use of DISTRIBUTED TRANSACTIONS method by using BEGIN DISTRIBUTED TRANSACTION -- to start a single distribute transaction and SET REMOTE_PROC_TRANSACTIONS {ON | OFF} -- to enable distributed transactions for all transactions started by the session.
The method of PRAGMA AUTONOMOUS_TRANSACTONS within Oracle can be used within a multi-server transactions which will provide a commit and/or rollback on each server independently and the same can be accomplished within SQL Server by first enabling the Distributed Transaction Coordinator (DTC) service to run on the server and use the above statements that were shown in italics.
The only different method of DTC works as compared to Oracles transactions is the DTC is mostly a two-phase commit tool. To have truly autonomous transactions on SQL Server, you can use these alternatives such as "Call a procedure on the remote server to perform your transaction and launch a DTS job on the remote server to perform your transaction, then create a linked server and then execute your transaction against the linked server.
Not a clean solution within the projects to migrate the Oracle to SQL Server, but at times the issue of working with transactions seamless may not be possible due to the design considerations of database. 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. The main advantage within SQL Server is 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.