Implement async transaction commit/rollback methods

[roji]

.NET Core 3.0 will fill in some missing async APIs (e.g. transaction management), these should be implemented in SqlClient at some point.

https://github.com/dotnet/corefx/issues/35012

[roji]

(note: these should hopefully also make it into .NET Standard 2.1)

[Wraith2]

Is there written guidance anywhere on the suggested patterns of do and don't for implementing IAsyncDisposable?

[roji]

I'm not sure there's an "official" guide or anything, but the basic idea is to bring the instance to the same state it would have had if Dispose() was called, except that all I/O and other blocking calls need to be done asynchronously. The rest is going to depend on your specific implementation (e.g. whether any code can be shared between Dispose() and DisposeAsync()).

[roji]

Note this user complaint about the lack of async transaction APIs: dotnet/efcore#21818. Not sure how complex this is to implement, but I'm hoping it can be done at some point to unblock perf issues such as this.

[tastyeggs]

I want to bump up this issue - I think it is important that this be prioritized. At a certain scale and workload type, we hit a ceiling due to this issue, and are forced to do manual transaction management (using BEGIN/COMMIT statements). I'm sure there are many users who aren't aware this issue even exists, and simply scale up their application more than they need to.

[nikolai-mb]

This also seems to be a partial blocker preventing Hangfire from fully adopting the async pattern in the job scheduler if my understanding is correct. Lack of proper async in the SqlClient seems to be causing quite a bit of friction in both MS and third-party libraries alike