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

オープン
  • 何か問題がありますか？
#4,965 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 2 日以内に返信

まだ誰も着手していません。

評価

難易度
4/5
見積もり時間
3〜5日
初心者へのやさしさ
25/100
issue の種類
バグ
明瞭さ
明確に書かれている
活発さ
活発
技術スタック
csharp, sql
領域
databases

調査の方向性

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.

索引モデルが issue の本文から書いたものです。

説明

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.

主要言語
C#
スター
1k
フォーク
352
平均マージ
3日 21時間
マージ済み PR（30日）
112

環境構築

Codespaces で開く

このプロジェクトの開発コンテナを、あなたの GitHub アカウントでブラウザ上に起動します。

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

dotnet/SqlClient のほかの issue

dotnet/SqlClient の issue をすべて見る

似ている issue

C# の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。