我可以从 MS SQL 中的事务中排除存储过程吗? [英] Can I exclude a stored procedure from a transaction in 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).

如何从外部事务中排除第二个存储过程?我想我正在 Oracle 中寻找类似自治事务"的东西.我寻找了一个可能的模拟，但所有的解决方案看起来都不是很好"(创建一个环回服务器，添加一些 .NET 方法，...)

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

有什么想法吗?谢谢！

推荐答案

尽管此类问题似乎很常见，但没有优雅的解决方案.begin transaction 和 commit 或 rollback 之间的所有内容都是作为一个整体完成的.例如，您不能只在日志表中插入一行并在最终 rollback 之后保留该行.

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.

但是你可以做一些小把戏.

But you can do some tricks.

1) 使用 xp_cmdshell 调用您的过程以调用 OSQL.exe.性能会很差，但外部命令不参与事务，没有什么可以阻止您在外部执行 SQL 语句.

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.

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

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.

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

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.

这篇关于我可以从 MS SQL 中的事务中排除存储过程吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！