InnoDB Locking And Transaction

2017-12-12  本文已影响72人  言西枣

1.Locking

1.1加锁模式：共享锁与独占锁

InnoDB实现了两类行级锁， shared（S）locks 和exclusive（X）locks

1.2加锁粒度：意向锁

InnoDB支持多粒度锁，也就是行锁与表锁的共存。意向锁就是InnoDB使用的表级锁，表明了事务之后对于这个表所需要行级锁的类型（S|X）。因此意向锁也分为Intention shared（IS）和Intention exclusive（IX）。

For example,SELECT ... LOCK IN SHARE MODE sets an IS lock and SELECT ... FOR UPDATE sets an IX lock.

反过来说，在获取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

隔离性是数据库在面对多个事务同时执行修改或者查询时，调节性能、可靠性、一致性以及结果的可重现性的平衡的关键。
共有如下四个级别：

InnoDB默认是REPEATABLE READ

REPEATABLE READ

  1. 同一个事务所有的Consistent reads（也就是默认情况，也就是不加锁）使用本事务第一次读时建立的快照，也就是说同一个事务中的各个SELECT彼此具有一致性（也就是可重复读，不会看到同时发生的其他事务对于数据的修改）。
  2. 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

  1. 每一个Consistent read，即使是在同一个事务中，都会重设读取最新的快照（所以会出现不可重复读的问题）。
  2. Locking reads，UPDATE，DELETE语句，InnoDB只会使用Record Lock锁住这个索引值，不会锁住Gap，也就是允许锁住的记录附近的值的插入（会有幻读的问题），Gap Locking只会在外键检查和重复键检查中使用。

READ UNCOMMITTED

SELECT语句不会加锁，可能会读到一个行稍早的数据版本，因此在这个级别上，读是不一致的。也就是脏读。其他方面这个级别和READ COMMITTED是类似的。

SERIALIZABLE

这个级别类似于REPEATABLE READ

2.2autocommit、commit、rollback

在InnoDB，所有的用户活动都发生在事务中，如果autocommit开启了，每个SQL语句都会自己构成一个事务，MySQL默认为每个Session开启autocommit，所以每个SQL语句执行完并没有出错的话，MySQL都会执行一次commit。如果语句返回错误，那么会根据错误执行commit\rollback。

  1. 对于开启了autocommit的Session，可以通过START TRANSACTION或者BEGIN语句来开启一个多语句的事务，然后通过COMMIT或ROLLBACK来结束事务。
  2. 如果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语句也不生效：

2.4Locking Reads

如果你在事务中查询数据，然后进行插入或者更新，普通的SELECT语句无法提供足够的保护，其他事务可以更新或者删除你刚刚查询的数据行。InnoDB支持两种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加锁的情况如下：

4.Phantom Rows

5.Deadlocks

上一篇 下一篇

猜你喜欢

热点阅读