0

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

4
  • When you have an actively blocked trx, run ‘SHOW ENGINE INNODB STATUS\G’ and review the transactions section.
    – eroomydna
    Aug 8, 2018 at 8:21
  • It's an exactly right direction to figure out the problem;
    – Jianqing
    Aug 8, 2018 at 10:39
  • ---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 ------------------
    – Jianqing
    Aug 8, 2018 at 10:41
  • Isn't this a pretty straightforward table lock during the update? Presuming you don't have an index on the predicate column, it will have to do a table scan.
    – pim
    Aug 8, 2018 at 12:36

2 Answers 2

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

0
0

I suspect this a pretty straightforward table lock during the update. Presuming you don't have an index on the predicate column, it will have to do a table scan to evaluate the > 85.

Adding an index on c_score should remedy this by allowing for a seek (or at least an index scan) to occur.

5
  • i have figure out the issue. Use the "update t_info force index(idx_score) set gender=1 where c_score > 85 ;" instead. The insert operation can be committed successfully. Using force index(idx_score) to disallow MySQL optimization due the minor rows.
    – Jianqing
    Aug 8, 2018 at 14:25
  • Glad you found a solution. If the force usage of the index stopped the locking, then you're on the right track. I wonder if your statistics are off? Try removing the force and running ANALYZE TABLE t_info; to rebuild the indexes and refresh the stats. Barring that, I'd reckon that the distribution of rows > 85 is so skewed that it's electing a table scan anyways because it's more efficient.
    – pim
    Aug 8, 2018 at 14:55
  • I did ANALYZE TABLE t_info, explain tools still use Key PRIMARY instead of idx_score since there are only 10 records in this table. So table scan may be more efficient. Only the optimization algorithm can tell the truth and why.
    – Jianqing
    Aug 9, 2018 at 1:32
  • 1
    @pimbrouwers - 40% of the table is >85, so the Optimizer is very likely to ignore the index and do a table scan since it is deemed faster. Change to >111 and it will probably automatically use the index.
    – Rick James
    Aug 23, 2018 at 2:15
  • @RickJames seems that way. The picture wasn't posted when I originally provided my answer. I will edit.
    – pim
    Aug 23, 2018 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.