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

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

標示明確本機交易的起點。Marks the starting point of an explicit, local transaction. 明確交易會以 BEGIN TRANSACTION 陳述式開頭,並以 COMMIT 或 ROLLBACK 陳述式結尾。Explicit transactions start with the BEGIN TRANSACTION statement and end with the COMMIT or ROLLBACK statement.

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

語法Syntax

--Applies to SQL Server and Azure SQL Database
 
BEGIN { TRAN | TRANSACTION }   
    [ { transaction_name | @tran_name_variable }  
      [ WITH MARK [ 'description' ] ]  
    ]  
[ ; ]  
--Applies to Azure SQL Data Warehouse and Parallel Data Warehouse
 
BEGIN { TRAN | TRANSACTION }   
[ ; ]  

引數Arguments

transaction_nametransaction_name
適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseAPPLIES TO: SQL Server (starting with 2008), Azure SQL Database

這是指派給交易的名稱。Is the name assigned to the transaction. transaction_name 必須符合識別碼的規則,但不允許超出 32 個字元的識別碼。transaction_name must conform to the rules for identifiers, but identifiers longer than 32 characters are not allowed. 請只在巢狀 BEGIN...COMMIT 或 BEGIN...ROLLBACK 陳述式的最外一組使用交易名稱。Use transaction names only on the outermost pair of nested BEGIN...COMMIT or BEGIN...ROLLBACK statements. 即使 SQL ServerSQL Server 的執行個體不區分大小寫,transaction_name 一律還是會區分大小寫。transaction_name is always case sensitive, even when the instance of SQL ServerSQL Server is not case sensitive.

@tran_name_variable@tran_name_variable
適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseAPPLIES TO: SQL Server (starting with 2008), Azure SQL Database

這是包含有效交易名稱之使用者定義變數的名稱。Is the name of a user-defined variable containing a valid transaction name. 這個變數必須用 charvarcharncharnvarchar 資料類型來宣告。The variable must be declared with a char, varchar, nchar, or nvarchar data type. 如果有 32 個以上的字元傳給變數,只會使用前 32 個字元,其餘字元會截斷。If more than 32 characters are passed to the variable, only the first 32 characters will be used; the remaining characters will be truncated.

WITH MARK [ 'description' ]WITH MARK [ 'description' ]
適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseAPPLIES TO: SQL Server (starting with 2008), Azure SQL Database

指定在記錄中標示交易。Specifies that the transaction is marked in the log. description 是說明標記的字串。description is a string that describes the mark. 系統會先將超出 128 個字元的 description 截斷為 128 個字元,才會將它儲存於 msdb.dbo.logmarkhistory 資料表。A description longer than 128 characters is truncated to 128 characters before being stored in the msdb.dbo.logmarkhistory table.

如果使用 WITH MARK,必須指定交易名稱。If WITH MARK is used, a transaction name must be specified. WITH MARK 可讓您將交易記錄還原到具名標記。WITH MARK allows for restoring a transaction log to a named mark.

一般備註General Remarks

BEGIN TRANSACTION 會遞增 @@TRANCOUNT,遞增量是 1。BEGIN TRANSACTION increments @@TRANCOUNT by 1.

BEGIN TRANSACTION 代表連接所參考的資料在邏輯和實體上都一致的點。BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. 如果發生錯誤,可以回復 BEGIN TRANSACTION 之後的所有資料修正,使資料返回這個已知的一致狀態。If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. 每項交易都會持續到它完成無誤,且發出 COMMIT TRANSACTION 使修正成為資料庫的永久部分為止,或持續到發生錯誤,且利用 ROLLBACK TRANSACTION 陳述式來清除所有修正為止。Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement.

BEGIN TRANSACTION 會針對發出陳述式的連接來啟動一項本機交易。BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. 依照目前的交易隔離等級設定而定,此時會取得許多資源來支援交易鎖定的連接所發出的 Transact-SQLTransact-SQL 陳述式,直到利用 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 陳述式來完成它為止。Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQLTransact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. 交易長時間未完成會使其他使用者無法存取這些鎖定的資源,也會使記錄無法截斷。Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation.

雖然 BEGIN TRANSACTION 會啟動一項本機交易,但它並不會記錄在交易記錄中,直到應用程式後來執行必須記錄的動作 (如執行 INSERT、UPDATE 或 DELETE 陳述式) 為止。Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. 應用程式可以執行取得鎖定來保護 SELECT 陳述式的交易隔離等級之類的動作,但在應用程式執行修改動作之前,不會在記錄檔案留下任何記錄。An application can perform actions such as acquiring locks to protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.

利用交易名稱在一系列巢狀交易中命名多項交易對交易的影響不大。Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. 系統只會登錄第一個 (最外層) 交易名稱。Only the first (outermost) transaction name is registered with the system. 回復任何其他名稱 (不是有效的儲存點名稱) 都會產生錯誤。A rollback to any other name (other than a valid savepoint name) generates an error. 事實上在發生這個錯誤時,並不會回復在回復之前所執行的任何陳述式。None of the statements executed before the rollback is, in fact, rolled back at the time this error occurs. 只有當外部交易回復時,才會回復陳述式。The statements are rolled back only when the outer transaction is rolled back.

如果在認可或回復陳述式之前執行下列動作,BEGIN TRANSACTION 陳述式所啟動的本機交易會擴大到分散式交易:The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:

  • 執行參考連結伺服器上遠端資料表的 INSERT、DELETE 或 UPDATE 陳述式。An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. 如果用來存取連結伺服器的 OLE DB 提供者不支援 ITransactionJoin 介面,INSERT、UPDATE 或 DELETE 陳述式就會失敗。The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.

  • 當 REMOTE_PROC_TRANSACTIONS 選項設為 ON 時,呼叫遠端預存程序。A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.

SQL ServerSQL Server 的本機複本會成為交易控制器,且會利用 MicrosoftMicrosoft 分散式交易協調器 (MS DTC) 來管理分散式交易。The local copy of SQL ServerSQL Server becomes the transaction controller and uses MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC) to manage the distributed transaction.

您可以利用 BEGIN DISTRIBUTED TRANSACTION,將交易明確當做分散式交易來執行。A transaction can be explicitly executed as a distributed transaction by using BEGIN DISTRIBUTED TRANSACTION. 如需詳細資訊,請參閱 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

當 SET IMPLICIT_TRANSACTIONS 設定為 ON 時,BEGIN TRANSACTION 陳述式會建立兩筆巢狀交易。When SET IMPLICIT_TRANSACTIONS is set to ON, a BEGIN TRANSACTION statement creates two nested transactions. 如需詳細資訊,請參閱 SET IMPLICIT_TRANSACTIONS (Transact-SQL)For more information see, SET IMPLICIT_TRANSACTIONS (Transact-SQL)

標示的交易Marked Transactions

WITH MARK 選項會使交易名稱出現在交易記錄中。The WITH MARK option causes the transaction name to be placed in the transaction log. 當您將資料庫還原到較早的狀態時,可以利用標示的交易來取代日期和時間。When restoring a database to an earlier state, the marked transaction can be used in place of a date and time. 如需詳細資訊,請參閱使用標示的交易以一致的方式復原相關資料庫 (完整復原模式)RESTORE (Transact-SQL)For more information, see Use Marked Transactions to Recover Related Databases Consistently (Full Recovery Model) and RESTORE (Transact-SQL).

另外,如果您必須將一組相關資料庫復原到邏輯一致的狀態,交易記錄標示便是必要的。Additionally, transaction log marks are necessary if you need to recover a set of related databases to a logically consistent state. 分散式交易可以將標示放在這組相關資料庫的交易記錄中。Marks can be placed in the transaction logs of the related databases by a distributed transaction. 將這組相關資料庫復原到這些標示會產生一組交易一致的資料庫。Recovering the set of related databases to these marks results in a set of databases that are transactionally consistent. 將標示放在相關資料庫中需要特殊程序。Placement of marks in related databases requires special procedures.

只有當標示的交易更新資料庫時,才會將標示放在交易記錄中。The mark is placed in the transaction log only if the database is updated by the marked transaction. 未修改資料的交易不會有標示。Transactions that do not modify data are not marked.

BEGIN TRAN new_name WITH MARK 可以巢狀結構方式,放在未標示的現有交易內。BEGIN TRAN new_name WITH MARK can be nested within an already existing transaction that is not marked. 當您這麼做時,儘管交易可能已有給定的名稱,但 new_name 還是會成為該交易的標記名稱。Upon doing so, new_name becomes the mark name for the transaction, despite the name that the transaction may already have been given. 在下列範例中,標示的名稱是 M2In the following example, M2 is the name of the mark.

BEGIN TRAN T1;  
UPDATE table1 ...;  
BEGIN TRAN M2 WITH MARK;  
UPDATE table2 ...;  
SELECT * from table1;  
COMMIT TRAN M2;  
UPDATE table3 ...;  
COMMIT TRAN T1;  

當建立巢狀交易時,試圖標示已標示的交易會產生一則警告 (不是錯誤) 訊息:When nesting transactions, trying to mark a transaction that is already marked results in a warning (not error) message:

"BEGIN TRAN T1 WITH MARK ...;""BEGIN TRAN T1 WITH MARK ...;"

"UPDATE table1 ...;""UPDATE table1 ...;"

"BEGIN TRAN M2 WITH MARK ...;""BEGIN TRAN M2 WITH MARK ...;"

"Server:Msg 3920, Level 16, State 1, Line 3""Server: Msg 3920, Level 16, State 1, Line 3"

"WITH MARK 選項只可套用至第一個 BEGIN TRAN WITH MARK。""WITH MARK option only applies to the first BEGIN TRAN WITH MARK."

"已忽略選項。""The option is ignored."

權限Permissions

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

範例Examples

A.A. 使用明確交易Using an explicit transaction

適用於: SQL Server (從 2008 開始)、Azure SQL Database、Azure SQL 資料倉儲、平行處理資料倉儲APPLIES TO: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

這個範例會使用 AdventureWorks。This example uses AdventureWorks.

BEGIN TRANSACTION;  
DELETE FROM HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT;  

B.B. 復原交易Rolling back a transaction

適用於: SQL Server (從 2008 開始)、Azure SQL Database、Azure SQL 資料倉儲、平行處理資料倉儲APPLIES TO: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse

下列範例示範復原交易的效果。The following example shows the effect of rolling back a transaction. 在此範例中,ROLLBACK 陳述式將復原 INSERT 陳述式,但所建立的資料表仍會存在。In this example, the ROLLBACK statement will roll back the INSERT statement, but the created table will still exist.

 
CREATE TABLE ValueTable (id int);  
BEGIN TRANSACTION;  
       INSERT INTO ValueTable VALUES(1);  
       INSERT INTO ValueTable VALUES(2);  
ROLLBACK;  

C.C. 命名交易Naming a transaction

適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseAPPLIES TO: SQL Server (starting with 2008), Azure SQL Database

下列範例顯示如何命名交易。The following example shows how to name a transaction.

DECLARE @TranName VARCHAR(20);  
SELECT @TranName = 'MyTransaction';  
  
BEGIN TRANSACTION @TranName;  
USE AdventureWorks2012;  
DELETE FROM AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
  
COMMIT TRANSACTION @TranName;  
GO  

D.D. 標示交易Marking a transaction

適用於: SQL Server (從 2008 開始)、Azure SQL DatabaseAPPLIES TO: SQL Server (starting with 2008), Azure SQL Database

下列範例顯示如何標示交易。The following example shows how to mark a transaction. 已標示交易 CandidateDeleteThe transaction CandidateDelete is marked.

BEGIN TRANSACTION CandidateDelete  
    WITH MARK N'Deleting a Job Candidate';  
GO  
USE AdventureWorks2012;  
GO  
DELETE FROM AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
GO  
COMMIT TRANSACTION CandidateDelete;  
GO  

另請參閱See Also

BEGIN DISTRIBUTED TRANSACTION (Transact-SQL) BEGIN DISTRIBUTED 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)