BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

指定啟動 Transact-SQLTransact-SQL 分散式交易協調器 (MS DTC) 所管理的 MicrosoftMicrosoft 分散式交易。Specifies the start of a Transact-SQLTransact-SQL distributed transaction managed by MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC).

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

  
BEGIN DISTRIBUTED { TRAN | TRANSACTION }   
     [ transaction_name | @tran_name_variable ]   
[ ; ]  

引數Arguments

transaction_nametransaction_name
這是在 MS DTC 公用程式內,用來追蹤分散式交易的使用者定義交易名稱。Is a user-defined transaction name used to track the distributed transaction within MS DTC utilities. transaction_name 必須符合識別碼的規則,且必須 <= 32 個字元。transaction_name must conform to the rules for identifiers and must be <= 32 characters.

@tran_name_variable@tran_name_variable
這是一個使用者定義變數的名稱,變數中包含在 MS DTC 公用程式內,用來追蹤分散式交易的交易名稱。Is the name of a user-defined variable containing a transaction name used to track the distributed transaction within MS DTC utilities. 這個變數必須用 charvarcharncharnvarchar 資料類型來宣告。The variable must be declared with a char, varchar, nchar, or nvarchar data type.

RemarksRemarks

執行 BEGIN DISTRIBUTED TRANSACTION 陳述式的 SQL Server Database EngineSQL Server Database Engine 執行個體是交易發起者,它會控制交易的完成。The instance of the SQL Server Database EngineSQL Server Database Engine executing the BEGIN DISTRIBUTED TRANSACTION statement is the transaction originator and controls the completion of the transaction. 當發出工作階段的後續 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 陳述式時,負責控制的執行個體會要求 MS DTC 跨越所涉及的所有執行個體來管理分散式交易的完成。When a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION statement is issued for the session, the controlling instance requests that MS DTC manage the completion of the distributed transaction across all of the instances involved.

交易層級快照集隔離不支援分散式交易。Transaction-level snapshot isolation does not support distributed transactions.

在已編列到分散式交易的工作階段執行參考連結伺服器的分散式查詢,是將 Database EngineDatabase Engine 遠端執行個體編列到分散式交易的主要方式。The primary way remote instances of the Database EngineDatabase Engine are enlisted in a distributed transaction is when a session already enlisted in the distributed transaction executes a distributed query referencing a linked server.

例如,如果對 ServerA 發出 BEGIN DISTRIBUTED TRANSACTION,工作階段會在 ServerB 上呼叫一個預存程序,在 ServerC 上呼叫另一個預存程序。For example, if BEGIN DISTRIBUTED TRANSACTION is issued on ServerA, the session calls a stored procedure on ServerB and another stored procedure on ServerC. ServerC 的預存程序會針對 ServerD 來執行分散式查詢,然後四部電腦才會涉及分散式交易。The stored procedure on ServerC executes a distributed query against ServerD, and then all four computers are involved in the distributed transaction. ServerA 的 Database EngineDatabase Engine 執行個體是交易的起始控制執行個體。The instance of the Database EngineDatabase Engine on ServerA is the originating controlling instance for the transaction.

Transact-SQLTransact-SQL 分散式交易所涉及的工作階段不會取得它們可傳給另一個工作階段的交易物件,以便將它明確加入分散式交易。The sessions involved in Transact-SQLTransact-SQL distributed transactions do not get a transaction object they can pass to another session for it to explicitly enlist in the distributed transaction. 遠端伺服器編列到交易的唯一方法,是當做分散式查詢或遠端預存程序呼叫的目標。The only way for a remote server to enlist in the transaction is to be the target of a distributed query or a remote stored procedure call.

在本機交易中執行分散式查詢時,如果目標 OLE DB 資料來源支援 ITransactionLocal,交易就會自動升級為分散式交易。When a distributed query is executed in a local transaction, the transaction is automatically promoted to a distributed transaction if the target OLE DB data source supports ITransactionLocal. 如果目標 OLE DB 資料來源不支援 ITransactionLocal,分散式查詢中就只允許唯讀作業。If the target OLE DB data source does not support ITransactionLocal, only read-only operations are allowed in the distributed query.

已編列到分散式交易的工作階段會執行參考遠端伺服器的遠端預存程序呼叫。A session already enlisted in the distributed transaction performs a remote stored procedure call referencing a remote server.

sp_configure remote proc trans 選項會控制在本機交易內對遠端預存程序的呼叫,是否會使本機交易自動升級為 MS DTC 所管理的分散式交易。The sp_configure remote proc trans option controls whether calls to remote stored procedures in a local transaction automatically cause the local transaction to be promoted to a distributed transaction managed by MS DTC. 連線層級的 SET 選項 REMOTE_PROC_TRANSACTIONS 可用來覆寫 sp_configure remote proc trans 所建立的執行個體預設值。當這個選項設為 ON 時,遠端預存程序呼叫會使本機交易升級為分散式交易。The connection-level SET option REMOTE_PROC_TRANSACTIONS can be used to override the instance default established by sp_configure remote proc trans. With this option set on, a remote stored procedure call causes a local transaction to be promoted to a distributed transaction. 建立 MS DTC 交易的連接會成為交易的發起者。The connection that creates the MS DTC transaction becomes the originator for the transaction. COMMIT TRANSACTION 會起始一項 MS DTC 協調認可。COMMIT TRANSACTION initiates an MS DTC coordinated commit. 如果 sp_configure remote proc trans 選項是 ON,本機交易中的遠端預存程序呼叫就會成為分散式交易的一部分並自動受到保護,而您不需重新撰寫應用程式,明確發出 BEGIN DISTRIBUTED TRANSACTION 來取代 BEGIN TRANSACTION。If the sp_configure remote proc trans option is ON, remote stored procedure calls in local transactions are automatically protected as part of distributed transactions without having to rewrite applications to specifically issue BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.

如需有關分散式交易環境和處理序的詳細資訊,請參閱 MicrosoftMicrosoft 分散式交易協調器文件集。For more information about the distributed transaction environment and process, see the MicrosoftMicrosoft Distributed Transaction Coordinator documentation.

權限Permissions

需要 public 角色中的成員資格。Requires membership in the public role.

範例Examples

這個範例會從 AdventureWorks2012AdventureWorks2012 本機執行個體和遠端伺服器之執行個體的 Database EngineDatabase Engine 資料庫中,刪除一個候選項。This example deletes a candidate from the AdventureWorks2012AdventureWorks2012 database on both the local instance of the Database EngineDatabase Engine and an instance on a remote server. 本機和遠端資料庫都會認可或回復交易。Both the local and remote databases will either commit or roll back the transaction.

注意

除非 MS DTC 目前安裝在執行 Database EngineDatabase Engine 執行個體的電腦中,否則,這個範例會產生錯誤訊息。Unless MS DTC is currently installed on the computer running the instance of the Database EngineDatabase Engine, this example produces an error message. 如需有關安裝 MS DTC 的詳細資訊,請參閱 Microsoft 分散式交易協調器文件集。For more information about installing MS DTC, see the Microsoft Distributed Transaction Coordinator documentation.

USE AdventureWorks2012;  
GO  
BEGIN DISTRIBUTED TRANSACTION;  
-- Delete candidate from local instance.  
DELETE AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
-- Delete candidate from remote instance.  
DELETE RemoteServer.AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT TRANSACTION;  
GO  

另請參閱See Also

BEGIN TRANSACTION (Transact-SQL) BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL) COMMIT TRANSACTION (Transact-SQL)
COMMIT WORK (Transact-SQL) COMMIT WORK (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL) ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL) ROLLBACK WORK (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)SAVE TRANSACTION (Transact-SQL)