锁模块之MyISAM与InooDB关于锁方面的区别

锁锁模块之MyISAM与InooDB关于锁方面的区别

了解完关系型数据库和索引相关的知识点后，我们来讲讲锁。我们来看看锁方面的常见问题。

• MyISAM和InnoDB关于锁方面的区别是什么？
• 数据库事务的四大特性
• 事务隔离级别，以及各级别下的并发访问的问题
• InnoDB可重复读隔离级别下如何避免幻读
• RC，RR级别下的InnoDB的非阻塞读如何实现

1.MyISAM和InnoDB关于锁方面的区别是什么

下面来解答第一个问题，这里先给出答案。

1. MyISAM默认使用的是表级锁，不支持行级锁。

2. InnoDB默认使用的行级锁，也支持表级锁。

下面我们通过实际的例子对什么是表级锁什么是行级锁进行，我们将通过实际的例子来对它们做一个形象的讲解。

我们用客户端打开几个新的session。注：一个tab就是一个session 如下图:test2 ,test3 ,test4

我们使用这些session来模拟一下并发的访问，即多个session操作同一个表。或者同一个表里面的同一条数据。这里我准备了两张数据比较多的表。person_info_large 它是基于InnoDB引擎的 另一张是person_info_myisam 顾名思义它是基于MyISAM引擎的。

由上图可知他们的表列结构是一样的。这里我们分别向person_info_myisam灌入240万条左右的数据，向person_info_large灌入200万条数据。

MyISAM

咋们先来讲讲myisam存储引擎，myisam存储引擎呢由于不支持事物，表级锁的情况不好模拟。直接显示加上 lock table xxx 又显得太假，因此这里只好把数据量增大到240万条。这样子我们去查找或者更新比较多条数据的时候会使用几秒钟的时间，这个时候我们再用另外一个session做别的操作。这样子呢就能模拟出并发访问的场景来，并且可以看到myisam会自动的为我们加上相应的表锁。

先上读锁再上写锁：

首先我们来对myisam表进行主键范围的查询，咋们可以由上图可以看到它的主键是 id ，我们现在取出主键为1-200万的数据。

select * from person_info_myisam where id between 1 and 2000000;

这个语句执行是需要一定的时间的，那这个时候我们就能在另外的一个session里面做操作了。对2000001条数据进行更新。这里只是假更新。

update person_info_large set account = account where id = 20000001;

当查询的SQL执行成功后，我们再执行 更新，这里会执行得非常的快。

我们再执行一下查询语句，当没执行完时执行update语句，它是先执行查询后再执行update。

也就意味着当表进行查询的时候MyISAM会自动的给表上了一个表锁。它会锁住这张表，并且block其他的session对其进行数据的更新。

其实原因是这样的，对于MyISAM当对数据进行select的时候。它会为我们自动加上一个表级别的读锁，而对数据进行增删改的时候，它会为我们操作表加上表级别的写锁。当读锁未被释放的时候，另外一个session想要对统一张表进行写锁它就会被堵塞。直到所有的读锁都释放为止。

那么如何显示给表加上读锁呢，咋们可以执行这样的一个命令。

lock tables person_info_myisam read; # 写锁就是 write

加上读锁之后我们在另外一个session对表进行更新，这样也会block住，直到我们给它的这个读锁释放掉怎么释放呢。

unlock tables; # 执行这条语句即可释放锁

执行完释放锁，update即可执行。

读锁还有一个名字，那就是共享锁。什么意思呢 我们在一个session中对表加上读锁。另一个session 进行范围查询也是可以执行成功的。

先上写锁再上读锁：

我们先执行

update person_info_myisam set account = account where id between 1 and 2000000;

同时执行

select * from person_info_myisam where id = 20000001;

这时也是需要等待写锁的释放才能执行 select

上写锁再上写锁：

先执行

update person_info_myisam set account = account where id between 1 and 2000000;

同时执行

update person_info_myisam set account = account where id = 2000000;

这时也是被block住；索引写锁有了别名也就是排他锁。

注：对select语句上排它锁

select * from person_info_myisam where id between 1 and 20000001 for update;

小结：

1.MyISAM 引擎默认支持表级锁，它是不支持行级锁的，表级锁会锁住整张表。当我们对1到200万条数据进行查询的时候，对2000001条数据进行update，这时它会被block住，行级锁则不会。

2.锁按级别分为共享锁和排它锁。上了共享锁之后依然支持上共享锁，不支持上排它锁。要是先上排它锁另外的对或者写是不允许的，共享锁和行级锁的这种情况，同样适用于支持行级锁的InnoDB引擎。

InnoDB

由于InnoDB支持事物，我们可以先通过session获取锁 占时不自动提交的方式，模拟并发访问的过程。mysql是默认自动提交事物的，看下面一个例子。

这里举一个两个session对同一行数据进行修改的情况，

update person_info_large set title = 'test' where id = 1; # session1

update person_info_large set title = 'test' where id = 1; # session2

咋们可以知道两条语句都是无需等待直接可以执行的。这样看来和不支持事物的 MyISAM一样，sql跑完之后就会自动解锁。其实InnoDB用到的是二段锁，也就是加锁和解锁是分成两个步骤来执行的。即先对一个事物里的同一批操作分别进行加锁然后 commit 的时候再对事物里加上的锁进行统一的解锁。而当前commit是自动提交的，所以看起来和MyISAM没有太大的区别。我们可以执行以下命令得到结论。

show variables like 'autocommit';

这个默认是 on .也就是是事物是默认自动提交的。

咋们先来关闭自动提交

set aotucommit = 0; # 关闭自动提交 该设置只针对当前session

这里我们再把另外一个session进行一样的关闭自动提交操作。

咋们现在可以执行语句了，这里需要补充的是如果你不愿意去做设置那么可以直接的显示调用下面这条语句

begin transaction 
xxx sql

同样能达到相同的效果，这里呢我们就不用做范围查询那么复杂了，因为我们这个表已经默认支持了事物的二段提交，我们直接对单行加共享锁，然后再通过另外一行加共享读，或者排它锁看看，比如说对id=3的数据加共享读锁，像这样子

select * from person_info_large where id = 3;    #session1

然后在另一个session，再对同一个id进行更新

update person_info_large set title = '深圳'  where id =3; #session 2

发现居然能够执行成功。我们这里的锁还没有commit，只有commit它才释放嘛，现在我们还没有执行，它居然能够执行成功了。这完全颠覆了我们建立起来对共享读锁加锁之后，无法加入排它写锁的认识。大家不要慌其实我们的InnoDB对select 进行了改进，该select 未对改行上锁，所以导致更新是成功的。关于非阻塞select 咋们后面会有交代。接下来咋们再各自提交一下刚刚的改动；

在两个session里执行同样的commit语句，然后再对它上锁；

commit;

执行完之后我们要显示地给select上共享锁。

select * from person_info_large where id = 3 lock in share mode; #session 1

这样就能给它上读锁了，紧接着执行刚刚的更新语句。

update person_info_large set title = '深圳'  where id =3; #session 2 

此时更新语句是被 block 住的。

需要在session 1,执行

commit;

执行之后这个update语句才能成功。也就是说只要当前行加了共享锁之后，别的session就无法加排它锁。那InnoDB是不是真正支持行级锁呢。咋们求证一下

接下来给id 加共享锁之后，另外的session给id为4的行做更新。

select * from person_info_large where id = 3 lock in share mode; #session 1

update person_info_large set title = '深圳'  where id = 4; #session 2 

这时更新语句不会被block住，session1加了锁但是我们的另外一个session对不是同一行的数据做更新的时候，他并没有被锁住证明InnoDB的锁是默认支持行级的。

我们再来验证一下共享锁和排它锁之间的兼容性，看是否之前的一样。前面是先读后写，咋们现在来先读后读。刚才id =3 的select并未提交，我们用另外一个session对该条数据加共享锁。看看能不能够读出来。

select * from person_info_large where id = 3 lock in share mode;

此时是能读出数据的，即便是同一行我们给它加共享锁，这两个共享锁是不会起冲突的。先写后读也是基本一致的。

我们很容易就理解到使用表级锁的时候，只要操作到表数据的时候均会上表锁，因此表级锁跟索引无关，接下来咋们来思考一个问题，行级锁是否跟索引有关？答案是，除了用id主键索引以外的其他键，如这里咋们的account,area只要sql用到索引涉及到的行呢，都会上共享锁或者排它锁。

咋们来看看person_info_large 这张表它的主键是id，也就是id是它的聚集索引，那其他的普通键如，account，area加title都是走索引的，我们来验证一下不走索引的情况看看会发生什么。

select * from person_info_large where motto = "座右铭1" lock in share mode; #motto 不走索引
# session 1

我们在另一个session对其进行写操作

update person_info_large set title = title where motto = "座右铭2"; #session 2

如果它是走行级锁的时候，因为这两个motto的数据是不一样的所以同时运行时应该不会block住。

但是实际这里运行的时候 update 被block住了。需要等待select解锁了之后才能够进行更新。我们commit了之后，update就能够顺利执行了。也就是说虽然每更新同一样数据，然而update的session还是被block住了。所以我们可以等到结论 InnoDB 锁 如果不走索引的时候整张表就会被锁住。也就是此时的查询用的是表级锁。所以InnoDB在SQL没用到索引的时候，用的是表级锁。而SQL用到索引的时候用到的是行级锁。

需要补充的是InnoDB需要除了支持这些行级锁之外，还支持表级的意向锁，意向锁也分为共享读锁 IS ，IX跟咋们MyISAM的表锁差不多，主要的是为了进行表级别的操作的时候，不用去轮询每一行看一看有没有上行锁。

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

session1对某一行数据上了一个排它锁（增删改）或者 select 时 for update,都会给它上排它锁，那么同时呢session2想个它上排它锁这是会冲突的，上共享锁 S 同样也会需要等待 X 锁的释放。

sessin1先对这行记录上共享锁，session2再对同一行记录上X锁的时候他也会冲突，但是如果session2对它上共享锁的时候，它是不冲突的，两者是兼容的。

那是不是行级锁会比表级锁要好？那倒未必，锁的粒度越细代价越高，相比表级锁在表的头部直接加锁来讲，行级锁还要在扫描到某行的时候对其上锁，这样代价是比较大的。InnoDB支持事物的同时也相比于MyISAM来说代价更高，同时咋们从索引部分也了解到，InnoDB是必须有且只有一个聚集索引，数据文件是和索引绑在一起的，必须要有主键。通过主键索引效率很高但是辅助索引需要查两次，先查到主键然后再通过主键再查到数据。而MyISAM是非聚集索引数据文件是分离的，索引保存的是数据文件的指针主键索引和辅助索引是独立的，因此MyISAM在纯检索的过程中，也就是增伤改很少的系统中，其性能要好于InnoDB,s所以我们接下来分析一下这两个引擎分别适合的场景。

MyISAM适合的场景

• 频繁执行全表count语句 （对于InonoDB来讲它是不保存表的具体行数的，执行select count(*) from table 时需要重新扫描统计，而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需读出该变量即可）
• 对数据进行增删改的频率不高，查询非常频繁 （因为执行增删改需要进行锁表的操作，虽然插入操作可以通过一些配置能够使改引擎从表的尾部插入数据，但是依然会产生很大碎片，索引比较影响性能。但是纯查询的时候效率是非常高的）
• 适合没有事务的场景

InnoDB适合的场景

• 数据增删改查都很频繁的系统 （增删改的时候只是某些行被锁，在大多数情况下避免了堵塞，而不像MyISAM每次对某行数据进行增删改的时，都会去锁住整张表）
• 可靠性要求比较高，要求支持事物

锁的分类

接下来我们来过一下锁的分类，方便大家多锁有一个全面的认识，看到那么多分类大家不要吓坏了，换汤不换药其实就那么几种类型的锁。只是按照不同概念去划分罢了。其实还是相同的东西

• 按锁的粒度划分可分为表级锁，行级锁，页级锁 （其中InnoDB默认支持行级锁，同时支持表级锁。InnoDB对行级上锁的时候会先上一种表级别的意向锁。MyISAM仅支持表级锁。而不常用的BDB引擎支持页级锁，页级锁就是结业表级和行级之间的锁就是我们之前所说的逻辑块，锁定位于相邻存储页的几行相邻数据）
• 按锁级别划分可分为共享锁和排它锁。
• 加锁方式划分可分为 自动锁和显示锁。（向意向锁和MyISAM的表锁以及update，insert，delete 的时候加上的锁就是自动锁了，因为这是mysql自动为我们上的。而select ， for update ，lock in share mode我们显示去加的锁就是显示锁）
• 按操作划分可分为DDL锁，DML锁 （对数据进行操作的锁就是DML锁，包括对数据的增删改查。而对表结构进行表更的如我们调用 alter table 而加上的锁就是DDL锁）
• 按使用方式划分可分为乐观锁，悲观锁。（乐观锁，悲观锁不仅在数据库里面常见，在我们的程序中也 十分常见）

悲观锁：正如其名，他指的是对数据被外界（本系统当前的其他事物，以及来自外部系统的事物处理），对这些外界的修改持保守态度。因此在整个数据的处理过程中将数据处于锁定状态，悲观锁的实现往往依靠数据库提供的锁机制，也就只有数据库层通过的锁机制才能真正保证数据访问的排他性，否则尽管实现了在本系统中实现了加锁机制，也无法保证外部系统无法修改数据，那全程用排它锁锁定正是悲观锁的一种实现，悲观并发控制通常是先取锁再访问的一种机制，为数据处理的安全提供了保证。但是在效率方面处理加锁的机制会让数据库产生一种而外的开销，还有增加产生死锁的机会，另外在一次执行事务处理中，由于不会产生冲突也没必要使用锁。如果上锁不仅会增加系统负担还会降低并行性。一个事物如果锁住了某行数据，其他事物就必须等待该事物处理完，才可以去处理。

乐观锁：相对于悲观锁而言，乐观锁认为数据在一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会对数据的冲突与否进行检测。如果发现冲突了则返回用户错误的信息。让用户决定如何去做。相对于悲观锁在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般实现乐观锁的方式就是记录数据版本，实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

像刚刚那种多session之间的互锁方式，就是咋们悲观锁的实现。这里就不再重复了。咋们演示一下乐观锁的一种实现方式，基于数据版本即version记录机制的实现方式。何为数据版本？即为数据增加一个版本标识。一般是为数据库表增加一个数字类型的version字段。

这里创建一张名为test_innodb的表。这个表示基于InnoDB引擎创建的表，其中version就是哪来做版本控制的，当读取数据的时候我们将这个version字段的值也一同读取出来。数据每更新异一次我们就对version 加 1，当我们去更新的时候去判断数据库表对应的当前版本信息与第一次取出来的version值进行比对，如果数据库 表当前版本号与第一次取出来的version值相等，则给予更新否则认为是过期数据。为了能够直观的认识乐观锁的实现机制，我们直接通过数据来进行讲解

这里已经给表加入了几条数据，我们假设有两个程序，它们更新id为2的数据。希望能是更新money，而这个程序在更新这条数据之前，都会先读入它的version这里对应的version是0，那我们假设程序1先对这行数据做更新，那它将会拿version = 0 做它的where条件传入到sql语句当中做更新。那由于数据库里面这行数据的version还是0，因此它的更新是成功的，更新成功之后数据库里的version就变成了1。那这个时候程序2在程序1之后再进行更新，那它此时还不知道version已经变化了。还是传入了version = 0 这么一个版本，那它更新自然是不成功的。不成功我们就认为这个程序2取到的version是过期的，这就是乐观锁的一种实现方式

接下来通过实操去做体现

我们在session里模拟程序的这么一个过程，

#第一步先读test_innodb的数据，得到version为versionValue
select version from test_innodb where id = 2; # 0 程序1
select version from test_innodb where id = 2; # 0 程序2
#第二步每次去更新表中的money字段时，为了防止冲突，先去检查version再做更新。更新成功的话version + 1
update test_innodb set money = 123,version=0+1 where version = 0 and id = 2; #程序1
update test_innodb set money = 345,version=0+1 where version = 0 and id = 2; #程序2
#此时程序2先执行，version变成了1。那这个使用程序1去执行还以为version=0此时就不成功。

像这样提交的时候去检查版本，而不是在提交之前去锁住这个字段。这就是乐观锁的实现了！

第二步每次去更新表中的money字段时，为了防止冲突，先去检查version再做更新。更新成功的话version + 1
update test_innodb set money = 123,version=0+1 where version = 0 and id = 2; #程序1
update test_innodb set money = 345,version=0+1 where version = 0 and id = 2; #程序2
#此时程序2先执行，version变成了1。那这个使用程序1去执行还以为version=0此时就不成功。

像这样提交的时候去检查版本，而不是在提交之前去锁住这个字段。这就是乐观锁的实现了！

乐观并发控制相信事物之间的数据竞争之间的概率是比较小的，因此尽可能做下去直到提交的时候再去锁定，所以呢不会产生死锁的问题。但如果之间简单这么做还是会遇到不可预期的结果，例如刚才我们说的两个事物都读取了数据库的某一行，经过修改之后回写数据库，这时就遇到了问题。