mysql innodb lock gap ICP 求解惑

ming201206

         MYSQL 5.6,ICP在使用中遇到LOCK的问题，求解惑，详情见下文：
环境：
        数据库版本：mysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using  EditLine wrapper
        数据库隔离级别： REPEATABLE-READ
        autocommit    ：OFF                     optimizer_switch：index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
数据：
1、 表结构：
  mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  `e` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_t1_bcd` (`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2、表数据：
mysql> select * from t1;
+---+---+---+---+------+
| a | b | c | d | e    |
+---+---+---+---+------+
| 1 | 1 | 1 | 1 | a    |
| 2 | 2 | 2 | 2 | b    |
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 5 | 2 | 3 | 5 | e    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
| 8 | 8 | 8 | 8 | h    |
+---+---+---+---+------+
8 rows in set (0.00 sec)


操作过程：
session 1:
         delete from t1 where b>2 and b<5 and c=2;
执行计划如下：
mysql> explain select * from t1 where b>2 and b<5 and c=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: idx_t1_bcd
          key: idx_t1_bcd
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index condition
1 row in set (0.00 sec)


session 2:
            delete from t1 where a=4
------------被锁住。
session 3:
            mysql> select * from information_schema.innodb_locks;+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 38777:390:3:5 | 38777       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
| 38771:390:3:5 | 38771       | X         | RECORD    | `test`.`t1` | PRIMARY    |        390 |         3 |        5 | 4         |
+---------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+


根据锁及ICP的知识，此时加锁的情况应该是在索引  idx_t1_bcd 上的b>2 and b<5之间加gap lock,
                    idx_t1_bcd 上的c=2 加 X锁
             主键 a=3 加 x 锁。
应该a=4上是没有加X锁的，可以进行删除与更改。
但是从session3上的结果来，此时a=4上被加上了X锁。
求大牛解惑，谢谢。

ming201206

设置：set global innodb_status_output_locks=on

show engine innodb status:

---TRANSACTION 39948, ACTIVE 15 sec
3 lock struct(s), heap size 360, 8 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f7f44263700, query id 15 127.0.0.1 root cleaning up
TABLE LOCK table `test`.`t1` trx id 39948 lock mode IX
RECORD LOCKS space id 390 page no 4 n bits 80 index `idx_t1_bcd` of table `test`.`t1` trx id 39948 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000003; asc     ;;
1: len 4; hex 80000001; asc     ;;
2: len 4; hex 80000001; asc     ;;
3: len 4; hex 80000004; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000003; asc     ;;
1: len 4; hex 80000002; asc     ;;
2: len 4; hex 80000002; asc     ;;
3: len 4; hex 80000003; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000004; asc     ;;
1: len 4; hex 80000005; asc     ;;
2: len 4; hex 80000005; asc     ;;
3: len 4; hex 80000007; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000006; asc     ;;
1: len 4; hex 80000004; asc     ;;
2: len 4; hex 80000004; asc     ;;
3: len 4; hex 80000006; asc     ;;

RECORD LOCKS space id 390 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 39948 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
0: len 4; hex 80000003; asc     ;;
1: len 6; hex 000000009c0c; asc       ;;
2: len 7; hex 0b000001e20224; asc       $;;
3: len 4; hex 80000003; asc     ;;
4: len 4; hex 80000002; asc     ;;
5: len 4; hex 80000002; asc     ;;
6: len 1; hex 63; asc c;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000004; asc     ;;
1: len 6; hex 000000009770; asc      p;;
2: len 7; hex 200000026e0e30; asc     n 0;;
3: len 4; hex 80000003; asc     ;;
4: len 4; hex 80000001; asc     ;;
5: len 4; hex 80000001; asc     ;;
6: len 1; hex 64; asc d;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000006; asc     ;;
1: len 6; hex 00000000970e; asc       ;;
2: len 7; hex dd000001eb0110; asc        ;;
3: len 4; hex 80000006; asc     ;;
4: len 4; hex 80000004; asc     ;;
5: len 4; hex 80000004; asc     ;;
6: len 1; hex 66; asc f;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000007; asc     ;;
1: len 6; hex 00000000970d; asc       ;;
2: len 7; hex dc000001710110; asc     q  ;;
3: len 4; hex 80000004; asc     ;;
4: len 4; hex 80000005; asc     ;;
5: len 4; hex 80000005; asc     ;;
6: len 1; hex 67; asc g;;

babynorthface

这个应该不是GAP锁的问题吧。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录。然后session1 的delete 根据你的where条件锁定了辅助索引后还会锁定主索引，session 2 直接就是找到那个被锁定的主索引。

ming201206

babynorthface 发表于 2015-11-9 11:46
这个应该不是GAP锁的问题吧。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录。然后s ...

根据where条件扫辅助索引，然后根据辅助索引找到主键索引的值并锁住，我的问题是根据辅助索引扫到的应该只有其他一条记录。你可以参考何登成的博客：http://hedengcheng.com/?p=771#comment-280767

dianluxitike

MYSQL_VERSION5.6.26没有阻塞，可以成功执行。

ming201206

dianluxitike 发表于 2015-11-17 10:10
MYSQL_VERSION5.6.26没有阻塞，可以成功执行。

有没有忘记设置 ：autocommit    ：OFF     ？

dianluxitike

ming201206 发表于 2015-11-18 14:32
有没有忘记设置 ：autocommit    ：OFF     ？

my bad ! 当时看你这个例子的时候忘记之前改隔离级别了。后来又重新考虑了一下，验证之后发现不仅是4 这条记录被加X锁，而且，主键6,7都是会阻塞的。结果都是和楼主的一样，如下所示。

1. *************************** 1. row ***************************
   
2.     lock_id: 3656229:30449:3:8
   
3. lock_trx_id: 3656229
   
4.   lock_mode: X
   
5.   lock_type: RECORD
   
6. lock_table: `onlineddl`.`locktest`
   
7. lock_index: PRIMARY
   
8. lock_space: 30449
   
9.   lock_page: 3
   
10.    lock_rec: 8
    
11.   lock_data: 7
    
12. *************************** 2. row ***************************
    
13.     lock_id: 3656228:30449:3:8
    
14. lock_trx_id: 3656228
    
15.   lock_mode: X
    
16.   lock_type: RECORD
    
17. lock_table: `onlineddl`.`locktest`
    
18. lock_index: PRIMARY
    
19. lock_space: 30449
    
20.   lock_page: 3
    
21.    lock_rec: 8
    
22.   lock_data: 7
    

复制代码

其中delete from locktest where b< 5 and b > 2 and c=2; 的explain 执行计划是下面这样的。这里和楼主explain select 得到的执行计划是不同的。这里索引可以覆盖where的过滤条件，所以select查询语句进行了ICP优化。

1. +----+-------------+----------+-------+---------------+------+---------+-------+------+-------------+
   
2. | id | select_type  | table  |   type  | possible_keys | key  | key_len |    ref  | rows |    Extra   |
   
3. +----+-------------+----------+-------+---------------+------+---------+-------+------+-------------+
   
4. |  1 |    SIMPLE    | locktest | range |       idx         |  idx |     4     | const |    2      | Using where |
   
5. +----+-------------+----------+-------+---------------+------+---------+-------+------+-------------+
   

复制代码

看到type为range也就是说执行时利用索引idx进行了过滤，也就是说并没有进行ALL Table Scan。那按照B+树辅助索引的结构来考虑应该是只用访问辅助索引的B+树结构的叶子节点就可以判断是否满足range条件。且不说此辅助索引还是多列索引。
辅助索引的页里已经包含了《辅助索引+主键》这一点在表空间ibd文件里也可以得到确认。如下图可以看到辅助索引页的情况（省去不相关数据）。从粗体处开始到最后。

1. 00010060  02 00 1a 69 6e 66 69 6d  75 6d 00 05 00 0b 00 00  |...infimum......|
   
2. 00010070  73 75 70 72 65 6d 75 6d  00 00 10 00 15 80 00 00  |supremum........|
   
3. 00010080  01 80 00 00 01 80 00 00  01 80 00 00 01 00 00 18  |................|
   
4. 00010090  00 15 80 00 00 02 80 00  00 02 80 00 00 02 80 00  |................|
   
5. 000100a0  00 02 00 00 20 00 15 80  00 00 02 80 00 00 03 80  |.... ...........|
   
6. 000100b0  00 00 05 80 00 00 05 04  00 28 00 15 80 00 00 03  |.........(......|
   
7. 000100c0  80 00 00 01 80 00 00 01  80 00 00 04 20 00 30 00  |............ .0.|
   
8. 000100d0  15 80 00 00 03 80 00 00  02 80 00 00 02 80 00 00  |................|
   
9. 000100e0  03 00 00 38 00 15 80 00  00 04 80 00 00 05 80 00  |...8............|
   
10. 000100f0  00 05 80 00 00 07 00 00  40 00 15 80 00 00 06 80  |........@.......|
    
11. 00010100  00 00 04 80 00 00 04 80  00 00 06 00 00 48 ff 60  |.............H.`|
    
12. 00010110  80 00 00 08 80 00 00 08  80 00 00 08 80 00 00 08  |................|

复制代码

而官方手册关于这个也有一段话：A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.The locks are normally next-key locks that also block inserts into the “gap” immediately before the record.而且还有一点就是当锁定的是二级索引的时候还会锁定当前记录的下一个Gap。综上，我的感觉是一共只需要扫描3行数据，也只用锁定3行数据和相应的Gap，而这里最不能让我理解的是主键为6 的这行记录为什么也锁定了。还有一点是我把表数据改为如下

1. +----+---+---+---+------+
   
2. | a  | b | c | d | e    |
   
3. +----+---+---+---+------+
   
4. |  1 | 1 | 1 | 1 | a    |
   
5. |  2 | 2 | 2 | 2 | b    |
   
6. |  5 | 2 | 3 | 5 | e    |
   
7. |  4 | 3 | 1 | 1 | d    |
   
8. |  3 | 3 | 2 | 2 | c    |
   
9. |  7 | 4 | 5 | 5 | g    |
   
10. | 10 | 6 | 4 | 4 | f    |
    
11. | 14 | 8 | 8 | 8 | h    |
    
12. +----+---+---+---+------+
    

复制代码

然后进行如下查询

1. 回话1
   
2. begin;
   
3. delete from locktest where b=4;
   
4. 未提交
   
5. 回话2
   
6. insert into locktest select 8,5,1,1,'s';   query 1
   
7. insert into locktest select 8,6,1,1,'s'; query 2

复制代码

query 1 阻塞因为锁定了（4,6）区间，但是query 2 同样也阻塞，这又是为什么？
还有楼主可以看看Mysql专区里一篇名叫Mysql Record Locking 和Next Locking的那个帖子，其实是我发的。那个
是我遇到的另一个问题。

digdeep126

很简单，可能是没有注意到：key_len: 4 和 Extra: Using index condition
这说明了，仅仅使用了索引 idx_t1_bcd 中的 b 一列，所以应该是 将所有 b>2 and b<5 的索引记录和gap都锁住了：
delete from t1 where b>2 and b<5 and c=2 其实锁定的行有：
mysql> select * from t1 where b>2 and b<=6;
+---+---+---+---+------+
| a | b | c | d | e    |
+---+---+---+---+------+
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
+---+---+---+---+------+
4 rows in set (0.00 sec)
同时也用 X 锁锁住了表记录：a=4,3,6,7 四条记录。因为是RR隔离级别，所以即使使用索引中的 c=2 过滤掉了 a=4,5这两条记录，但是锁并没有释放。这是RR和RC的区别之一，RC会在where条件过滤之后，释放掉不符合条件的记录上的锁(显然这违反了“两阶段加锁”的原则)。
所以显然：
session2 的：
delete from t1 where a=4 被阻塞了。

digdeep126

dianluxitike 发表于 2015-11-18 18:01
my bad ! 当时看你这个例子的时候忘记之前改隔离级别了。后来又重新考虑了一下，验证之后发现不仅是4 这条 ...

刚开始，对于锁定了a=6这一条记录，我也不是很理解。
delete from t1 where b>2 and b<5 and c=2 其实锁定的行有：
mysql> select * from t1 where b>2 and b<=6;
+---+---+---+---+------+
| a | b | c | d | e    |
+---+---+---+---+------+
| 3 | 3 | 2 | 2 | c    |
| 4 | 3 | 1 | 1 | d    |
| 6 | 6 | 4 | 4 | f    |
| 7 | 4 | 5 | 5 | g    |
+---+---+---+---+------+
4 rows in set (0.00 sec)

这里 b<=6 的原因是，b 列中没有等于 5 的记录，所以 and b<5 实现为锁定 b<=6 的所有索引记录，这里有等于号的原因是，如果我们不锁定 =6 的索引记录，那么怎么实现锁定 <5 的gap 呢？也就是说锁定 b=6 的索引记录，是为了实现锁定 b< 5 的gap。也就是不能删除 b=6 记录的原因。

而这里 b >2 没有加等于号(b>=2) 的原因，是因为 b>2的这个gap 是由 b=3这个索引记录(的gap)来实现的，不是由 b=2索引记录(的gap) 来实现的，b=2的索引记录的gap lock只能实现锁定<2的gap，b>2的gap锁定功能，需要由 b=3的索引记录对应的gap来实现(b>2，b<3的gap)。

所以我们在session2中可以删除：a=1,2,8的记录，但是不能删除 a=6(因为该行的b=6)的记录。

这是我自己的理解。欢迎讨论。

dianluxitike

digdeep126 发表于 2015-11-23 12:02
刚开始，对于锁定了a=6这一条记录，我也不是很理解。
delete from t1 where b>2 and b select * from t1 ...

正解，这也是我的理解。对于区间锁定问题，假设区间为 a < X  < b。 对于左操作数，只会锁定区间而不会锁定该条记录，而对于右操作数，则不仅会锁定区间，而且还会锁定该条记录。或者说是形成了一种左开右闭的区间。同样的道理应用于 X < b(只会锁定小于b的区间和b这条记录) 和 X <=b(不仅锁定区间和b而且还会锁定下一条记录)，对应的X > b自然不用说是锁定的后面的区间。而且总结一下应该是这样的，当是非等值比较的时候才会产生上面的这种现象，当是等值比较的时候就不会有上述现象。当然对于主键索引，等值比较只是仅仅锁定记录，区间比较还是会锁定区间，但是锁定的元素还是与上述一样。其实还有一点不明。详述如下。表结构

1. +-------+---------+------+-----+---------+-------+
   
2. | Field | Type    | Null | Key | Default | Extra |
   
3. +-------+---------+------+-----+---------+-------+
   
4. | a     | int(11) | NO   | PRI | NULL    |       |
   
5. | b     | int(11) | YES  | MUL | NULL    |       |
   
6. +-------+---------+------+-----+---------+-------+
   

复制代码

表数据

1. +----+------+
   
2. | a | b |
   
3. +----+------+
   
4. | 1 | 1 |
   
5. | 5 | 3 |
   
6. | 8 | 6 |
   
7. | 10 | 12 |
   
8. +----+------+
   
9. 执行语句
   
10. select * from ts where b=3 for update

复制代码

该查询语句锁定了b=3这条记录，对辅助索引b而言，还锁定了（1,3）和（3,6）区间。直接导致的结果就是不可以插入在区间内的值。而且b=1和b=6该条记录并没有加锁，因为执行select * from z where b=6 for update 或者 select * from z where b=1 for update 都是可以加锁成功的。但是却不可以再插入任何一条如（2,1）（2,6），（3,1），（3,6），（4,1），(4,6),对于a 在（5,8）区间的时候相应的插入组合也是一样的结果。
对于这个结果到底是为什么我自己也不是很清楚，只能暂时的就这样勉强理解。不知仁兄怎么看？？