引言

select * from table where id = ?
select * from table where id < ?
select * from table where id = ? lock in share mode
select * from table where id < ? lock in share mode
select * from table where id = ? for update
select * from table where id < ? for update

要知道以上的区别，首先要知道以下问题

• 当前事务隔离级别是什么
• id列是否存在索引
• 如果存在索引是聚簇索引还是非聚簇索引呢？

正文

• innodb一定存在聚簇索引，默认以主键作为聚簇索引
• 有几个索引，就有几棵B+树（不考虑hash索引的情形）
• 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引，指向聚簇索引B+树

锁类型

• 共享锁(S锁)：假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A
• 排他锁(X锁)：假设事务T1对数据A加上共享锁，那么事务T2不能读数据A，不能修改数据A

​我们通过update、delete等语句加上的锁都是行级别的锁。只有LOCK TABLE … READ和LOCK TABLE … WRITE才能申请表级别的锁。

• 意向共享锁(IS锁)：一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁
• 意向排他锁(IX锁)：一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁

意向锁存在的目的

假设事务T1，用X锁来锁住了表上的几条记录，那么此时表上存在IX锁，即意向排他锁，那么此时事务T2要进行LOCK TABLE…WRITE的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。

加锁算法

• Record Locks：简单翻译为行锁，注意了，该锁是对索引记录进行加锁！锁是在加索引上而不是行上。注意了，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上
• Gap Locks：简单翻译为间隙锁，是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在Read Committed隔离级别下，不会使用间隙锁。隔离级别比Read Committed低的情况下，也不会使用间隙锁，如隔离级别为Read UnCommitted时，也不存在间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁
• Next-Key Locks：这个理解为Record Lock+索引前面的Gap Lock。记住了，锁住的是索引前面的间隙！比如一个索引包含值，10、11、13和20。那么，间隙锁的范围如下

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

快照读和当前读

在mysql中select分为快照读和当前读，执行下面的语句

select * from table where id=?;

执行的是快照读，读的是数据库记录的快照版本，是不加锁的（这种说法在隔离级别为Serializable中不成立，后面我会补充）
那么，执行

select * from table where id=? lock in share mode;

会对读取记录加S锁（共享锁），执行

select * from table where id=? for update；

会对读取记录加X锁（排他锁），那么

加的是表锁还是行锁呢？

针对这点，下面是事务的四个隔离级别，他们由弱到强如下所示：

• Read Uncommitted(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！
• Read Committed(RC)：读已提交，一个事务可以读到另一个事务已提交的数据
• Repeatable Read(RR)：可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免！另外记住从该级别才开始加入间隙锁
• Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读

那么关于是表锁还是行锁，大家可以看到网上最流传的一个说法是这样的：

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点Mysql与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

上面这句话有两处错误！
错误一：并不是用表锁来实现锁表的操作，而是利用了Next-Key Locks，也可以理解为是用了行锁+间隙锁来实现锁表的操作！为了便于说明，举例如下，其中，pid为主键索引。

pid(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
7	ccc	200

执行语句（name列无索引）

select * from table where name='aaa' for update

那么此时在pid=1,2,7这三条记录上存在行锁（把行锁住了）。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁（把间隙锁住了）。因此，给人一种整个表锁住的错觉！

ps:对该结论有疑问的，可自行执行show engine innodb status;语句进行分析。

错误二：所有文章都不提隔离级别

锁表是通过行锁+间隙锁实现的。由于RU和RC都不存在间隙锁，因此，该说法只在RR和Serializable中成立，如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行

分析

假设有下表如下，pid为主键索引

pid(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
3	bbb	300
7	ccc	200

RC/RU + 条件列非索引

select * from table where num = 200

不加任何锁，是快照读

select * from table where num > 200

不加任何锁，是快照读

select * from table where num = 200 lock in share mode

当num=200，有两条记录。这两条记录对应的pid=2、7，因此在pid=2、7的聚餐索引上加行级S锁，采用当前读

select * from table where num > 200 lock in share mode

当num>200，有一条记录。这条记录对应的pid=3，因此在pid=3的聚簇索引上加上行级S锁，采用当前读

select * from table where num = 200 for update

当num=200，有两条记录。这两条记录对应的pid=2、7，因此在pid=2、7的聚簇索引上加行级X锁，采用当前读

select * from table where num > 200 for update

当num>200，有一条记录。这条记录对应的pid=3，因此在pid=3的聚簇索引上加上行级X锁，采用当前读

RC/RU + 条件列是聚簇索引

大家知道条件列是主键列，因此pid用的就是聚簇索引，此情况其实和RC/RU + 条件列非索引情况是类似的

select * from table where pid = 2

不加任何锁，是快照读

select * from table where pid > 2

不加任何锁，是快照读

select * from table where pid = 2 lock in share mode

在pid=2的聚簇索引上，加S锁，为当前读

select * from table where pid > 2 lock in share mode

在pid=3、7的聚簇索引上，加S锁，为当前读

select * from table where pid = 2 for update

在pid=2的聚簇索引上，加X锁，为当前读

select * from table where pid > 2 for update

在pid=3、7的聚簇索引上，加X锁，为当前读

为什么条件列加不加索引，加锁情况都是一样的？

Ok，其实是不一样的。在RC/RU隔离级别中，Mysql Server做了优化。在条件列没有索引的情况下，尽管通过聚簇索引来扫描全表，进行全表加锁。但是，Mysql Server层会进行过滤并把不符合条件的锁当即释放掉，因此你看起来最终结果都是一样的。但是RC/RU + 条件列非索引比本例多了一个释放不符合条件的锁的过程！

RC/RU + 条件列是非聚簇索引

我们在num列上建立非唯一索引，此时有一颗聚簇索引（主键索引，pid）形成的B+索引树，其叶子节点为硬盘上的真实数据。以及另一颗非聚簇索引（非唯一索引num）形成的B+索引树，其叶子节点依然为索引节点，保存了num列的字段值，和对应的聚簇索引

接下来分析开始

select * from table where num = 200

不加任何锁，快照读

select * from table where num > 200

不加任何锁，快照读

select * from table where num = 200 lock in share mode

当num=200，由于num列上有索引，因此先在num=200的两条索引记录上加行级S锁。接着，去聚簇索引树上查询，这两条记录对应的pid=2、7，因此在pid=2、7的聚簇索引上加行级S锁，采用当前读

select * from table where num > 200 lock in share mode

当num>200，由于num列上有索引，因此先在符合条件的num=300的一条索引记录上加行级S锁。接着，去聚簇索引树上查询，这条记录对应的pid=3，因此在pid=3的聚簇索引上加行级S锁，采用当前读

select * from table where num = 200 for update

当num=200，由于num列上有索引，因此先在num=200的两条索引记录上加行级X锁。接着，去聚簇索引树上查询，这两条记录对应的pid=2、7，因此在pid=2、7的聚簇索引上加行级X锁，采用当前读

select * from tab;e where num > 200 for update

当num>200，由于num列上有索引，因此先在符合条件的num=300的一条索引记录上加行级X锁。接着，去聚簇索引树上查询，这条记录对应的pid=3，因此在pid=3的聚簇索引上加行级X锁，采用当前读

RR/Serializable + 条件列非索引

RR级别需要多考虑的就是gap lock，它的加锁特征在于，无论你怎么查都是锁全表，如下所示，分析开始

select * from table where num = 200

在RR级别下，不加任何锁，是快照读。在Serializable级别下，在pid=1、2、3、7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num > 200

在RR级别下，不加任何锁，是快照读。在Serializable级别下，在pid=1、2、3、7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num = 200 lock in share mode

在pid=1、2、3、7（全表所有记录）的聚簇索引上加S锁，并且在聚簇索引的所有间隔(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num > 200 lock in share mode

在pid=1、2、3、7（全表所有记录）的聚簇索引上加S锁，并且在聚簇索引的所有间隔(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num = 200 for update

在pid=1、2、3、7（全表所有记录）的聚簇索引上加X锁，并且在聚簇索引的所有间隔(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

select * from table where num > 200 for update

在pid=1、2、3、7（全表所有记录）的聚簇索引上加X锁，并且在聚簇索引的所有间隔(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock

RR/Serializable + 条件列是聚簇索引

大家都知道pid是主键列，因此pid用的就是聚簇索引。该情况的加锁特征在于，如果where后的条件为精确查询（=的情况），那么只存在record lock。如果where后的条件为范围查询（>或<的情况），那么存在的是record lock + gap lock

select * from table where pid = 2

在RR级别下，不加任何锁，是快照读；在Serializable级别下，是当前读，在pid=2的聚簇索引上加S锁，不存在gap lock

select * from table where pid > 2

在RR级别下，不加任何锁，是快照读。在Serializable级别下，是当前读，在pid=3、7的聚簇索引上加S锁，在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where pid = 2 lock in share mode

是当前读，在pid=2的聚簇索引上加S锁，不存在gap lock

select * from table where pid > 2 lock in share mode

是当前读，在pid=3、7的聚簇索引上加S锁，在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where pid = 2 for update

是当前读，在pid=2的聚簇索引上加X锁

select * from table where pid > 2 for update

是当前读，在pid=3、7的聚簇索引上加X锁，在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where pid = 6 [lock in share mode|for update]

注意了，pid=6是不存在的列，这种情况会在（3，7）加上gap lock

select * from table where pid > 18 [lock in share mode|for update]

注意了，pid>18，查询结果是空的。在这种情况下，是在(7,+∞)上加gap lock

RR/Serializable + 条件列是非聚簇索引

这里是非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的
先说一下，唯一索引的情况。如果是唯一索引，情况和RR/Serializable+条件列是聚簇索引类似，唯一有区别的是：这个时候有两颗索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上
下面说一下，非聚簇索引是非唯一索引的情况，它和唯一索引的区别就是通过索引进行精确查询后，不仅存在record lock，还存在gap lock。而通过唯一索引进行精确查询后，只存在record lock，老规矩在num列建立非唯一索引

select * from table where num = 200

在RR级别下，不加任何锁，是快照读。在Serializable下，是当前读，在pid=2、7的聚簇索引上加S锁，在num=200的非聚簇索引上加S锁，在(100,200)(200,300)加上gap lock

select * from table where num > 200

在RR级别下，不加任何锁，是快照读。在Serializable级别下，是当前读，在pid=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock

select * from table where num = 200 lock in share mode

是当前读，在pid=2、7的聚簇索引上加S锁，在num=200的非聚簇索引上加S锁，在(100,200)(200,300)加上gap lock

select * from table where num > 200 lock in share mode

是当前读，在pid=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁，在(200,300)(300,+∞)加上gap lock

select * from table where num = 200 for update

是当前读，在pid=2的聚簇索引上加S锁，在num=200的非聚簇索引上加X锁，在(200,300)(300,+∞)加上gap lock

select * from table where num > 200 for update

是当前读，在pid=3的聚簇索引上加S锁，在num=300的非聚簇索引上加X锁，在在(200,300)(300,+∞)加上gap lock

select * from table where num = 250 [lock in share mode|for update]

注意了，num=250是不存在的列，这种情况会在(200,300)上加gap lock

select * from table where num > 400 [lock in share mode|for update]

注意了，pid>400，查询结果为空，在这种情况下，是在(400,+∞)上加gap lock