【线上问题】运行程序mysql报错 lock wait timeout excceded

今日是周日，周五接到个紧急任务，线上mysql报错了。

org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may involve defaultParameterMap
### The error occurred while setting parameters

### SQL: delete from xxx

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction;

SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

在执行某一条删除语句时报错的。

业务背景是：接收kafka消息，消费端线程池消费，消费的时候业务较为复杂，有循环删表写表的操作，并且执行的方法是Transactional事务执行。

只要两个请求时间间隔过短就会报错,通过日志可以看到，出问题时生产者和消费者的时间确实很短

2021-04-07 09:36:10.825  no

2021-04-07 09:36:10.833

2021-04-07 09:37:08.174  no

2021-04-07 09:37:08.195  no

2021-04-07 09:46:08.213

一个事务还未提交，另外一个事务又去删除表。

网上关于该问题的解决办法都是去设置数据库，一搜一大堆，显然不是解决我这问题的办法，我会试着从业务层面去解决。

 

初步想法是加一个ReentrantLock，如果还不能解决，那我考虑用一个全局map来缓存消费消息，然后循环去执行方法，改成单线程，有返回结果再执行下一个

未完待续....

==================4.16===============================

多线程改成单线程处理业务之后，这个问题确实解决了，但是测试提要求了，如果跑的数据量过多，单线程效率是个问题，所以还是从多线程实现去解决问题

初步定位到，多线程同时去操作数据库，如果该表的条件字段未加索引，会触发表锁，加了索引之后，变成行锁

https://blog.csdn.net/alexdamiao/article/details/52049993   这篇文章很有借鉴意义。

目前还存在一个问题，那就是多条件查询，会有重复的条件

2、 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。 
表tab_with_index的id字段有索引，name字段没有索引。 
mysql> select * from tab_with_index where id = 1;
+------+------+
| id   | name |
+------+------+
| 1    | 1    |
| 1    | 4    |
+------+------+

InnoDB存储引擎使用相同索引键的阻塞例子 
session1    session2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)    mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select from tab_with_index where id = 1 and name = ‘1’ for update;
+——+——+
| id | name |
+——+——+
| 1 | 1 |
+——+——+
1 row in set (0.00 sec)

 
     虽然session_2访问的是和session_1不同的记录，但是因为使用了相同的索引，所以需要等待锁：
mysql> select from tab_with_index where id = 1 and name = ‘4’ for update;
等待

 

这个问题还得思考一下