mysql删除表中不存在的记录会导致 Lock wait timeout exceeded

测试环境：

mysql5.6.46

测试表

mysql> desc ff;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(100) | YES | | NULL | |
±------±-------------±-----±----±--------±------+
mysql> select * from ff;
±-----±-----+
| id | name |
±-----±-----+
| NULL | aa |
| 1 | bb |

测试场景一：通过未建索引的字段删除记录

会话1
mysql> start transaction;
mysql> delete from ff where name=‘ddddd’;
Query OK, 0 rows affected (0.01 sec)

不commit
会话2
mysql> start transaction;
mysql> delete from ff where name=‘fffffff’;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction会话1
会话1查看连接信息
mysql> show processlist;
±—±-----±----------±-----±--------±-----±---------±------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±----------±-----±--------±-----±---------±------------------------------------+
| 34 | root | localhost | qjqx | Query | 0 | init | show processlist |
| 35 | root | localhost | qjqx | Query | 25 | updating | delete from ff where name=‘fffffff’ |
±—±-----±----------±-----±--------±-----±---------±------------------------------------+

测试场景二：通过创建索引的字段删除记录

会话1
mysql> create index idx_name on ff(name);
mysql> start transaction;
mysql> delete from ff where name=‘ddddd’;
Query OK, 0 rows affected (0.00 sec)
会话2

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from ff where name=‘fffffff’;
Query OK, 0 rows affected (0.00 sec)
mysql> explain delete from ff where name=‘fffffff’;
±—±------------±------±------±--------------±---------±--------±------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±---------±--------±------±-----±------------+
| 1 | SIMPLE | ff | range | idx_name | idx_name | 203 | const | 1 | Using where |
±—±------------±------±------±--------------±---------±--------±------±-----±------------+

总结：

1、mysql是通过索引来实现行锁的
2、在无索引的字段上删除存在或不存在的记录，会导致另一个会话执行超时