How to program a "distributed transaction"?

Question

In the past, all my db needs have been resolved with sqlite. Now, when designing a db that could potentially be large, I reviewed Distributed Transaction. How do I program this? I found some results explaining which distributed transaction is, but does not explain how to program it.

I know that there are several transactions in the code that I might want to make into a larger transaction. I am using .NET. How can I do something like creating a user, where his PC is in one database, and his user information, such as name and settings, is in another database. This may be a separate question, but if I have two functions. One updates the media description, and the other updates content that can reside on the same server or separately. How do I insert these transactions and only commit at the end?

Answer 1

For an introduction to the concept, see Wikipedia .

For use in .NET, browse the System.Transactions namespace, especially TransactionScope . This will allow you to connect to using DTC . If you look at the project System.Data.SQLite , you can see how they have integrated DTC with SQLite.

Answer 2

The approach you described can lead to performance issues. Each request requires a transaction with multiple databases. It is expensive. Distributed transactions can be a good solution if you need, for example, to include a web service call and a database query in the same transaction.

If you need scalability to improve performance under heavy load in the future, you can consider clustering . Instead of distributing parts of one use case between different servers, you will run simple (unallocated) transactions. And you will benefit from scalability, because different requests will be processed by different servers.

There are different opinions about the main goal of clustering and its applicability in various situations. I think it depends on the domain and requires careful analysis. Some links to clustering: Database clustering by Neil McAllister, Overview of SQL Server 2000 database clustering using MSCS (deprecated), Wikipedia clustering (computing), and Clustering Algorithms for general knowledge.

I would recommend that you take a look at Martin Fowler's Errant Architectures (mostly talking about distributed computing, but also applicable to transactions), Overview of Distributed Transactions from MSDN and these two views:

Is distribution really bad? and (O) Martin Fowler First Law of Distribution .