BEGIN TRANSACTION (T-SQL)

Marks the starting point of an explicit, local transaction. BEGIN TRANSACTION increments @@TRANCOUNT by 1.

Syntax

BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable]

Arguments

transaction_name

Is the name assigned to the transaction. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. Use transaction names only on the outermost pair of nested BEGIN...COMMIT or BEGIN...ROLLBACK statements.

@tran_name_variable

Is the name of a user-defined variable containing a valid transaction name. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Remarks

BEGIN TRANSACTION represents a point at which the data referenced by a connection is known to be logically and physically consistent. 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. 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 starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-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.

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. 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 an action that must be recorded in the log.

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 are 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 starts a local transaction. The local transaction is escalated to a distributed transaction if the following actions are performed before it is committed or rolled back:

• An INSERT, DELETE, or UPDATE statement is executed that references a remote table on a linked server. The INSERT, UPDATE or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.
• A call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.

The local copy of SQL Server becomes the transaction controller and uses MS DTC to manage the distributed transaction.

Permissions

BEGIN TRANSACTION permissions default to any valid user.

Example

This example increases the royalties paid for all popular computer books by 10 percent.

DECLARE @TranName VARCHAR(20)

SELECT @TranName = 'MyTransaction'

BEGIN TRANSACTION @TranName

GO

USE pubs

GO

UPDATE roysched

SET royalty = royalty * 1.10

WHERE title_id LIKE 'Pc%'

GO

COMMIT TRANSACTION MyTransaction

GO

See Also