SqlBulkCopy.WriteToServerAsync cancellation does not interrupt a table-lock wait

Aperta
  • Qualcosa non va?
#4,965 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 2 giorni

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
25/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
csharp, sql
Ambito
databases

Direzione di ricerca

The bulk-completion path is in src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlBulkCopy.cs, around CopyBatchesAsyncContinuedOnSuccess, where the report says a synchronous RunParser() call runs without the operation's cancellation token. Start by running the reproduction against a disposable SQL Server with a table lock held, then trace where that synchronous read blocks. Done means canceling the token returns promptly with an OperationCanceledException instead of waiting for BulkCopyTimeout.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Describe the bug

SqlBulkCopy.WriteToServerAsync can remain blocked by a SQL Server table lock after its cancellation token is canceled. It returns only when BulkCopyTimeout expires, with a timeout SqlException, rather than promptly abandoning the operation. Applications cannot use the token to bound this server-side wait.

To reproduce

Run this console program with Microsoft.Data.SqlClient 7.1.1 on .NET 10. Set SQLCLIENT_PROBE_CONNECTION to a disposable SQL Server database connection string. The account needs DDL permission and access to sys.dm_exec_requests (the reproduction uses the SQL Server container's sa account).

The program observes that the bulk-copy session is blocked before canceling. Task.Run isolates the invocation because the async API can reach the synchronous server-read path before returning its Task.

using System.Data;
using System.Diagnostics;
using Microsoft.Data.SqlClient;

var cs = Environment.GetEnvironmentVariable("SQLCLIENT_PROBE_CONNECTION")!;
await using var observer = new SqlConnection(cs);
await observer.OpenAsync();
var name = "[dbo].[cancel_" + Guid.NewGuid().ToString("N") + "]";
using (var create = new SqlCommand($"CREATE TABLE {name} (id int NOT NULL)", observer))
    await create.ExecuteNonQueryAsync();
try
{
    await using var writer = new SqlConnection(cs);
    await writer.OpenAsync();
    await using var writeTransaction = (SqlTransaction)await writer.BeginTransactionAsync();
    int session;
    using (var spid = new SqlCommand("SELECT @@SPID", writer, writeTransaction))
        session = Convert.ToInt32(await spid.ExecuteScalarAsync());
    await using var blocker = new SqlConnection(cs);
    await blocker.OpenAsync();
    await using var blockingTransaction = (SqlTransaction)await blocker.BeginTransactionAsync();
    using (var block = new SqlCommand($"SELECT COUNT_BIG(*) FROM {name} WITH (TABLOCKX, HOLDLOCK)", blocker, blockingTransaction))
        await block.ExecuteScalarAsync();
    using var input = new DataTable();
    input.Columns.Add("id", typeof(int));
    input.Rows.Add(1);
    using var copy = new SqlBulkCopy(writer, SqlBulkCopyOptions.Default, writeTransaction)
        { DestinationTableName = name, BulkCopyTimeout = 5 };
    using var cancellation = new CancellationTokenSource();
    var pending = Task.Run(() => copy.WriteToServerAsync(input, cancellation.Token));
    using var deadline = new CancellationTokenSource(TimeSpan.FromSeconds(10));
    using var wait = new SqlCommand("SELECT COUNT(*) FROM sys.dm_exec_requests WHERE session_id=@session AND blocking_session_id > 0", observer);
    wait.Parameters.AddWithValue("@session", session);
    while ((int)(await wait.ExecuteScalarAsync(deadline.Token))! == 0)
        await Task.Delay(10, deadline.Token);
    var watch = Stopwatch.StartNew();
    cancellation.Cancel();
    try { await pending; Console.WriteLine("completed"); }
    catch (Exception error) { Console.WriteLine($"{watch.Elapsed.TotalSeconds:F2}s: {error.GetType().Name}: {error.Message}"); }
    await blockingTransaction.RollbackAsync();
}
finally
{
    using var drop = new SqlCommand($"DROP TABLE {name}", observer);
    await drop.ExecuteNonQueryAsync();
}
Expected behavior

Canceling the token should abandon the blocked operation before BulkCopyTimeout, as documented for the cancellation-token overload.

Actual result with BulkCopyTimeout = 5:

5.01s: SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
Further technical details
  • Microsoft.Data.SqlClient: 7.1.1
  • .NET SDK: 10.0.403; target net10.0; Windows x64 client
  • SQL Server: 2022 CU14, mcr.microsoft.com/mssql/server:2022-CU14-ubuntu-22.04, Linux Docker container
  • Checked current main and the 7.1 release notes; the bulk-completion path still calls synchronous RunParser() without the operation token.
  • Pinned source: CopyBatchesAsyncContinuedOnSuccess.

Please make the cancellation token interrupt this blocked server-read phase.

Found while implementing transaction enlistment in Relayer #290.

Lingua principale
C#
Stelle
1k
Fork
352
Merge medio
3g 21h
PR unite (30g)
112

Preparare l'ambiente

Apri in Codespaces

Avvia il container di sviluppo del progetto nel browser, con il tuo account GitHub.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di dotnet/SqlClient

Tutte le issue di dotnet/SqlClient

Issue simili

Altre issue su C#

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.