Autonomous transactions allow you to leave the context of the calling transaction, perform an independent transaction, and return to the calling transaction without affecting it's state. The autonomous transaction has no link to the calling transaction, so only committed data can be shared by both transactions. With previous versions of SQL Server you would write a C++ DLL and add it in as a extended stored procedure, which is not a great solution. SQL Server 2008 provides a means to do this by using a Linked Server.
I use this linked server approach with the error logging I designed for a recent customer's database, which I will call DbName for the following examples. This insures that the record does not get deleted in the case of a ROLLBACK action. To implement this I created Linked Server connection, called DbNameLoopback, to the DbName database, and enabled RPC. The script follows. Note the sp_serveroption statement is the option to not include the call in the current transaction.
Script to Implement You may have to ask your DBA or system administrator to run this.
USE MASTER
GO
IF EXISTS (SELECT srv.name FROM sys.servers srv
WHERE srv.server_id != 0 AND srv.name = N'DbNameLoopback')
EXEC master.dbo.sp_dropserver @server=N'DbNameLoopback', @droplogins='droplogins'
GO
EXEC sp_addlinkedserver @server = N'DbNameLoopback',@srvproduct = N' '
,@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO
EXEC sp_serveroption DbNameLoopback, N'remote proc transaction promotion','FALSE'
-- FALSE = the local transaction will not be promoted to distributed transaction
Go
EXEC sp_serveroption DbNameLoopback,N'RPC OUT','TRUE' -- Enable RPC to the given server.
GO
Test Example:
1) Create a table and stored procedure:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo._TestTable') AND type in (N'U'))
DROP TABLE dbo._TestTable
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE dbo._TestTable
( TestKey int IDENTITY(1, 1) NOT NULL
, Value varchar(max) NOT NULL
, CreateDate datetime2 NOT NULL CONSTRAINT DF_TestTable_CreateDate DEFAULT (getdate())
, CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
(
TestKey ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo._Test_ins') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo._Test_ins
GO
CREATE PROCEDURE dbo._Test_ins
( @Value varchar(50))
AS
SET NOCOUNT ON
INSERT DBNAME.dbo._TestTable(Value)
VALUES(@Value)
GO
2) Run the following script to see the effects of a Rollback.
TRUNCATE TABLE dbo._TestTable
exec _Test_ins 'Test 1: _Test_ins'
SELECT * FROM dbo._TestTable
BEGIN TRAN
exec _Test_ins 'Test 2: _Test_ins in trans'
SELECT 'Before ROLLBACK', * FROM dbo._TestTable
ROLLBACK
SELECT 'After ROLLBACK', * FROM dbo._TestTable -- one record deleted :-(
2) Results: The second record dissapears after the rollback.
TestKey | Value | CreateDate | |
1 | Test 1: _Test_ins | 2009-08-06 16:43:55 | |
TestKey | Value | CreateDate | |
Before ROLLBACK | 1 | Test 1: _Test_ins | 2009-08-06 16:43:55 |
Before ROLLBACK | 2 | Test 2: _Test_ins in trans | 2009-08-06 16:43:55 |
TestKey | Value | CreateDate | |
After ROLLBACK | 1 | Test 1: _Test_ins | 2009-08-06 16:43:55 |
3) Run the following script to see the effects using the Loopback linked server:
TRUNCATE TABLE dbo._TestTable
exec DbNameLoopback.DbName.dbo._Test_ins 'Test 3 - use DbNameLoopback _Test_ins'
SELECT * FROM dbo._TestTable
BEGIN TRAN
-- exec dbo._TestLoopback_ins 'Test 4 - use _TestLoopback_ins in transaction'
exec DbNameLoopback.DbName.dbo._Test_ins 'Test 4 - use DbNameLoopback _Test_ins in trans'
SELECT 'Loopback: Before ROLLBACK', * FROM dbo._TestTable
ROLLBACK
SELECT 'Loopback: After ROLLBACK', * FROM dbo._TestTable -- No record deleted :-)
3) Results: The second record remains after the rollback - yea!
TestKey | Value | CreateDate | |
1 | Test 3 - use DbNameLoopback _Test_ins | 2009-08-06 16:50:57 | |
TestKey | Value | CreateDate | |
Loopback: Before ROLLBACK | 1 | Test 3 - use DbNameLoopback _Test_ins | 2009-08-06 16:50:57 |
Loopback: Before ROLLBACK | 2 | Test 4 - use DbNameLoopback _Test_ins in trans | 2009-08-06 16:50:57 |
TestKey | Value | CreateDate | |
Loopback: After ROLLBACK | 1 | Test 3 - use DbNameLoopback _Test_ins | 2009-08-06 16:50:57 |
Loopback: After ROLLBACK | 2 | Test 4 - use DbNameLoopback _Test_ins in trans | 2009-08-06 16:50:57 |
For more information search online for SQL Server 2008 Autonomous Transaction.
Internet Marketing London
ReplyDeleteexciting weblog. It would be excellent if you can offer more information about it. Thanks you