InnoDB事务和锁
[TOC]
前言
事务支持是我们选择InnoDB存储引擎的主要原因。
我们通常以@Transactional注解的方式使用事务，Spring会利用AOP在调用目标方法之前开启事务、调用异常回滚事务、调用完成提交事务。Spring还定义了7种类型的事务传播行为，它们规定了事务方法之间发生嵌套调用时事务如何进行传播。
下面我们深入了解一下事务及事务相关的知识。
事务
事务的概念
事务由一组SQL语句组成的逻辑处理单元，具有以下四个属性，通常称为事务的ACID属性。
-
原子性（atomicity）
一个事务必须被视为一个不可分隔的最小单元，整个事务要么全部提交成功，要么失败全部回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
-
一致性（consistency）
在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B+树索引或双向列表）也都必须是正确的。
数据库总是从一个一致性的状态转换到另外一个一致性的状态。
-
隔离性（isolation）
数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
-
持久性（durability）
一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
InnoDB事务语法：
# 开始事务
BEGIN;
BEGIN WORK;
START TRANSACTION;
# 提交事务
COMMIT;
COMMIT WORK;
# 回滚事务
ROLLBACK;
# 通过定义savepoint，可以指定回滚事务的一个部分
# 声明一个savepoint
SAVEPOINT savepoint_name;
# 回滚到savepoint
ROLLBACK TO savepoint_name;
自动提交
MySQL默认采用自动提交（AUTOCOMMIT）模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。
# 查看当前会话自动提交开关,也可不加SESSION
mysql> SHOW SESSION VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
# 修改当前会话自动提交开关,也可不加SESSION
mysql> SET SESSION AUTOCOMMIT=0;
# 查看全局会话自动提交开关
mysql> SHOW GLOBAL VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
# 修改全局会话自动提交开关，不影响已连接的会话，只影响新会话的自动提交默认值
mysql> SET GLOBAL AUTOCOMMIT=0;
修改AUTOCOMMIT对非事务型的表，比如MyISAM或者Memory表，不会有任何影响。
事务的隔离级别
相对于串行处理来说，并发事务处理能力大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发处理也会带来一些问题：
- 更新丢失：两个或多个事务更新同一行，最后的更新覆盖了由其它事务所作的更新。
- 脏读：一个事务未提交的修改被其它事务读取
- 不可重复读：一个事务中多次读取得到不同的结果
- 幻读：事务读取某个范围内的记录时，另外的事务在该范围插入了新的记录，再次读取时会产生幻行
为平衡隔离与并发的矛盾，SQL92定义了4种隔离级别：
READ_UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其它事务也是可见的。READ_COMMITTED（已提交读）：一个事务开始时，只能看见已经提交的事务所作的修改。不会出现脏读，但是不可重复读。REPEATABLE_READ（可重复读）：该级别保证了在同一个事务中多次读取同样的记录结果是一致的。SERIALIZABLE（可串行化）：强制事务串行执行。
InnoDB实现了全部四种隔离级别，默认是REPEATABLE_READ。
用户可以通过SET TRANSACTION语句或修改transaction_isolation变量来隔离级别。（transaction_isolation在MySQL 5.7.20版本增加，是tx_isolation的别名，tx_isolation已过时并在8.0移除。）
# SET TRANSACTION语句，缺少scope时只会对当前会话中的下一个事务生效，可选值：REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 修改transaction_isolation变量，枚举类型可选值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE
mysql> SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
mysql> SET transaction_isolation=READ-COMMITTED;
下面说明MySQL是如何支持不同的隔离级别的：
-
REPEATABLE READ
同一事务中，通过第一次读取时建立快照来实现快照读。
对于加锁读、
UPDATE和DELETE语句，锁依赖该语句是使用了唯一索引的相等搜索条件的还是范围类型搜索条件。- 对于使用了唯一索引的相等搜索条件，InnoDB仅锁定找到的记录索引。
- 对其它搜索条件，InnoDB锁定索引扫描的范围，使用next-key lock去阻塞其它事务向这个范围的间隙的插入操作。
-
READ COMMITTED
在同一事务中，每次读取都会建立快照。
对于加锁读、
UPDATE和DELETE语句，InnoDB仅对索引记录加锁，不会对间隙加锁。因此允许向加锁的记录后自由插入新的记录，gap lock锁仅用来做唯一性的约束检查及外键约束检查。（ Gap locking is only used for foreign-key constraint checking and duplicate-key checking. ）READ COMMITTED隔离级别下，binlog必须设为ROW模式。如果设置了binlog_format=MIXED，服务器也会自动使用ROW模式的binlog。使用
READ COMITTED有以下额外影响：- 对
UPDATE或DELETE语句，InnoDB只持有它更新或者删除的行的锁。在MySQL计算完WHERE条件后就会释放不匹配的行的记录锁。这大大减少了产生死锁的可能。 - 对
UPDATE语句，如果一个行已经加锁，InnoDB执行半一致性读，将最新的提交版本返回给MySQL，这样MySQL就可以确定该行是否与UPDATE的WHERE条件匹配。如果行匹配（必须更新），MySQL再次读取这行，并且这次InnoDB要么对其加锁要么等待对其加锁。（For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.）
- 对
-
READ UNCOMMITTED
SELECT语句以非锁定方式执行，但是可能读取到行的旧版本（脏读）。除此之外，跟READ_COMMITTED一样。 -
SERIALIZABLE
此隔离级别和
REPEATABLE READ相似。区别是，如果autocommit禁用，InnoDB对所有普通SELECT语句后加上LOCK IN SHARE MODE，即为每个读取操作加一个共享锁；如果autocommit启用，每个SELECT语句就是单独的只读事务，采用一致性非锁定读，事务可以并发。（If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions.）
快照读，使用快照信息基于时间点显示查询结果，而不考虑其它事务对记录的修改。如果查询的数据被另一个事务修改，根据undo log重建原始数据。这样就避免的加锁，提高并发性。
使用REPEATABLE-READ级别时，快照基于执行第一次读取操作的时间；使用READ-COMMITTED级别时，快照将重置为每个一致性读取操作的时间。
事务日志
事务隔离性由锁和MVCC机制来实现。原子性、持久性通过数据库的redo log和undo log来完成。redo log保证了持久性，undo log保证了原子性。
redo和undo的作用都可以视为是一种恢复操作，redo恢复提交事务修改的页操作，undo回滚行记录到某个特定版本。
redo log
重做日志由两部分组成：一是内存中的重做日志缓冲（redo log buffer），是易失性的；二是重做日志文件（redo log file），是持久性的。当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。
redo log以块为单位进行存储，每个块占512字节，称为redo log block。不管是redo log buffer还是redo log file都是这样以512字节的块存储的。
redo log buffer中未刷到磁盘的日志称为脏日志。默认情况下事务每次提交的时候都会刷盘，这是因为innodb_flush_log_at_trx_commit=1。该参数的解释：
| 值 | 简要解释 | 详细含义 |
|---|---|---|
| 0 | 延迟写 | log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。 logbuffer --每隔1s--> logfile --实时--> disk |
| 1 | 实时写，实时刷 | 每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。 logbuffer --> 实时 --> logfile --实时--> disk |
| 2 | 实时写，延迟刷 | 每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。 logbuffer --> 实时 --> logfile --每隔1s--> disk |
内存中（buffer pool）未刷到磁盘的数据称为脏数据。在innoDB中，数据刷盘的规则只有一个：checkpoint。
redo log基本上都是顺序写的，在数据库运行时不需要对redo log的文件进行读取操作，而undo log是需要进行随机读写的。
在MySQL中还有一种二进制日志binlog，用来进行POINT-IN-TIME（PIT）的恢复及主从复制（Replication）环境的建立。表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，两者区别很大。
首先，产生方式不同。重做日志是InnoDB存储引擎层产生的，binlog是服务器层产生的，并且MySQL中任何存储引擎对于数据库的更改都会产生binlog。
其次，内容格式不同。重做日志是物理格式日志，记录的是对于每个页的修改。binlog是一种逻辑日志，记录的是对应的SQL语句或记录。
此外，写入时间点不同。重做日志在事务进行中不断被写入，不是随事务提交的顺序写入的。binlog只是在事务提交完成后进行一次写入。
undo log
undo log有两个作用：回滚和MVCC。
在数据修改的时候，不仅记录了redo log，还记录了对应的undo log。事务失败或主动回滚时，都要借助undo log。undo会产生redo log，这是因为undo log也需要持久化的保护。
undo log分为insert undo log和update undo log，它们的数据结构是不同的。
-
insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。
下图显示了insert undo log的格式，其中星号表示对存储的字段进行了压缩。
字段 含义 next 下个undo log的开始位置 type_cmpl undo log的类型，对于insert undo log该值为11 undo no 事务ID table id undo log对应的表对象 len & col 所有主键的列和值，在进行rollback时，根据这些值定位到记录，然后进行删除。 -
update undo log : 事务对记录进行delete和update操作时产生的undo log, 不仅在事务回滚时需要, 一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。
update undo log相对于insert undo log，记录的内容更多，所需的空间更大。 next、start、undo no、table id与之前介绍的 insert undo log部分相同。
type_cmpl 含义 12 TRX_UNDO_UPD_EXIST_REC更新non-delete-mark的记录 13 TRX_UNDO_UPD_DEL_REC将 delete的记录标记为not delete 14 TRX_UNDO_DEL_MARK_REC将记录标记为delete 字段 含义 update_vector update操作导致发生改变的列 DATA_TRX_ID 同TRX_ID或DB_TRX_ID DATA_ROLL_PTR 同ROLL_PTR或DB_ROLL_PTR
回滚日志存放在数据库内部的一个特殊段中，这个段称为回滚段（undo segment），位于共享表空间内。
在InnoDB中MVCC是通过undo来实现的。当用户读取一行记录时，若该记录已经被其它事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。在InnoDB多版本化方案中，使用SQL语句删除行时，不会立即从数据库中删除。InnoDB仅在舍弃update undo log时才会从数据库删除行及其索引记录，此删除操作被称为purge。
多版本并发控制
MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制MVCC。MVCC没有一个统一的实现标准。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制不同，但大都实现了不加锁的读操作，写操作也只锁定必要的行。
MVCC的实现，是通过保存数据在某个时间的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。在执行普通SELECT操作时通过访问版本链，使得不同事务的读-写、写-读并发执行，从而提升系统性能。
MVCC只在RC、RR两个隔离级别下生效。
InnoDB在聚簇索引项中增加了三个属性。
| 属性 | 长度（Byte） | 作用 |
|---|---|---|
| DB_TRX_ID | 6 | 最近一个插入或更新此行的事务ID。删除在内部被视为更新，其中行中的特殊位将标记为已删除 |
| DB_ROLL_PTR | 7 | 回滚指针，指向回滚段中写入的undo log。每个undo log也有roll_ptr属性。 |
| DB_ROW_ID | 6 | 自动生成的隐藏主键，当表中有主键或非NULL唯一键时不会生成 |
下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的：
-
SELECT
InnoDB只查找事务ID小于当前事务的数据行，这样可以确保当前事务读取的行，要么是在当前事务开始前已经存在的，要么是当前事务自身插入或修改过的。
-
INSERT
新插入的行DB_TRX_ID为当前事务ID
-
DELETE
被删除的行DB_TRX_ID为当前事务ID，并将标记为已删除
-
UPDATE
修改行DB_TRX_ID为当前事务ID
如何选择隔离级别
首先，项目中是不会使用RU和Serializable两个隔离级别的，原因分别是：
- RU有脏读的问题，一般项目都不会接受脏读
- Serializable级别在禁用自动提交时，会在每一行数据上都加锁，所以可能产生大量的阻塞和超时问题。一般只有在使用mysql自带的分布式事务功能时才会用到此级别。
在RR和RC之间：从并发性上来说（1）RC不锁间隙，出现死锁的几率较底；（2）未命中索引时也不会“锁表”；（3）半一致性读增加了update操作的并发性。因此只要业务能够容忍不可重复读的问题，就可以使用RC级别。
InnoDB锁
InnoDB实现了两种标准的行级锁，共享（shared，S）锁和排他（exclusive，X）锁，也叫读锁和写锁。
- 共享锁允许持有锁的事务读取当前行
- 排他锁允许持有锁的事务更新或删除当前行
如果事务T1持有行r的共享锁，另一个事务T2对行r的请求这样处理：
- T2请求共享锁会立即被允许，最终T1和T2都持有r的读锁
- T2请求排他锁不会立即被允许
如果事务T1持有行r的排他锁，另一个事务T2也请求行r的排他锁则不会立即被允许，而是直到T1释放行r上的锁。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加X锁；对于普通SELECT语句，InnoDB不会加任何锁；MySQL 5.7中，事务可以通过以下语句显式给记录集加S/X锁。
- S：
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE - X：
SELECT * FROM table_name WHERE ... FRO UPDATE
表锁
对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们选择InnoDB表的理由。在个别特殊事务中，也可以考虑使用表级锁：
- 事务需要更新大部分或全部数据，表又比较大
- 事务涉及多个表，比较复杂，可能引起死锁，造成大量事务回滚
使用表锁时要注意以下两点：
-
表锁不是由InnoDB存储引擎层管理的，而是由MySQL Server层负责的，仅当autocommit=0、innodb_table_locks=1时，InnoDB层才知道MySQL加的表锁，MySQL Server才能感知到InnoDB加的行锁，这种情况下InnoDB才能自动识别涉及表锁的死锁。
-
在用LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放。
SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES;
意向锁
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁。意向锁是InnoDB自动加的，不需要用户干预。
意向锁（Intention Locks）是表级锁，用于指示事务对表中一行有加锁（共享锁或者排他锁）的意向。意向锁有两种类型：
- 意向共享（IS）锁：指示事务打算对表中某一行设置共享锁
- 意向排他（IX）锁：指示事务打算对表中某一行设置排他锁
意向锁协议如下：
- 在事务可以获取表中某行的共享锁之前，它必须首先获得该表的意向共享锁或者意向排他锁
- 在事务可以获取表中某行的排他锁之前，他必须首先获得该表的意向排他锁。
表级锁兼容性：
| 当前锁\请求锁 | X | IX | S | IS |
|---|---|---|---|---|
| X | Conflict | Conflict | Conflict | Conflict |
| IX | Conflict | Compatible | Conflict | Compatible |
| S | Conflict | Conflict | Compatible | Compatible |
| IS | Conflict | Compatible | Compatible | Compatible |
如果一个事务请求的锁和表中现有锁兼容，那么就能获取到；反之就获取不到。
行锁
InnoDB行锁是通过给聚簇索引的索引项上加锁来实现的。InnoDB这种行锁实现特点意味着：
- 如果不通过索引条件查询，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。
- 使用普通索引，即使访问不同行的记录，如果使用相同的索引键，会出现锁冲突。
- 不同的事务可以使用不同的索引锁定不同的行。
InnoDB行锁分为3种情形（算法）：
- Record lock
- Gap lock
- Next-key lock
Record locks
记录锁，仅对索引项加锁。
例子：
# 表结构
mysql> create table t(
c1 int not null auto_increment,
c2 int not null default 0,
c3 varchar(16) not null default '',
primary key(c1) using btree,
index idx_c2(c2) using btree
)engine=innodb;
表t中有数据：
| c1 | c2 | c3 |
|---|---|---|
| 1 | 1 | a |
| 2 | 2 | a |
| 3 | 3 | b |
| 4 | 8 | c |
| 5 | 15 | d |
那么SELECT c1 FROM t WHERE c1=1 FOR UPDATE会对c1=1行的主键索引上加X锁。
Gap locks
Gap lock对索引之间的间隙（开区间）加锁。Gap lock的唯一作用就是防止其它事务的插入操作。
间隙在索引项之间或第一个索引记录之前和最后一个索引记录之后，间隙可以跨越零个、一个或多个索引项。
间隙锁特点：
-
当使用一个唯一索引去查找一行记录时是用不上间隙锁的。（不包括这种情况：当搜索条件仅包括了复合唯一索引中的某些列）。
-
不同事务间锁可以加在一个间隙上，因为它们的功能是一样的。
例子：
SELECT * FROM t WHERE c2=3 FOR UPDATE;
会在c2=3行上的idx_c2索引项上加X锁，在(2,3)，(3,8)上加gap lock。
Next-key locks
Next-key lock是索引记录上的记录锁和索引记录之前间隙上的间隙锁的结合（左开右闭区间），当next-key lock加在某索引上，则该记录及它前面的间隙都被锁定。
当我们使用范围而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据的索引项和间隙加锁，这种锁机制就是Next-key锁。
InnoDB使用Next-key锁的目的，一方面是为了防止幻读，另一方面是为了满足其恢复和复制的需要。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，并发插入较多的应用，尽量使用相等条件来访问更新数据，避免使用范围条件。
例子：
SELECT * FROM t WHERE c1 > 5 FOR UPDATE;
会在虚拟上界（supremum pseudo-record）上加next-key lock，即区间区间(5, +∞)。
SELECT加锁分析例子
假设有表如下，id为主键：
| id(int) | name(varchar) | num(int) |
|---|---|---|
| 1 | aaa | 100 |
| 2 | bbb | 200 |
| 3 | ccc | 300 |
| 7 | ddd | 200 |
以下六条sql在不同事务隔离级别下，是否加锁，加的是共享锁还是排他锁，是否存在间隙锁？
select * from t where c = ?
select * from t where c < ?
select * from t where c = ? lock in share mode
select * from t where c < ? lock in share mode
select * from t where c = ? for update
select * from t where c < ? for update
解答：
| 条件列索引类型 | sql | RU/RC | RR/Serializable | |
|---|---|---|---|---|
| 条件列非索引 | select * from t where num = 200 | 不加锁 | RR: 不加锁，快照读 Serializable: 所有记录的聚簇索引加S锁，聚簇索引的所有间隙加gap lock | |
| - | select * from t where num < 200 | 不加锁 | RR:不加锁，快照读 Serializable: 所有记录的聚簇索引加S锁，聚簇索引的所有间隙加gap lock | |
| - | select * from t where num = 200 lock in share mode | 在id=2,7两行的聚簇索引上加S锁 | 所有记录的聚簇索引加S锁，聚簇索引的所有间隙加gap lock | |
| - | select * from t where num < 200 lock in share mode | 在id=3行的聚簇索引上加S锁 | 所有记录的聚簇索引加S锁，聚簇索引的所有间隙加gap lock | |
| - | select * from t where num = 200 for update | 在i=2,7两行的聚簇索引上加X锁 | 所有记录的聚簇索引加X锁，聚簇索引的所有间隙加gap lock | |
| - | select * from t where num < 200 for update | 在id=3行的聚簇索引上加X锁 | 所有记录的聚簇索引加X锁，聚簇索引的所有间隙加gap lock | |
| 条件列是聚簇索引 | select * from t where id = 2 | 不加锁 | RR: 不加锁，快照读 Serializable: 在id=2的聚簇索引上加S锁 | |
| - | select * from t where id > 2 | 不加锁 | RR: 不加任何锁，快照读 Serializable: 在id=3,7的聚簇索引上加S锁，在(2,3)(3,7)(7,+∞)加gap lock | |
| - | select * from t where id = 2 lock in share mode | 在id=2的聚簇索引上加S锁 | 在id=2的聚簇索引上加S锁 | |
| - | select * from t where id > 2 lock in share mode | 在id=3,7的聚簇索引上加S锁 | 在id=3,7的聚簇索引上加S锁，在(2,3)(3,7)(7,+∞)加gap lock | |
| - | select * from t where id = 2 for update | 在id=2的聚簇索引上加X锁 | 在id=2的聚簇索引上加X锁 | |
| - | select * from t where id > 2 for update | 在id=3,7的聚簇索引上加X锁 | 在id=3,7的聚簇索引上加X锁，在(2,3)(3,7)(7,+∞)加gap lock | |
| - | `select * from t where id = 6 [lock in share mode | for update]` | - | id=6是不存在的行，会在(3,7)上加gap lock |
| - | `select * from t where id > 18 [lock in share mode | for update]` | - | 在(7,+∞)上加gap lock |
| 条件列普通（非唯一）索引 | select * from t where num = 200 | 不加锁 | RR: 不加锁，快照读 Serializable: 在id=2,7的聚簇索引加S锁，在num=200的普通索引上加S锁，在(100,200)(200,300)加gap lock | |
| - | select * from t where num > 200 | 不加锁 | RR: 不加锁，快照读 Serializable: 在id=3的聚簇索引加S锁，在num=300的普通索引上加S锁。在(200,300)(300,+∞)加gap lock | |
| - | select * from t where num = 200 lock in share mode | 在id=2,7的聚簇索引上加S锁，在num=200的普通索引上加S锁 | 在id=2,7的聚簇上加S锁，在num=200的普通索引上加S锁，在(100,200)(200,300)加gap lock | |
| - | select * from t where num > 200 lock in share mode | 在id=3的聚簇索引上加S锁，在num=300的普通索引上加S锁 | 在id=3的聚簇索引上加S锁，在num=300的普通索引上加S锁，在(200,300)(300,+∞)加gap lock | |
| - | select * from t where num = 200 for update | 在id=2,7的聚簇索引上加X锁，在num=200的普通索引上加X锁 | 在id=2,7的聚簇索引上加X锁，在num=200的普通索引上加X锁，在(100,200)(200,300)加gap lock | |
| - | select * from t where num > 200 for update | 在id=3的聚簇索引上加X锁，在num=300的普通索引上加X锁 | 在id=3的聚簇索引上加X锁，在num=300的普通索引上加X锁，在(200,300)(300,+∞)加gap lock | |
| - | `select * from t where num = 250 [lock in share mode | for update]` | - | 在(200,300)加gap lock |
| - | `select * from t where num > 400 [lock in share mode | for update]` | - | 在(300,+∞)加gap lock |
说明：
- RC/RU隔离级别中，InnoDB做了优化，在条件列没有索引的情况下，尽管通过聚簇索引扫描全表，对每个记录进行加锁，但是稍后会把不符合条件的锁释放掉。
- RC/RU级别下可以认为没有gap lock
- 聚簇索引和辅助索引同时加锁时，是先给辅助索引加锁，再通过辅助索引找到聚簇索引，然后给聚簇索引加锁
Insert Intention Locks
插入意向锁是一种gap lock，也是实施在索引上的，专门针对insert操作。
多个事务，在同一个间隙插入记录时，如果插入的位置不冲突，不会阻塞彼此。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个(4,7)上的插入意向锁，获取在插入行上的排他锁，但是不会被相互锁住，因为数据行并不冲突。
插入意向锁的作用是为了提高并发插入的性能，多个事务同时写入不同数据至同一间隙内，不需要等待其它事务完成，不会发生锁等待。
插入意向锁既然是一种gap锁，那么其与gap锁有什么不同，为什么不能使用gap锁来代替？
gap锁是可以共存的，gap锁存在的唯一目的是防止有其他事务进行插入，从而造成幻读。假如利用gap锁来代替插入意向锁，那么两个事务则不能同时对一个gap进行插入。因此为了更高的并发性所以使用插入意向gap锁；
在插入时是否可以不用插入意向gap锁，而仅使用记录锁？
由于数据在插入之前，对应的索引记录是不存在的，因此索引记录锁必须是插入后才能添加;在更新或者删除时要添加next-key lock，因此在插入时需要一种gap锁来与更新和删除进行互斥，否则就会发生幻读，但是使用普通的gap锁或者next-key lock 会造成并发插入效率低，所以为了trade-off mysql引入了插入意向gap锁。总之一句话，为了不造成幻读和性能，所以使用了插入意向gap锁；
AUTO-INC Locks
AUTO-INC lock是向具有AUTO_INCREMENT列的表插入数据时，事务获取的特殊表级锁。当对含有AUTO_INCREMENT列的表进行插入时，首先会执行SELECT MAX(AUTO_INC_COL) FROM t FOR UPDATE来得到计数器的值，然后再将这个值加一赋予自增列。在完成自增长插入的SQL语句后，会立即释放锁。
使用innodb_autoinc_lock_mode参数来控制自增长的模式。
死锁
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。 下面介绍几种避免死锁的常用方法。
- 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
- 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
- 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
- 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。
- 当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。 对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁 。
尽管通过上面介绍的设计和SQL优化等措施，可以大大减少死锁，但死锁很难完全避免。因此，在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。
附录
MySQL系统变量
MySQL通过[系统变量][dev.mysql.com/doc/refman/…
查看：
mysql> SHOW [GLOBAL|SESSION] VARIABLES LIKE 'variable_name';
mysql> SELECT @@[GLOBAL|SESSION].variable_name;
修改：
mysql> SET [GLOBAL|SESSION] variable_name = variable_value;
注：
-
scope缺省为SESSION
-
修改SESSION变量只会影响当前会话（连接），修改GLOBAL变量只影响之后建立的会话（连接）。重启MySQL均会失效
-
通过配置文件my.cnf/my.ini修改可以永久生效
-
MySQL除了系统变量外，还有局部变量、用户变量
查看InnoDB状态
show engine innodb status\G;
查看锁状态
select * from information_schema.innodb_locks;
lock_id: InnoDB内部唯一的锁id。
lock_trx_id: 持有锁的事务id。和INNODB_TRX表的TRX_ID列联合可以看到事务的详细信息。
lock_mode: 锁模式描述符，包括S、X、IS、IX、GAP、AUTO_INC和UNKNOWN。锁模式描述符可以组合使用来表示特定的锁模式。
lock_type: 锁类型，可选值有RECORD代表行锁、TABLE代表表锁。
lock_table: 被锁定或包含锁定记录的表名称。
lock_index: lock_type为RECORD时，表示索引名称，否则为NULL。
lock_space: lock_type为RECORD时，表示被锁定记录的表空间id，否则为NULL。
lock_page: lock_type为RECORD时，表示被锁定记录的页号，否则为NULL。
lock_rec: lock_type为RECORD时，表示被锁定记录的页所在的堆号，否则为NULL。
lock_data: 锁相关的数据。lock_type为RECORD时有值，否则为NULL。主键索引上加锁时显示被锁记录的主键值，唯一辅助索引上加锁时显示被锁记录的辅助索引值，非唯一辅助索引上加锁时显示被锁记录的辅助索引值和主键索引值。显示supremum pseudo-record表示锁加在最大的虚拟记录上。如果页包含的锁记录不在buffer pool里，显示NULL。
Q&A
幻读的含义及InnoDB如何解决幻读问题？
根据MySQL 5.7官方文档Phantom Row一节的解释。幻读：一个事务中用相同的查询条件多次查询产生不同的行集合。例如，如果一个SELECT语句执行了2次，但是第二次返回的行在第一次没有被返回，那么这个行就是幻行。
准确地说，对于快照读，幻读依赖MVCC解决；对于当前读，幻读依赖gap lock（next-key lock）解决。
直接BEGIN能否开启事务？
我错了，可以。
redo log和undo log的结构？
文中已补充。
Gap locks中的例子？
意向锁到底有什么作用？
比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。
举个例子，如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。
行锁实现特点第二条的含义？
“使用普通索引，即使访问不同行的记录，如果使用相同的索引键，会出现锁冲突”。举例：
| id | name |
|---|---|
| 1 | 1 |
| 1 | 4 |
上表，id有普通索引；name无索引。
事务1先执行select * from t where id=1 and name='1' for update;，事务2再执行select * from t where id=1 and name='4' for updata;时，会出现锁冲突。因为使用了相同的索引键（索引项）。
插入意向锁补充
gap锁兼容性：
| 当前锁\请求锁 | gap lock | insert intention lock |
|---|---|---|
| gap lock | 兼容 | 冲突 |
| insert intention lock | 冲突 | 兼容 |
首先，由于gap lock的存在，插入的时候需要判断当前间隙是否有gap lock。如果有gap lock，锁等待；如果没有gap lock或者有insert intention lock，加锁成功（这时另一个事务2再来加gap lock，事务2就锁等待），然后再去INSERT。经过测试，当当前列没有唯一约束，插入同一值不会阻塞。当当前列有有唯一约束，插入同一值时，先加意向插入锁的事务会阻塞后加意向插入的事务。
