1.Locking
1.1加锁模式：共享锁与独占锁
InnoDB实现了两类行级锁， shared（S）locks 和exclusive（X）locks
- 共享锁用于事务读取
- 独占锁用于事务更新或者删除
- S锁与S锁不冲突，与X锁冲突；X锁与S锁冲突，与X锁冲突
1.2加锁粒度：意向锁
InnoDB支持多粒度锁，也就是行锁与表锁的共存。意向锁就是InnoDB使用的表级锁，表明了事务之后对于这个表所需要行级锁的类型（S|X）。因此意向锁也分为Intention shared（IS）和Intention exclusive（IX）。
For example,
SELECT ... LOCK IN SHARE MODEsets anISlock andSELECT ... FOR UPDATEsets anIXlock.
反过来说，在获取S|X锁之前，需要取得相应的IS（或IX）|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 |
1.3加锁类型
Record Lock
Record lock是在索引项上的锁，例如下面的SQL会将所有c1=10的行全部锁住，无法insert、update、delete
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
对于没有索引的表，InnoDB也会创建一个隐藏的聚簇索引。
READ COMMITTED只有Record Lock没有Gap Lock和Next-key Lock，所以locking read（即LOCK IN SHARE MODE和FOR UPDATE）、UPDATE和DELETE用的都是Record Lock
REPEATABLE READ则有Record Lock、Gap Lock和Next-key Lock
InnoDB使用行级锁时，是当它搜索或扫描一个表的索引，在它遇到的那个索引记录上加上共享或独占锁，所以InnoDB的行级锁其实就是索引记录锁（Record Lock）。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_read-committed
Gap Lock
Gap Lock是将索引记录之间的范围加锁的锁，索引值在这段范围内的记录无法insert，如下SQL将锁住10<=c1<=20
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
Gap Lock可以是一段索引值、单个索引值或者空，这是在性能和并发度之间权衡的结果，所以只有部分事务隔离等级使用了。
Gap Lock在使用唯一索引查询单行结果的时候并不会被用到，但当这个唯一索引是联合索引，并且查询条件只包括了部分索引列的时候还是会被使用的。例如下面的SQL只会使用一个Record Lock，如果id有唯一索引的话，并不影响之前索引值上的插入。
SELECT * FROM child WHERE id = 100;
当id没有索引，或者不是唯一索引的时候，那么索引值之前的到上一个索引值之间的范围也会被锁。
Gap Lock只会阻止在这个gap范围内的插入操作，所以Gap X-Lock与Gap S-Lock的效果是一样的，并不会冲突。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-gap-locks
Next-key Lock
Next-key Lock是在某一个索引记录上的Record Lock和这个索引记录之前的这段范围的Gap Lock的组合。
所以如果Session A拥有一个索引记录R上的共享|独占Next-key Lock，Session B就不能在R之前的这段索引记录范围内插入新的索引记录。
例如一个索引拥有10、11、13、20几个值，那么可能的Next-key Lock就包括了如下的间隔
(-infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, infinity)
InnoDB的默认事务隔离级别是REPETABLE READ，InnoDB使用Next-key Lock来搜索和扫描索引，可以防止Phantom Rows。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
Insert Intention Lock
Insert Intention Lock是insert操作在插入行（获取该行的X锁）之前使用的一种Gap Lock，多个Session如果要插入同一个Gap，但如果是不同的索引位置那么是不会冲突的。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
AUTO-INC Lock
AUTO-INC Lock是事务往有Auto Increment字段的表插入记录时使用的一种表级锁，多个插入的事务需要等待来保证主键值的连续性。
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-auto-inc-locks
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode
2.Transaction Model
多版本（multi-versioning）数据库与两阶段加锁（two-phase locking)的结合。默认是行级锁以及无锁的一致性读（nonlocking consistent reads）。
2.1Transaction Isolation Levels
隔离性是数据库在面对多个事务同时执行修改或者查询时，调节性能、可靠性、一致性以及结果的可重现性的平衡的关键。
共有如下四个级别：
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
InnoDB默认是REPEATABLE READ
REPEATABLE READ
- 同一个事务所有的Consistent reads（也就是默认情况，也就是不加锁）使用本事务第一次读时建立的快照，也就是说同一个事务中的各个SELECT彼此具有一致性（也就是可重复读，不会看到同时发生的其他事务对于数据的修改）。
- Locking reads（SELECT WITH FOR UPDATE OR LOCK IN SHARE MODE），UPDATE，DELETE语句，会根据语句是否在唯一索引上使用唯一的条件而选择不同的锁。
- 唯一索引上的唯一条件，InnoDB会使用Record Lock把这个index Record锁住，而不是Gap Lock。
- 其他的情况下，InnoDB会使用Gap Lock或Next-Key Lock将扫描的索引范围给锁住，以阻止其他Session在这段范围插入记录。
READ COMMITTED
- 每一个Consistent read，即使是在同一个事务中，都会重设读取最新的快照（所以会出现不可重复读的问题）。
- Locking reads，UPDATE，DELETE语句，InnoDB只会使用Record Lock锁住这个索引值，不会锁住Gap，也就是允许锁住的记录附近的值的插入（会有幻读的问题），Gap Locking只会在外键检查和重复键检查中使用。
- 对于UPDATE、DELETE语句，InnoDB只会保持它需要更新或删除的行的锁，对于并不匹配的行的Record Locks，会在MySQL计算完WHERE语句后释放。
- 对于一个UPDATE语句，如果一个行已经被锁，InnoDB使用一个半一致性读（semi-consistent read），返回最新提交到MySQL的数据版本，如果这个行匹配UPDATE的WHERE字句，那么MySQL会重读这个行，并加锁或者等待锁。
READ UNCOMMITTED
SELECT语句不会加锁，可能会读到一个行稍早的数据版本，因此在这个级别上，读是不一致的。也就是脏读。其他方面这个级别和READ COMMITTED是类似的。
SERIALIZABLE
这个级别类似于REPEATABLE READ
- autocommit disabled，InnoDB会隐性地把所有SELECT转换为SELECT IN SHARE MODE
- antocommit enabled，SELECT就是自己的事务
2.2autocommit、commit、rollback
在InnoDB，所有的用户活动都发生在事务中，如果autocommit开启了，每个SQL语句都会自己构成一个事务，MySQL默认为每个Session开启autocommit，所以每个SQL语句执行完并没有出错的话，MySQL都会执行一次commit。如果语句返回错误，那么会根据错误执行commit\rollback。
- 对于开启了autocommit的Session，可以通过
START TRANSACTION或者BEGIN语句来开启一个多语句的事务，然后通过COMMIT或ROLLBACK来结束事务。 - 如果autocommit关闭了，那么session永远处于一个开启的事务中，一个
COMMIT或ROLLBACK语句会结束当前的事务然后开启新的事务。（如果session结束时没有显式地提交最后的事务，那么MySQL会回滚）
某些语句会隐式地提交事务。
一个COMMIT语句表明当前事务的修改已经持久化，并且对其他session可见；而一个ROLLBACK语句取消了当前事务做的所有修改。COMMIT与ROLLBACK会释放所有当前事务的InnoDB锁。
2.3Consistent nonblocking read
snapshot
某一个特定时间的数据表示，即使其他事务提交了修改也保持不变，特定的隔离级别使用这个来实现一致性读（consistent read）。
consistent read
是使用快照（snapshot）信息来展示查询结果的一个读操作，也就意味着InnoDB使用multi-versioning来展示某个时刻数据库快照的查询结果。这个查询能看到这个时刻之前提交的事务修改，而不会看到同一时刻其他事务进行的修改，例外是能看到本事务之前提交的修改。如果查询的数据已经被其他事务修改，那么原始数据会通过undo log的内容来重建（恢复）。这个技术避免了一些锁带来的并发问题。
REPEATABLE READ级别，snapshot是进行本事务第一次读操作时的数据，只有事务提交之后才能得到一个新版本的snapshot。
READ COMMITTED级别，snapshot在每次consistent读操作时重设。
Consistent read是InnoDB在RC|RR级别处理SELECT语句时的默认模式，因为consistent read不会给它访问的表加锁，其他session可以在一个consistent read操作时自由地修改这些表。
假设默认的级别上，当你执行一个consistent read时，InnoDB会给你的事务一个时间点，你的查询看到的数据库就是这个时间点的数据库。如果其他事务在这个时间点之后删除了一行并提交，你看到的数据库中那一行并不会被删掉，插入与更新也类似。
snapshot状态适用于SELECT语句，而不是DML语句，如果事务A插入或更新了一些行并提交，那么RR级别的其他事务B即使无法通过查询看到这个变化，但事务B的DELETE/UPDATE语句也是会影响到刚才提交的那些行，如果发生了这种情况，那么这些变化对于当前事务B就会可见，例如：
SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.
你可以通过提交事务来推进时间点。
这也就是所谓的Multi-versioned concurrency control。
下面的例子中，session A只有在session B提交了插入操作并且自己也提交之后才能看到B插入的行，因为只有在A提交之后A的时间点才能越过B的提交。
Session A Session B
SET autocommit=0; SET autocommit=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------
如果你需要时刻看到最新状态的数据库，使用RC级别或者locking read。
Consistent read对于特定DDL语句也不生效：
- DROP TABLE，因为MySQL无法使用一个删除了的表。
- ALTER TABLE，因为这个语句会生成一个临时复制表，然后删除原表，当你重新执行一个consistent read的时候，新表里的行对于你的snapshot来说是不存在的，所以事务会报错。
2.4Locking Reads
如果你在事务中查询数据，然后进行插入或者更新，普通的SELECT语句无法提供足够的保护，其他事务可以更新或者删除你刚刚查询的数据行。InnoDB支持两种locking reads来提供额外的保护：
- SELECT...LOCK IN SHARE MODE
给要读得行上加共享锁，其他的事务可以读这些行，但只有你的事务提交以后才能修改它们。如果其他事务修改了这些行但是没有提交，那么你的查询需要等待那个事务结束然后使用最新的值。 - SELECT...FOR UPDATE
给行以及相关的索引记录加排他锁，与UPDATE语句类似。其他事务对于这些行的更新、Locking Reads、或者某些隔离级别下的读都会阻塞。Consistent reads会忽视记录上的锁。
所有被Locking reads设置的锁会在事务提交或回滚的时候释放。
SELECT FOR UPDATE对于行的加锁只有在autocommit禁用的时候有效，可以通过START TRANSACTION或者将autocommit设为0来禁用。
3.Locks set by different SQL Statements in InnoDB
Locking read、UPDATE、DELETE通常会给处理SQL过程中所有扫描到的Index Record加上Record Lock。它并不会在意WHERE语句是否将这些行排除了。InnoDB不会记住WHERE条件，只知道它扫描过的index范围。加的锁一般来说是Next-key Lock，同时也会阻塞对Record前面的Gap进行插入的操作。但是，gap Locking可以被显式地禁用，事务隔离等级也会影响所使用的锁。
如果一个查询用了非聚簇索引并且加的Record Lock也是排它锁，InnoDB也会取出对应的聚簇索引，并在上面加锁。
如果你的语句没有合适的索引使用，那么MySQL必须扫描整个表来处理语句，那么表中的每一行都会被加锁，也就会阻塞其他用户对于这个表所有的插入，所以给表建立好的索引很重要，以防查询时扫描很多不必要的行。
InnoDB加锁的情况如下：
-
SELECT...FROM是consistent read，不加锁除非隔离级别设为SERIALIZABLE。SERIALIZABLE级别下，查询会给它遇到的索引记录加Next-key Lock，但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。
-
SELECT...FROM...LOCK IN SHARE MODE给它遇到的所有索引记录加
共享Next-key Lock，但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。 -
SELECT...FROM...FOR UPDATE给它遇到的所有索引记录加
排它Next-key Lock，但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。
对于查询时遇到的索引记录，SELECT-FOR-UPDATE会阻塞其他session进行SELECT-IN-SHARE或者某些级别下的读。Consistent read会忽视它数据视图上的记录上的所有锁。 -
UPDATE...WHERE...给它遇到的所有索引记录加
排它Next-key Lock，但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。 -
当UPDATE修改了一个聚簇索引记录，那么相关的非聚簇索引记录上的隐式锁会被拿掉。UPDATE操作也会把受到影响的非聚簇索引记录上的共享锁拿掉，当执行插入新的非聚簇索引记录前重复值扫描或者插入新的非聚簇索引记录时。
-
DELETE FROM...WHERE...给它遇到的所有索引记录加
排它Next-key Lock，但是在使用唯一索引查询唯一的行时只会给索引记录加Record Lock。 -
INSERT会给插入的行加上一个
排它锁。这个锁是一个索引Record Lock，不是Next-key Lock（也就是没有Gap Lock）并且不会阻止其他事务往插入行之前的Gap中插入记录。
插入行之前，会加一种Insert intention Gap Lock，表明打算要插入，这样多个插入同一个Gap的事务如果不是要插入到同一个位置那就不需要等待。
如果发生duplicate key error，会加一个共享锁在冲突的行上。详见链接里的INSERT
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html -
INSERT...ON DUPLICATE KEY UPDATE与简单的INSERT不同，当发生duplicate key error发生的时候，会加一个排它锁在需要更新的行上。
-
REPLACE
-
INSERT INTO T SELECT...FROM S WHERE...加一个排它index Record Lock在每一个要插入到T的行上。详见文档。
-
AUTO_INCREMENT 详见文档。
-
LOCK TABLES 会加表锁，但这是在比InnoDB层更高的MySQL层加的锁。InnoDB在
innodb_table_locks = 1(the default) andautocommit = 0的情况下能感知到表锁，而MySQL层一直能感知到行锁。
网友评论