RDS纯查询压测，200线程的并发下，出现了"table level lock"

1. 问题描述：您在使用RDS MySQL进行纯查询压测时，在（提交读）隔离级别下，随着并发线程增加至200，数据库出现了“table level lock”现象，导致后续查询请求等待，性能下降。期望QPS能随并发数正向增长，但实际遭遇锁争用问题。

2. 可能原因：

   • 锁机制冲突：在RR（可重复读）隔离级别下，即使执行的是只读查询，也可能因为Next-Key Locks或间隙锁等机制引发表级锁争用，尤其是在并发访问相同数据集时。
   • 索引不完善：缺乏合适的索引可能导致全表扫描，进而触发更多的锁行为。
   • 查询缓存影响：尽管查询缓存通常用于加速重复查询，但在高并发写入场景下，任何相关表的更新会清空涉及表的所有查询缓存，这可能间接影响到查询性能和锁行为。
   • 参数配置不当：某些参数设置如innodb_lock_wait_timeout、事务隔离级别的选择等，可能对锁行为有直接影响。

3. 排查步骤：

   • 检查隔离级别：确认当前使用的隔离级别是否为RR（Repeatable Read），考虑调整为RC（Read Committed）以减少锁的范围和时间。
   • 分析查询计划：使用EXPLAIN查看是否有查询导致了全表扫描，考虑添加或优化索引以减少锁的需要。
   • 监控锁状态：利用RDS MySQL提供的性能监控工具，检查锁等待情况和锁争用的详细信息。
   • 审查参数配置：检查与锁管理相关的参数设置，如锁等待超时时间，确保它们适合当前的工作负载。
   • 评估查询缓存策略：如果开启了查询缓存，分析其对当前工作负载的影响，必要时可尝试关闭以排除干扰。

4. 解决方案：

   • 调整隔离级别：根据业务需求，考虑将隔离级别调整为Read Committed，以减少锁的范围。
   • 优化索引：针对频繁查询路径创建或优化索引，避免不必要的全表扫描。
   • 调整参数：适当调整锁等待超时时间，或者根据监控结果调整其他相关参数。
   • 禁用查询缓存：如果查询模式多变且写操作频繁，考虑关闭查询缓存功能。
   • 应用层优化：在应用层面实现更细粒度的锁控制或采用乐观锁策略，减少数据库层面的锁竞争。

5. 预防措施：

   • 定期性能审计：定期进行SQL审计和性能调优，确保索引有效、查询高效。
   • 监控与警报：设置数据库性能监控和警报系统，及时发现并处理锁争用等问题。
   • 资源规划：合理规划硬件资源和实例规格，确保有足够的资源应对高并发场景。

请根据上述建议针对性地检查和优化您的RDS MySQL实例，特别是关注查询计划、隔离级别以及参数配置，以解决“table level lock”问题并提升并发查询性能。