How SQL Server Explicitly Initiates a Transaction

SQL Server’s Transact-SQL programming language has been extended to include the Begin Distributed Transaction statement. A SQL Server explicitly initiated transaction works as follows:

1. When a stored procedure invokes the Transact-SQL Begin Distributed Transaction statement, SQL Server invokes the MS DTC BeginTransaction method and obtains a transaction object representing the transaction. After obtaining the transaction object, SQL Server enlists in the transaction with its local MS DTC transaction manager. This permits SQL Server to participate in the two-phase commit protocol and to receive transaction commit or abort notifications from MS DTC.
2. All database updates, inserts, and deletes performed by the stored procedure are done under the auspices of the MS DTC transaction. If the stored procedure invokes a remote stored procedure in another database, SQL Server propagates the MS DTC transaction with the call to the remote stored procedure. All updates to both databases are protected by the MS DTC transaction.
3. When the work of the transaction is complete, the stored procedure that initiated the transaction calls the Transact-SQL COMMIT TRANSACTION statement. In response to the COMMIT TRANSACTION call, SQL Server invokes the MS DTC Commit method. MS DTC uses the two-phase commit protocol to coordinate commitment of the transaction. Alternatively, the stored procedure could call the Transact-SQL ROLLBACK TRANSACTION statement. In this case, SQL Server calls the MS DTC Abort method to undo the effects of the transaction.
4. The stored procedure may then go on to perform more transactions.

A resource manager that initiates and participates in MS DTC transactions must reside on a system on which the Complete MS DTC Service has been installed. For information on installation and configuration of MS DTC, refer to "Setting Up an MS DTC System” in the MS DTC Administrator’s Guide and Programmer’s Reference.

The following example illustrates how a distributed MS DTC transaction can be used within a stored procedure to ensure that two SQL Server databases are updated consistently. The stored procedure explicitly initiates the distributed transaction using the Transact-SQL BEGIN DISTRIBUTED TRANSACTION statement.

/*******************************************************/
/* Using BEGIN DISTRIBUTED TRANSACTION for explicit    */
/* server initiated transactions.                      */
/*******************************************************/
CREATE PROCEDURE change_addr(@au_id varchar(11),
                             @addr varchar(40),
                             @toserver varchar(12) ) AS
declare @execstr varchar(200)
 
-- 1. Start a Transaction
BEGIN DISTRIBUTED TRANSACTION
 
-- 2. Change local author information
update authors set address = @addr where au_id = @au_id
 
-- 3. Make a string with the server name, procedure to
--    execute and parameters
select @execstr = @toserver  '.pubs..update_addr '
 
-- 4. Update remote server
--     ( Note that these servers must be added to each
--      other via sp_addserver and sp_addremotelogin )
exec @execstr @au_id, @addr
 
-- 5. Commit the MS DTC transaction
COMMIT TRANSACTION
 
/*******************************************************/
/* Stored procedure to update an author's address on   */
/* the remote server.                                            */
/*******************************************************/
CREATE PROCEDURE update_addr(@au_id varchar(11),
                                @addr varchar(40)) AS
    update authors set address = @addr
    where au_id = @au_id