跨云数据库的分布式事务Distributed transactions across cloud databases

通过 Azure SQL 数据库 (SQL DB) 的弹性数据库事务可在 SQL DB 中跨多个数据库运行事务。Elastic database transactions for Azure SQL Database (SQL DB) allow you to run transactions that span several databases in SQL DB. SQL DB 的弹性数据库事务适用于使用 ADO .NET 的 .NET 应用程序,并且与你熟悉的使用 System.Transaction 类的编程体验相集成。Elastic database transactions for SQL DB are available for .NET applications using ADO .NET and integrate with the familiar programming experience using the System.Transaction classes. 若要获取该库,请参阅 .NET Framework 4.6.1(Web 安装程序)。To get the library, see .NET Framework 4.6.1 (Web Installer).

在本地,这种方案通常需要运行 Microsoft 分布式事务处理协调器 (MSDTC)。On premises, such a scenario usually required running Microsoft Distributed Transaction Coordinator (MSDTC). 由于 MSDTC 不适用于 Azure 中的平台即服务应用程序,协调分布式事务的功能现在已直接集成到 SQL DB。Since MSDTC is not available for Platform-as-a-Service application in Azure, the ability to coordinate distributed transactions has now been directly integrated into SQL DB. 应用程序可以连接到任何 SQL 数据库来启动分布式事务,其中一个数据库以透明方式协调分布式事务,如下图所示。Applications can connect to any SQL Database to launch distributed transactions, and one of the databases will transparently coordinate the distributed transaction, as shown in the following figure.

使用弹性数据库事务在 Azure SQL 数据库中执行分布式事务Distributed transactions with Azure SQL Database using elastic database transactions

常见方案Common scenarios

SQL DB 的弹性数据库事务可让应用程序对多个不同 SQL 数据库中存储的数据进行原子性更改。Elastic database transactions for SQL DB enable applications to make atomic changes to data stored in several different SQL Databases. 预览版着重于 C# 和 .NET 的客户端开发体验。The preview focuses on client-side development experiences in C# and .NET. 已计划在将来添加使用 T-SQL 的服务器端体验。A server-side experience using T-SQL is planned for a later time.
弹性数据库事务面向以下方案:Elastic database transactions targets the following scenarios:

  • Azure 中的多数据库应用程序:在此方案中,数据垂直分区到 SQL DB 中的多个数据库,使得不同种类的数据位于不同的数据库。Multi-database applications in Azure: With this scenario, data is vertically partitioned across several databases in SQL DB such that different kinds of data reside on different databases. 某些操作需要更改两个以上的数据库中保存的数据。Some operations require changes to data which is kept in two or more databases. 应用程序使用弹性数据库事务来协调数据库之间的更改并确保原子性。The application uses elastic database transactions to coordinate the changes across databases and ensure atomicity.
  • Azure 中的分片数据库应用程序:在此方案中,数据层使用弹性数据库客户端库或自我分片,将数据水平分区到 SQL DB 中的多个数据库。Sharded database applications in Azure: With this scenario, the data tier uses the Elastic Database client library or self-sharding to horizontally partition the data across many databases in SQL DB. 常见的用例之一是在分片的多租户应用程序中,当更改涉及到多个租户时,需要执行原子更改。One prominent use case is the need to perform atomic changes for a sharded multi-tenant application when changes span tenants. 例如,从一个租户转移到另一个租户,而两者位于不同的数据库。Think for instance of a transfer from one tenant to another, both residing on different databases. 第二种方案是以细致分片来适应大租户的容量需求,这又通常表示某些原子操作需要扩展到用于同一租户的多个数据库。A second case is fine-grained sharding to accommodate capacity needs for a large tenant which in turn typically implies that some atomic operations needs to stretch across several databases used for the same tenant. 第三种方案是以原子更新来引用数据库之间复制的数据。A third case is atomic updates to reference data that are replicated across databases. 现在,可以使用预览版跨多个数据库协调这几个方面原子性事务操作。Atomic, transacted, operations along these lines can now be coordinated across several databases using the preview. 弹性数据库事务使用两阶段提交,确保跨数据库的事务原子性。Elastic database transactions use two-phase commit to ensure transaction atomicity across databases. 如果事务涉及的数据库少于 100 个,则适合并入单个事务内。It is a good fit for transactions that involve less than 100 databases at a time within a single transaction. 这些限制不不是强制施加的,但是,如果超出这些限制时,弹性数据库事务的性能和成功率很有可能会下降。These limits are not enforced, but one should expect performance and success rates for elastic database transactions to suffer when exceeding these limits.

安装和迁移Installation and migration

我们更新了 .NET 库 System.Data.dll 和 System.Transactions.dll,以支持在 SQL DB 中执行弹性数据库事务。The capabilities for elastic database transactions in SQL DB are provided through updates to the .NET libraries System.Data.dll and System.Transactions.dll. DLL 确保必要时使用两阶段事务提交,以确保原子性。The DLLs ensure that two-phase commit is used where necessary to ensure atomicity. 若要使用弹性数据库事务来开始开发应用程序,请安装 .NET 4.6.1 或更高版本。To start developing applications using elastic database transactions, install .NET Framework 4.6.1 or a later version. 在旧版 .NET Framework 上运行时,事务无法升级为分布式事务,并会引发异常。When running on an earlier version of the .NET framework, transactions will fail to promote to a distributed transaction and an exception will be raised.

安装后,可以使用 System.Transactions 中的分布式事务 API 和 SQL DB 连接。After installation, you can use the distributed transaction APIs in System.Transactions with connections to SQL DB. 如果现有的 MSDTC 应用程序使用了这些 API,只需在安装 4.6.1 Framework 之后,以 .NET 4.6 为目标重建现有的应用程序。If you have existing MSDTC applications using these APIs, simply rebuild your existing applications for .NET 4.6 after installing the 4.6.1 Framework. 如果项目以 .NET 4.6 为目标,它们自动使用新 Framework 版本中更新的 DLL,而结合 SQL DB 连接的分布式事务 API 调用现在会成功。If your projects target .NET 4.6, they will automatically use the updated DLLs from the new Framework version and distributed transaction API calls in combination with connections to SQL DB will now succeed.

请记住,弹性数据库事务不需要安装 MSDTC。Remember that elastic database transactions do not require installing MSDTC. 弹性数据库事务直接由 SQL DB 管理。Instead, elastic database transactions are directly managed by and within SQL DB. 这可以大幅简化云方案,因为 MSDTC 的部署不需要使用分布式事务和 SQL DB。This significantly simplifies cloud scenarios since a deployment of MSDTC is not necessary to use distributed transactions with SQL DB. 第 4 部分更详细说明了如何将弹性数据库事务和所需的 .NET Framework 连同云应用程序一起部署到 Azure。Section 4 explains in more detail how to deploy elastic database transactions and the required .NET framework together with your cloud applications to Azure.

开发体验Development experience

多数据库应用程序Multi-database applications

以下示例代码使用熟悉的 .NET System.Transactions 编程体验。The following sample code uses the familiar programming experience with .NET System.Transactions. TransactionScope 类在 .NET 中创建环境事务。The TransactionScope class establishes an ambient transaction in .NET. (“环境事务”是位于当前线程中的事务。)在 TransactionScope 内打开的所有连接都参与该事务。(An "ambient transaction" is one that lives in the current thread.) All connections opened within the TransactionScope participate in the transaction. 如果有不同的数据库参与,事务自动提升为分布式事务。If different databases participate, the transaction is automatically elevated to a distributed transaction. 通过设置完成范围来指示提交,即可控制事务的结果。The outcome of the transaction is controlled by setting the scope to complete to indicate a commit.

using (var scope = new TransactionScope())
{
    using (var conn1 = new SqlConnection(connStrDb1))
    {
        conn1.Open();
        SqlCommand cmd1 = conn1.CreateCommand();
        cmd1.CommandText = string.Format("insert into T1 values(1)");
        cmd1.ExecuteNonQuery();
    }

    using (var conn2 = new SqlConnection(connStrDb2))
    {
        conn2.Open();
        var cmd2 = conn2.CreateCommand();
        cmd2.CommandText = string.Format("insert into T2 values(2)");
        cmd2.ExecuteNonQuery();
    }

    scope.Complete();
}

分片数据库应用程序Sharded database applications

SQL DB 的弹性数据库事务还支持协调分布式事务,这需要使用弹性数据库客户端库的 OpenConnectionForKey 方法,打开扩大的数据层的连接。Elastic database transactions for SQL DB also support coordinating distributed transactions where you use the OpenConnectionForKey method of the elastic database client library to open connections for a scaled out data tier. 假设需要保证事务一致性,使更改跨多个不同的分片键值。Consider cases where you need to guarantee transactional consistency for changes across several different sharding key values. 与托管不同分片键值的分片的连接由 OpenConnectionForKey 来中转。Connections to the shards hosting the different sharding key values are brokered using OpenConnectionForKey. 在一般情况下,可以连接到不同的分片,以确保事务保证需要分布式事务。In the general case, the connections can be to different shards such that ensuring transactional guarantees requires a distributed transaction. 以下代码示例演示了此方法。The following code sample illustrates this approach. 假设使用一个称为 shardmap 的变量代表来自弹性数据库客户端库的分片映射:It assumes that a variable called shardmap is used to represent a shard map from the elastic database client library:

using (var scope = new TransactionScope())
{
    using (var conn1 = shardmap.OpenConnectionForKey(tenantId1, credentialsStr))
    {
        conn1.Open();
        SqlCommand cmd1 = conn1.CreateCommand();
        cmd1.CommandText = string.Format("insert into T1 values(1)");
        cmd1.ExecuteNonQuery();
    }

    using (var conn2 = shardmap.OpenConnectionForKey(tenantId2, credentialsStr))
    {
        conn2.Open();
        var cmd2 = conn2.CreateCommand();
        cmd2.CommandText = string.Format("insert into T1 values(2)");
        cmd2.ExecuteNonQuery();
    }

    scope.Complete();
}

适用于 Azure 云服务的 .NET 安装.NET installation for Azure Cloud Services

Azure 为托管 .NET 应用程序提供了多个产品。Azure provides several offerings to host .NET applications. 如果产品的来宾 OS 版本低于弹性事务所需的 .NET 4.6.1,需要将来宾 OS 升级到 4.6.1。If the guest OS of the offering is smaller than .NET 4.6.1 required for elastic transactions, you need to upgrade the guest OS to 4.6.1.

对于 Azure 应用服务,当前不支持升级到来宾 OS。For Azure App Services, upgrades to the guest OS are currently not supported. 对于 Azure 虚拟机,只需要登录到 VM 并运行最新的 .NET framework 安装程序即可。For Azure Virtual Machines, simply log into the VM and run the installer for the latest .NET framework. 对于 Azure 云服务,需要将更高版本的 .NET 安装包括到部署的启动任务中。For Azure Cloud Services, you need to include the installation of a newer .NET version into the startup tasks of your deployment. 在云服务角色上安装 .NET 中说明了概念和步骤。The concepts and steps are documented in Install .NET on a Cloud Service Role.

请注意,与 .NET 4.6 的安装程序相比,.NET 4.6.1 的安装程序在 Azure 云服务上执行引导过程时,可能需要更多的临时存储空间。Note that the installer for .NET 4.6.1 may require more temporary storage during the bootstrapping process on Azure cloud services than the installer for .NET 4.6. 为了确保安装成功,需要在 ServiceDefinition.csdef 文件中启动任务的 LocalResources 部分和环境设置中,增加 Azure 云服务的临时存储,如以下示例所示:To ensure a successful installation, you need to increase temporary storage for your Azure cloud service in your ServiceDefinition.csdef file in the LocalResources section and the environment settings of your startup task, as shown in the following sample:

<LocalResources>
...
    <LocalStorage name="TEMP" sizeInMB="5000" cleanOnRoleRecycle="false" />
    <LocalStorage name="TMP" sizeInMB="5000" cleanOnRoleRecycle="false" />
</LocalResources>
<Startup>
    <Task commandLine="install.cmd" executionContext="elevated" taskType="simple">
        <Environment>
    ...
            <Variable name="TEMP">
                <RoleInstanceValue xpath="/RoleEnvironment/CurrentInstance/LocalResources/LocalResource[@name='TEMP']/@path" />
            </Variable>
            <Variable name="TMP">
                <RoleInstanceValue xpath="/RoleEnvironment/CurrentInstance/LocalResources/LocalResource[@name='TMP']/@path" />
            </Variable>
        </Environment>
    </Task>
</Startup>

跨多个服务器的事务Transactions across multiple servers

备注

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

重要

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

Azure SQL 数据库中支持跨不同 SQL 数据库服务器的弹性数据库事务。Elastic database transactions are supported across different SQL Database servers in Azure SQL Database. 当事务跨越 SQL 数据库服务器边界时,参与的服务器将首先需要进入相互通信关系。When transactions cross SQL Database server boundaries, the participating servers first need to be entered into a mutual communication relationship. 一旦建立了通信关系,任意两个服务器中的任何数据库都可以与另一服务器的数据库参与弹性事务。Once the communication relationship has been established, any database in any of the two servers can participate in elastic transactions with databases from the other server. 当事务跨越两个以上的 SQL 数据库服务器时,任意 SQL 数据库服务器对之间的通信关系需要准备就绪。With transactions spanning more than two SQL Database servers, a communication relationship needs to be in place for any pair of SQL Database servers.

使用以下 PowerShell cmdlet 来管理弹性数据库事务的跨服务器通信关系:Use the following PowerShell cmdlets to manage cross-server communication relationships for elastic database transactions:

  • New-AzSqlServerCommunicationLink:使用此 cmdlet 在 Azure SQL 数据库中的两个 SQL 数据库服务器之间创建新的通信关系。New-AzSqlServerCommunicationLink: Use this cmdlet to create a new communication relationship between two SQL Database servers in Azure SQL Database. 这种通信关系是对称的,这意味着这两台服务器可以使用另一台服务器启动事务。The relationship is symmetric which means both servers can initiate transactions with the other server.
  • Get-AzSqlServerCommunicationLink:使用此 cmdlet 来检索现有通信关系及其属性。Get-AzSqlServerCommunicationLink: Use this cmdlet to retrieve existing communication relationships and their properties.
  • Remove-AzSqlServerCommunicationLink:使用此 cmdlet 来删除现有通信关系。Remove-AzSqlServerCommunicationLink: Use this cmdlet to remove an existing communication relationship.

监视事务状态Monitoring transaction status

使用 SQL DB 中的动态管理视图 (DMV) 监视正在进行的弹性数据库事务的状态和进度。Use Dynamic Management Views (DMVs) in SQL DB to monitor status and progress of your ongoing elastic database transactions. 与事务相关的所有 DMV 与 SQL DB 中的分布式事务相关。All DMVs related to transactions are relevant for distributed transactions in SQL DB. 可以在此处找到相应的 DMV 列表:与事务相关的动态管理视图和函数 (Transact-SQL)You can find the corresponding list of DMVs here: Transaction Related Dynamic Management Views and Functions (Transact-SQL).

这些 DMV 特别有用:These DMVs are particularly useful:

  • sys.dm_tran_active_transactions:列出当前正在使用的事务及其状态。sys.dm_tran_active_transactions: Lists currently active transactions and their status. UOW(工作单位)列可以标识属于同一分布式事务的不同子事务。The UOW (Unit Of Work) column can identify the different child transactions that belong to the same distributed transaction. 同一分布式事务中的所有事务具有相同的 UOW 值。All transactions within the same distributed transaction carry the same UOW value. 有关详细信息,请参阅 DMV 文档See the DMV documentation for more information.
  • sys.dm_tran_database_transactions:提供有关事务的其他信息,例如事务在日志中的位置。sys.dm_tran_database_transactions: Provides additional information about transactions, such as placement of the transaction in the log. 有关详细信息,请参阅 DMV 文档See the DMV documentation for more information.
  • sys.dm_tran_locks:提供当前进行中事务所持有的锁的相关信息。sys.dm_tran_locks: Provides information about the locks that are currently held by ongoing transactions. 有关详细信息,请参阅 DMV 文档See the DMV documentation for more information.

限制Limitations

SQL DB 中的弹性数据库事务当前存在以下限制:The following limitations currently apply to elastic database transactions in SQL DB:

  • 仅支持 SQL DB 中跨数据库的事务。Only transactions across databases in SQL DB are supported. 其他 X/Open XA 资源提供程序和除 SQL DB 以外的数据库无法参与弹性数据库事务。Other X/Open XA resource providers and databases outside of SQL DB cannot participate in elastic database transactions. 这意味着,弹性数据库事务无法扩展到本地 SQL Server 和 Azure SQL 数据库。That means that elastic database transactions cannot stretch across on premises SQL Server and Azure SQL Database. 对于本地的分布式事务,请继续使用 MSDTC。For distributed transactions on premises, continue to use MSDTC.
  • 仅支持来自 .NET 应用程序的客户端协调事务。Only client-coordinated transactions from a .NET application are supported. 目前已规划 T-SQL 的服务器端支持,例如 BEGIN DISTRIBUTED TRANSACTION,但尚未推出。Server-side support for T-SQL such as BEGIN DISTRIBUTED TRANSACTION is planned, but not yet available.
  • 不支持跨 WCF 服务的事务。Transactions across WCF services are not supported. 例如,有一个执行事务的 WCF 服务方法。For example, you have a WCF service method that executes a transaction. 事务范围内的调用将失败,并显示异常 System.ServiceModel.ProtocolExceptionEnclosing the call within a transaction scope will fail as a System.ServiceModel.ProtocolException.