Example: How SQL Server Explicitly Initiates a Transaction
Applies To: Windows 10, Windows 7, Windows 8, Windows 8.1, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, Windows Server Technical Preview, Windows Vista
A SQL Server explicitly initiated transaction works as follows:
When a stored procedure invokes the Transact-SQL BEGIN DISTRIBUTED TRANSACTION statement, SQL Server calls the DTC BeginTransaction method to obtain a transaction object representing the transaction. SQL Server then enlists in the transaction with its local DTC transaction manager and can participate in the two-phase commit protocol and receive commit or abort notifications from the DTC.
All database updates, inserts, and deletes performed by the stored procedure are done under the auspices of the DTC transaction. If the stored procedure invokes a remote stored procedure in another database, SQL Server propagates the DTC transaction with the call to the remote stored procedure. All updates to both databases are protected by the DTC transaction.
When the transaction completes, the stored procedure that initiated the transaction invokes the Transact-SQL COMMIT TRANSACTION statement. SQL Server then invokes the DTC Commit method. The 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. SQL Server then calls the DTC Abort method to undo the effects of the transaction.)
The stored procedure may then go on to perform more transactions.
The following example illustrates how a distributed DTC transaction can be used within a stored procedure to ensure that two SQL Server databases are updated consistently. The example uses a Microsoft SQL Server database called "Pubs," identical copies of which are maintained on two different systems. The example updates the address of an author on both database systems under the control of a DTC distributed transaction. 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), -- author ID
@addr varchar(40), -- new address
@toserver varchar(12) ) -- server name
AS -- procedure follows
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
-- and the procedure to execute with its 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 DTC transaction
COMMIT TRANSACTION
/*******************************************************/
/* Stored procedure to update an author's address on */
/* each remote server. */
/*******************************************************/
CREATE PROCEDURE update_addr(@au_id varchar(11), -- author ID
@addr varchar(40) ) -- new address
AS -- procedure follows
update authors set address = @addr where au_id = @au_id