我可以从MS SQL中的事务中排除存储过程吗？

To my problem: I call a stored procedure from my business code. This call is in a explicit transaction. The stored procedure sometimes calls another one to write something into the database. This data should stay in the database even when the transaction is rolled back. A similar scenario is when you want to write something in a log table and the log message should be kept (this is not my case, it is just a similar requirement).

我的问题:我从业务代码中调用存储过程。此调用是在显式事务中。存储过程有时会调用另一个来将某些内容写入数据库。即使事务回滚,此数据也应保留在数据库中。类似的情况是当你想在日志表中写一些东西并且应该保留日志消息时(这不是我的情况,这只是一个类似的要求)。

How could I exclude the second stored procedure from the outer transaction? I think that I am looking for something like "autonomous transactions" in Oracle. I looked for a possible emulation but all the solutions didn't look very "nice" (create a loopback server, add some .NET methods, ...)

如何从外部事务中排除第二个存储过程?我认为我正在寻找像Oracle中的“自治事务”这样的东西。我寻找可能的仿真,但所有的解决方案看起来都不是很“好”(创建一个环回服务器,添加一些.NET方法,......)

Any ideas? Thank you!

有任何想法吗?谢谢!

3 个解决方案

#1

There is no elegant solution to this type of problem although it seems to be common. Everything between begin transaction and either commit or rollback is done as a whole. You cannot just insert a line into a log table for instance and keep that one after an eventual rollback.

尽管这种问题似乎很普遍,但这种问题并没有优雅的解决方案。 begin transaction与commit或rollback之间的所有内容都是作为一个整体完成的。例如,您不能仅在日志表中插入一行,并在最终回滚后保留该行。

But you can do some tricks.

但你可以做一些技巧。

1) Call your procedure with xp_cmdshell to call OSQL.exe. Performance would be bad, but external commands do not participate in the transaction and nothing keeps you from executing SQL statements externally.

1)使用xp_cmdshell调用您的过程来调用OSQL.exe。性能会很差,但外部命令不参与事务,没有任何东西阻止您在外部执行SQL语句。

2) in the stored procedure you could add the records into a table-variable instead of a real table. Table-variables do not participate in the transaction, as they do not alter the database. Afterwards append the content of the variable to your table when you closed the transaction in either way.

2)在存储过程中,您可以将记录添加到表变量而不是真实表中。表变量不参与事务,因为它们不会更改数据库。然后,当您以任一方式关闭事务时,将变量的内容附加到表中。

3) If you cannot change the inner procedure, you can fetch the record(s) that it possibly did create into a table variable after the call but from within the still open transaction. Rollback the transaction and append the fetched records to the table again.

3)如果你不能改变内部过程,你可以在调用之后但是从仍然打开的事务中获取它可能创建的记录到表变量中。回滚事务并再次将提取的记录附加到表中。

#2

Yes you can! Use a linked server to yourself and set the 'remote proc transaction promotion' to false. Here is an example:

是的你可以!使用链接服务器自己并将“远程proc事务促销”设置为false。这是一个例子:

EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK', @srvproduct=N'Microsoft', @provider=N'SQLNCLI', @datasrc=N'MYMACHINE\INSTANCE', @catalog=N'DB_NAME_HERE'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'remote proc transaction promotion', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'rpc out', @optvalue=N'true'
--I think most below are defaults
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LOOPBACK', @optname=N'use remote collation', @optvalue=N'true'

CREATE PROCEDURE dbo.ap_deleteme_outsideTransaction  AS
    --codes is just some random table I have
    exec ('insert into codes values (1, ''TEST'', ''TEST_ED'', ''DELTE_ME'', 0, ''1234'', ''myId'', getDate())') at LOOPBACK
GO

CREATE PROCEDURE dbo.ap_deleteme_test_transaction  AS
    begin transaction
        insert into codes values (10, 'TEST', 'TEST_ED', 'DELTE_ME_1', 0, '1234', 'myId', getDate())
        --exec ('generic query you may want to execute') at LOOPBACK
        exec dbo.ap_deleteme_outsideTransaction
        insert into codes values (20, 'TEST', 'TEST_ED', 'DELTE_ME_2', 0, '1234', 'myId', getDate())
    --rolling back like this makes no sense, but here you should be able to see 3 records 
    --inserted and then two rolled back.  The record inserted in the second proc call
    --will still remain.
    rollback transaction
GO

I would not call this optimal, but once you setup this link, you should be able to direct any call you do not want to participate in the transaction to this link and it will not participate. My initial tests do not show a significant performance hit, but be aware that making calls in a loop may prove to be costly.

我不会称之为最佳,但是一旦你设置了这个链接,你应该能够将你不想参与交易的任何电话指向这个链接,它就不会参与。我的初始测试没有显示出显着的性能损失,但请注意,在循环中进行调用可能会被证明是昂贵的。

-Update: Performance tests show it is 1-2 millisecond hit, but this is about 60x slower than a "direct" call. 500 or so hits is not noticeable, but when you get thousands, you can start to see seconds add up - not that this would be a common practice. We see that it far outweighs other side effects such as blocking, which is what we put this in place for.

-Update:性能测试显示它的命中时间为1-2毫秒,但这比“直接”呼叫慢约60倍。 500次左右的命中率并不明显,但是当你获得数千次时,你可以开始看到秒加起来 - 而不是这是一种常见的做法。我们看到它远远超过其他副作用,例如阻塞,这就是我们为此所做的。

#3

To the best of my knowledge, you can't exclude specific operations from the transactions that contain them. My approach would be to try to explicitly do the things you want to do instead of rolling back the entire transaction. Or, if it's an option, rollback the transaction, and then re-run the stored procedure that you didn't want to rollback.

据我所知,您不能从包含它们的事务中排除特定操作。我的方法是尝试明确地执行您想要执行的操作,而不是回滚整个事务。或者,如果它是一个选项,则回滚事务,然后重新运行您不想回滚的存储过程。

#1