SQL Server Difference between Local and Distributed Transactions

Difference between Local and Distributed Transactions

Local transaction means the transaction that makes changes in a single database. Distributed transaction means transaction that makes changes to more than one database. In case of distributed transaction the different databases should not be on the same server. The servers may be distributed across network.

In case of distributed transaction, commit is slightly complex. SQL Server uses a protocol called two-phase commit. In the first phase, SQL Server asks every database involved with distributed transaction to prepare the transaction. Individual database verifies that it can commit the transaction and set sides all the resources necessary to do so. When all the databases involved in the transaction responds to the SQL Server with okay message then second phase begins. Then, SQL Server sends message to every database to commit the transaction. On the other hand, in first phase, if there is any error in a database then that database will not send okay message and then SQL Server will send message to rollback to all databases.

Microsoft DTC

Microsoft SQL Server provides a separate service for distributed transaction known as distributed transaction coordinator DTC. This service is installed separately in SQL Server to carry out distributed transactions.

In case of distributed transaction, you should use BEGIN DISTRIBUTED TRANSACTION statement.