在当前事务之外提交事务（例如Oracle中的自主事务）

Question

我需要从存储过程中写入日志表。现在，此日志信息必须能够在回滚过程中幸免。

我知道以前曾问过这个问题，但是我的情况有所不同，在这些问题中找不到我的问题的答案。

当存储过程中没有错误时，事情就很简单了，日志表中的条目就在那里。
当有错误时，事情就变得复杂了。
在该过程中，我可以在catch中进行回滚，然后将数据插入日志表，我知道并且我已经在这样做了。
但是问题是当存储过程这样调用时：

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable

我知道这段代码没有多大意义，我尽量减少代码来演示我的问题。
如果存储过程的调用者执行了提交/回滚，那么我在存储过程中所做的操作就无关紧要。我的登录信息将始终回滚。

我也不能使用临时表技巧，该技巧是返回我要记录的数据，并让调用者在完成回滚后使用该数据将其插入到日志表中，因为调用者是我不希望使用的外部应用程序有来源。

日志记录是在一个单独的过程中完成的，该过程只有一行代码，即插入到日志表中。
我需要的是一种在当前事务之外的此过程中提交插入的方法，以便它在任何回滚后都可以幸免。

有没有办法做到这一点 ？

解决方案：

我使用了lad2025答案，到目前为止，它没有任何问题或性能问题。
但是这个程序每天只会被调用大约1000次，所以这并不是很多，所以我想我也不必指望任何问题。

Answer 1

这是一个非常有趣的话题，因此让我们检查一下MS如何处理它。

第一份文档：将Oracle迁移到SQL Server-2014-和-Azure-SQL-
DB.pdf

第152页。

模拟Oracle自主事务

本部分描述了SSMA for Oracle V6.0如何处理自主事务（PRAGMA
AUTONOMOUS_TRANSACTION）。这些自主事务在Microsoft SQL Server 2014中没有直接等效项。

当您将PL /
SQL块（匿名块，过程，函数，打包过程，打包函数，数据库触发器）定义为自主事务时，会将该块中的DML与调用者的事务上下文隔离。该块成为由另一个事务（称为主事务）启动的独立事务。

要将PL / SQL块标记为自主事务，只需在声明部分中包含以下语句：PRAGMA AUTONOMOUS_TRANSACTION;

SQL Server 2014不支持自主事务。 将Transact-SQL块与事务上下文隔离的唯一方法是打开新连接。

使用 xp_ora2ms_exec2 扩展过程及其与SSMA 6.0 Extension Pack捆绑在一起的扩展版本
xp_ora2ms_exec2_ex
打开新事务。该过程的目的是在新连接中调用任何存储过程，并帮助在函数体内调用存储过程。xp_ora2ms_exec2过程具有以下语法：

xp_ora2ms_exec2
<active_spid> int,
<login_time> datetime,
<ms_db_name> varchar,
<ms_schema_name> varchar,
<ms_procedure_name> varchar,
<bind_to_transaction_flag> varchar,
[optional_parameters_for_procedure];

然后，您需要在服务器上安装存储过程和其他脚本： 适用于Oracle Extension
Pack的SSMA（仅适用于Oracle Extension
Pack.7.5.0.msi的SSMA）。

您的存储过程将变为：

CREATE TABLE myLogTable(i INT IDENTITY(1,1),
                        d DATETIME DEFAULT GETDATE(),
                        t NVARCHAR(1000));
GO

CREATE OR ALTER PROCEDURE my_logging
   @t NVARCHAR(MAX)
AS
BEGIN
   INSERT INTO myLogTable(t) VALUES (@t);
END;
GO

CREATE OR ALTER PROCEDURE myStoredProcedure
AS
BEGIN
    -- some work
    SELECT 1;
    INSERT INTO myLogTable(t) 
    VALUES ('Standard logging that will perish after rollback');

    DECLARE @login_time DATETIME = GETDATE();
    DECLARE @custom_text_to_log NVARCHAR(100);
    SET @custom_text_to_log=N'some custom loging that should survive rollback';
    DECLARE @database_name SYSNAME = DB_NAME();

    EXEC master.dbo.xp_ora2ms_exec2_ex 
       @@spid,
       @login_time,
       @database_name,
       'dbo',
       'my_logging',
       'N',
       @custom_text_to_log;
END;

最后的电话：

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable;

输出：

i   d          t
2   2017-08-21 some custom loging that should survive rollback