MySQL锁和隔离级别及InnoDB实现机制

数据库中通常用锁机制来实现事务的隔离性。
本笔记主要针对锁及隔离级别说明。

锁和隔离级别及InnoDB实现机制

事务的隔离级别和引发的问题

事务的隔离级别

从低到高：

1. 读未提交：这是事务的最低级别，它充许令外一个事务可以看到这个事务未提交的数据。
2. 读已提交：保证一个事务修改的数据提交后才能被另外一个事务读取，另外一个事务不能读取该事务未提交的数据。
3. 可重复读：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
4. 串行化：这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。

导致的三种问题

1. 脏读：读取到其他事务未提交的数据。
2. 不可重复读：在执行两次Select其中有另外一个事务对此数据修改，导致两次select结果不一致。
3. 幻读：执行两次select期间，有另外一个事务insert了一行新的数据，导致两次select的记录行数不一致。隔离级别和引发问题的对应关系：

---

InnoDB引擎中的锁机制

首先要明确的是，数据库中的锁并不是只对表中数据上锁，还有数据库内部例如缓冲池LRU列表等资源上锁。因此提供了两种锁机制，Lock和latch。

lock和latch

latch

latch一般称为闩锁，是一种轻量级的锁， 用于保证并发线程操作临界资源的正确性。
要求锁定的时间必须非常短，如果持续时间长则应用性能则变得非常差，通常没有死锁检测机制。

lock

lock的对象是事务，用来锁定数据库中的表、页、行等对象。
lock的对象一般仅在事务commit或者rollback之后才会释放。

lock存在死锁检测机制。

我们以下的讨论皆对于lock事务级别下的实现与影响。

多粒度lock锁

InnoDB中采用的是多粒度锁
所谓多粒度锁：如果需要对下层对象上锁，首先要对上层粗粒度的对象上锁，在粗粒度对象上的锁成为意向锁。

也就是说如果要对某一条记录上锁，需要分别对数据库A、表、页上加意向锁，最后才是对记录上行锁。

InnoDB中的意向锁仅支持到表级别

意向锁意味着事务希望在更细粒度上加锁。
InnoDB中意向锁设计比较简练，仅支持表级别的意向锁。
也就是说，在InnoDB中，最粗的粒度就是表级别，首先对表级别加意向锁，之后再对行记录加行锁。

读写锁和意向锁

这里介绍InnoDB中常用的两种锁：一种是具体锁住记录的读写锁，一种是表明意愿的表级意向锁。

读写锁分类

1. 共享锁（S）：允许事务读取数据。
2. 排他锁（X）：允许事务删除或更新数据。

InnoDB中默认使用行级读写锁。
但是行级锁都是基于索引的，当增删改查时匹配的条件字段不带有索引时，数据库会全表查询，所以这需要将整张表加锁,才能保证查询匹配的正确性，innodb使用的将是表级读写锁。

注意的是表级读写锁、行级读写锁都是读写锁，只有触发读写锁之后才会根据索引情况决定锁定一行还是锁定整个表。

表级意向锁

意向锁意味着事务希望在更细粒度上加锁，只有在行级读写锁的情况下，才会给表加意向锁。如果是扫描全表的情况，表上的就是读写锁，没有意向锁。

innodb的意向锁包括共享意向表锁和排他意向表锁。

1. 共享意向表锁（IS）：事务获取此表中几行的共享锁，则给表加入共享意向锁。
2. 排他意向表锁（IX）：事务获取此表中几行的排他锁，则给表加入共享排他锁。

虽然意向锁之间并不互斥(只要是加了意向锁说明具体的读写锁是加在行记录上的，将具体的互斥交于行读写锁处理)，但是会和扫描全表的表级读写锁互斥(表级读写锁表明要扫描整个表，同样有可能影响正在操作的某一行记录)

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁 ，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁的意义(好好体会)

(阻塞全表扫描的任务，而不必逐个检查每个行)
1.表级意向锁会与表级的共享 / 排他锁互斥！ 如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。
2. 表级意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。

如何查看锁

1. 查看当前锁请求的信息

show engine innodb status\G;

2. 查询视图的方式
   数据库系统数据库information_schema下表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS获取锁信息。

关于读请求时的锁

注意这里说的是读请求(是请求)时的锁，不是读锁的分类。
在某些隔离级别中读请求是不加锁的，但是如果加锁的话就只有读写锁，S或者X锁。

一致性非锁定读

一致性非锁定读是通过行多版本控制（MVCC）实现的。
读取操作不加锁，同时如果读取的行正在执行写操作而被加了X锁，此时读操作并不会等待行上锁的释放，而是去读取行的一个快照数据。

快照数据

快照数据是该行之前的版本，读取快照数据是不需要上锁的，快照数据是历史数据，没有任何事物可以对历史数据修改。

不同隔离级别，读取的快照数据不同

非锁定读取机制是InnoDB默认读取方式，这种方式极大提高了数据库并发性。

在隔离级别：读已提交、可重复读下采用的非锁定读取机制，但是两者定义的快照数据有些许差别。

1. 在读已提交隔离级别中，快照数据读取的是被锁定行最新一份快照数据。
2. 在可重复读隔离级别中，快照数据读取的是事务开始时的行数据版本。

也是如此解决了读已提交下不可重复读的问题。

一致性锁定读

使用SELECT操作时为行数据加锁

1. select * for update为行记录加X锁。阻塞其他事务的任何加锁操作。
2. select * lock in share mode为行记录加S锁。阻塞其他事务的加X锁操作。
   当然不加锁的操作，是没有办法被阻塞的。

其他锁

自增长锁

自增长锁是一种特殊的锁，是对含有自增长列的表进行插入insert时使用的。

自增长插入场景分类

传统auto-inc locking

每一个含有自增长值的表都会有一个自增长值计数器，当进行插入操作时

select max(auto_incr_col) from t for update

将采用此语句获取计数器的值。
这种锁是一种特殊的表锁，在完成自增长值插入的SQL语句后立即释放，不等待事务完成。

但是依然带来了并发性能的问题：

1. 必须要等待前一个inert完成，才能进行下一个插入。
2. 如果一个事务总需要插入大量数据，另外一个事务可能处于长时间阻塞过程。

InnoDB优化

为了提高自增长的性能，InnoDB提供innodb_autoinc_lock_mode控制自增长模式，默认值为1。

需要注意的是，自增长列必须是索引，如果是联合索引的话必须是第一列，否则将抛出异常。

行锁的实现算法

在这里，在重申一遍，如果加行锁，那么寻找过程一定是走的索引。
下面所谓的范围其实都是对索引而言，根据索引的值划分出的区间：

(索引值1，索引值2)，(索引值2，索引值3)，(索引值3，索引值4)…

record lock，锁定记录本身

record lock锁住索引记录。

gap lock，锁定记录附近的一个范围

gap lock，锁定记录附近的一个范围，但不包含记录本身。

next-key lock，锁定记录本身+附近范围

锁定一个范围并且包括记录本身，Innodb对于行的查询都采用这种算法。
有一些需要注意的点：

1. 查询的列是唯一索引（主键或唯一索引）时(如果唯一索引是一个多个列的联合索引，查询条件仅在查询该唯一索引所有列方降级)，innodb存储引擎会对next-key lock进行优化，将其降级为Record Lock。即仅锁住索引本身，而不是范围。PPS：即便是唯一索引貌似也只有定值条件下会降级，如果为index <= key的查询条件，依然是next-key locking，如果要降级一定是一个point，不是一个range。
   
2. 若是辅助索引使用Next-Key Lock锁定，除此之外还会对下一个键值加gap lock。
3. 如果表单中有多个索引，即便某些索引不在查询的列中，只要是涉及这一行数据的索引都要加锁：对于主键、唯一索引加record lock，其他辅助索引按照第2条加。

举一个例子：

主键a	辅助索引b
1	1
3	1
5	3
7	6
10	8

现在对辅助索引b查询 select * from t where b = 3
索引b区间划分 （1，3）（3，6）（6，8）

1. 为辅助索引加next-key locking，锁住(1,3]区间，此外锁住辅助索引下一个键值的gap即(3,6)区间
2. 主键索引5，加record lock
3. 此时加锁情况为：主键a:[5]，索引b:(1,6)

InnoDB锁住下一个键值的gap来解决幻读问题

InnoDB中使用锁住下一个索引键值的gap，阻塞该范围内的insert操作，所有对本次select语句将造成insert影响的操作，都会被阻塞，由此防止其他事务将记录插入到select影响范围中，引发幻读问题。

这一点上不同于其他数据库，像Oracle等数据库可能需要可串行化的事务隔离级别才能解决幻读问题，但是InnoDB引擎可以在可重复读隔离级别下通过gap lock解决问题。

阻塞

有些时刻一个事务的锁需要等待另一个事务中锁释放，即为阻塞。

innodb通过设置 innodb_lock_wait_timeout来控制等待时间
并通过设置innodb_rollback_on_timeout来设置是否等待超时对事务进行回滚，默认不回滚，超过等待时间则抛出异常，由用户判断是该rollback还是commit。

死锁

死锁是指两个及两个以上事务在执行过程中因争夺资源而造成一种互相等待的状态。

如果没有外力作用，事务将无法推进。

解决死锁

超时回滚

解决死锁的一个方法是设置等待时间，当两个事务相互等待时，其中一个事务超时进行回滚，另外一个事务就可以继续进行。

这种方式虽然简单，但是回滚哪一个事务一般是根据FIFO的机制，但若是此时超时的事务权重较大，回滚这个事务则增加较多的开销。

死锁检测

采用等待图的方式进行死锁检测,通过深度优先的算法实现，如果图中有环则说明存在死锁。

死锁概率

P = \frac{n^2 + r ^ 4}{4R^2 }

n为事务个数，r为每一个事务的操作数r，R为总共的资源R行数据

说明：

1. 事务数量越多，发生死锁概率越大
2. 一个事务越复杂，操作数越多，发生死锁概率越大
3. 公有资源越少，发生死锁概率越大

InnoDB中不存在锁升级

很多数据库如：SQL server就有锁升级的想象，但是innodb并没有锁升级。
这是因为innodb根据事务访问的每个页对锁进行管理，采用位图方式，因此不管一个事务锁住页中的一行还是多个记录，其开销通常都是一样的。