(I'm writing this post in English, as I assume it would be easier for me to express my thoughts regarding this subject in a more technical manner. Please try to excuse me for abusing the language…)

When dealing with cross domain transactions, with processes that may take more than split seconds, we may have a "too long transaction" that will hang one or more tables in the database. Let me start with an example. Assume you have an n-tier application with a business tier. Each domain has its own business process, and the tiers, by any mean, interact one with the other.

Figure 1 - Add Customer - Simple

In Figure 1, a system dealing with customers, companies, etc. (possibly some kind of a CRM solution), when adding a customer will also add a company, and to that company – a default warehouse. While relatively a simple process, considering the amount of necessary validation etc. that might be a lengthy operation. Of course, it should be an atomic operation, wrapped in a transaction (see Figure 2).

Figure 2 - Add Customer - Elaborative

A way to solve the lengthy transactions problem is to accumulate the proposed database interactions within a separate component, and when the time to "commit" arrives, and only then, to actually begin the transaction, execute the SQL statements and commit the transaction. In order to achieve that goal, we have changed the way our data access layer interacts with our Transaction Manager. The transaction manager is no longer just handling transaction, but has become an actual statements aggregator. It has two modes of operations: the common way – which is still needed in some cases, and the aggregator way – which solves the above problem.

The Transaction Manager

Generally, as mentioned there should be two methods – the "regular" one (use TransactionScope against the connection) and the "aggregator" one – which should solve the discussed problem. The way to solve that, is instead of opening a transaction against a database connection and directly call the execute methods on that connection, is to collect the statements and upon commit, and only then, begin the actual database connection and execute the collected statements. So the time the actual transaction locks the relevant table shrinks to the amount of time it takes to actual run the statements rather than the time it takes to run the whole business process.

Another benefit from this solution is that in some cases one can delay the whole process to a later time, and even stop it in the middle – pretty easily and without a compensation mechanism.

If you have any questions, drawbacks and/or comments – please write them down here or send me an email to amit@because.co.il