Hello everyone!
I know, I know, it has been a long time since you read anything interesting here. Well, as you should know by now, I changed job, and stepped out of the LIMS industry a bit. You will not be seeing much STARLIMS code moving forward; in fact, probably none at all. That nice framework is on its decline, and unfortunately, I don’t think it’s up for revival.
But that’s not why you got here, nor why you are reading this! I caught your attention with the title, didn’t I?
So, in my new job, I am working on a product, and one of the challenge we’re facing is a problem that comes back often: database transactions, nesting database transactions, and nowadays: asynchronous nested transactions!
THE PROBLEM.
Imagine you are building a super-duper application that runs with some RDBMS (i.e. SQL Server). Your application is web-based (!) and running in ASP.NET 8 (or ASP .NET Core, for those of you older).
Let’s now say you have an order processing system where you have the following scenarios:
Independent Transactions: Orders are processed concurrently to improve performance. Each order’s creation and inventory update should be isolated, with its own transaction context.
Nested Transactions: When adding items to an order, if any item fails, the entire order should fail. This nested operation should commit only at the outermost level, ensuring atomicity.
To represent better the problem, imagine the above 2 scenarios running from the same process, in 2 different tasks:
How do we implement this in such a way that the outcome will be an independent result? Let’s say we want the outcome of Task 1 to have no impact on Task 2, and vice-versa?
And how do we make it so that the sub-transaction in Order Creation is “nested” within the Create Order transaction?
Let’s assume the following code (it is not obvious to come with an actual scenario, but since we don’t know how the system will scale, let’s presume the following):
async Task CreateOrder(OrderRequest req)
{
using (var mainTransaction = Database.BeginTransaction() )
{
// nested transaction
await GenerateInvoice(newOrder);
await mainTransaction.Commit();
}
}
async Task GenerateInvoice(Order order)
{
using (var nestedTransaction = Database.BeginTransaction() )
{
/* invoice generation logic */
await nestedTransaction.Commit();
}
}
async Task UpdateInventory(Order order)
{
using (var tr = Database.BeginTransaction() )
{
/* update inventory logic */
await tr.Commit();
}
}
And then, for testing purposes, let’s say we were running CreateOrder in one Task, and UpdateInventory in the other Task.
How can we ensure that they will be independent? How can we make them “awaiter aware”?
THE SOLUTION
LocalAsync. We can make the transaction stack and the IDbTransaction local async by decorating them like this:
LocalAsync<List<Transactions>> transactions = new();
LocalAsync<Transaction> scopedTransaction = new();And this, my friend, will make the variable unique per async call.
In the Database class (or wherever you will handle your transactions), you will wrap the BeginTransaction like this:
public ITransaction BeginTransaction()
{
var transactions = GetTransactions();
if (transactions.Count == 0)
{
var connection = GetConnection();
scopedTransaction.Value = connection.BeginTransaction();
}
var tr = new Transaction(this, scopedTransaction.Value!);
transactions.Add(tr);
return tr;
}
public List<Transaction> GetTransactions()
{
transactions.Value ??= [];
return transactions.Value!;
}What happens: whenever the code calls BeginTransaction, it will look in the current async scope for the list of transactions. If found, it will use it, else create a new one (see GetTransactions).
Then, the actual database transaction is abstracted within a custom Transaction class and defined only for the 1st transaction of your stack. So, if you call BeginTransaction 3 times in the same scope, you’ll have 3 “Transactions” (our custom class), but only the one in position 0 will have an actual IDbTransaction. That guy will be responsible to Commit(); or Rollback in the Dispose. The others become “virtual” transactions with no effect other than tracking (the transactions are wrappers to ensure transactional context without actual commits/rollbacks except for the first in the list).
This allows Nested transactions, and this allows multi-task transactions. Assuming the following code:
var t1 = Task.Run(async () =>
{
using (var tr = Database.BeginTransaction())
{
await repo1.DoSomething();
await repo2.DoSomethingElse(); // Let's assume this repo function also has a Database.BeginTransaction() because it can be used outside of this code!!!
await tr.Commit();
}
});
var t2 = Task.Run(async () =>
{
using (var tr = Database.BeginTransaction())
{
await repo1.DoSomethingAgain();
await repo1.LetsGo();
await tr.Commit();
}
});
await Task.WhenAll([t1, t2]);All outcomes are acceptable:
- t1 and t2 both succeed
- t1 succeeds and t2 fails
- t1 fails and t2 succeeds
- t1 and t2 both fail.
t1 would succeed only if everything in the using succeeds, including the nested DoSomethingElse() that contains its own BeginTransaction + Commit().
Of course, this example code would never be implemented other than in an integration test; but heh, I think it’s the best way to explain the concept.
CONCLUSION
We’ve been banging our heads against the wall, running in circle, trying plenty of stuff including TransactionScope, but we couldn’t figure it out. Until someone said “We need somehow to figure out the context of the async call“.
That was it. That was the key. Now, I’m not saying this is a complete solution, but definitely, this is part of it. I have multiple different scenarios in my integration tests similar to the one I mention above, with sometimes forcefully fails, and everything works as expected. I just need to be careful on the concurrency; SQL Server developer is limited to 100 connections so to force collision, test retries and such, is quite a challenge; topic for another post maybe?..
I hope this can come in handy for you too!