在当前事务之外提交事务(例如Oracle中的自治事务)

I need to write into a log table from a stored procedure. Now this log info has to survive a rollback offcourse.

我需要从存储过程中写入一个日志表。现在这个日志信息必须在回滚之后才能运行。

I know this question has been asked before, but my situation is different and I cannot find an answer to my problem in these questions.

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

When there is no error in the stored procedure things are simple, the entry in the logtable will just be there.
When there is an error than things are complicated.
Inside the procedure I can do rollback in the catch and then insert the data into the log table, I know that and I am already doing that.
But the problem is when the stored procedure is called like this :

当存储过程中没有错误时，事情很简单，logtable中的条目就会在那里。当有错误时，事情就复杂了。在这个过程中，我可以在catch中执行回滚，然后将数据插入到日志表中，我知道这一点，我已经在这么做了。但是问题是当存储过程被这样调用时:

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable

I know this code makes not much sense, I kept it mimimal to demonstrate my problem.
If the caller of the stored procedure does the commit/rollback then it does not matters what I do in the stored procedure. My logentry will always be rolled back.

我知道这段代码没有什么意义，我保留了它来演示我的问题。如果存储过程的调用者执行提交/回滚，那么我在存储过程中做什么并不重要。我的logentry总是被回滚。

I also cannot use the temporary table trick, which is to return the data I want to log and let the caller use that data to insert it into the logtable after it has done the rollback, because the caller is an external application that I do not have the source from.

我也不能使用临时表的技巧,这是返回数据我想日志,让调用者使用这些数据来将其插入到logtable后做了回滚,因为调用者是一个外部应用程序,我没有来源。

The logging is done in a seperate procedure that only has one line of code, the insert into the logtable.
What I need is a way to commit the insert in this procedure, outside the current transaction so it survives any rollback.

日志记录是在一个单独的过程中完成的，该过程只有一行代码，即对日志表的插入。我需要的是在这个过程中提交insert的方法，在当前事务之外，以便它能在任何回滚中存活。

Is there a way to do this ?

有办法吗?

The Solution:

解决方案:

I used lad2025 answer and thus far it is working without problems or performance issues.
But this procedure will only be called about 1000 times each day which is not that much so I guess I don't have to expect any problems either.

我使用了lad2025的答案，到目前为止，它的工作没有问题或性能问题。但是这个程序每天只会被调用1000次，这并不多，所以我想我也不需要期待任何问题。

2 个解决方案

#1

It is quite interesting topic so let's check how MS approaches it.

这是一个非常有趣的话题，让我们来看看MS是如何处理它的。

First documentation: Migrating-Oracle-to-SQL-Server-2014-and-Azure-SQL-DB.pdf

第一个文档:迁移- oracle - sql - server - 2014和azure - sql - db.pdf

Page 152.

152页。

Simulating Oracle Autonomous Transactions

模拟甲骨文自治事务

This section describes how SSMA for Oracle V6.0 handles autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION). These autonomous transactions do not have direct equivalents in Microsoft SQL Server 2014.

本节描述Oracle V6.0的SSMA如何处理自治事务(PRAGMA autonomous _transaction)。这些自治事务在Microsoft SQL Server 2014中没有直接的等价物。

When you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, database trigger) as an autonomous transaction, you isolate the DML in that block from the caller's transaction context. The block becomes an independent transaction started by another transaction, referred to as the main transaction.

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

To mark a PL/SQL block as an autonomous transaction, you simply include the following statement in your declaration section: PRAGMA AUTONOMOUS_TRANSACTION;

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

SQL Server 2014 does not support autonomous transactions. The only way to isolate a Transact-SQL block from a transaction context is to open a new connection.

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

Use the xp_ora2ms_exec2 extended procedure and its extended version xp_ora2ms_exec2_ex, bundled with the SSMA 6.0 Extension Pack, to open new transactions. The procedure's purpose is to invoke any stored procedure in a new connection and help invoke a stored procedure within a function body. The xp_ora2ms_exec2 procedure has the following syntax:

使用与SSMA 6.0扩展包绑定的xp_ora2ms_exec2_ex扩展过程及其扩展版本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];

Then you need to install on your server stored procedures and other scripts: SSMA for Oracle Extension Pack (only SSMA for Oracle Extension Pack.7.5.0.msi).

然后需要在服务器存储过程和其他脚本上安装:SSMA用于Oracle扩展包(仅用于Oracle扩展包的SSMA)。

Your stored procedure will become:

您的存储过程将变为:

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;

And final call:

和最后的呼声:

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable;

OUTPUT:

输出:

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

#2

So you really search for some sort of Autonomous transaction (like in Oracle).

因此，您实际上是在搜索某种自治事务(比如在Oracle中)。

One ugly way to simulate it is to use loopback linked server.

一个丑陋的模拟方法是使用环回链接服务器。

Warning: This is PoC (I would think twice before I would use it in PROD) and do a lot of testing.

警告:这是PoC(我在使用PROD之前会考虑再三)，并进行大量测试。

DECLARE @servername SYSNAME;
SET     @servername = CONVERT(SYSNAME, SERVERPROPERTY(N'ServerName'));

EXECUTE sys.sp_addlinkedserver
            @server = N'loopback', 
            @srvproduct = N'', 
            @provider = N'SQLNCLI', 
            @datasrc = @servername;

EXECUTE sys.sp_serveroption 
            @server = N'loopback', 
            @optname = 'RPC OUT', 
            @optvalue = 'ON';

EXECUTE sys.sp_serveroption 
        @server = N'loopback', 
        @optname = 'remote proc transaction promotion',
        @optvalue = 'OFF';

And code:

和代码:

DROP TABLE IF EXISTS myLogTable;
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');

    EXEC loopback.T1.dbo.my_logging 
       @t = N'some custom loging that should survive rollback';
END;

Final call:

最后调用:

begin transaction
exec myStoredProcedure
rollback transaction
select * from myLogTable

Output:

输出:

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

#1