MyISAM与InnoDB关于锁方面的区别

MyISAM与InnoDB关于锁方面的区别：

• MyISAM默认使用的是表级锁，不支持行级锁
• InnoDB默认用的是行级锁，也支持表级锁
• InnoDB支持事务，在事务中被加锁的数据行需要 等事务commit之后才会统一解锁，否则不会解锁。而MyISAM不支持事务，所以不会有这个问题
• MyISAM和InnoDB都支持共享锁和排他锁，读锁共享，写锁排他
• InnoDB在开启事务时，若select语句不走索引的情况会锁住整张表，也就是说InnoDB在SQL没有利用到索引的时候使用的是表级锁，而SQL用到索引的时候则是使用行级锁和gap锁，gap锁是走普通非唯一索引时用到的
• InnoDB除了支持行级锁之外，还支持表级的意向锁，意向锁分为共享读锁（IS）和排他写锁（IX）

注：

实际上在不走索引的时候，InnoDB的实现方式和MyIsam的表锁方式不同，单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁（排他锁），此时想改变树型结构即索引结构的话，是会被锁住的，这个类似于表锁，但原理上和表锁是完全不同的

MyISAM适合的场景：

• 频繁执行全表count语句
• 对数据进行增删改的频率不高，而查询非常频繁的场景
• 没有事务场景

InnoDB适合的场景：

• 数据进行增删改查都相当频繁的系统
• 可靠性要求比较高，需要事务特性的系统

数据库锁的分类：

• 按锁的粒度划分，可分为表级锁、行级锁、页级锁
• 按锁级别划分，可分为共享锁、排他锁
• 按加锁方式划分，可分为自动锁、显式锁
• 按操作划分，可分为DML锁、DDL锁
• 按使用方式划分，可分为乐观锁、悲观锁；悲观锁通常需要利用数据库提供的锁机制来实现；而乐观锁通常用版本号或时间戳来实现

总结：

MyISAM默认使用的是表级锁，不支持行级锁。InnoDB默认用的是行级锁，也支持表级锁。无论是表级锁还是行级锁，均分为共享锁和排他锁，它们的关系如下表所示（X：排他锁，S：共享锁）：

事务隔离级别以及各级别下的并发访问问题以及事务隔离机制

事务并发访问引起的问题以及如何避免：

1.更新丢失：

即一个事务的更新覆盖了另一个事务的更新；由于现在主流数据库都会自动加锁来避免更新丢失的情况，所以在数据库层面通常不会发生这个问题。例如mysql所有事务隔离级别在数据库层面上均可避免更新丢失

下图模拟了更新丢失的过程：

2.脏读（Dirty read）：

即一个事务读到另一个事务的未提交数据；该问题在READ-COMMITTED（读已提交）以上的事务隔离级别可避免

3.不可重复读（Non-repeatable read）：

即事务A多次读取同一数据，但事务B在事务A多次读取的过程中对该数据做了更新操作并提交，导致事务A多次读取同一数据时结果不一致；该问题在REPEATABLE-READ（可重复读）以上的事务隔离级别可避免，这也是MySQL的默认隔离级别

4.幻读（Phantom read）：

事务A读取以搜索条件相匹配的若干行数据，而事务B则对事务A查询匹配的数据进行了插入或删除操作，导致事务A多次读取的结果集行数不一致；该问题在SERIALIZABLE（串行化）以上的事务隔离级别可避免，需要注意的是：在MySQL数据库中，REPEATABLE-READ事务隔离级别下也可以避免幻读

总结：

当前读和快照读

表象：快照读（非阻塞读）-- 伪MVCC（多版本并发控制）
内在：next-key锁（行级锁+gap锁）

首先我们需要知道两个概念：当前读和快照读；当前读其实就是加了锁的增删改查语句，例：

• select ... lock in share mode；select ... for update
• update，delete，insert（自动加锁）

之所以叫当前读，是因为读取的是当前记录的最新版本，而RR事务隔离级别下在读取数据之后还需要保证其他事务不能修改当前记录，那么就会对读取的记录加next-key锁，所以RR事务隔离级别下的当前读可以避免发生幻读现象：

快照读则是不加锁的非阻塞读，例如不加锁的普通select操作。但需要注意的是在串行化的事务隔离级别下，任何的增删改查操作都会被加锁。

在mysql中，读已提交隔离级别下，快照读和当前读都是读到同样的数据。而在可重复读隔离级别下，快照读读到的是开启事务时第一条select语句读到的快照版本数据，当前读则是会读到当前数据库中最新的数据。

RC、RR级别下的InnoDB的快照读（非阻塞读）是如何实现的：

• 一是依靠数据行里的隐藏字段：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段
  • DB_TRX_ID：最后修改本行数据的事务id
  • DB_ROLL_PTR：回滚指针，指向undo日志的历史版本数据
  • DB_ROW_ID：行号，即密集索引维护的自增id
• 二是undo日志，当我们对数据进行变更操作时就会产生undo日志，undo日志中存储的是历史数据，当一个旧事务需要读取数据时，会顺着undo链找到满足其可见性的数据；undo日志还分为insert undo日志和update undo日志
  • insert undo日志：记录insert操作产生的undo日志，该日志记录只在事务回滚时需要，而在事务提交后会立即丢弃
  • update undo日志：记录update或delete操作产生的undo日志，该日志记录不仅在事务回滚时需要，快照读也需要，所以不会马上被删除，只有当数据库所使用的快照中不涉及该日志记录才会被删除
• 三是read view，它主要用来做可见性判断的，即当我们去执行快照读时，会针对我们查询的数据创建一个read view，以此来决定该事务能看到的是哪个版本的数据。read view的创建时机是开启事务后执行的第一条select语句
  • read view遵循一个可见性算法，该算法会先取出将要变更数据行的DB_TRX_ID，与系统其他活跃的事务id做对比，如果大于等于这些活跃的事务id就会通过DB_ROLL_PTR去undo日志里取出DB_TRX_ID小于当前活跃事务id的历史数据

事务对行的更新过程：

RR事务隔离级别下是如何避免幻读的

在之前的小节中，我们了解到在MySQL的RR事务隔离级别下，是可以避免幻读的。但并不意味着快照读是避免发生幻读现象的根本，因为快照读只是读的发生变化前的历史数据。实际在RR及SERIALIZABLE事务隔离级别下真正防止幻读发生的原因是事务对数据加上了next-key锁，而next-key锁由行锁和gap锁两部分组成。行锁就不多说了，gap锁才是重点，所谓gap就是索引树中插入新记录的间隙，而gap锁是用于锁定一个间隙范围但不包括记录本身，gap锁的目的是为了防止同一事务的两次当前读而导致出现幻读的情况。

gap锁只在RR和SERIALIZABLE事务隔离级别中存在，其他的隔离级别是没有的，所以RC和RU是无法避免幻读的。这里我们主要讨论RR事务隔离级别下gap锁出现的场景：

• 增删改查及当前读若用到主键索引或唯一索引会对其加gap锁吗？
  • 答：视情况而定，如果where条件全部命中，则不会用gap锁，只会加行锁；而where条件部分命中或者全不命中，则会加gap锁；所以gap锁会用在非唯一索引或者不走索引的当前读中

where条件全部命中，只会加行锁：

走非唯一索引时会对该索引间隙加gap锁：

不走索引则会对表里所有的间隙加gap锁，其效果就类似于表级锁了，但是其代价比表级锁更大：

总结：

无论是当前读还是快照读，在innodb的RR的事务隔离级别下都可以避免幻读。在快照读的情况下，innodb通过mvcc来避免幻读；在当前读的情况下，innodb通过next-key锁来避免幻读。