一个InnoDB 加锁的案例

        最近一直在优化各个产品的SQL语句，同时还帮一个同事解决deadlock问题，收获就是对InnoDB加锁的理解更加深入了。先来看看今天的这个案例：

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.5.13.4-log         |
+----------------------+

mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB;

mysql> select * from t1;
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    1 |
|  3 |    1 |    1 |
| 10 |    1 |    1 |
| 11 |    1 |    1 |
| 12 |    1 |    1 |
| 14 |    1 |    1 |
| 15 |    1 |    1 |
+----+------+------+

         测试环境就是如上，开始下面的测试：

session1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where a in (2, 10, 11, 12,14) for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  2 |    1 |    1 |
| 10 |    1 |    1 |
| 11 |    1 |    1 |
| 12 |    1 |    1 |
| 14 |    1 |    1 |
+----+------+------+
5 rows in set (0.00 sec)

session2:
mysql> insert into t1 values(7, 1, 1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t1 where a=15 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(18,1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t1 values(18000,1,1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

        从上面可以看出InnoDB仿佛是将[2, 无穷大)这个区间给锁了，那么原因是什么呢？ 你可以先思考下

        再来看第二组测试

session1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where a in (2, 14) for update;
+----+------+------+
| a  | b    | c    |
+----+------+------+
|  2 |    1 |    1 |
| 14 |    1 |    1 |
+----+------+------+
2 rows in set (0.00 sec)

session2：
mysql> insert into t1 values(7, 1, 1);
Query OK, 1 row affected (0.00 sec)

       从上面可以看出，这次2与14之间的区间并没有被锁住（14后面的肯定也没锁，只是没贴出结果而已），那这又是为什么呢？

        要理解上面的现象首先得明白InnoDB是怎么加锁的。在InnoDB加锁时它是这样做的：InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters.也就是说它是在扫描表或者索引时遇到的行都会被加锁，一定请注意encounters这个单词，是扫描过程中遇到的行都会被锁住。那么再来看上面的现象，为什么对主键的查询还会锁一个那么大的区间呢？首先第一感觉肯定是只加in()中列出的几行，但又想InnoDB是对扫描过程中遇到的行都加锁，所以曾误以为where a in(val1, ..., valn)这种形式的会是锁val1到valn中最小、最大值组成的区间（因为觉得扫描就是从2到14），但是上面的结果不是显示18000也无法插入吗？这又是为什么呢？后来经同事指点才猛然大悟：

mysql> explain select * from t1 where a in (2, 10, 11, 12,14) for update;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

        看了这个之后相信大家都明白原因了吧，因为这个查询走的是全表扫描，所以所有的行都加了记录，包括那些现在不存在记录的区间。那么对于第二个例子呢？

mysql> explain select * from t1 where a in (2, 14) for update;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

        从上面可以看出，这次没走全表扫描，而是主键索引(range，将扫描区间分为[2, 2], [14, 14]两者区间)，因为主键索引可以定位到具体的行，自然加锁的行就只有2和14了。

        从这个小例子也可以联想到InnoDB的三种类型的锁gap lock/next-key lock/record lock。为什么gap lock/next-key lock是锁一个区间（当然[a, a+1)也是一个区间，这个区间只是特殊点只有一条记录而已）。它的目的是防止幻读。它为什么能实现防止幻读？因为扫描过程中的区间全部加锁，那自然下次再读的时候别人是无法插入数据进来的。

        从这个小例子我们也可以明白一点为什么加索引如此重要。假如没有合适的索引，不仅查询的速度会很慢，而当你对表进行update/delete的时候你会发现很多意想不到的记录被锁住，导致锁等待超时或者死锁错误。

参考文章

1. refman-5.5-en.pdf 13.3.9/The InnoDB Transaction Model and Locking