MySQL Insert Operation Hang – Troubleshooting Update Operation Blocking

blockingMySQL

Transaction 1:

begin;
update t_info set gender=1 where c_score > 85;

Transaction 2:

begin;
insert into t_info(c_id,c_class_no,c_score,c_name,gender) values (4,99,50,'gap',1);

Transaction 2 hangs, and I can't figure out why.

Preconditions: mysql Ver 14.14 Distrib 5.7.19-17 tx_isolation: REPEATABLE-READ

DROP TABLE IF EXISTS `t_info`;
CREATE TABLE `t_info` (
  `c_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `c_class_no` int(11) NOT NULL DEFAULT '-1',
  `c_score` int(11) NOT NULL DEFAULT '0',
  `c_name` varchar(25) NOT NULL,
  `gender` tinyint(4) NOT NULL COMMENT '0 male, 1 female',
  PRIMARY KEY (`c_id`),
  KEY `idx_name_score` (`c_name`,`c_score`),
  KEY `idx_score` (`c_score`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO `t_info` 
VALUES 
(1,1,60,'no1',0),
(2,2,60,'no2',0),
(3,3,60,'no3',0),
(6,4,78,'gap',1),
(7,5,95,'no5',0),
(10,8,87,'jianhaiqing',0),
(11,36,20,'no20',0),
(14,13,100,'no13',0),
(20,21,120,'no21',0),
(21,24,83,'no87-2',0);

table recods

Best Answer

---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).

Related Question