阿里面试宝典（九）：MyISAM 和 InnoDB、并发事务、锁

问题导读：
1、MyISAM 和 InnoDB有什么区别？
2、并发事务会带来什么问题？
3、事务隔离级别及锁的实现机制是怎样的？

上一篇：阿里面试宝典（八）：数据库范式、开发规范和索引


四、MyISAM vs InnoDB

Mysql 数据库中，最常用的两种引擎是 innordb 和 myisam。InnoDB 是 Mysql 的默认存储引擎。

1.事务处理上方面：

MyISAM 强调的是性能，查询的速度比 InnoDB 类型更快，但是不提供事务支持。InnoDB 提供事务支持事务。

2.外键：MyISAM 不支持外键，InnoDB 支持外键。

3.锁方面的介绍：

MyISAM 只支持表级锁，InnoDB 支持行级锁和表级锁，默认是行级锁，行锁大幅度提高了多用户并发操作的性能。innodb 比较适合于插入和更新操作比较多的情况，而 myisam 则适合用于频繁查询的情况。另外，InnoDB表的行锁也不是绝对的，如果在执行一个 SQL 语句时，MySQL 不能确定要扫描的范围，InnoDB 表同样会锁全表，例如 update table set num=1 where name like “%aaa%”。

4.全文索引：

MyISAM 支持全文索引， InnoDB 不支持全文索引。innodb 从 mysql5.6 版本开始提供对全文索引的支持。

5.表主键的区别：

MyISAM：允许没有主键的表存在。

InnoDB：如果没有设定主键，就会自动生成一个 6 字节的主键(用户不可见)。

6.表的具体行数问题：

MyISAM：select count() from table,MyISAM 只要简单的读出保存好的行数。因为MyISAM 内置了一个计数器，count()时它直接从计数器中读。

InnoDB：不保存表的具体行数，也就是说，执行 select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行。

一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 Mysql 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15 ？

如果表的类型是 MyISAM， 那么是 18。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里， 重启MySQL自增主键的最大 ID 也不会丢失。

如果表的类型是 InnoDB， 那么是 15。InnoDB 表只是把自增主键的最大 ID 记录到内存中， 所以重启数据库会导致最大 ID 丢失。

五、并发事务带来的问题

丢失更新

如果两个事务都要更新数据库一个字段X，x=100



两个不同事物同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的
更新给覆盖掉，这种情况事务A的更新就被覆盖掉了、丢失了。

脏读（未提交读）

防止一个事务读到另一个事务还没有提交的记录。 如：



事务读取了未提交的数据，事务B的回滚，导致了事务A的数据不一致，导致了事务A的脏读 ！

不可重复读

一个事务在自己没有更新数据库数据的情况，同一个查询操作执行两次或多次的结果应该是一致的；如果不一致，
就说明为不可重复读。 还是用上面的例子



这种情况事务A多次读取x的结果出现了不一致，即为不可重复读 。

幻读（Phantom Read）

事务A读的时候读出了15条记录，事务B在事务A执行的过程中 增加 了1条，事务A再读的时候就变成了 16 条，这种情况就叫做幻影读。 不可重复读说明了做数据库读操作的时候可能会出现的问题。


六、事务隔离级别及锁的实现机制

排他锁 写锁 被加锁的对象只能被持有锁的事务读取和修改，其他事务无法在该对象上加其他锁，也不能读取和修改该对象

共享锁 **读锁** 被加锁的对象可以被持锁事务读取，但是不能被修改，其他事务也可以在上面再加共享锁。

特别的，对共享锁： 如果两个事务对同一个资源上了共享锁，事务A 想更新该数据，那么它必须等待 事务B 释放其共享锁。

在运用 排他锁 和 共享锁 对数据对象加锁时，还需要约定一些规则，例如何时申请 排他锁 或 共享锁、持锁时间、何时释放等。称这些规则为封锁协议（Locking Protocol）。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。

一级封锁协议 （对应 read uncommited）

一级封锁协议是：事务 在对需要修改的数据上面（就是在发生修改的瞬间） 对其加共享锁（其他事务不能更改，
但是可以读取-导致“脏读”），直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。

一级封锁协议不能避免 丢失更新，脏读，不可重复读，幻读！

二级封锁协议 （对应read commited)

二级封锁协议是：

1）事务 在对需要更新的数据 上（就是发生更新的瞬间） 加 排他锁 （直到事务结束），防止其他事务读取未提交的数据，这样，也就避免了 “脏读” 的情况。

2）事务 对当前被读取的数据 上面加共享锁（当读到时加上共享锁），一旦读完该行，立即 释放该该行的共享锁 -

上面只能防止不读脏数据

2）事务 对当前被读取的数据 上面加共享锁（当读到时加上共享锁），直到事务结束才释放

可以防止不可重复读

从数据库的底层实现更深入的来理解，既是，数据库会对游标当前的数据上加共享锁 ， 但是当游标离开当前行的时候，立即释放该行的共享锁。

二级封锁协议除防止了“脏读”数据，但是不能避免 丢失更新，不可重复读，幻读 。

但在二级封锁协议中，由于读完数据后立即 释放共享锁，所以它不能避免可重复读 ，同时它也不能避免 丢失更新，如果事务A、B同时获取资源X，然后事务A先发起更新记录X，那么 事务B 将等待事务 A 执行完成，然后获得记录X 的排他锁，进行更改。这样事务 A 的更新将会被丢失。 具体情况如下：




由此可以看到，事务A的提交被事务B覆盖了，所以不能防止 丢失更新。

如果要避免 丢失更新，我们需要额外的操作， 对凡是读到的数据加 共享锁 和排他锁 ，这个往往需要程序员自己编程实现，比如在Oracle 中，需要加 SELECT FOR UPDATE 语句，表明，凡是该事务读到的数据，额外的加上排他锁，防止其他数据同一时间获取相同数据，这样就防止了 丢失更新 ！

三级封锁协议 （对应reapetable read ）

三级封锁协议是：二级封锁协议加上事务 在读取数据的瞬间 必须先对其加 共享锁 ，但是 直到事务结束才释放 ，这样保证了可重复读（既是其他的事务职能读取该数据，但是不能更新该数据）。

三级封锁协议除防止了“脏”数据 和不可重复读 。但是这种情况不能避免 幻读 和 丢失更新 的情况，在事务 A 没有完成之前，事务 B 可以新增数据，那么 当事务 A 再次读取的时候，事务B 新增的数据会被读取到，这样，在该封锁协议下，幻读 就产生了。 如果事务A 和 事务B 同时读取了资源X=100，同样，如果事务A先对X进行 更新X=X+100，等待事务A执行完成X=200，那么事务B 获得X的排他锁，进行更新 X=X+200，然后提交 X=300，同样A的更新被B所覆盖！( 如果要避免 丢失更新，我们需要额外的操作， 对凡是读到的数据加 共享锁 和排他锁 ，这个往往需要程序员自己编程实现，比如在Oracle中，需要加 SELECT FOR UPDATE 语句，表明，凡是读到的数据，我会加 排他锁，防止其他数据同一时间获取相同数据) ！

进阶：repeatable read 导致死锁的情况（即便是 不同的资源在相同的顺序下获取）。 比如 事务1 读取 A，同时事务2 也读取 A，那么事务1和事务2 同时对 A 上了共享锁，然后事务1 要UPDATE A，而此时 事务2 也要 UPDATE A，这个时候 事务1 等待 事务2 释放其在 A 上的共享锁，然后 事务2 要等待 事务1 释放其在 A 上的共享锁，这
样，事务1 和 事务2 相互等待，产生死锁！（SQL Server/DB2 里面有 UPDATE LOCK 可以解决这种情况，具体的

思路是，在 repeatable read 的情况下，将读取的数据 上的 UPDATE 锁，介于 共享锁 和 排他锁之间的一种锁，该锁的作用是 当出现上面这种情况后，事务1 和 事务2 对 A 上的是 UPDATE 锁，那么谁先 要修改 A，那么该事务就会将 UPDATE 锁可以顺利升级为 排他锁对该数据进行修改！）

最强封锁协议（对应Serialization）

四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对 事务中 所 读取 或者 更改的数据所在的表加表锁，也就是说，其他事务不能 读写 该表中的任何数据。这样所有的 脏读，不可重复读，幻读 ，都得以避免！

七、MVCC（多版本并发控制）

mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能

MVCC只在REPEATABLE READ和READ COMMITED两个隔离级别下工作，其它两个隔离级别下不存在MVCC什么是多版本并发控制呢 ？其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。
1、在插入操作时 ： 记录的创建版本号就是事务版本号。

比如我插入一条记录, 事务id 假设是1 ，那么记录如下：也就是说，创建版本号就是事务版本号。



2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。

比如，针对上面那行记录，事务Id为2 要把name字段更新

update table set name= 'new_value' where id=1;



3、删除操作的时候，就把事务版本号作为删除版本号。比如

delete from table where id=1;



4、查询操作：

从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1）InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，只么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

2）行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

这样就保证了各个事务互不影响。从这里也可以体会到一种提高系统性能的思路，就是：

通过版本号来减少锁的争用。

另外，只有read-committed和 repeatable-read 两种事务隔离级别才能使用mVcc read-uncommited由于是读到未提交的，所以不存在版本的问题
而serializable 则会对所有读取的行加锁。

八、间隙锁与幻读

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

Select * from emp where empid > 100 for update;
复制代码

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

RR级别下防止幻读

快照读：使用MVCC防止幻读
当前读：使用间隙所防止幻读

最新经典文章，欢迎关注公众号

学习学习