Local and distributed Transactions

Transactions are directly executed by the database and not by data access layers like ADO.NET or OleDB. A transaction might look like this (SQL server)

BEGIN TRANSACTION

USE pubs;

UPDATE titles

SET advance = advance * 1.25

WHERE ytd_sales > 8000;

COMMIT;

Start and end of the transaction are marked by special SQL commands being sent to a certain data source; or, in other words, to a certain connection. That limits transactions to just one connection. But what can be done if partial operations of a transaction should be executed on different connections?

Picture 1: The concept of distributed transactions

For that a protocol is necessary, which is called Two-Phase-Commit. In the first phase of the commit all participating data sources are asked if they are ready to perform a commit. If this is the case for all data sources, the commit is executed for all data sources.