Is it possible to commit/rollback SqlTransaction in asynchronous?

Question

I'm trying to commit/rollback SqlTransaction in asynchronous. But it look like asynchronous is not supported. Is there any way to make it asynchronous without using raw SQL to start transaction?

Answer 1

It doesn't look like it. Going through the relevant code, all the other methods are asynchronous through and through (the synchronous version is the special case), while SqlTransaction and the other relevant code is synchronous only. For the parts that overlap, the SqlTransaction simply synchronously waits for task completion (e.g. when handling reconnects).

In fact, as you get through the depths of the code, the transaction operations explicitly forbid any asynchronous operations, so not including asynchronous transaction operations seems to be by design. If you do find a workaround, keep this in mind - the system is not designed to allow concurrent operations, so always use await as soon as you get the (whatever) task back.

If you wanted to go around this, you'd have to dig all the way down to creating the binary messages for SQL Server directly (or at least using reflection to execute some of the internal helper methods), which is not going to be all too easy (and it would require you to get access to the internal TCP connection used by SqlConnection, of course - and handle the reconnects etc.).

Looking at EntityFramework code, their solution is pretty simple - they just call Commit. This isn't as crazy as it might sound - the brunt of the work is done in the ExecuteXXXAsync methods themselves, the Commit is "free" - it only costs you the communication with the server, which usually isn't too expensive.

Given those limitations, your performance still shouldn't be affected noticeably - your thread pool might have to allocate a thread or two over the usual amount if you've got a few concurrent Commits, but the alternative is much more painful.

Answer 2

With .Net Core 3.0, it is now theoretically doable to commit or rollback asynchronously a transaction, with any transaction deriving from DbTransaction. So with SqlTransaction too.

See .Net Core issue #35012. (Or DbTransaction documentation.)

But more important for your concern, SqlTransaction underlying implementations do not leverage it yet: you can call async methods on it, but currently (up to .Net 7 at least and very likely with .Net 8 too) as far as I can see in the source code of Microsoft.Data.SqlClient, they are still delegating to their sync counterparts through DbTransaction default implementation. And this is of course the case also with System.Data.SqlClient (no async overrides).

It can be checked on the documentation too: DbTransaction.CommitAsync remarks:

The default implementation of this asynchronous method delegates to its synchronous counterpart and returns a completed Task, potentially blocking the calling thread.

And (Microsoft) SqlTransaction methods list: no overrides listed for async methods.

And also (System) SqlTransaction methods list:

CommitAsync(CancellationToken): Asynchronously commits the database transaction. (Inherited from DbTransaction)

So you can get your code ready to commit or rollback asynchronously with .Net Core 3.0 or higher, but you will have to wait quite more to get them actually async.

There is an issue about it here for Microsoft.Data.SqlClient. It does not look like it will come any time soon. (And it should never come for System.Data.SqlClient, since it is frozen since .Net Core 3.1.)

Answer 3

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    . . .
}