事务另一个数据库的调用存储过程 [英] TRANSACTIONS Call Stored Procedure of another database

问题描述

有关此问题:在BEGIN/END内部执行存储过程交易

我正在通过BizTalk调用存储过程.因为BizTalk正在创建事务，所以我的存储过程没有事务处理.但是，我必须在BizTalk调用的存储过程中调用另一个存储过程.但是第二个存储过程是针对同一MSSQL实例中的另一个数据库调用的.

I am calling my stored procedure via BizTalk. Because BizTalk is creating a TRANSACTION my stored procedure has no TRANSACTION handling. However, I have to call another stored procedure within the one called by BizTalk. BUT the second stored procedure is called against another database within the same MSSQL Instance.

MS SQL是否了解此跨数据库过程调用，是否也回滚了第二个存储过程?

Is MS SQL aware of this cross database procedure call and does a rollback of the second stored procedure as well?

如果发生以下情况，会发生什么情况

What happens if:

• 跨数据库存储过程失败?
• 外部存储过程在调用跨数据库存储过程之后失败吗?

推荐答案

例如，如果您使用的是使用sqlBinding的WCF-Custom，则可以在WCF适配器配置中指定TRANSACTION ISOLATION LEVEL.当然，您可以将其设置为所需的状态.

If you are using WCF-Custom using a sqlBinding for example, you can specify the TRANSACTION ISOLATION LEVEL in your WCF adapter configuration. Off course, you can set this to your desired state.

仅供参考:默认情况下，BizTalk Server中的此设置为Serializable隔离级别.

FYI: By default, this setting in BizTalk server is the Serializable isolation level.

实际上，您的WCF适配器在使用事务时将启动分布式事务，并固有地支持跨数据库甚至跨服务器的事务.根据您在SQL中确切执行的操作，可能会或可能不支持此操作.例如，某些通过某些提供程序的有限链接服务器不支持它.

In fact, your WCF adapter, when using a transaction, will start a distributed transaction and will inherently support transactions cross-database and even cross-server. Depending on what you exactly do within SQL, this may or may not be supported. Some limited linked servers via certain providers do not support it for example.

另一个示例是，不在SQL Server 2016之前(包括SQL Server 2016)不始终支持AlwaysOn可用性组中的跨数据库事务.(说明: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supports-ags-dtccross-database-transactions/) 据说从SQL Server 2017开始将支持此(AlwaysOn AG的跨数据库事务).

Another example is that cross-database transactions in AlwaysOn availability groups are not supported up to, and including, SQL Server 2016. (explanation: https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/06/not-supported-ags-with-dtccross-database-transactions/) It is said that this (cross-database transactions in AlwaysOn AG's) is to be supported starting from SQL Server 2017 however.

TLDR;如果您使用的是分布式事务，并且未使用任何不寻常的东西(例如旧式链接服务器提供程序或AlwaysOn可用性组)，则该功能受支持并按预期工作.

TLDR; if you are using a distributed transaction and are not using anything out of the ordinary like legacy linked server providers or AlwaysOn availability groups, this is supported and working as expected.

这篇关于事务另一个数据库的调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！