MySQL insert operation hang while another update operation is running, but can't figure out why
---TRANSACTION 11465908, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 51589, OS thread handle 139964999091968, query id 157268 172.18.70.57 root update
insert into t_info(c_id,c_class_no,c_score,c_name,gender) values (4,99,50,'gap',1)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5547 page no 3 n bits 80 index PRIMARY of table `lock_spy`.`t_info` trx id 11465908 lock_mode X locks gap before rec insert intention waiting
------------------


Transaction 1: would lock the whole table because of  MySQL optimization itself since the table rows is very small.

One thing: if the table rows is large enough, eg. more than 1000, or others. MySQL would use the idx_score index to scan c_score>85;

Another:
Using  force index(idx_score) to query the data. Then, MySQL only lock secondary index idx_score ( also gaps), and corresponding records( not record gap).