How do i program a "Distributed Transaction"?

Question

In the past all my db needs were solved by sqlite. Now designing a db that can potentially be large i looked into "Distributed Transaction". How do i program that? I found some results explaining what distributed transaction is but non explained how to program it.

I know in code i have a few transactions that i may want to batch into a larger transaction. I use .NET. How might i do something like create a user where his PK is in one database and his user information such as name and settings are in another database. This may be a separate question but if i have two functions. One updates a media description and the other updates the content which could be on the same server or separate. How i nest these transactions and only commit at the end?

Answer 1

Approach that you described may lead to performance problems. Each request needs a transaction involving several databases. That is costly. Distributed transactions may be a good solution if you need, for example, involve a web service call and a DB request in the same transaction.

If you will need scalability to improve performance under heavy load in future, you might consider clustering. Instead of distributing parts of a single use-case among different servers, you will run simple (not distributed) transactions. And you will have benefit of scalability, because different requests will be handled by different servers.

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

I would recommend you to take a look at Errant Architectures by Martin Fowler (primarily talking about distributed computing, but also applicable to transactions), Distributed Transactions Overview from MSDN, and these two opinions:

Is Distribution really that bad? and (About) Martin Fowler's First Law of Distribution.

Answer 2

For an introduction to the concept, see Wikipedia.

For use in .NET, look at the System.Transactions namespace, especially the TransactionScope class. This will enable you to hook into the use of DTC. If you look at the System.Data.SQLite project then you can see how they have integrated DTC with SQLite.