mysql 查询语句中采用Like查询，锁的范围是什么

Question

表结构:

CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `b1` int(11) DEFAULT NULL,
  `b2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b1` (`b1`)
) ENGINE=InnoDB;

插入数据(1,1,1)、(2,2,2)...以此类推到(100000,100000,100000)

事务一	事务二	事务三
begin
select id from b where b1 like '12%' lock in share mode
	delete from b where b1=1 （block）
		insert into b (b1,b2) values(100001, 100001) (block)

事务一的查询语句，加的共享锁的范围是多少。现在看到的是，事务二和事务三都会被阻塞住，是为什么？

Answer 1

锁的范围是根据sql扫描范围来定（正常是根据使用到的索引）。
事务一select id from b where b1 like '12%'的这条SQL正常应该要使用索引b1来锁定所有以12开头的行，也就是12,121,122...等1111行的数据，其它行就不锁定。
explain看下↓

dba test>explain select id from b where b1 like '12%' ;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | b     | NULL       | index | b1            | b1   | 5       | NULL | 100256 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

有使用到key b1，但扫描的rows 100256！！！
说明什么，全表扫描，锁住了整张表，也就是事务二的b1=1也会被锁住。
为什么全表扫描？因为b1的类型是int，而事务一中where条件需要转换成字符串去匹配，存在隐式转换，导致使用不到索引，只能全表扫。

怎么证明只锁事务中使用索引的指定行？把b1的类型改成varchar(10),再来执行你上面的三个事务，看事务二和三是否还阻塞住。（我自测不会）

Answer 2

Innodb，索引行用行锁，没有用到索引退化为表锁。

Answer 3

数字类型的列，不应该用like来搜索，不然无法用索引来定位，只能是全表扫描。既然是全表扫描，那就是锁全表，其他任何DML操作都会被锁。