mysql大数据表删除操作锁表，导致其他线程等待锁超时（Lock wait timeout exceeded; try restarting transaction;）...

背景：

1.有一个定时任务，每10分钟入一批统计数据；

2.另一个定时任务，每天定时清理7天前数据，此定时任务每天01:18:00执行；

现象：

每天01:20:00的统计数据入库失败，异常信息如下，其他时间点均无问题：

分析：

1.按异常信息显示，插入数据时，等待锁超时，mysql事务锁等待时间默认为50秒，出现此问题，说明向此表写入数据时，有其他线程将表锁住了。

2.每天只在01:20:00的统计时，等待锁超时，而其他时间点没有问题，怀疑与定时任务有关。

3.搜索代码，每天01:20:00左右执行，且对该表进行操作的。经查，发现只有每天01:18:00开始执行的定时任务，会对此表操作，具体操作语句为：

delete from table_name where statistics_time < ?

statistics_time字段有索引（非唯一索引），非主键字段，此操作会将表锁住，另外，此表数据量为800万，删除操作执行时间较长。

4.结论：经如上分析可知，delete from操作导致该表被锁住时间较长，入库时无法获取锁，超时。

修改方案：

1.分批次删除

2.为每次删除，设定时间间隔

具体步骤如下：

假设今天为2018-03-15日

1.数据保存天数为7天，删除数据的定时任务每天执行一次，正常情况下，不会有8天前的数据。但系统停止一段时间，重新运行时，可能会有8天前数据，以防万一，第一步先执行一条sql，删除8天前数据。

delete from table_name where statistics_time < '2018-03-07 00:00:00'

2.时间点加3个小时，再执行一次删除，然后休眠5秒，以此类推，每次加3小时进行8次，总计删除了24小时的数据。

为什么要休眠呢？

其实，最初的修改时并未加休眠操作，结果现象是，上一次删除操作刚刚释放掉锁，下一次删除又立即获取了锁，其他线程仍然等待锁超时。这个现象只在现网（现网数据库较为繁忙）发现，本地测试并没有发现，为确保没有问题，并且此删除操作并不要求很短时间内完成，所以每次删除后，加了5秒的休眠，以确保其他线程可以获取该表的锁。

delete from table_name where statistics_time < '2018-03-07 03:00:00'

Thread.sleep(5000);

经如上修改后，数据删除正常，其他对此表的修改操作也正常，问题解决。

另：

1.示例中是每3小时删除一次，若数据量过大，这个时间可以再缩短；

2.每次删除后休眠5秒，猜测可以再缩短休眠时间，可以自行尝试；

转载于:https://www.cnblogs.com/sybblogs/p/8524525.html