MySQL的GAP LOCK(间隙锁) 的陷阱

http://fucheng.blog.51cto.com/2404495/1619359 

mysql(innodb storage engine)的行锁主要是通过在相应的索引记录来实现,作为一个最佳的实践方式就是
使用innodb的时候，最好是每个表明确定义主健，如果没有显式定义主健，mysql为自动创建隐含的主健。

当在某一行上加share或是exclusive锁时，对该记录之前(<）或是之后(>)都会被锁定，从而无法在这些记录
上进行操作，即使没有行满足条件，也会是类似的情形,

 例:

mysql> use frank;

mysql> create table t3 (a int primary key,b varchar(10),c int);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from t3;  

--insert如下记录
+---+------+------+
| a | b    | c    |
+---+------+------+
| 5 | d    |   56 |
| 6 | df   |    6 |
| 7 | df   |    7 |
| 8 | df   |    8 |
| 9 | df   |    9 |
+---+------+------+
5 rows in set (0.00 sec)

打开一个会话，禁止自动提交

mysql> use frank;

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t3 where a<4;
Query OK, 0 rows affected (0.00 sec)

删除a小于4的行，从表中来看是没有满足a小于4的行，通过gap的属性知道，对于a<4的行,innodb都会加锁,一直加到等于4

打开另一个会话,禁止自动提交
mysql> use frank;

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t3 values(3,'df',45);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看到由于锁的原因已经无法insert了.

另一个实验,假设现有记录情况如下:
mysql> select * from t3;
+---+------+------+
| a | b    | c   |
+---+------+------+
| 1 | df   |    4 |
| 5 | d    |   56 |
| 6 | df   |    6 |
| 7 | df   |    7 |
| 8 | df   |    8 |
| 9 | df   |    9 |
+---+------+------+
6 rows in set (0.00 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> update t3 set c=10 where a>1 and a<6;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

根据gap的锁特点在1和6之间的记录都会被锁定。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t3 values(4,'df',45);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
因为锁等待，insert无法成功，4落在（1，6）之间，被第一个事务的gap锁给锁住了。

 可以看出这种gap lock的方式锁定了不必要的行，使并发变差了.

在mysql中使用where条件的时候，要很小心.在很忙的MYSQL系统，使用 select ....for update 也是一种很不好的主意，一不小心带来锁的问题.