彻底搞懂MySQL InnoDB存储引擎的锁和事务的并发问题

锁

两种标准的行级锁

共享锁（S Lock），SQL语句：SELECT … LOCK IN SHARE MODE
排他锁（X Lock），SQL语句：SELECT … FOR UPDATE
InnoDB存储引擎实现了以上两种标准的行级锁。他们的兼容性如下：

对于以上两种标准的行级锁来说，有如下规律：

1. 只有处在事务中才会生效，事务结束后会自动释放锁；
2. 当只有一个事务A对记录1加了共享锁，事务A能修改或删除记录1；当有多个事务对记录1加了共享锁，记录1不能被修改或删除；
3. 事务A对记录1加了排他锁，事务A能修改或删除记录1；此时其他事务对记录1加锁会被阻塞，更无法修改或删除记录1；
4. 无论是共享锁还是排他锁，都不会阻塞普通未加锁的SELECT.

表级读写锁

读锁： LOCK TABLE my_table_name READ;
写锁： LOCK TABLE my_table_name WRITE;
释放锁：UNLOCK TABLE或UNLOCK TABLES;
他们的兼容性如下：

对于以上两种读写锁来说，有如下规律：

1. 针对会话（session）生效，而不是事务；
2. 会话A对表t加读锁后，会话A只能读表t的数据，不能写入，写数据会报错；此时其他会话可以读表t的数据，但是写数据会阻塞，直到会话A的读锁释放；
3. 会话A对表t加写锁后，会话A能读写表t的数据。此时其他会话不能对表t进行读写，直到会话A释放写锁。

通常在修改表结构时使用写锁，如果不加锁，刚好在修改表结构时有数据写入，会导致表结构修改失败，或者引起数据不一致。

意向锁

意向共享锁（IS Lock）：事务想要获得一张表中某些行的共享锁
意向排他锁（IX Lock）：事务想要获得一张表中某些行的排他锁

意向锁是表级别的锁，不需要用户主动声明，由InnoDB存储引擎自动完成。只有在表上加了意向共享锁，才能对表里的行加共享锁；同理，只有对表加了意向排他锁，才能给表里的行加排他锁。意向锁之间是互相兼容的，下表给出了四种锁之间的兼容性情况：

InnoDB支持多粒度的锁定，这种锁定允许事务在行级别的锁和表级别的锁同时存在。为了支持在不同粒度上进行加锁操作，所以引入了意向锁。

怎么理解上面这段话？举个例子，假如记录r被加了行级排他锁，现在另外一个事务t要给表加表级共享锁，这时如果发现表上有意向排他锁，事务t就会阻塞。如果没有这个表级别的意向排他锁，事务t要一个个去遍历所有行，才能发现记录r被加了排他锁，如果有几百万条记录，这个效率是很低的。况且在遍历的过程中，记录加锁的状态是会变的。

两种读模式

一致性非锁定读（快照读）

InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中的行数据。如果读取的行正在执行DELETE或UPDATE操作（这两个操作会加锁），这时读取操作不会因此去等待行上的锁释放，而是读取行的快照。快照是指该行之前版本的数据，快照也用来在事务中回滚数据。
一个行记录可能有不止一个快照，这种行多版本技术带来的控制称为多版本并发控制（Multi Version Concurrency Control，MVCC）

一致性锁定读（当前读）

InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读操作：
SELECT … LOCK IN SHARE MODE
SELECT … FOR UPDATE

行锁的三种算法

Record Lock：单个行记录上的锁
Gap Lock：间隙锁。锁定一个范围，但不包含记录本身（左右都是开区间）
Next-Key Lock：Record Lock+Gap Lock,锁定一个范围，并锁定记录本身（右边闭区间，左边开区间）
对于索引列，如果该列有10、11、15、20这四个值，那么该索引可以被Next-Key Locking的区间为：

事务

事务的ACID特性

原子性（atomicity）：事务是不可分割的工作单位，事务中的一组数据库操作要么都成功，要么都失败
一致性（consistency）：事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏
隔离性（isolation）：每个读写事务的对象对其他事务的操作对象能互相分离，即该事务提交前对其他事务不可见
持久性（durability）：事务一旦提交，其结果就是永久性的

事务的并发问题

脏读：指的是在不同的事务中，当前事务可以读到别的的事务未提交的数据。比如事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：是指在一个事务内多次读取同一数据集合，在这个事务还没有结束时，另外一个事务也访问了同一数据集合，并做了一些DML（Data Manipulation Language）操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的
幻读：“幻读”是指先查询再更新数据而出现的“幻象”问题，而不可重复读是多次查询而出现的“幻象”问题。比如说，在事务A中，先SELECT查询符合条件1的数据是否存在，发现不存在再INSERT,结果在此时，另外一个事务B INSERT了一条符合条件1的数据，这就导致我在事务A中的SELECT看到的就像幻觉一样。

事务的隔离级别

READ UNCOMMITTED: 俗称“读未提交”或“浏览访问”。会出现“脏读”，这种隔离级别最低，一般是在理论上存在，很少用到
READ COMMITTED:俗称“读已提交”或“游标稳定”。这种隔离级别高于“读未提交”，
可以避免“脏读”，但会导致“不可重复读”
REPEATABLE READ:俗称“可重复读”。这种隔离级别高于“读已提交”，可以避免“脏读”和实现可重复读，不能避免“幻读”
SERIALIZABLE:俗称“隔离”或“串行化”。此事务隔离级别最高，能够避免“脏读”、“幻读”以及实现可重复读。当使用此隔离级别时，InnoDB存储引擎会自动给每个SELECT语句后面加上LOCK IN SHARE MODE，因此对一致性的非锁定读不再予以支持

讨论

关于行锁锁定的范围

共享锁和排他锁锁定的范围很难用一两句话总结，因为事务的隔离级别、查询是否带索引、索引是普通索引还是唯一索引等这些条件都会对锁的范围有影响。即使这样，还是有几点规律可以总结：

1. 在READ COMMITTED隔离级别，使用的是Record Lock算法，所以只会锁住已经存在的值；
2. REPEATABLE READ隔离级别肩负着解决Phantom Problem中的“不可重复读”的使命，所以Record Lock、Gap Lock、Next-Key Lock它都用到了，锁定的范围和这三种算法相关，具体使用哪种算法，又跟查询条件以及是否匹配到了数据有关，情况比较复杂，所以用语言描述比较麻烦，这里就偷个懒。但是有一点比较明确，那就是当查询条件不包含索引时，为了实现可重复读，会极端的去锁表
3. SERIALIZABLE是REPEATABLE READ的增强版，基本规律和REPEATABLE READ一致

如何实现可重复读

在事务隔离级别READ COMMITTED和REPEATABLE READ，InnoDB可以使用一致性非锁定读，然而，他们对快照数据的定义却不同。在READ COMMITTED事务隔离级别下，非一致性读总是读取被锁定行的最新一份快照数据，而在REPEATABLE READ事务隔离级别下，非一致性读总是读取当前事务开始时的行数据版本。
Next-Key Lock和Gap Lock算法的引入是为了解决一致性锁定读情景下的可重复读的问题，不同的事务隔离级别，使用的算法不同，大致有如下规律：
READ COMMITTED事务隔离级别
使用Record Lock
假如表里有主键为1、2、5、9这四个记录，当我使用SELECT * FROM t WHERE id>1 FOR UPDATE查询时，READ COMMITTED隔离级别仅锁住id为2、5、9这三个记录，所以它不能阻止我INSERT一条id为3或10的记录，所以再次执行查询语句时两次读到的结果不一样。
REPEATABLE READ和SERIALIZABLE事务隔离级别
查询条件包含主键或者唯一索引，并且查询结果不为空的等值查询（=），Next-Key Lock会降级为Record Lock，这时仅锁住一行，而不是一个范围；其他情况下一般使用Next-Key Lock或Gap Lock或结合使用Next-Key Lock和Gap Lock.上述例子，在REPEATABLE READ隔离级别，锁住的是(1,2]、(2,5]、(5,9]、(9,+∞)这些范围。

如何避免幻读

READ COMMITTED隔离级别使用Record Lock算法，所以无法避免“幻读”。而在REPEATABLE READ隔离级别，可以通过共享锁或排他锁来避免“幻读”。例如有主键id为10、11、20、23的记录，查询语句SELECT * FROM t WHERE id=15 LOCK IN SHARE MODE会通过Gap Lock锁住(11,20)这个区间，所以其他事务无法INSERT一条id为15的数据。SERIALIZABLE正是通过给每个SELECT语句加共享锁来解决“幻读”问题的