Repository navigation
Add missing async methods in System.Data.Common and implement IAsyncDisposable #28596
Description
Provider implementation tracking issues
- SqlClient: Feature | Implement async transaction begin/commit/rollback methods SqlClient#113
- Npgsql: Override new async methods introduced in corefx npgsql/npgsql#2481
- MySqlConnector: Implement new async methods coming in .NET Core 3.0 mysql-net/MySqlConnector#642
Specification
This issue consolidates #18225, #24244.
There are several APIs in System.Data.Common which possibly involve I/O, where we're missing async APIs altogether. This proposal adds these missing APIs, with default implementations that call the sync counterparts - much like the existing async methods on those APIs. In addition, all System.Data.Common types that implement IDisposable would be updated to implement IAsyncDisposable as well.
Note that in other cases async methods already exist but return Task, and we'd like to add overloads returning ValueTask. This is out of scope for this issue (but we'll work on these soon, see #19858, #25297).
This would ideally go into .NET Standard 2.1.
Proposal:
class DbConnection : IAsyncDisposable
{
protected virtual ValueTask<DbTransaction> BeginDbTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken);
// The following delegate to BeginDbTransactionAsync, like their sync counterparts
public ValueTask<DbTransaction> BeginTransactionAsync(CancellationToken cancellationToken = default);
public ValueTask<DbTransaction> BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken = default);
public virtual Task ChangeDatabaseAsync(string databaseName, CancellationToken cancellationToken = default);
public virtual Task CloseAsync(CancellationToken cancellationToken = default);
public virtual ValueTask DisposeAsync();
}
class DbTransaction : IAsyncDisposable
{
public virtual Task CommitAsync(CancellationToken cancellationToken = default);
public virtual Task RollbackAsync(CancellationToken cancellationToken = default);
public virtual ValueTask DisposeAsync();
}
class DbCommand : IAsyncDisposable
{
public virtual Task PrepareAsync(CancellationToken cancellationToken = default);
public virtual ValueTask DisposeAsync();
}
class DbDataReader : IAsyncDisposable
{
public virtual Task CloseAsync(CancellationToken cancellationToken = default);
public virtual ValueTask DisposeAsync();
}Note that this is only about adding async methods where non currently exist. #25297 is about adding ValueTask overloads where Task-returning async methods already exist (and where we have to think about naming etc.).
Returning Task vs. ValueTask
For the return value of the new proposed methods, our current guiding principle has been to opt for Task when the method in question represents a full database roundtrip operation, where any perf advantages of ValueTask are likely to be dominated by networking etc. Thus, method such as CommitAsync() and RollbackAsync() would return Task (aside from them having no return value).
Methods used for processing a resultset being streamed do seem like they'd benefit from returning ValueTask; examples include DbDataReader.ReadAsync(), DbDataReader.GetFieldValueAsync<T>(), which are out of scope of this proposal.
Cases such as DbConnection.CloseAsync() and DbDataReader.CloseAsync() seem to be between the two and we could probably go either way with them.
It would definitely be good to get some input on this.
Cancellation token overloads
Existing async methods in System.Data.Common come in pairs - a virtual one accepting CancellationToken and a non-virtual one delegating to the first. For the new methods we can just add one method with cancellation token defaulting to CancellationToken.None.
/cc @divega @ajcvickers @terrajobst @stephentoub @bgrainger
Edit history
| Date | Modification |
|---|---|
| 16/4 | DbConnection.Open() returns Task instead of ValueTask as per @terrajobst's question |
| 18/4 | Added DbDataReader.Get{Stream,TextReader}Async() |
| 18/4 | Removed DbDataReader.Get{Stream,TextReader}Async() again (GetFieldValueAsync<T>()) already provides this functionality) |
| 18/4 | Removed DbCommand.CancelAsync() |
| 15/5 | Changed BeginTransactionAsync() overloads to return ValueTask instead of Task, and changed to use the standard ADO.NET API pattern, as per design review. Changed DbDataReader.Close() to return Task instead of ValueTask. |
Activity
benaadams commentedon Feb 1, 2019
Don't think
default(ValueTask)has any advatange overTask.CompletedTaskin this scenario?/cc @stephentoub
bgrainger commentedon Feb 1, 2019
ChangeDatabaseAsync?bgrainger commentedon Feb 1, 2019
For most providers, that support pooling, it seems like
DbConnection.Close[Async]would by default just synchronously return the connection to the pool (and not perform any I/O). Perhaps the ability to returnTask.CompletedTaskfrom a (non-asynchronous) implementation means this isn't a problem in practice? Cf. mysql-net/MySqlConnector#467.For MySQL,
DbCommand.Cancel[Async]is always (lots of) network I/O, to send a "kill query" command to the server (and wait for acknowledgement). I would put it firmly in the "likely to be dominated by networking etc." category.roji commentedon Feb 1, 2019
@benaadams,
I agree.. It's a question of whether we expect these operations (command cancellation, physical connections) to occur intensively enough, and whether the I/O involved to be light enough, so that ValueTask would have a perf impact. I agree that it probably doesn't make much sense.
@bgrainger,
Yep, corrected...
I agree, and like with
OpenAsync()it makes sense to me forCloseAsync()to return a Task. It also fits squarely with the idea that calls involving a network roundtrip should return Task (because the Task vs. ValueTask difference is negligible anyway).But just as a note, the fact that an operation is usually expected to return synchronously doesn't necessarily seem decisive - both Task- and ValueTask- returning methods can have zero overhead in this case. I think it matters more what happens when the operation does return asynchronously, and how often that's expected to occur. Compare with
NpgsqlDataReader.Read(), which is also expected synchronously in most cases because rows would typically be buffered in memory - it still seems to make sense to have a version returning ValueTask (not a database roundtrip, but rather continuously parsing results).I agree...
benaadams commentedon Feb 1, 2019
Yeah, but then if its instant, you can return a
Task.CompletedTaskwhich is no allocations anIntPtrsized to pass around.ValueTask<T>makes a lot of sense for most scenarios; but the non-generic versionValueTaskmakes most sense when itsIValueTaskSourcebacked at some point as its2 x IntPtrin size so can't be passed via register; so can be slower if you aren't taking advantage of its other properties.roji commentedon Feb 7, 2019
Note: rather than providing an empty virtual implementation ofDisposeAsync(), we probably need to provide the async analogue of the dispose pattern (Dispose(bool)called by bothDispose()and the finalizer). Need to confirm.After thinking about this again (and not at 3AM), this doesn't seem to make any sense - finalizers shouldn't be doing I/O or calling async methods, and the classes in question call into the synchronous
Dispose(bool).MaceWindu commentedon Feb 9, 2019
Task BeginTransactionAsync: shouldn't it haveTask<DbTransaction>return type?roji commentedon Feb 9, 2019
Thanks, yes - corrected.
implement async connection/transaction methods support from https://g…
New async connection and transaction API (#1582)
terrajobst commentedon Apr 16, 2019
Video
@roji @divega
CancelAsync()? Also, how is callingCancelAsync()different from cancelling the cancellation token that was passed to the operation thatCancelAsync()would cancel?CloseAsync()returnsValueTask? It doesn't seem to on the perf critical path?DbDataReader.CloseAsync()seems odd to add an API that is known to be not useful.roji commentedon Apr 16, 2019
Unlike some other async cancellations, cancelling an ongoing database operation typically involves a (potentially) heavy networking operation (e.g. establish a new connection to the database). For this reason it may make sense to treat the cancellation as its own async operation that may need to be cancelled (although I admit it may be a bit contrived).
Assuming we decide to keep CancelAsync() (see below), we could drop the cancellation token (although this would be the first case I've heard of where an async method doesn't accept one).
But I do admit it could make sense to not have CancelAsync() at all, and say that Cancel() is there for sync only, whereas sync has the original cancellation token - that does seem to be a simpler API surface around cancellation. @divega what do you think?
For DbDataReader.CloseAsync() - assuming we actually add it (see next point) - it returns ValueTask for consistency with DisposeAsync(), to which it's very similar. Note that unlike with DbConnection, there's no way to reopen a DbDataReader, so disposing and closing really mean the same thing (unless some provider decides to implement them differently). Since DisposeAsync() must return ValueTask (because of IDisposableAsync), it seems to make sense to return the same from CloseAsync().
For DbConnection.CloseAsync(), I think you're right - will update the proposal.
It's definitely not absolutely necessary, but:
roji commentedon Apr 16, 2019
Just to clarify one of my points from above: in the current state of things, some providers already implement DbCommand.Cancel() via a database-specific mechanism. It should be possible to trigger that same mechanism asynchronously, but if we do that when the cancellation token is triggered we lose the option to pass it down into the socket layer.
roji commentedon Apr 18, 2019
Note: added
DbDataReader.GetStreamAsync()andDbDataReader.GetTextReaderAsync()to the proposal above - async support seems especially important for these column-streaming methods./cc @divega
31 remaining items
Add async methods in System.Data.Common, implement IAsyncDisposable
Add async methods in System.Data.Common, implement IAsyncDisposable
Add async methods in System.Data.Common, implement IAsyncDisposable
roji commentedon Jul 1, 2019
FYI for all those following, we're proposing to remove the cancellation token from {DbDataReader,DbConnection}.CloseAsync: dotnet/corefx#39070, see discussion in dotnet/standard#1283 (review)
Add async methods in System.Data.Common, implement IAsyncDisposable (#…
Implement new .NET Core 3.0 ADO.NET APIs
Add missing IAsyncDisposable interfaces to System.Data
Add missing IAsyncDisposable interfaces to System.Data
Add missing IAsyncDisposable interfaces to System.Data (#40872)
[automated] Merge branch 'release/3.0' => 'release/3.1' (#41079)