C# .NET Core异步迁移后Async SQLClient间歇性异常问题求助

问题背景 #

.NET Core项目将同步数据库访问逻辑迁移为异步实现后，出现两类随机触发的异常，相关业务代码如下：

using (var con = new SqlConnection(_fimpConnectionString))
using (var cmd = new SqlCommand("EMP2_GetPrositeDetails", con) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.Add(new SqlParameter("@SiteURL", site.Host));
    await con.OpenAsync();
    using (var dr = await cmd.ExecuteReaderAsync())
    {
        while (await dr.ReadAsync())
        {
            return MapToPrositeDetails(dr);
        }
    }
}

private PrositeDetails MapToPrositeDetails(DbDataReader dr)
{
    var prositeDetails = new PrositeDetails();

    prositeDetails.EntityId = dr.GetInt32("EntityId");
    prositeDetails.MemId = dr.GetInt32("MemId");
    prositeDetails.Address = new Address()
    {
        Building = dr.GetValueOrDefault<string>("address"),
        Street = dr.GetValueOrDefault<string>("streetName"),
        Town = dr.GetValueOrDefault<string>("town"),
        Postcode = dr.GetValueOrDefault<string>("postCode"),
        County = dr.GetValueOrDefault<string>("county"),
    };
    prositeDetails.Phone = dr.GetValueOrDefault<string>("phone");
    prositeDetails.IMPStatus = dr.GetInt32("IMPStatus");
    prositeDetails.GolfClub = dr.GetValueOrDefault<string>("GolfClub");
    prositeDetails.Url = dr.GetValueOrDefault<string>("url");
    prositeDetails.FirstName = dr.GetValueOrDefault<string>("firstName");
    prositeDetails.LastName = dr.GetValueOrDefault<string>("lastName");
    prositeDetails.JobTitle = dr.GetValueOrDefault<string>("JobTitle");
    prositeDetails.ClubUrl = dr.GetValueOrDefault<string>("clubURL");
    prositeDetails.GoogleAnalyticsId = dr.GetValueOrDefault<string>("googleAnalyticsID");
    prositeDetails.Email = dr.GetValueOrDefault<string>("Email");
    prositeDetails.CpEmail = dr.GetValueOrDefault<string>("CpEmail");
    prositeDetails.SocialMedia = new SocialMedia()
    {
        Twitter = dr.GetValueOrDefault<string>("twitter"),
        Facebook = dr.GetValueOrDefault<string>("facebook"),
        Youtube = dr.GetValueOrDefault<string>("youTube"),
        Instagram = dr.GetValueOrDefault<string>("flickr"),
        LinkedIn = dr.GetValueOrDefault<string>("linkedIn"),
    };
    prositeDetails.Coordinates = new Coordinates()
    {
        Latitude = float.Parse(dr.GetValueOrDefault<double>("latitude").ToString(), CultureInfo.InvariantCulture.NumberFormat),
        Longitude = float.Parse(dr.GetValueOrDefault<double>("longitude").ToString(), CultureInfo.InvariantCulture.NumberFormat)
    };
    prositeDetails.BannerText = dr.GetValueOrDefault<string>("BannerText");
    prositeDetails.reCAPTCHASecret = dr.GetValueOrDefault<string>("reCAPTCHASecret");
    prositeDetails.reCAPTCHASiteKey = dr.GetValueOrDefault<string>("reCAPTCHASiteKey");
    prositeDetails.NoFollowLinks = dr.GetBoolean("NoFollowLinks");
    prositeDetails.RequestTeeTime = dr.GetBoolean("RequestTeeTime");
    prositeDetails.PrivateEmail = dr.GetValueOrDefault<string>("privateEmail");
    prositeDetails.ProImageUrl = dr.GetValueOrDefault<string>("ProImageUrl");
    prositeDetails.HotJar = dr.GetValueOrDefault<string>("HotJar");
    prositeDetails.WebIntroHtml = dr.GetValueOrDefault<string>("WebIntroHtml");
    prositeDetails.ThemeId = dr.GetInt32("ThemeId");
    prositeDetails.HomepageHeader = dr.GetValueOrDefault<string>("HomepageHeader");
    prositeDetails.HomepageSubHeader = dr.GetValueOrDefault<string>("HomepageSubHeader");
    prositeDetails.HomePageBannerURL = dr.GetValueOrDefault<string>("ImageUrl");
    return prositeDetails;
}

运行过程中频繁抛出第一类异常，无法定位问题；偶发第二类异常，存储过程从未修改，不确定两类异常是否存在关联。

异常1：SafeHandle空引用错误 #

System.ArgumentNullException: SafeHandle cannot be null. (Parameter 'pHandle')
   at System.StubHelpers.StubHelpers.SafeHandleAddRef(SafeHandle pHandle, Boolean& success)
   at System.Data.SqlClient.SNINativeMethodWrapper.SNIPacketAllocateWrapper(SafeHandle pConn, IOType IOType)
   at System.Data.SqlClient.SNIPacket..ctor(SafeHandle sniHandle)
   at System.Data.SqlClient.TdsParserStateObjectNative.WritePacketCache.Take(SNIHandle sniHandle)
   at System.Data.SqlClient.TdsParserStateObjectNative.GetResetWritePacket(Int32 dataSize)
   at System.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.BeginExecuteReader(AsyncCallback callback, Object stateObject, CommandBehavior behavior)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl[TArg1](Func`4 beginMethod, Func`2 endFunction, Action`1 endAction, TArg1 arg1, Object state, TaskCreationOptions creationOptions)
   at System.Data.SqlClient.SqlCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at FgEmp.Services.MemberModule.MemberService.GetPrositeDetailsAsync(Uri site) in C:\Windows\system32\config\systemprofile\AppData\Local\Jenkins\.jenkins\workspace\PrositesCore\FgEmp.Services\MemberModule\MemberService.cs:line 327

异常2：字段不存在索引越界错误 #

2022-06-23 12:58:57,425 FATAL Logger.MoveNext [331] - MESSAGE: EMP2_GetPrositeDetails Failed: 
 System.IndexOutOfRangeException: EntityId
   at System.Data.ProviderBase.BasicFieldNameLookup.GetOrdinal(String fieldName)
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at FgEmp.Services.MemberModule.MemberService.MapToPrositeDetails(DbDataReader dr) in C:\Windows\system32\config\systemprofile\AppData\Local\Jenkins\.jenkins\workspace\PrositesCore\FgEmp.Services\MemberModule\MemberService.cs:line 348
   at FgEmp.Services.MemberModule.MemberService.GetPrositeDetailsAsync(Uri site) in C:\Windows\system32\config\systemprofile\AppData\Local\Jenkins\.jenkins\workspace\PrositesCore\FgEmp.Services\MemberModule\MemberService.cs:line 331

问题根因 #

两个异常由同一个问题触发，和存储过程逻辑无关，是异步场景下SQL Client对象生命周期管理异常导致的：

1. SafeHandle cannot be null的直接原因是正在执行异步IO的SqlConnection底层网络句柄被提前释放。旧版System.Data.SqlClient在.NET Core环境下的异步实现存在已知缺陷：异步IO操作执行过程中不会持有连接、命令对象的强引用，await触发线程切换时，如果GC刚好回收了超出代码作用域的连接/命令对象，底层的SNI网络句柄会被直接释放，后续IO操作访问已释放的空句柄就会抛出该错误。
2. 偶发的字段不存在索引越界是连接池串话导致的：被提前释放的连接会被直接归还到连接池，很快被其他并发请求拿到执行新的查询，此时前一个请求的DataReader还未完成读取，新查询返回的结果集元数据会覆盖Reader原有元数据，前一个请求继续读取时自然找不到预期的EntityId字段，属于连接生命周期异常引发的并发问题。

修复方案 #

按优先级依次处理即可彻底解决问题：

• 替换数据访问驱动
  卸载项目中引用的旧版System.Data.SqlClient，替换为微软官方长期维护的Microsoft.Data.SqlClient。新版本修复了大量异步IO下的句柄生命周期、连接池串话问题，是.NET Core环境下访问SQL Server的官方推荐驱动。
• 为数据库相关异步操作配置ConfigureAwait(false)
  所有数据库操作的await调用默认加上ConfigureAwait(false)，避免同步上下文捕获导致的线程切换延迟、对象生命周期异常，写法示例：

  await con.OpenAsync().ConfigureAwait(false);
  using (var dr = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
  {
      while (await dr.ReadAsync().ConfigureAwait(false))
      {
          return MapToPrositeDetails(dr);
      }
  }
  

• 显式指定CommandBehavior参数
  调用ExecuteReaderAsync时传入CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection，前者告知驱动按顺序读取结果集、不做额外的结果集缓存，后者保证DataReader释放时连接会被正确回收，避免连接提前归还到连接池：

  using (var dr = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection).ConfigureAwait(false))
  

• 排查全局连接复用问题
  检查项目其他代码，确认不存在共享SqlConnection、SqlCommand实例的写法。这两个对象都不是线程安全的，绝对不能注册为单例全局复用，必须在每个请求中通过using块新建实例。当前贴出的代码中using块的写法本身没有问题，需要排查其他业务逻辑中是否存在违规复用的情况。

内容的提问来源于stack exchange，提问作者Daniel Frappell