（三）数据库系统-锁模块

（一）MyISAM与InnoDB的区别：

• MyISAM是MySQL的默认数据库引擎，由早期的ISAM所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）
• InnoDB，是MySQL的数据库引擎之一，现为MySQL的默认存储引擎，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能；

主要区别:

1.锁方面：

• MyISAM默认表级锁，不支持行级锁
• InnoDB默认行级锁，也可以支持行级锁

2.构成方面：

• 每个MyISAM在磁盘上存储成三个文件，每个文件都以数据表名称为文件主名，并搭配不同扩展名区分文件类型：
  1.frm文件存储表定义。
  2.数据文件的扩展名为.MYD (MYData)。
  3.索引文件的扩展名是.MYI (MYIndex)。
• InnoDB将数据划分为若干页，以页作为磁盘与内存交互的基本单位，一般页的大小为16KB,基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB

3.事务处理方面：
InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能,MyISAM不支持，其强调性能

4.表的具体行数：
select count() from table,MyISAM只要简单的读出保存好的行数，InnoDB要扫描一遍整个表来计算有多少行，注意的是，当count()语句包含 where条件时，两种表的操作是一样的

5.对AUTO_INCREMENT的操作：
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引，有 更好和更快的auto_increment处理

6.SELECT UPDATE,INSERT，Delete操作：

• 如果执行大量的SELECT，MyISAM是更好的选择
• 1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表
  2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
  3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用

（二）数据库事务的四大特性：

ACID

• 原子性（Atomic）：事物包含的操作要么全部执行，要么全部回滚，
• 一致性（Consistency）：事务应确保数据库的状态从一个一致的状态转移到另一个一致的状态，数据库中元素满足完整性
• 隔离性（Isolation）：多个事务并发执行时不会相互影响
• 持久性（Durability）：一个事务一旦提交，它对于数据库的修改应该永久保存再数据库中；当系统或介质发生故障时，确保已提交事务的更新不会丢失，即对于已提交事务的更新可以恢复

（三）隔离性分析：

select @@tx_isolation;    //查询事务隔离级别
set session transaction isolation level read uncommitted;   //修改事务隔离级别

利用锁机制创建出不同的隔离级别

• .事务并发引起的问题：

  • 1.更新丢失（Lost Update）：一个事物的更新覆盖了另一个事务的更新：如图，两个事务同时对一个账户进行操作：
    

  解决方案：1.不使用事务+锁定读，即for update 2.不使用事务，用CAS自旋操作

  • mysql所有事务隔离级别在数据库层面上均可避免更新丢失

  • 2.脏读（Dirty reads）： 一个事务正在对一条记录进行修改，这个事务完成并提交前，这条数据就处于不一致的状态，同时，另一个事务也读取同一条记录，造成第二个事务读“脏”了这些数据

  解决方法：READ COMMITTDE(读已提交）事物隔离级别以上可以避免

  • 3.不可重复读（Non-Reapeated reads)：一个事务在读取某些数据后某个时间，再次读取以前读过的数据，发现读出的数据已经变化或某些记录已经被删除，重复读的是不同的数据

  解决方法：REPEATABLE-READ（可重复读）事物隔离级别以上可以避免

  • 4幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入满足其查询条件的新数据；即A事务读取到了B事务提交的新数据，不符合隔离性；

  解决方法：SERILIZABLE（串行化）事物隔离级别可避免

  • 事务隔离级别:

    • 1.READ UNCOMMITTED（读未提交）：可读取其它事务未提交的数据
    • 2.READ COMMITTED（读已提交）：只能读取到别的事务已提交的数据
    • 3.REPEATED READ（可重复读）：事务可以重复读取数据
    • 4.SERILIZABLE（串行化）：会锁表
      

MVCC：

• ANSI SQL STANDARD 定义了4类隔离级别，随着隔级别的提升，并发事务产生数据不一致性的问题就会大大越低，但是并发处理能力也会大大降低，而不同的隔离级别，往往都是通过锁机制来解决并发事务产生的各种问题。

• 数据的锁定分为两种方法，一种叫悲观锁，另外一种叫做乐观锁。绝大部分商业数据库（ MySQL 、 Oracle 等）为了性能考虑，都是使用了以乐观锁为理论基础的 MVCC 即Multi - Version Concurrency Control 多版本并发控制来解决并发事务帯来的数据访问问题。

• 悲观锁
  数据对外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，
  在整个的数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销（特别是长事务）。
• 乐观锁：
  大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据増加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表増加个 version 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则训为是过期数据。 MVCC 的实现没有固定的规范，每个数据库都会有不同的实现方式。
• MVCC 在 MySQL 的 InnoDB 实现
  在 InnoDB 中，会在每行数据后添加两个额外的隐藏的值来实现 MVCC ，这两个值一个记录这行数据何时被创建，另外个记录这行数据何时过期（或者被删除）。在实际操作中存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。在可重读 Repeatable Reads 事务隔离级别下：
  • SELECT 时，读取创建版本号く=当前事务版本号，删除版本号为空或＞当前事务版本号。
  • INSERT 时，保存当前事务版本号为行的创建版本号
  • DELETE 时，保存当前事务版本号为行的删除版本号
  • UPDATE 时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

通过 MVCC ，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。

InnoDB在RR(可重复读隔离)级别下如何避免幻读：

表象：快照读（非阻塞读）—伪MVCC
内在：next-key锁，由 行锁+gap锁组成；

1、表象：快照读(非阻塞读)——伪MVCC

• 当前读：加锁的增删改查语句，无论什么锁，因为读取的是当前最新版本，还要保证并发事务不能修改当前记录，对读取记录加锁。
• 快照读：可能读取到的数据不是最新版本而是历史版本。

select   tablename   lock  in share mode     select * from tablename for update //加共享锁,排它锁
update  delete  insert  //加排它锁

RDBMS主要是由两大部分组成的：MySQL程序和存储引擎InnoDB，

• update和delete都会通过where进行不断的遍历条件，每遍历一个条件查询到的数据都会加锁，直到所有条件都被遍历完然后返回。而insert会触发唯一键的查询，同样也会触发当前读。。

• 快照读：就是简单的指不加锁的非阻塞的select操作。但是不加锁的前提就是事务隔离级别不为Serializable的条件下才成立的，在Serializable事务隔离级别下因为是串行化查询，因此会退化成当前读即select … lock in share mode。。

• 快照读简单来说是为了在多线程情况下提升并发性能，快照读是基于多版本并发控制，认为MVCC是行级锁的一个变化，在很多情况下避免了加锁操作开销自然会降低，但是缺点在于可能获取的数据不是最新的数据。

2.内在：next-key(行锁和Gap锁)：

• 行锁：行锁就是对行上锁。

• Gap锁：指一段距离将插入的索引占用的空隙用锁包住，Gap锁目的就是防止事务因为两次当前读出现幻读的情况，但是READ-COMMITTED及以下的级别都没有Gap锁因此无法避免幻读

REPEATABLE-READ级别下，无论是删改如果要使用到主键索引或者唯一索引那么问题是还用到Gap锁吗

• 这个要根据情况而定，where条件全部命中就不需要使用Gap锁否则就需要使用Gap锁

RC，RR级别下的InnoDB的非阻塞如何实现：

• 1.数据行里的DB_TEX_ID;DB_ROLL_PTR;DB_ROW_ID
• 2.undo日志
• 3.read view：做可见性判断

实际上在InnoDB存储数据的时候，还会额外存储三个不显示出来的字段：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID：
DB_TRX_ID----该字段标明最近一次对数据做修改
DB_ROLL_PTR----回滚指针，写入回滚段的undo日志
DB__ROW_ID ----行号 随着新行出现单调递增的id

undo日志逻辑日志，记录时间点为修改缓冲中页面之前；主要分为insert undo日志（事务回滚涉及）和update undo日志（事务回滚和快照读都涉及）
对事务变更就会产生undo记录，存储的是老版数据，事务回滚需要,

read view —可见性判断来决定当前看的是哪个版本数据

Sql一些语法分析：

group by和having：

• group by：1.满足SELECT子句中的列名必须分为列或列函数；2.列函数低于group by子句定义的每一组各返回一个结果。
• having：1.通常与group by子句一起使用；2.WHERE过滤行，HAVING过滤组；3.出现在同一个SQL的顺序：WHERE>GROUP BY>HAVING