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.