What is a distributed transaction?1
May 28, 2019 by Kenneth Fisher
A while back I did a post defining a transaction. Basically, a transaction is a unit of work. The example I gave in that post was pretty straightforward. Transferring money between two individuals.
UPDATE Balances SET CurrentBalance = CurrentBalance-50 WHERE name = 'Bob' UPDATE Balances SET CurrentBalance = CurrentBalance+50 WHERE name = 'ImaPain'
If you are transferring money from one person to another you absolutely have to have both commands happen, or fail together. I’m hoping this is pretty obvious. So what happens if one of the statements is on another SQL instance or even another type of data store? That’s going to require a special type of transaction. A Distributed Transaction! It’s really that simple.
Well, it’s a bit more than that of course. You can explicitly make a transaction distributed using the command BEGIN DISTRIBUTED TRANSACTION and all distributed transactions are controlled by the Microsoft Distributed Transaction Coordinator sometimes known as DTC, MS DTC or MSDTC.
As with many simple concepts the actual execution is far more complicated. Setting up MSDTC, configuring servers to make it work, errors when it doesn’t, does it work with various high availability features, how is it affected by the cloud, etc.
A few final points. I wasn’t able to find the Microsoft documentation for MSDTC so if anyone has it and would add it into the comments that would be a big help. Also when reading Allan Hirt’s (b/t) post (the high availability link in the last paragraph) he mentioned that a distributed transaction would include transactions between different databases and not just between different SQL instances. That certainly makes sense but I wasn’t able to find any decisive evidence (other than Allan himself of course) one way or the other.
Category: Microsoft SQL Server, SQLServerPedia Syndication, Transactions | Tags: Microsoft SQL Server, transactions
One thought on “What is a distributed transaction?”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Nice one! I would, however, put the +/-50 bucks transfer UPDATE in an EXPLICIT Txn, maybe with BEGIN-TRY to show the transaction-control and all-or-nothing, some way of managing the 2 Update Transactions as one batch, but in essence to atomic operations…just so it is clear.
I’ve used Ryan’s blog in the past: https://www.ryanjadams.com/2018/07/msdtc-configuration-sql-support/
And obvious Alans’: https://sqlha.com/2017/06/07/full-dtc-support-ags-now-sql-server-2017/
But I’ve seen some crazy stuff with DTC causing DB’s to go SUSPECT when DTC Txn could not recover properly after a power failure in DC and SQL went down hard: https://sqlserver911.wordpress.com/2012/07/24/database-in-suspect-due-to-orphaned-msdtc-transaction/ (the closest blog I could find…)