初识innoDB

InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况 , 所以innoDB适用于大数据量 , 高并发的互联网业务.

行锁，细粒度，在数据量大，并发量高时，性能比较优异

事务，提供了commit，rollback，崩溃修复等能力，对数据一致性帮助很大

 

innoDB的隔离级别

ACID 事务管理

在讲innoDB的隔离级别前我们需要先了解下什么是隔离 , 隔离就是ACID中的I , 简单说ACID就是事务拥有的特性.

原子性（Atomicity

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）

事务前后数据的完整性必须保持一致。

隔离性（Isolation

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

隔离级别

innoDB的隔离级别分为4级 , 默认的是可重复读

隔离级别(隔离程度由低到高)	概述	问题
未提交读（READUNCOMMITTED）	当前事务可以读取其他事务未提交的数据	脏读 , 不可重复读 , 幻读 , 丢失更新
提交读（READCOMMITTED）	当前事务读取到的数据一定是其他事务已提交的数据	不可重复读 , 幻读 , 丢失更新
可重复读（REPEATABLEREAD）	在同一个事务里，SELECT的结果是事务开始时时间点的状态	幻读(innoDB通过MVVC已经解决了幻读 , 但是其实还是可以进行update和delete操作的) , 丢失更新
串行化（SERIALIZABLE）	读操作会隐式获取共享锁，可以保证不同事务间的互斥	解决所有事务相关问题 , 但是由于事务串行执行, 资源消耗大

什么是脏读,不可重复读,幻读

锁问题	锁问题描述	会出现锁问题的隔离级别	解决办法
脏读	一个事务中会读到其他并发事务未提交的数据，违反了事务的隔离性；	Read Uncommitted	提高事务隔离级别至Read Committed及以上；
不可重复读	一个事务会读到其他并发事务已提交的数据，违反了数据库的一致性要求；可能出现的问题为幻读.(与幻读的区别在于, 这个更加强调的是数据的一致性 , 比如某条数据的列值是否被更改)	Read Uncommitted、Read Committed	默认的RR隔离级别下 ，解决办法分为两种情况：1、当前读：Next-Key Lock机制对相关索引记录及索引间隙加锁，防止并发事务修改数据或插入新数据到间隙；2、版本读：MVVC，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见；提高事务隔离级别至Serializable；
幻读	幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能返回之前不存在的行记录；(幻读相对于不可重复读的区别在于幻读的关注点在于insert新插入数据的影响)	Read Uncommitted、Read Committed、Repeatable Read	innoDB原生实现中采用了MVVC解决了幻读问题, 但准确的说是解决了部分 , 下面的模块会进行简单的介绍 .
丢失更新	一个事务的更新被另一个事务覆盖	Read Uncommitted、Read Committed、Repeatable Read	默认的RR隔离级别下 ，解决办法分为两种情况：1、乐观锁：数据表增加version字段，读取数据时记录原始version，更新数据时，比对version是否为原始version，如不等，则证明有并发事务已更新过此行数据，则可回滚事务后重试直至无并发竞争；2、悲观锁：读加排他锁，保证整个事务执行过程中，其他并发事务无法读取相关记录，直至当前事务提交或回滚释放锁；

 

举个栗子

事务一	事务二
begin;	begin;
select * from test_lock where id=5;
	update test_lock set code=551 where id=5;
select * from test_lock where id=5; (脏读）
	commit;
select * from test_lock where id=5; （不可重复读）
commit;

事务一	事务二
begin;	begin;
select * from test_lock where id>3;
	insert test_lock (code,descr) values(4,44);
	commit;
select * from test_lock where id>3;  （幻读）
commit;

 

丢失更新

事务一	事务二
begin;
	begin;
select * from test_lock where id=5;
	select * from test_lock where id=5;
update test_lock set num=6 where id=5;
	update test_lock set num=7 where id=5;
commit;
	commit;

InnoDB RR下的实现与幻读的解决

innoDB默认的隔离级别就是可重复读 ,  常规RR级别下时候是有幻读问题的 , InnoDB通过MVVC实现了可重复读并解决了幻读问题(部分解决)

MVVC概述

MVCC全称Mutli Version Concurreny Control,多版本并发控制，也可称之为一致性非锁定读；它通过行的多版本控制方式来读取当前执行时间数据库中的行数据。实质上使用的是快照数据，这样就可以实现不加锁读。MVCC 主要应用于 Read Commited  和 Repeatable read  两个事务隔离级别 , mvcc是一种能够进一步提高并发的方法  , 本文仅对其进行简要介绍.

MVVC核心原理

（1）写任务发生时，将数据克隆一份，以版本号区分；

（2）写任务操作新克隆的数据，直至提交；

（3）并发读任务可以继续读取旧版本的数据，不至于阻塞通过mvvc机制innoDB实现了可重复读 , 并解决了由insert带来的幻读问题 , 但是innoDB是如何确定快照版本的呢 ?

ReadView确定快照版本

在innodb中，创建一个新事务的时候 , innodb会将当前系统中的活跃事务列表创建一个副本（read view），副本中保存的是系统当前不应该被本事务看到的其他事务id列表 . 当用户在这个事务中要读取该行记录的时候，innodb会将该行当前的版本号与该read view进行比较.

ReadView生成时机:

RR下，事务在第一个Read操作时，会建立Read View

RC下，事务在每次Read操作时，都会建立Read View

确定快照的算法思路:

当数据(每行为一单位)发生变动时 , 数据版本会更新操作的事务id

假设当前事务的id 为 x ReadView中最小的事务id为Min 最大的事务id为Max 当前行的事务id为now

Now < Min 说明当前行对应的事务完成时间是小于当前事务开始时间的 , 此行数据可见

Now > Max 说明当前行对应事务完成时间是晚于当前事务开始时间的 , 数据不可见 , 寻找上一版本数据

Min<Now<Max 若Now在ReadView中则不可见 , 若不在则可见.

对于快照读这种策略 , 在update 或 insert 等操作情况下 , 则需要读取最新的数据 , 我们把修改数据前隐式的读取最新数据叫做当前读 , 那当前读的可重复读和幻读问题如何解决呢?

当前读的可重复读与幻读问题

通过行锁 + 间隙锁构成nextKey lock  , 保证修改插入数据的唯一性.

innoDB行锁的实现

InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁

只有执行计划真正使用了索引，才能使用行锁

行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的

行锁算法

锁在SQL上的应用

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务；

悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁；

InnoDB支持多种锁粒度，默认使用行锁，锁粒度最小，锁冲突发生的概率最低，支持的并发度也最高，但系统消耗成本也相对较高；

共享锁与排他锁是InnoDB实现的两种标准的行锁；

InnoDB有三种锁算法——记录锁、gap间隙锁、还有结合了记录锁与间隙锁的next-key锁，InnoDB对于行的查询加锁是使用的是next-key locking这种算法，一定程度上解决了幻读问题；

意向锁是为了支持多种粒度锁同时存在；

普通select

在读未提交(Read Uncommitted)，读提交(Read Committed, RC)，可重复读(Repeated Read, RR)这三种事务隔离级别下，普通select使用快照读(snpashot read)，不加锁，并发非常高；

在串行化(Serializable)这种事务的隔离级别下，普通select会升级为select ... in share mode;

加锁Select

如果，在唯一索引(unique index)上使用唯一的查询条件(unique search condition)，会使用记录锁(record lock)，而不会*记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)；

其他的查询条件和索引条件，InnoDB会*被扫描的索引范围，并使用间隙锁与临键锁，避免索引范围区间插入记录；

UPDATE_DELETE

和加锁select类似，如果在唯一索引上使用唯一的查询条件来update/delete，例如：update t set name=xxx where id=1;也只加记录锁；

否则，符合查询条件的索引记录之前，都会加排他临键锁(exclusive next-key lock)，来*索引记录与之前的区间；

尤其需要特殊说明的是，如果update的是聚集索引(clustered index)记录，则对应的普通索引(secondary index)记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。隐式锁是在索引中对二级索引的记录逻辑加锁，实际上不产生锁对象，不占用内存空间。

INSERT操作

用排它锁*被插入的索引记录，而不会*记录之前的范围。

同时，会在插入区间加插入意向锁(insert intention lock)，但这个并不会真正*区间，也不会阻止相同区间的不同KEY插入。

自增锁(AUTO-INC Locks)是事务插入时自增列上特殊的表级别的锁。索引末尾设置独占锁。最简单的一种情况：在访问自增计数器时，InnoDB使用自增锁，但是锁定仅仅持续到当前SQL语句的末尾，而不是整个事务的结束

插入意向锁

假设数据表中存在(1,1)、(5,5)和(10,10)三条记录。

事务开启，尝试获取插入意向锁。例如，事务一执行了select * from test where id>8 for update，事务二要插入(9,9)，此时先要获取插入意向锁，由于事务一已经在对应的记录和间隙上加了X锁，因此事务二被阻塞，并且阻塞的原因是获取插入意向锁时被事务一的X锁阻塞。

获取意向锁之后，插入之前进行重复索引检查。重复索引检查为当前读，需要添加S锁。

如果是已经存在唯一索引，且索引未加锁。直接抛出Duplicate key的错误。如果存在唯一索引，且索引加锁，等待锁释放。

重复检查通过之后，加入X锁，插入记录

事务一	事务二
select * from test where id>8 for update
	insert into test (9,9) 执行之后获取意向锁被阻塞，等待事务一
commit;
	获取意向锁，当前读S锁重复检查OK，加X锁，insert Query OK, 1 row affected

死锁

举个栗子

事务一	事务二	事务三
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); Query OK, 1 row affected (0.01 sec)
	mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); 执行之后被阻塞，等待事务一
		mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test values (2,2); 执行之后被阻塞，等待事务一
mysql>rollback; Query OK, 0 rows affected (0.00 sec)
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
		Query OK, 1 row affected (16.13 sec)

 

事务一	事务二
begin;	begin;
select * from t where code = 6 for update;
	select * from t where  = 8 for update;
insert into t values (4,5);
	insert into t values (4,5);
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Query OK, 1 row affected (5.45 sec)

死锁产生

死锁是指两个或多个事务在同一资源上相互占用（间隙锁、S锁），并请求锁定对方占用的资源，从而导致恶性循环。

当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁

数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复

死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能

死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

InnoDB避免死锁

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。

• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁

• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会

• 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。

• 改变事务隔离级别

• 程序设计中总是捕获并处理死锁异常

• 如果出现死锁，可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

一些优化锁性能的建议

• 尽量使用较低的隔离级别

• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会

• 选择合理的事务大小，小事务发生锁冲突的几率也更小

• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁

• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会

• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响

• 不要申请超过实际需要的锁级别

• 除非必须，查询时不要显示加锁。MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作

• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能