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）当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁