点击量：842
MySQL数据库提供了好几种引擎，比如ISAM、MyISAM、HEAP、InnoDB和Berkley DB（BDB），比较常用的就是Innodb了。Innodb主要有以下几种锁，分别用在不同的场景里。
共享锁和排它锁（Shared and Exclusive Locks）
意图锁（Intention Locks）
记录锁（Record Locks）
区间锁（Gap Locks）
Next-Key Locks
共享锁和排它锁
首先需要指出的是这两种锁都是针对行（row）而言的，是innodb在行级锁（row-level locking）方面的两个实现。共享锁（shared (S) locks），每当读取一行记录时会加上一个共享锁。排它锁（exclusive (X) lock ），每当update或者delete一条记录时都会加上一个排它锁。当事务T1对某一行R拥有了一个读锁时，另一个事务T2对该条记录可以立即获得共享锁，但是不能获得排他锁。当事务T1对某一行R拥有了一个排他锁时，另一个事务T2无论是申请排他锁还是共享锁都需要等待T1释放锁。他们之间的关系如下表所示：
[table caption=”” width=”150″ colwidth=”50|50|50″ colalign=”left|left|left”]
,S,X,
S,不冲突,冲突,
X,冲突,冲突,
[/table]
接下来举个例子看一下他们之间的关系，首先创建一张表然后再插入一些基础数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE student( id int(11) NULL, name varchar(255) NULL, PRIMARY KEY(id) ); insert into student values (1,'eric'),(2,'tom'),(3,'allen'),(5,'tom'),(9,'jerry'),(12,'carl'); mysql> select * from student; +----+-------+ | id | name | +----+-------+ | 1 | eric | | 2 | tom | | 3 | allen | | 5 | tom | | 9 | jerry | | 12 | carl | +----+-------+ 2 rows in set (0.00 sec) |
这里一共有两个控制台模拟两个事务（T1，T2），当T1拥有共享锁时，T2是可以获得共享锁的，但是不能获得排它锁。如下测试所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
//Transaction1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1 LOCK IN SHARE MODE; +----+------+ | id | name | +----+------+ | 1 | eric | +----+------+ 1 row in set (0.00 sec) //Transaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1 LOCK IN SHARE MODE; +----+------+ | id | name | +----+------+ | 1 | eric | +----+------+ 1 row in set (0.00 sec) mysql> select * from student where id = 1 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
当T1拥有排它锁时，T2是不能获得共享锁或者排它锁的，同时也可以验证这两个锁是行级锁，只是加在了id=1那一行，对其他行没有影响。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
//Transaction1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1 FOR UPDATE; +----+------+ | id | name | +----+------+ | 1 | eric | +----+------+ 1 row in set (0.00 sec) //Transaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1 LOCK IN SHARE MODE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from student where id = 1 FOR UPDATE; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from student where id = 2 FOR UPDATE; +----+------+ | id | name | +----+------+ | 2 | tom | +----+------+ 1 row in set (0.00 sec) mysql> select * from student where id = 2 LOCK IN SHARE MODE; +----+------+ | id | name | +----+------+ | 2 | tom | +----+------+ 1 row in set (0.00 sec) |
上面的测试用了两个SQL语句，分别是：
|
1 |
SELECT ... LOCK IN SHARE MODE |
|
1 |
SELECT ... FOR UPDATE |
这两种SQL语句是显式地在select语句上加上锁（S/X），但是我们一般使用select语句时是没有加上LOCK IN SHARE MODE或者FOR UPDATE的，那么简单的select语句会不会加上共享锁呢？常识告诉我们答案是不会。其实仔细思考下就会发现如果MySQL对所有select语句都加上共享锁，那么就不会存在脏读，幻读等情况了，也不需要设置什么隔离级别了，并且这样做会导致并发效率急剧下降，所以MySQL是肯定不会这么实现的。下面弄个实例测试下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
//Transaction1 mysql> use gcld_test_1 Database changed mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1; +----+------+ | id | name | +----+------+ | 1 | eric | +----+------+ 1 row in set (0.00 sec) //Transaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1; +----+------+ | id | name | +----+------+ | 1 | eric | +----+------+ 1 row in set (0.00 sec) mysql> update student set name = 'eric2' where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
很显然，当一个事务对某条记录进行读操作时，另外的事务是可以对这条记录进行读写操作的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
//Transaction1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update student set name = 'eric1' where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 //Transaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update student set name = 'eric3' where id = 1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from student where id = 1; +----+-------+ | id | name | +----+-------+ | 1 | eric2 | +----+-------+ 1 row in set (0.00 sec) |
当一个事务对某条记录持有写锁时，另一个事务必须等到该事务的写锁释放才能执行update/delete操作，而相同的是read操作不受影响。也正是因为不同事务之间存在的读写冲突问题才提出了隔离级别的概念，级别越高，数据越完整但是并发效率也随之降低。（具体的关于隔离级别可参考本人的另一篇博文：RMDB中的事务隔离级别）
Intention Locks
Innodb支持多粒度的加锁机制，允许行级锁（low-level locking）和表级锁（table-level locking）的共存,而intention lock就是基于整表的锁，他跟行级锁类似也分为两种：Intention shared (IS)：当一个事务T准备获得某些行的共享锁的时候，会在这个表上加上IS。Intention exclusive (IX)：当一个事务T准备获得某些行的排它锁的时候，会在这个表上加上IX锁。也就是说：一个事务要想获得某一行的共享锁他必须要先获得这个表上的IS锁或者IX锁。一个事务想要获得某一行的排它锁，他必须要先获得这个表上的IX锁。他们之间的冲突关系如下表所示：
[table caption=”” width=”150″ colwidth=”50|50|50″ colalign=”left|left|left”]
,X,IX,S,IS
X,Conflict,Conflict,Conflict,Conflict
IX,Conflict,Compatible,Conflict,Compatible
S,Conflict,Conflict,Compatible,Compatible
IS,Conflict,Compatible,Compatible,Compatible
[/table]
从表中冲突关系可以看出Intention Locks之间并不会互相block，所以intention lock的主要作用就是表明有人正在准备锁一行，或者打算去锁那一行。
Record Locks
记录锁，其实就是加在索引记录上的行锁。InnoDB的行锁实现方式是锁定索引记录，而不是行数据。如果一个查询条件没有索引，那么将会使用表锁（table lock），这一点要特别注意。比如：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select * from student; +----+-------+ | id | name | +----+-------+ | 1 | eric2 | | 2 | tom | | 3 | allen | | 5 | tom | | 9 | jerry | | 12 | carl | +----+-------+ 6 rows in set (0.00 sec) //Transaction1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where name = 'tom' for update; Empty set (0.00 sec) //Trnsaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
以上的例子可以看出：T1只查询了name=tom的行，但是T2中却不能获得id=1的X锁，所以说id=1的X锁被T1锁定了。那为什么会这样呢？原因很简单，因为如果这一列没有索引，那么通过这个条件查询的时候只能通过全表扫描，而不能通过索引精确快速地定位到那条记录，所以需要对整张表加锁，防止表内数据发生变化。
Gap Locks
当查询条件是一个范围的时候，innoDB会对这段区间上的记录加上锁（其实就是多个行级锁）。这个区间锁是可以被显式关闭的，把MySQL的隔离级别设置成READ COMMITTED或者开启innodb_locks_unsafe_for_binlog（把这个值设置成1）。当查询条件是唯一索引，且是一个范围时，只会加上区间锁！ 测试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
mysql> select * from student; +----+-------+ | id | name | +----+-------+ | 1 | eric2 | | 2 | tom | | 3 | allen | | 5 | tom | | 9 | jerry | | 12 | carl | +----+-------+ 6 rows in set (0.00 sec) //Transaction1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id between 5 and 12 for update; +----+-------+ | id | name | +----+-------+ | 5 | tom | | 9 | jerry | | 12 | carl | +----+-------+ 3 rows in set (0.00 sec) //Transaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from student where id = 3 for update; +----+-------+ | id | name | +----+-------+ | 3 | allen | +----+-------+ 1 row in set (0.00 sec) mysql> insert into student values(4,'xxx'); Query OK, 1 row affected (0.00 sec) mysql> select * from student where id = 5 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
Next-Key Locks
如果查询条件是非唯一索引，那么不仅会加上区间锁还会加上记录锁，这两个结合起来就是Next-Key Locking。这个锁会把要查询的范围分成几个区间，比如说现在的索引值是：3,5,7,10,16，则innoDB会分成以下几个区间：(-∞,3],(3,5],(5,7],(7,10],(10,16],(16,+∞)。所以如果只查询一个值，比如7，那么锁定的区间和记录就包括：7和(5,7],(7,10]。下面通过一个例子验证下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
//创建一张表，id是非唯一索引 CREATE TABLE teacher( id int(11) NULL, name varchar(255) NULL, INDEX idx USING BTREE (id) ); //准备数据 insert into teacher values(1,'1'),(3,'3'),(5,'5'),(7,'7') ,(9,'9'),(11,'11'),(13,'13'),(15,'15'); //查询数据 mysql> select * from teacher; +------+------+ | id | name | +------+------+ | 1 | 1 | | 3 | 3 | | 5 | 5 | | 7 | 7 | | 9 | 9 | | 11 | 11 | | 13 | 13 | | 15 | 15 | +------+------+ 8 rows in set (0.00 sec) //Tranaction1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) //实际上锁定了范围，7-9,9-11，并不包括记录本身 mysql> select * from teacher where id = 9 for update; +------+------+ | id | name | +------+------+ | 9 | 9 | +------+------+ //Transaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) //没有该记录，不会被锁定，这个区间其实是相对于insert而言的 mysql> select * from teacher where id = 8 for update; Empty set (0.00 sec) //区间被锁定，不能插入 mysql> insert into teacher values (8, '8'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from teacher where id = 7 for update; +------+------+ | id | name | +------+------+ | 7 | 7 | +------+------+ 1 row in set (0.00 sec) mysql> select * from teacher where id = 9 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from teacher where id = 11 for update; +------+------+ | id | name | +------+------+ | 11 | 11 | +------+------+ 1 row in set (0.00 sec) //-----------------另一个例子----------------- //Transaction1 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from teacher where id between 9 and 11 for update; +------+------+ | id | name | +------+------+ | 9 | 9 | | 11 | 11 | +------+------+ 2 rows in set (0.00 sec) //Transaction2 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into teacher values (8, '8'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into teacher values (10, '10'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from teacher where id = 11 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
举了好多例子可以发现Gap Locking和Next-key Locking的区别在于
1.后者包含前者，Gap Locking是Next-key Locking的一部分（Gap Locking + Record Locking），Gap Locking只是锁定一个区间，而Next-key Locking会锁定当前区间的前后区间。
2.单一的Gap Locking主要用于唯一索引，而当涉及到非唯一索引时会采用Next-key Locking（不管是单行查找还是区间查找）。
总结以下几点：
1.共享锁，排它锁是行级锁（row-level locking）上的两种基本锁，而意向锁是表级锁（table-level locking）上的基本锁。（我觉得这两种锁只是个概念而已，并不单独存在）而像Record Locks，Gap Locks，Next-Key Locks这些具体的加锁方法是基于以上两种锁实现的。
2.显式地加上共享锁和排它锁的SQL语句分别是：SELECT … LOCK IN SHARE MODE和SELECT … FOR UPDATE。
3.如果查询条件不加上索引，则innoDB会进行全表扫面，并且对整表加上排它锁，其他事务不能获得该表内任何一条记录的排它锁，所以会导致性能瓶颈。
4.以上提到的这些锁都是基于索引的，innoDB的行级锁实际上就是针对索引记录加锁，而不是行数据。
5.如果是查询唯一索引，gap locking只会锁住查询范围内的那些索引记录，而如果查询的是非唯一索引，则innoDB会采用Next-key Locking，不仅会锁定查询范围那些记录，还会锁定该范围之前和之后的记录。
