【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)两步。涉及到写入数据，时间较长