最近一直在最佳化各個產品的SQL語句，同時還幫一個同事解決deadlock問題，收穫就是對InnoDB加鎖的理解更加深入了。先來看看今天的這個案例：
- mysql> select version();
- +----------------------+
- | version() |
- +----------------------+
- | 5.5.13.4-log |
- +----------------------+
- mysql> show variables like'%iso%';
- +---------------+-----------------+
- | Variable_name | Value |
- +---------------+-----------------+
- | tx_isolation | REPEATABLE-READ |
- +---------------+-----------------+
- CREATETABLE `t1` (
- `a` int(11) NOTNULL,
- `b` int(11) DEFAULTNULL,
- `c` int(11) DEFAULTNULL,
- PRIMARYKEY (`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) forupdate;
- +----+------+------+
- | a | b | c |
- +----+------+------+
- | 2 | 1 | 1 |
- | 10 | 1 | 1 |
- | 11 | 1 | 1 |
- | 12 | 1 | 1 |
- | 14 | 1 | 1 |
- +----+------+------+
- 5 rowsinset (0.00 sec)
- session2:
- mysql> insertinto t1 values(7, 1, 1);
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- mysql> select * from t1 where a=15 forupdate;
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- mysql> insertinto t1 values(18,1,1);
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- mysql> insertinto t1 values(18000,1,1);
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
從上面可以看出InnoDB彷彿是將[2, 無窮大)這個區間給鎖了，那麼原因是什麼呢？ 你可以先思考下
