DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 54--------------------------------------------------
Exploring SQL Server's Distributed Transactions
DIR By: srinivasma_exceldbp
Date: April 18, 2014, 1:18 am
---------------------------------------------------------
Distributed transactions are one of the more powerful and
important functions within SQL Server. A distributed transaction
is just like any other transaction within the SQL Server
database, with a single key difference. The difference is this
type of transaction can exist across multiple different servers.
The transactions across different servers are all tied together
so the operations included within the transactions must all
succeed for the transaction to be completed.
We normally think of distributed transactions just within the
scope of a SQL Server transaction. However, you can also put
things like file system access by a .NET application within a
distributed transaction.
An example of this might be an application that creates PDFs of
receipts for a billing application. The application that creates
the files might begin a distributed transaction within the .NET
application code, get the data needed for the receipt from the
database, update the database with the new timestamp on which
the receipt was created and then create the PDF for the receipt.
If writing the PDF fails, we want to ensure changes to the
database are rolled back. That's why we use the distributed
transaction. In the event of any problems along the way, we'll
want to roll back the transaction and undo all changes,
including automatically creating the PDF.
You can also use distributed transactions whenever there's SQL
Server to SQL Server communication and changes will be made to
data during the course of the transaction, often done via a
linked server. However, you only need distributed transactions
when data is being changed. Select statements issued across
linked servers will simply use two different transactions, one
on the source server and one on the remote server.
If data is modified during the transaction, the two transactions
will be escalated to a distributed transaction. The transactions
are effectively merged so either server can cause the other
server to roll back the transaction on both servers. Within
T-SQL, there's no requirement that transactions are manually
started as distributed transactions.
SQL Server will automatically escalate transactions from normal
transactions to distributed transactions as needed. This
escalation can be expensive, however. If you're going to use
distributed transactions frequently within an application, it's
often better to specifically tell the SQL Server at the
beginning of the transaction it needs to be a distributed
transaction so that the escalation cost isn't there.
Using a Distributed Transaction
Creating a transaction manually as a distributed transaction
simply requires adding the keyword DISTRIBUTED into the BEGIN
TRANSACTION command as
BEGIN DISTRIBUTED TRANSACTION .
Committing or rolling back the distributed transaction is done
via the normal COMMIT and ROLLBACK keywords.
If a transaction has escalated to a distributed transaction,
there's nothing special you have to do within the T-SQL code to
commit or rollback the transaction. Simply use the COMMIT or
ROLLBACK keywords to cause the transaction on all the servers
involved with the transaction to commit or rollback as
specified.
Why Do You Need Distributed Transactions?
Distributed transactions ensure that data changes within
databases on multiple servers are kept in sync within the
context of the single T-SQL transaction. This ensures that
applications made up of multiple databases on multiple servers
are transnationally consistent with each other.
If this wasn't a requirement, then data could mismatch and
application data would be compromised.
Imagine you have an order entry and fulfillment system that
didn't require distributed transactions.
The order entry system is on server1. The fulfillment system is
on server2. We have our application built so when orders are
entered into SQL Server by calling a stored procedure. After
writing the data to the tables on server1, it calls a stored
procedure on server2 via a linked server that inputs the needed
information into the fulfillment systems tables. When the
fulfillment system is done running its stored procedure, output
from that stored procedure is returned via output parameters.
Then the order system uses those values to update some
additional tables within the order entry system.
If this system was able to run without a distributed
transaction, and there was a problem using the values the
fulfillment passed to back to the order entry system, the
transaction on the order entry system would fail and rollback.
That would prevent the order from being entered, even though it
had been entered into the fulfillment system. The end result
would be that the order would ship, but without any order
showing in the orders table. Therefore, there would be no way to
charge the customer who placed the order.
As we have distributed transactions, when the same situation is
put into place and there's a problem processing the values from
the fulfillment system, both the order and the data written to
the fulfillment system rolls back. So there is no order placed,
and there is no order shipped.
Coordinating Distributed Transactions
Within the Microsoft platform, all distributed transactions are
controlled by the Microsoft Distributed Transaction Coordinator
(MSDTC). The Distributed Transaction Coordinator talks to the
application using transactions such as SQL Server, custom .NET
applications and so on.
It also provides the transaction coordination link to the
distributed transaction coordinator on the remote machine. The
distributed transaction coordinator on the remote machine
doesn't need to be the Microsoft Distributed Transaction
Coordinator.
It could be a distributed transaction coordinator running on a
Linux server as an example.
No matter what distributed transaction coordinator the Microsoft
Distributed Transaction Coordinator is using to connect to the
application (SQL Server or .NET), it still maintains its
connections to the remote resource as normal. Only transaction
management, not the actual transaction, is done via the
Microsoft Distributed Transaction Coordinator.
*****************************************************
Page 1 of 1