Example: How SQL Server Implicitly Initiates a Transaction

 

Updated: July 19, 2016

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

Microsoft SQL Server is an example of a resource manager that supports implicit initiation of the DTC transactions. A SQL Server implicitly initiated transaction works as follows:

  1. When a stored procedure invokes the Transact-SQL BEGIN TRANSACTION statement, SQL Server determines whether the Remote Procedure Transactions option has been enabled by the database administrator. If the option has been enabled, SQL Server invokes the 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 DTC transaction manager. This permits SQL Server to participate in the two-phase commit protocol and to receive transaction commit or abort notifications from the DTC.

  2. 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.

  3. When the work of the transaction is complete, the stored procedure calls the Transact-SQL COMMIT TRANSACTION statement. In response to the COMMIT TRANSACTION call, SQL Server calls 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. In this case, SQL Server calls the DTC Abort method to undo the effects of the transaction.

  4. The stored procedure may then go on to perform more transactions.

The following example illustrates how Microsoft SQL Server can be used to implicitly begin a DTC transaction.

First, SP_CONFIGURE is used to enable the Remote Procedure Transactions option, as follows:

SP_CONFIGURE " REMOTE PROC TRANS" 1  
RECONFIGURE WITH OVERRIDE  
  

Other resource managers may choose to provide a similar means for existing applications to exploit the DTC.

/*******************************************************/  
/* Using the server configuration parameter for    */  
/* implicit 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. This transaction will become  
--  a DTC distributed transaction when the remote   
--  stored procedure is called because the server  
--  configuration parameter is set.  
  
BEGIN 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 procedure  
--  to execute  
  
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  

Example: How SQL Server Explicitly Initiates a Transaction

Community Additions

ADD
Show: