mysql 5.7 innodb 行级锁_InnoDB存储引擎行级锁引起锁争用问题

在事务中执行select…for update,update,delete会引起锁，对于事务的修改，事务中会使用X锁，X锁是行级锁，InnDB行锁是通过给索引上的索引项加锁实现的(只有通过索引条件检索数据(即explain sql语句，type=index或range)，InnoDB才使用行级锁，否则使用表锁)

为了验证这个，我做了一个小测验

数据表t_bitfly：

CREATE TABLE `t_bitfly` (

`id` bigint(20) NOT NULL DEFAULT '0',

`value` varchar(32) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

session1:

start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_bitfly where id < 4 for update;

+----+-------+

| id | value |

+----+-------+

| 1 | a |

| 2 | b |

| 3 | c |

+----+-------+

3 rows in set (0.00 sec)

session2:

start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_bitfly where id >= 4 for update;

select * from t_bitfly where id >= 4 for update;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

出现死锁

此刻我的心情

想不明白啊，不是应该只锁住<4的那几行吗

explain:

mysql> explain select * from t_bitfly where id < 4\G;

*************************** 1\. row ***************************

id: 1

select_type: SIMPLE

table: t_bitfly

partitions: NULL

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 8

ref: NULL

rows: 3

filtered: 100.00

Extra: Using where; Using index

type=range，使用上索引了啊，为什么，为什么，不明白啊

我的心情

这个问题困扰自己好久，终于在今天晚上找到答案了，恍然大悟的感觉真的是贼爽

原因：在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

继续做测验

session2:

start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_bitfly where id > 5 for update;

mysql> select * from t_bitfly where id > 4 for update;

+----+-------+

| id | value |

+----+-------+

| 5 | e |

| 6 | f |

| 7 | g |

| 8 | h |

| 9 | i |

| 10 | j |

| 11 | k |

+----+-------+

7 rows in set (0.00 sec)

现在就不出现死锁的情况下，因为此时没有包括4

出现next-key有两种情况，一是上面的使用索引范围，二是使用相等条件请求给一个不存在的记录：

例如：

session 1:

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_bitfly where id = 17 for update;

Empty set (0.00 sec)

session 2:

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_bitfly values(17,'n');

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

一般情况下相等情况不会出现锁争用问题

使用next-key的目的：

1)解决幻读

2)满足mysql的复制和恢复

带来的问题

造成严重的锁等待

如何解决：

尽量避免使用范围条件，使用相等条件来访问和更新数据

行锁的使用还需要注意的几点，否则很有可能会在执行的时候出现死锁或获取不到锁的情况(比如我遇到的)：

1)在MySQL中，行级锁并不是直接锁记录，而是锁索引。

2)索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引(自定义索引+主键)，MySQL会先锁定该非主键索引，再锁定相关的主键索引

3)当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁