mysql数据库中的引擎，锁，事务控制以及MVCC

锁

锁是计算机中对于多进程，多线程并发访问同一个资源的控制机制。在mysql中，同样存在并发情况下对数据的访问，因此锁的存在就显得很有必要，其中锁的级别可以分为以下三种：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

锁的类型可以分为以下两种:

    共享锁(share lock):也叫读锁，S锁，一般是在读取数据时加这个锁，添加上这个锁的数据可以重入其它的共享锁，但不能被添加独占锁。    独占锁(exclusive lock):也叫写锁，X锁，排它锁，一般是在写入数据时加这个锁，添加上这个锁的数据不可重入其它任何的锁。

锁的级别和类型是组合使用的，既可以组合出：表共享锁，表独占锁，行共享锁，行独占锁，页共享锁，页独占锁等6种模式
在mysql中，不同的数据库引擎使用的锁的等级是不同的。myisam使用的是表锁，innodb使用的是行锁，至于页面锁则是更早期的dbd使用的锁级别，但这个已经被innodb所取代了。另外需要注意的是,myisam是不支持事务的，目前只有innodb是支持事务的。

重点：需要注意的是，不要被锁的名字所误导，因此认为读锁就肯定是在读的时候上锁，写锁就肯定是在写的时候上锁。在myisam中情况确实是这样，但是在目前应用更加广泛的innodb中，进行insert,update,delete的时候，的确是会加上写锁。但是普通的select操作是不会加上读锁的，读锁通常需要显式开启，只有在innodb的 Serializable 事务级别中，mysql才会默认为你加上读锁。除了读锁这个名字坑外，甚至行锁也会引人误会，因此我更愿意把行锁称之为索引锁，这个具体后面再讲。

MyISAM

在myisam中是没有事务概念的。由于它使用的是表级锁，而且在默认隐式加锁的情况下，它总是一次把所有需要的锁获取到，因此在默认情况下彻底杜绝了死锁的出现。
在myisam中，由于每次读取都会上表级的读锁，写入都会上表级的写锁，而读，写锁之间又是不可重入的，因此可以理解为，在myisam表中，读写是串行操作。
但是，实际上在一定条件下，myisam是读写并发操作的。

在mysql中有一个全局变量 concurrent_insert,你可以使用语句 `SHOW GLOBAL VARIABLES LIKE '%concurrent_insert%';` 来查看他，对于稍久一点的版本来说，它的值分别为0,1,2。笔者使用的是5.6版本的mysql，它的值为NEVER,AUTO,ALWAYS。实际上新旧版本都是兼容的，只不过把真实的数值改成了枚举值显示更为直观而已。以笔者现在使用的5.6版本为说明:NEVER:从不并发插入AUTO:在数据库没有中间列删除导出的数据碎片的情况系，可以进行表尾并发插入AlWAYS:一直在表尾并发插入你可以通过 `set global concurrent_insert=ALWAYS` ，来进行修改，同时在ALWAYS下，你需要定期使用 OPTIMIZE TABLE 来清理数据碎片

在myisam中显式加锁语句为:

        lock table tableName read; --加读锁        locak table tableName write; --加写锁        unlock tables; --在语句最后释放锁        需要注意的是，由于在myisam中，读写必须加锁，因此在加锁和释放锁的过程中，你只能操作加了锁的表

INNODB

innodb作为目前主流的mysql引擎，已经几乎取代了myisam的地位。虽然按照表锁，行锁的特点来分析，在大量读取，少量更新的情况下，使用myisam的效率理论上要优于innodb，但是随着mysql对innodb引擎的优化，在非极端的情况下，innodb都应该是我们的首选。
innodb的两大特性分别是：使用行锁，以及支持事务。
事务的主要作用是为了保证一组sql语句的原子性，隔离性，一致性，持久性，下图是innodb支持的4种事务隔离级别

从上图可以看出，mysql的事务严谨的保证了之前说的4个特性，但是在可见性上做了适当的调整，让用户可以自由的根据业务对数据可见性的要求选择适当的隔离级别，在性能和可见性之间做取舍。

脏读：指一个事务内可以读取到其它事务未提交的内容不可重复读：指一个事务中，多次读取的数据内容不一致，这通常是因为在重复读取的间隙中，数据被其它的事务做了修改。幻读：指一个事务中，多次读取的数据数量不一致，这通常是因为在重复读取的间隙中，其它事务进行了插入操作。

有些同学看到上图可能会说我画错了，因为网上许多版本都是说可重复读会这个隔离级别出现幻读问题，但我的图里却说不会出现，这是因为数据库版本不同导致的。网上现在许多留存下来的文章都是几年前写的，而笔者现在写的这篇文章是基于mysql 5.6.38,在这个版本，mysql已经调整了可重复读隔离的实现方式，因此并不会出现幻读，这里简单证明一下，详细的下面在锁部分再讲。

首先在session1中开启一个事务，然后进行一次查询，接着在session2中插入一条数据，如图。

在session1的事务内再执行一次查询，发现数据没有变化，但session2中查询发现已经变成了3条数据

session1中提交事务后再次查询，出现3条数据

可以得出结论，可重复读的隔离级别是不会存在幻读的，但千万不要以为可重复读跟可串行化的隔离级别是一样的，这可能会在程序中导致一些严重的后果，这个具体也后面再说。

INNODB中的锁

说完了innodb中的事务，下面来看锁这一块。
在innodb中锁的类型同样有读锁和写锁两种，在锁的等级上，innodb使用的是行锁。
在前面也说过，行锁是个名字坑，很多人会误以为是锁住一行数据，但是在innodb中实际的实现是：锁住索引。正是基于这种实现，所以当where后的检索条件不是索引列的时候，这时候进行的是锁表操作。

可以看到，在session1对id=4的数据进行了更新操作，在session2中手动对id=4的数据添加读锁，此时session2一直处于阻塞状态，这是因为user表的id不是索引列，所以update操作对全表添加了写锁。

下面我们为id添加索引，重新一次刚才的操作。

可以看到，添加索引后，此时添加的是索引锁，因此session2没有阻塞，直接查出了数据。

由上面引申，由于innodb是对索引上锁，而不是真正意义上的行锁，所以即便你访问的是不同的行，但只要访问到同一个索引，一样会产生锁冲突

可以看到，我们的id是索引列,name是非索引列，理论上我们访问的是不同的行数据，但因为使用的是同样的索引，所以还是产生了锁冲突。

在当有多个索引的时候，innodb会对该行的所有索引上锁，所以如果有有一条数据id=1,name=2,且id,name都是索引列。此时一个事务对通过where id =1对id=1上了锁的同时，其实也对name=2上了锁，如图。

在innodb中，还有一种叫做间隙锁的锁。这种锁通常是在对索引列进行范围判断时，不但会锁住已存在的索引列，同时也会把范围内的其它索引一起锁住，如图。

在上图中，我们并没有id=1112这条数据，但是使用范围检索 id > 750这样的条件时，innodb会把>750的id索引全部锁住，即便他不存在。

看到这里，可能会有人心想我为什么在上一个例子中突然换了一张表来操作，这里面涉及到mysql执行计划的问题。在mysql解析sql的时候，实际上会有多种执行计划，mysql会自动帮你选择最优的，而有的时候，因为数据量等一系列原因，最优的计划并不一定会使用到索引，也有可能执行全表扫描，如下图。

可以看到，同样是使用id查询，但是 id< 10是使用的全表扫描，而id<4是使用的id索引，所以进行性能分析，锁分析的时候，也要善用explain查看执行计划，不可妄自推断。当然，真的十分牛逼的大牛除外。

需要注意，myisam的锁是在sql执行前一次获取的，因此不会产生死锁，但innodb的锁是在执行过程中逐个获取的，因此操作不当的时候，有死锁风险。

MVCC

前面说到，可重复读的事务级别现在不会产生幻读，因此从事务隔离级别的表格上看，他应该是等同于可串行化的，但笔者后面也说了，如果把他们当做一样的事务隔离级别，那程序可能会出现一些严重的后果。归根究底的原因就是：
可重复读防止幻读产生的手段是MVCC(并发版本控制)机制。
可串行化防止幻读产生的手段是在每次select操作的时候为该行加上读锁，阻塞对所有已锁行的写操作，因此他的读写操作是串行非并发的，这样就有效防止了幻读的产生。
看起来，似乎可重复读就是性能加强版的可串行化事务，我们只需要一直使用mysql的默认事务级别-可重复读就好了。在大多数的情况下确实如此，可重复读已经可以满足我们大部分生产环境的要求了，可是即便是从用户角度来说，这两者之间也仍然有些细微的区别。
可重复读解决了幻读问题，可是却并没有解决幻操作的问题。虽然你在select操作中找不到相应的数据，但是你却仍然可以通过update或者delete语句删除他。

session1开启事务后执行查询，session2插入一条记录，session1在事务内重新查询，结果不变。

session虽然查询不到id =1113的数据，但是根据where条件修改id=1113的数据，显示修改成功

提交事务，重新查询，可以看到id = 1113的数据被修改。

可以发现，我们虽然查不到事务开始后新加的数据，但是却可以对他进行修改。当然，大部分情况下我们是不需要考虑这个问题的，因为我们几乎总是会写update … where … 这样的语句，而不是单独查询出来后再在程序内部进行判断是否修改。
但这依然值得引起我们的注意，究竟为什么会出现这样的问题呢？
究根结底，其实是因为可重复读这个事务级别使用的是mvcc来解决不可重复读和幻读的问题。

mysql中的mvcc的实现原理，简单的说来就是：
在mysql的每个表中的数据行中，设置2个隐藏列，我们可以把他们叫做create_transaction_id（创建行的事务id）,以及delete_trransaction_id（删除行的事务id）。
每个事务开启，执行第一条sql后，会为这个事务创建一个自增长得id，我们叫他transaction_id
数据库的各种操作在mvcc中的处理大致如下：

insert:新增一行数据，create_transacticon_id 设置为当前事务的id，,delete_transaction_id设为null。update:复制update行的数据，不改变原先行的数据，把复制行的create_transaction_id设置为当前事务的id,delete_transaction_id设置为nulldelete:把要删除行的数据的delete_transaction_id设置为事务id，create_transaction_id不改变。select:查找create_transaction_id小于当前transaction_id的数据行，这个操作保证了找到的数据都是在事务开始前存在的。接着判断这些行是否的delete_transaction_id是否为null或者大于当前事务的transaction_id，这个操作保证了找到的数据在当前事务开启前是存在。

我们可以看到，在mvcc中，一条数据是由一条多个版本组成的，你能查看到哪些数据，主要取决于你当前事务的transaction_id，但是insert,delete,update这些操作，却一直是对最新的那个版本做修改，因此也就出现了幻操作这种现象。