在当前事务之外提交事务(如 Oracle 中的自治事务) [英] Commit transaction outside the current transaction (like autonomous transaction in 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.

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

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 :

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.

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

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.

日志是在一个单独的过程中完成的，只有一行代码，插入到日志表中.
我需要的是一种在此过程中提交插入的方法，在当前事务之外，以便它在任何回滚中都能存活.

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.

有没有办法做到这一点?

Is there a way to do this ?

解决方案:

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

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.

推荐答案

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

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

第一个文档:迁移-Oracle-to-SQL-Server-2014-and-Azure-SQL-DB.pdf

第 152 页.

模拟 Oracle 自治事务

本节介绍 SSMA for Oracle V6.0 如何处理自治事务(PRAGMA AUTONOMOUS_TRANSACTION).这些自治事务不在 Microsoft SQL Server 2014 中具有直接等效项.

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.

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

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 块标记为自治事务，您只需包含您的声明部分中的以下声明:PRAGMA AUTONOMOUS_TRANSACTION;

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

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

使用xp_ora2ms_exec2 扩展程序及其扩展版本xp_ora2ms_exec2_ex，与 SSMA 6.0 扩展包捆绑在一起，打开新的交易.该过程的目的是调用新的存储过程连接并帮助调用函数体内的存储过程.这xp_ora2ms_exec2 过程具有以下语法:

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:

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];

然后你需要在你的服务器上安装存储过程和其他脚本:SSMA for Oracle Extension Pack(仅适用于 Oracle 的 SSMA扩展包.7.5.0.msi).

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).

你的存储过程将变成:

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

这篇关于在当前事务之外提交事务(如 Oracle 中的自治事务)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！