mysql gap_mysql innodb lock gap ICP 求解惑

本帖最后由 ming201206 于 2015-11-2 01:20 编辑

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锁。

求大牛解惑，谢谢。