.NET 6/Dapper调用MySQL存储过程遇锁等待超时问题求助

一、调整代码超时配置 #

现有代码未设置命令超时，默认时长不足以支撑10分钟级的存储过程执行，修改代码显式配置超时：

foreach (string sProc in SpList)
{
    using (IDbConnection connection = new MySqlConnection(connString))
    {
        await connection.OpenAsync();
        // 命令超时设为720秒（12分钟），根据实际存储过程耗时调整
        var result = await connection.ExecuteScalarAsync(
            sProc, 
            null, 
            commandType: CommandType.StoredProcedure,
            commandTimeout: 720
        );
    }
}

还可在连接字符串追加连接超时参数：Connection Timeout=180;（单位：秒），避免连接建立阶段超时。

二、核心优化：重构长耗时存储过程 #

存储过程执行10分钟是锁超时的根源，必须针对性优化：

• 用EXPLAIN分析存储过程内的SQL语句，定位全表扫描、无索引的慢查询，补加合适索引。
• 缩小事务范围：把非必要的查询操作移到事务外，仅在修改数据时开启事务，减少锁持有时间。
• 分批处理数据：若涉及大量数据插入/更新，每次处理1000-5000条，避免长时间占用锁资源。
• 避免使用LOCK TABLES锁表语句，依赖InnoDB的行级锁特性，降低锁冲突概率。

三、调整MySQL服务器参数 #

• 增大锁等待超时时间：默认innodb_lock_wait_timeout为50秒，临时调整执行：

  SET GLOBAL innodb_lock_wait_timeout = 300;
  

  永久生效需修改my.cnf/my.ini，添加innodb_lock_wait_timeout=300后重启MySQL（数值按需调整，不建议过大）。
• 检查innodb_buffer_pool_size，确保足够容纳常用数据，减少磁盘IO，加快存储过程执行速度。

四、优化执行逻辑 #

• 选择业务低峰期执行后端任务，避开高并发时段，减少锁竞争。
• 给锁超时异常添加重试机制，针对MySQL错误码1205（锁超时）做指数退避重试：

  int maxRetry = 3;
  foreach (string sProc in SpList)
  {
      bool executed = false;
      for (int i = 0; i < maxRetry; i++)
      {
          try
          {
              using (IDbConnection connection = new MySqlConnection(connString))
              {
                  await connection.OpenAsync();
                  await connection.ExecuteScalarAsync(
                      sProc, 
                      null, 
                      commandType: CommandType.StoredProcedure,
                      commandTimeout: 720
                  );
              }
              executed = true;
              break;
          }
          catch (MySqlException ex) when (ex.Number == 1205)
          {
              if (i == maxRetry - 1) throw;
              await Task.Delay(1000 * (i + 1)); // 每次重试间隔递增
          }
      }
  }
  

• 若存储过程间无依赖，可考虑并行执行，但需控制并发数，避免压垮数据库。

五、降低事务隔离级别 #

若当前使用默认的可重复读隔离级别，可降至读提交（READ COMMITTED），减少锁持有时长：

using (IDbConnection connection = new MySqlConnection(connString))
{
    await connection.OpenAsync();
    await connection.ExecuteAsync("SET TRANSACTION ISOLATION LEVEL READ COMMITTED;");
    var result = await connection.ExecuteScalarAsync(
        sProc, 
        null, 
        commandType: CommandType.StoredProcedure,
        commandTimeout: 720
    );
}

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