More About Transactions and Composite Transact-SQL Constructs - Distributed Transactions
(Page 5 of 6 )
Microsoft Distributed Transaction Coordinator (MSDTC) is a component that allows you to span transactions over two or more servers while maintaining transactional integrity.
Servers in this scenario are called resource managers, and MSDTC performs the function of transaction manager. In fact, all those resource managers do not even have to be Microsoft servers; they just have to be compatible with MSDTC. For example, it is possible to execute a single transaction against databases on Microsoft SQL Server and Oracle.
When transactions are distributed over different resource managers, different mechanisms have to be applied by the transaction coordinator to compensate for problems that might occur in such an environment. A typical problem is network failure. For example, everything might be executed properly by each individual resource manager, but if the transaction coordinator is not informed due to a network failure, the result is the same as if one of the resource managers had failed, and the transaction will be rolled back.
The mechanism for dealing with such problems is called the two-phase commit (2PC). As the name implies, it consists of two phases:
- Prepare phase Starts when a transaction manager receives a request to execute a transaction. It notifies the resource managers and informs them of the work that needs to be done. The resource managers perform all changes and even write everything from the transaction login memory to the disk. When everything is completed, each resource manager sends a status message indicating success or failure to the transaction manager.
- Commit phase Starts when the transaction manager receives messages from resource managers. If the resource managers successfully complete the preparation phase, the transaction manager sends a Commit command to the resource managers. Each of them makes the changes permanently to the database and reports the success of the operation to the transaction manager. If any of the resource managers reports failure during the preparation phase, the transaction manager will send a Rollback command to all resource managers.
From a developer’s point of view, distributed transactions are very similar to regular transactions. The major difference is that you need to use the following statement to start the transaction:
Begin Distributed Tran[saction] [transaction_name]
Distributed transactions can also be started implicitly, by executing a query or stored procedure that will be run against distributed servers.
Transactions are completed with regular Commit or Rollback statements. The following stored procedure updates two tables in a local database and then updates information in a remote database using a remote stored procedure:
Alter Procedure ap_LeaseShedule_Clear_distributed
-- Set value of Lease of all equipment associated to 0
-- Set total amount of Lease Schedule to 0. -- notify lease company that lease schedule is completed
@intLeaseScheduleId int
As
Declare @chvLeaseNumber varchar(50),
@intError int
-- Verify that lease has expired
If GetDate() < (Select EndDate
From dbo.LeaseSchedule
Where ScheduleId = @intLeaseScheduleId)
Raiserror ('Specified lease schedule has not expired yet!', 16,1)
If @@Error <> 0
Begin
Print 'Unable to eliminate lease amounts from the database!'
Return 50000
End
-- get lease number
Select @chvLeaseNumber = Lease.LeaseNumber
From dbo.Lease Lease
Inner Join dbo.LeaseSchedule LeaseSchedule
On Lease.LeaseId = LeaseSchedule.LeaseId
Where (LeaseSchedule.ScheduleId = @intLeaseScheduleId)
Begin Distributed Transaction
-- Set value of Lease of all equipment associated to 0
Update dbo.Inventory
Set Lease = 0
Where LeaseScheduleId = @intLeaseScheduleId
If @@Error <> 0 Goto PROBLEM
-- Set total amount of Lease Schedule to 0
Update LeaseSchedule
Set PeriodicTotalAmount = 0
Where ScheduleId = @intLeaseScheduleId
If @@Error <> 0 Goto PROBLEM
-- notify lease vendor
Exec @intError = lease_srvr.LeaseShedules..prLeaseScheduleComplete
@chvLeaseNumber, @intLeaseScheduleId
If @intError <> 0 GoTo PROBLEM
Commit Transaction
Return 0
PROBLEM:
print 'Unable to complete lease schedule!'
Rollback Transaction
Return 50000
Apart from a reference to the remote stored procedure, the only thing that needed to be done was to use the Distributed keyword to start the transaction. Everything else was managed by MSDTC.
Next: Typical Transaction-related Problems >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
