1. 事故现场还原批量删除操作引发的连锁反应那天下午我正对生产环境的用户行为日志表执行例行清理。这张表已经积累了近2亿条记录按照 retention policy 需要删除3个月前的数据。我像往常一样执行了看似无害的DELETE语句DELETE FROM user_behavior_logs WHERE created_at DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 5000;为降低对线上影响我特意添加了LIMIT分批次删除选择业务低峰期执行使用pt-archiver工具控制速率但十分钟后监控系统突然告警——数据库连接池耗尽应用服务开始大面积超时。紧急查看processlist发现大量会话处于Waiting for table metadata lock状态而罪魁祸首正是我的删除会话。2. 死锁形成机制深度解剖2.1 MySQL元数据锁的隐藏陷阱当批量删除操作遇上长事务时元数据锁MDL的获取顺序成为关键。在我的案例中会话A删除操作首先获取MDL读锁会话B业务查询尝试获取MDL读锁成功会话A需要将MDL读锁升级为写锁等待会话B释放会话C新查询又请求MDL读锁排队等待A的写锁关键发现MDL锁是队列化处理的写锁请求会阻塞后续所有读锁申请2.2 事务持续时间的影响因子通过分析被阻塞的会话发现几个加剧问题的因素部分报表查询运行时间超过30秒事务隔离级别为REPEATABLE-READ持有锁时间更长连接池设置过大200连接导致雪崩效应graph TD A[DELETE请求MDL写锁] --|被阻塞| B[长查询持有MDL读锁] B -- C[新查询排队] C -- D[连接池耗尽]3. 应急处理与临时解决方案3.1 打破死锁链的实战步骤快速诊断SHOW PROCESSLIST; SELECT * FROM performance_schema.metadata_locks;精准止血优先kill执行时间超过10s的查询会话保留至少20%的连接池余量服务恢复# 渐进式重启应用节点 for i in {1..10}; do kubectl rollout restart deployment/app-$i sleep 15 done3.2 临时调整方案对比方案实施复杂度恢复速度数据风险Kill所有删除会话低快中降级读流量中中低重启数据库高慢高最终选择组合方案kill 50%的长时间查询 临时降级非核心功能4. 根本解决之道无锁化设计实践4.1 分区表冷热分离架构-- 改造为按时间分区 ALTER TABLE user_behavior_logs PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p_2023_q1 VALUES LESS THAN (TO_DAYS(2023-04-01)), PARTITION p_2023_q2 VALUES LESS THAN (TO_DAYS(2023-07-01)), PARTITION p_current VALUES LESS THAN MAXVALUE ); -- 删除整分区代替DELETE ALTER TABLE user_behavior_logs DROP PARTITION p_2023_q1;优势对比传统DELETE逐行扫描写undo logDROP PARTITION元数据操作毫秒级4.2 事件驱动TTL混合模式新架构设计原始数据写入KafkaFlink实时消费并写入热数据MySQL保留7天温数据TiDB保留30天冷数据S3Parquet长期存储// 使用状态TTL自动清理 StateTtlConfig ttlConfig StateTtlConfig .newBuilder(Time.days(30)) .cleanupInBackground() .build();5. 关键决策背后的权衡思考5.1 为什么不选择乐观锁虽然乐观锁在某些场景有效但批量删除存在本质差异冲突检测成本高需要比较全表时间戳重试机制不适用业务上不允许部分失败5.2 连接池参数的黄金比例经过压测得出的经验值# 应用连接池配置 spring: datasource: hikari: maximum-pool-size: ${DB_CORES * 2} connection-timeout: 3000 leak-detection-threshold: 60000关键公式最大连接数 (核心数 * 2) (磁盘数 * 2)6. 监控体系升级方案6.1 新增监控指标项# MDL等待时间监控 mysql_global_status_mdl_lock_wait_time{instance$host} # 长事务检测规则 - alert: LongRunningTransaction expr: mysql_processlist_time_seconds{state!Sleep} 10 for: 2m6.2 智能熔断策略基于历史数据训练的预测模型def should_circuit_break(): return (mdl_wait_time threshold and active_transactions cpu_cores * 3 and time.hour in business_peak_hours)这套系统在后续的618大促中成功拦截了3次潜在死锁风险