【MySQL】truncate抛异常System Lock

一. 背景

表数据大概几十万,不到五十万
每隔10几分钟使用truncate清空数据,然后再写入等量数据
数据库主从结构
数据库版本5.7

二. 现象

执行truncate时从库偶发抛异常【System Lock】,非必现。占用线程资源,导致后续SQL语句全部阻塞
从库不可用,进而导致路由到该从库的请求全部阻塞,从而导致应用雪崩

三. 分析

网上搜了很多资料,都说是truncate在MySQL5.7及以下版本的bug,drop命令可以解决此问题,升级到8.0也可以解决此问题。
网上资料说数据量达到几千万才会出现此问题,实际上几十万就出现。原因可能和相关配置、系统并发量有关
MDL写锁原因(猜测?)

  1. 参考资料:https://time.geekbang.org/column/article/69862
  2. 资料中说:对表的增删改查会加MDL读锁(表级锁);对表结构做变更会加写锁(表级锁)(DDL语句),读写锁/写锁之间互斥。
  3. truncate属于DDL语句,即执行后会加MDL写锁(表级锁),其它对表的操作都会堵塞(包括查询),而由于truncate的一些机制(可能是bug)导致一直无法释放锁(偶发现象),结果导致【System Lock】

四. 解决思路

MySQL版本:5.7
表名:tb1
思路:使用drop 替代 truncate

方案一

  1. drop tb1_temp(如果存在)
  2. 重命名tb1为tb1_temp
  3. 创建新表tb1
  4. 写入数据到tb1
  5. drop tb1_temp

方案二(选中)

  1. drop tb1_new,tb1_bak(如果存在就删除)
  2. 创建表tb1_new
  3. 写入数据到tb1_new
  4. 重命名tb1到tb1_bak
  5. 重命名tb1_new到tb1
  6. drop tb1_bak

五. 结论

最终选中方案二
两者的区别在于

  1. 方案二的数据不一致及表不可以用的时间点在(4,5)两步。只涉及表重命名,时间较短
  2. 方案一的数据不一致及表不可以用的时间点在(2,3,4)两步。涉及到写入数据,时间较长
posted @ 2021-05-06 14:25    阅读(207)  评论(0编辑  收藏  举报