关于innodb引擎的锁(一)

innodb锁的基本介绍

mysql的innodb引擎，支持行锁和表锁，但默认情况下是采用行锁。

innodb实现了两种类型的行锁：共享锁(S)、排它锁(X)
另外，为了允许行锁和表锁共存，innodb还有两种内部使用的意向锁：意向共享锁(IS)、意向排他锁(IX),这两种意向锁都是表锁。意向锁是innodb自动加的，用户不需要干预。

对于update、delete、insert语句，innodb会自动给涉及数据家排它锁；对于普通的select语句，innodb不会加锁；事务可以通过以下方式给记录集加锁。
共享锁：SELECT * FROM table WHERE …LOCK IN SHARE MODE
排它锁：SELECT * FROM table WHERE…FOR UPDATE

innodb行锁实现方式

innodb行锁是通过给索引上的索引项加锁来实现的，如果没有索引，innodb将通过隐藏的聚簇索引来对记录加锁，innodb行锁分为3种情形
Record lock：对索引项加锁
Gap lock：对索引项之间的范围加锁
Next-key lock：前两种的组合

innodb这种行锁实现特点意味着：如果不通过索引条件检索数据，那么innodb将对表中的所有记录加锁。
另外需要注意的是，因为是针对索引加的锁，所以可能出现虽然访问的是不同行的数据，但由于使用的是相同的索引键而出现锁冲突。
例如：
id为普通索引

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    1 | b    |
+------+------+
2 rows in set (0.00 sec)

事务1：
mysql> select * from test where id=1 and name='a' for update;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

事务2：
mysql> select * from test where id=1 and name='b' for update;
等待

再来说一下Next-key lock，当我们用范围条件而不是等于条件检索数据，并且请求共享或者排他锁时，innodb就会给符合条件呢的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙（GAP），innodb也会对这个间隙加锁。
例如：
其中id为主键

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | b    |
|    5 | b    |
+------+------+
5 rows in set (0.00 sec)

事务1
mysql> select * from test where id between 3 and 5 for update;
+------+------+
| id   | name |
+------+------+
|    3 | b    |
|    5 | b    |
+------+------+
2 rows in set (0.00 sec)

事务2
mysql> insert into test values(4,'d');
等待
innodb会对不存在的值，也就是间隙进行上锁。

还要注意的是，innodb除了对主键或者唯一键的范围查找外，对普通索引的等于查找也会产生Next-key lock
例如：
id为普通索引

 mysql> select * from ceshi;
+----+------+
| id | id1  |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set (0.00 sec)

事务1：
mysql> select * from ceshi where id=3;
+----+------+
| id | id1  |
+----+------+
|  3 |    3 |
+----+------+
1 row in set (0.00 sec)

事务2：
insert into ceshi values(0,4);
Query OK, 1 row affected (0.00 sec)
insert into ceshi values(1,4);
等待
insert into ceshi values(2,4);
等待
insert into ceshi values(4,4);
等待
insert into ceshi values(3,4);
等待
insert into ceshi values(5,4);
Query OK, 1 row affected (0.00 sec)

可以看到，锁定的范围是[1,5) 锁上不锁下

恢复和复制，对锁机制的影响

在备份恢复和主从复制的场景中，我们会使用到binlog，为了保证数据的一致性，在下面这种情况时也会发生锁冲突：

mysql> select * from t1;
+----+------+
| id | id1  |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set (0.00 sec)

事务1：
mysql> insert into t2 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

事务2：
update t1 set id1=10 where id=3;
等待

这里加锁是为了保证使用binlog恢复出来的时候数据的一致性（如果事务2先提交，事务1再提交就会产生不一致）
进行一下演示：
把innodb_locks_unsafe_for_binlog参数调为1，重复上面的操作，这时，事务2不会发生所冲突。

事务2：
mysql> commit;					//先对事务2进行提交
Query OK, 0 rows affected (0.01 sec)
事务1：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

select * from t2;			//查看t2表的内容，没有问题，是事务2修改之前的数据
+----+------+
| id | id1  |
+----+------+
|  1 |    1 |
|  3 |    3 |
|  5 |    5 |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from t1;		//查看t1表的数据，也没有问题，已经修改过
+----+------+
| id | id1  |
+----+------+
|  1 |    1 |
|  3 |   10 |
|  5 |    5 |
+----+------+
3 rows in set (0.00 sec)

但是我们来看一下binlog的内容

可以看出来，binlog中先记录了update操作，然后才是insert操作，从而会导致数据的不一致性（可以通过改成row模式解决）

参考：《深入浅出MySQL》