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

Abierto
  • ¿Algo no está bien?
#4,965 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 2 días

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
25/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
csharp, sql
Área
databases

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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.

Lenguaje dominante
C#
Estrellas
1k
Forks
352
Merge medio
3 d 21 h
PR fusionados (30 d)
112

Preparar el entorno

Abrir en Codespaces

Inicia el contenedor de desarrollo del proyecto en tu navegador, con tu propia cuenta de GitHub.

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de dotnet/SqlClient

Todos los issues de dotnet/SqlClient

Issues similares

Más issues de C#

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.