目录

文章目录

  • ​​目录​​
  • ​​前言​​
  • ​​MySQL常用的存储引擎​​
  • ​​MyISAM​​
  • ​​InnoDB​​
  • ​​MyISAM和InnoDB的对比​​
  • ​​讲一下让人敬畏的锁机制​​
  • ​​MySQL中的锁​​
  • ​​MyISAM的表锁​​
  • ​​案例分析​​
  • ​​MyISAM的并发插入​​
  • ​​案例分析​​
  • ​​MyISAM的并发调度​​
  • ​​InnoDB中的行锁​​
  • ​​乐观锁和悲观锁​​
  • ​​讨论​​
  • ​​总结​​
  • ​​重要知识点概括​​
  • ​​参考资料​​

前言

时光冉冉，转眼间，距我上回提笔写字发表文章，已经是很久之前的事了，久到甚至我需要翻看记录才回想起上一篇文章的标题，而且仅仅是标题。因为工作上的繁忙这一原因之外，更重要的原因还在于我能力有限的问题，所学的技术或者知识太过浅显，致使我在每每想提笔描述自己所学的知识点的时候却也望而却步。我实在想不出有什么好写的了，毕竟网上有太多太多技术文章供大家参考学习的了。
　　话虽如此，在学习知识点的时候，秉着记录知识点以及分享知识的想法，还是想写一下相关的文章。作为自己的学习过程的总结，以及给其他学习人的参考。所以，今日，提笔写一下关于《MySQL的存储引擎和锁机制》等相关知识点。在此，还要感谢一下某个可爱的小伙伴，可以分享相关的文章，一同学习讨论这些个知识点。文中参考了很多的文章，如有冒犯请海涵。文中描述有不当之处，还望读者友善指出。

MySQL常用的存储引擎

MySQL常用的存储引擎有两种，即MyISAM和InnoDB。MySQL 5.5之前默认的存储引擎为MyISAM，而5.6之后引入的新的存储引擎，即InnoDB，并作为默认的存储引擎。下面将对这两种分别做简单的介绍。（参考：​​MySQL常见的两种存储引擎：MyISAM与InnoDB的爱恨情仇​​）

MyISAM

MyISAM的特性：

  1. 不支持行级锁（只支持表级锁），读取时对需要读到的所有表加锁，写入时对表加排他锁；
  2. 不支持事务；
  3. 不支持外键；
  4. 不支持崩溃后的安全恢复；
  5. 支持全文索引；
  6. 支持延迟更新索引，极大提升写入性能；
  7. 支持在查询表的同时，往该表插入新的记录；（提问：读者看到这里的时候，可能就会纳闷了，明明第一点还说，读取时会对所有需要查询的表加锁的，为什么还可以插入新的记录到表中呢？这里简单地回答下，实际上可以通过对MySQL进行设置实现的，对于不同的设置的值，实现不同的作用，后续将详细讲解。）
  8. 对于不会进行修改的表，支持压缩表，极大地减少了磁盘空间的占用。

InnoDB

InnoDB的特性：

  1. 支持行级锁，采用MVCC来支持高并发，有可能死锁；（MVCC，即多版本的并发控制（与MVCC相对的，是基于锁的并发控制），实现读不加锁，读写不冲突）；
  2. 支持事务；
  3. 支持外键；
  4. 支持崩溃后的安全恢复；
  5. 在MySQL 5.6.24之后同样也支持全文索引。
温馨提示：这里提及到的行锁、表锁等知识将在后续内容中做更详细的说明。不要慌，问题不大。

MyISAM和InnoDB的对比

通过对MyISAM和InnoDB的特性进行描述后，我们可以知道：

  1. 锁机制不同：MyISAM支持表级锁，而InnoDB支持行级锁（当然InnoDB也是支持表级锁的）；
  2. 事务处理：MyISAM不支持事务，而InnoDB支持；
  3. 外键支持：MyISAM不支持外键，而InnoDB支持；
    　　除此之外，还有如下几点说明：（参考：​​Innodb与Myisam引擎的区别与应用场景​​）
  4. 应用场景：MyISAM更适合读密集的表，而InnoDB更适合写密集的表（InnoDB在执行SELECT操作的时候，要维护的东西比MyISAM多很多）；
  5. 查询表的行数：
  • MyISAM：通过​​count(*)​​​查询表的行数时，可以从缓存中获取，不需要扫描全表来计算行数，但是如果​​count(*)​​查询语句中包含where条件时，同InnoDB查询表行数的操作一样；
  • InnoDB：不保存表的行数，需要扫描全表来计算行数。

讲一下让人敬畏的锁机制

介绍完MySQL的存储引擎之后，接来的重头戏“锁”这些个让人脑阔疼的知识点来了。但是别怕，我相信读一遍可能还是读不懂的。（调皮）所以，我希望读者也能结合一些更好的文章反复阅读。
　　那么在讲MySQL中的不同存储引擎不同锁的机制前，我们先来学习理解一下基本的概念和知识。

MySQL中的锁

事实上，讲解MySQL中的锁，我们需要针对不同的存储引擎加以分析。因为在MySQL中不同的存储引擎之间的锁机制不一定相同。所以，在后续的内容中，我希望读者能结合不同的存储引擎进行分析，区分理论与具体在MySQL不同存储引擎中的应用，否则很容易把自己搞糊涂的呢。
　　那么在MySQL中，按照锁的粒度划分，我们可以将锁大体分为3种：

  • 表锁：开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突几率高，并发度最低；（MyISAM和 InnoDB引擎都支持表级锁，其中MyISAM）
  • 行锁：开销大，加锁慢；会出现死锁；锁粒度小，发生锁冲突几率小，并发度最高；（InnoDB默认采用行级锁，在InnoDB中有3种行锁的算法，后续再针对InnoDB的行锁做具体的分析）
  • 页锁：开销和加锁时间介于表锁和行锁之间；会出现死锁，锁粒度介于表锁和行锁之间。（MyISAM和InnoDB均不支持，该粒度的锁将不在本文的讨论范围内）
    　　按照是否可写，我们又可以把锁分为：
  • 共享锁（S锁，也称读锁）：事务A对数据加S锁后，事务A只能对该数据进行读取操作；其他事务只能再加S锁，不能再加X锁，除非加在该数据上的所有S锁得到释放。这就保证了，被加了S锁的数据只能被读取，而不能被任何事务进行修改。
  • 排他锁（X锁，也称写锁）：事务A对数据加X锁后，事务A可以对该数据进行读写操作；但其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了，被加了X锁的数据只能被加锁的事务修改，而不能被其他事务进行修改。

MyISAM的表锁

（参考：​​MySQL MyISAM与表锁​​​）
　　MyISAM的表锁，有两种模式，即共享读锁和独占写锁。实现读锁与读锁兼容，读锁与写锁互斥，写锁与写锁互斥。也就是，MyISAM表的一个会话进行读取操作，并不会阻塞其他会话对该表的读取操作，但会阻塞其他会话对该表的写操作（但是在一定条件下，是允许查询和插入的并发执行）；MyISAM表的一个会话进行写操作，会阻塞其他会话对该表的读操作以及写操作。
　　MyISAM在执行查询（​​SELECT​​）操作时，会自动对所有涉及的表加读锁；在执行更新（​​INSERT​​、​​UPDATE​​、​​DELETE​​）时，会自动对所有涉及的表加写锁。加锁的过程是自动的，但是我们也可以手动的加锁或解锁。

案例分析

下面，我们通过手动加锁来模拟一下并发过程中MyISAM加锁影响。（采用手动加锁的原因是，如果不使用手动加锁，那么所有SQL操作将是“瞬时”的，马上加锁操作完就解锁了，通过两个会话的操作过程，我们并不能感受到加锁的效果，所以采用手动加锁。对于实际应用中，除非有业务或者其他特殊需要，否则无需我们手动加锁）
　　如果读者已经对上述的文字描述理解的特别通透了，那么这一部分的案例分析可以大致浏览下，最后细看总结部分。
（记得每个实验后，对有加锁的地方释放锁，否则可能对后续的实验产生影响。）　　
（1）创建案例所需的表​​​mi_user​​​和​​mi_news​​，并指定表的存储引擎为MyISAM

CREATE TABLE `mi_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `flag` int(10) unsigned NOT NULL DEFAULT '0',
  `flag_idx` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `flag_idx_index` (`flag_idx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `mi_news` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

（2）验证“读锁与读锁兼容，读锁与写锁互斥”

会话A

会话B

#对表​​mi_user​​​进行显示加读锁，并成功查询​​mi_user​​表

LOCK TABLE mi_user READ;

SELECT * FROM mi_user;

#向表​​mi_user​​​插入数据，发生错误​​Table 'mi_user' was locked with a READ lock and can't be updated​​，原因是对表加的是读锁，无法进行更新操作

INSERT INTO mi_user(`name`) VALUES(‘Zzz’);

#对表​​mi_user​​进行查询（自动地在表上加读锁），执行成功（释放读锁），未被阻塞

SELECT * FROM mi_user;

#向表​​mi_user​​插入数据（自动地在表上加写锁，但是加写锁失败），处于阻塞状态

INSERT INTO mi_user(`name`) VALUES(‘DreamBoy’);

#阻塞中

#释放锁

UNLOCK TABLES;

#会话A释放锁后，会话B上述执行的插入语句不再阻塞，接着执行成功

（3）​​LOCK​​加锁后，只能访问加锁了的表，不能访问没加锁的表

会话A

#对表​​mi_user​​​进行显示加读锁，并成功查询​​mi_user​​表

LOCK TABLE mi_user READ;

SELECT * FROM mi_user;

#访问未加锁的表​​mi_news​​​，发生错误​​Table 'mi_news' was not locked with LOCK TABLES​​

SELECT * FROM mi_news;

（4）​​LOCK​​​加锁后，使用到表别名操作，同样在​​LOCK​​时也需要对别名加锁

会话A

#对表​​mi_user​​进行显示加读锁，使用别名查询

LOCK TABLE mi_user READ;

SELECT * FROM mi_user u;

#发生错误​​Table 'u' was not locked with LOCK TABLES​​

#释放锁，重新加锁，进行测试

UNLOCK TABLES;

#为别名也加一下锁

LOCK TABLE mi_user u READ;

SELECT * FROM mi_user u;

总结：

  1. MyISAM的表锁，读锁与读锁兼容，读锁与写锁互斥，写锁与写锁互斥；​​LOCK​​某个表加读锁后，该会话也无法对这个表做更新操作；
  2. ​​LOCK​​加锁后，只能访问加锁了的表，不能访问没加锁的表；
  3. ​​LOCK​​​加锁后，使用到表别名操作，同样在​​LOCK​​时也需要对别名加锁。

MyISAM的并发插入

在MyISAM存储引擎下，表的读写操作是串行的。但是，在前文“MyISAM的特性”描述中提及MyISAM“支持在查询表的同时，往该表插入新的记录”，那么这又是为什么呢？
　　原来，在一定条件下，MyISAM表也是支持查询和插入并发执行的，通过设置系统变量​​​concurrent_insert​​来控制并发插入的行为，其值说明如下：

​​concurrent_insert​​的值

说明

0

不允许并发插入，即查询表的同时无法向该表插入数据

1

如果表中没有空洞（即表中没有被删除的行）时，允许并发插入，即一个会话查询表的同时，允许另外一个会话向同个表插入数据。该值为默认值

2

无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

查询当前系统变量​​concurrent_insert​​的值：

SHOW GLOBAL VARIABLES LIKE '%concurrent_insert%';

设置系统变量​​concurrent_insert​​的值，如设置为0：

SET GLOBAL concurrent_insert = 0;
案例分析

这里演示一下​​concurrent_insert​​​各个值的效果。其中这里需要强调的是：​​LOCK​​加锁时，需要加​​LOCAL​​关键字，LOCAL关键字用于指示允许并发插入。
（1）设置​​​concurrent_insert​​为0

会话A

会话B

#设置​​concurrent_insert​​为0

SET GLOBAL concurrent_insert = 0;

#对表​​mi_user​​​进行显示加读锁，并成功查询​​mi_user​​表

LOCK TABLE mi_user READ LOCAL;

SELECT * FROM mi_user;

#向表​​mi_user​​插入数据，阻塞中

INSERT INTO mi_user(`name`) VALUES(‘Yyy’);

#释放锁

UNLOCK TABLES;

#释放锁后，插入数据成功

（2）设置​​concurrent_insert​​为1

会话A

会话B

#设置​​concurrent_insert​​为1

SET GLOBAL concurrent_insert = 1;

#对表​​mi_user​​​进行显示加读锁，并成功查询​​mi_user​​表

LOCK TABLE mi_user READ LOCAL;

SELECT * FROM mi_user;

#向表​​mi_user​​插入数据，直接插入成功（因为此时表没有空间碎片(可以用optimize table table_name整理碎片)）

INSERT INTO mi_user(`name`) VALUES(‘Xxx’);

#释放锁

UNLOCK TABLES;

删除表中的某些数据制作空洞，并重新进行上述测试。

会话A

会话B

#删除数据制造空洞

DELETE FROM mi_user WHERE id = 1;

#对表​​mi_user​​​进行显示加读锁，并成功查询​​mi_user​​表

LOCK TABLE mi_user READ LOCAL;

SELECT * FROM mi_user;

#向表​​mi_user​​插入数据，因为空洞的原因，所以阻塞中

INSERT INTO mi_user(`name`) VALUES(‘Aaa’);

#释放锁

UNLOCK TABLES;

#释放锁后，插入数据成功

（3）设置​​concurrent_insert​​为2

会话A

会话B

#设置​​concurrent_insert​​为2

SET GLOBAL concurrent_insert = 2;

#对表​​mi_user​​​进行显示加读锁，并成功查询​​mi_user​​表

LOCK TABLE mi_user READ LOCAL;

SELECT * FROM mi_user;

#向表​​mi_user​​插入数据，直接插入成功

INSERT INTO mi_user(`name`) VALUES(‘Baa’);

#释放锁

UNLOCK TABLES;

MyISAM的并发调度

MyISAM存储引擎下，读与写是互斥的，读操作是串行的。那么，如果一个进程请求某个表的读锁的同时，另外一个进程也在请求这个表的写锁，那么谁会获得锁呢？答案是，写进程先获得锁。
　　不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。（转自：​​MySql锁的概念​​）具体调节的方式，可以查询参考的文章。

InnoDB中的行锁

在MySQL InnoDB引擎中，默认查询（SELECT）操作并不加锁（属于MVCC的快照读）；更新（INSERT、UPDATE、DELETE）操作对涉及的数据加排他锁。通过如下语句可以对SELECT加共享锁或排他锁：

共享锁（Ｓ）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB存储引擎有3种行锁的算法，其分别是：
（下列描述摘自：​​Mysql锁机制简单了解一下​​）

  • Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
  • Gap Lock: 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
  • Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。InnoDB事务隔离级别RR下，通过Next-key Lock，可解决幻读问题。

实际上，看完上述的描述，我想作为一个初学者的话，仍将是一脸的懵逼的。“什么时候会应用到Gap Lock间隙锁？”、“什么时候又应用到Next-key Lock？”、“使用Gap Lock的前置条件是什么呢？”等等一系列问题在脑海中还将不停打转。
　　所以这里强烈推荐阅读一下：​​​MySQL 加锁处理分析​​​、​​Innodb锁机制：Next-Key Lock 浅谈​​，对理解MySQL的InnoDB加锁机制将会有所帮助。
　　InnoDB行锁是通过索引上的索引项来实现的，只有通过索引条件检索数据，且EXPLAIN执行计划中确实用了索引查询（因为有可能MySQL认为全表扫描效率更高而放弃索引检索）才会使用行锁，否则InnoDB将使用表锁（20190309，“将会在聚簇索引上所有的记录都被加上锁，其次对于事务隔离级别RR来说在这些记录的“间隙”中默认还会加GAP锁。这里对于不满足查询条件的记录也同样会被加锁，这是为什么呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。”这句话描述可能存在一些问题，所以这里暂做保留！）（在《​​​MySQL 加锁处理分析​​​》一文中案例有所提及）
　　
// TODO 未完待续

乐观锁和悲观锁

讨论

最后，我们来讨论一下，这样的一个问题：MySQL Innodb 中 RR 隔离级别能否防止幻读？
　　首先参考一下：​​​Innodb 中 RR 隔离级别能否防止幻读？​​

总结

  1. 文中讲述了MySQL的两大存储引擎MyISAM和InnoDB的特性，并对两者进行了对比。
  2. 分别讲述了MyISAM和InnoDB的锁机制。
  3. 最后简单介绍了一下乐观锁和悲观锁。

重要知识点概括

  1. ** 相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。**
  2. MyISAM表的读和写是串行的（读写互斥），读操作串行，但在一定条件下，MyISAM表也支持查询和插入的并发执行，通过设置​​concurrent_insert​​​进行调整。MyISAM表的读和写是串行的（读写互斥），读操作串行，但在一定条件下，MyISAM表也支持查询和插入的并发执行，通过设置​​concurrent_insert​​进行调整。
  3. 不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。
  4. 通过锁定机制可以实现事务隔离性要求，使得事务可以并发的工作。
  5. 在事务隔离级别RC和RR下，InnoDB存储引擎使用非锁定的一致性读。然而对于快照数据的定义却不同，在RC级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在RR级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
  6. InnoDB除了通过范围条件加锁时使用间隙锁外，通过主键或者唯一索引来锁定不存在的值，也会产生GAP锁定。（记得间隙锁的前置条件）
  7. GAP锁只会阻塞要INSERT到“间隙”中的INSERT操作，因为GAP间隙中是不存在任何记录的，除了INSERT操作，其他的操作结果应该都等价于空操作，MySQL就不去阻塞它了。

参考资料

  1. ​​MySQL常见的两种存储引擎：MyISAM与InnoDB的爱恨情仇​​
  2. ​​SQL–共享锁（S）和排它锁（X）之间不得不说的那些事！​​
  3. ​​关于共享锁，排它锁，乐观锁，悲观锁​​
  4. ​​共享锁（S锁）和排它锁（X锁）​​
  5. ​​mysql悲观锁中的共享锁和排他锁​​
  6. ​​Innodb与Myisam引擎的区别与应用场景​​
  7. ​​myisam不适合大量更新操作​​、
  8. ​​MySQL MyISAM与表锁​​
  9. ​​MySql锁的概念​​
  10. ​​Innodb 中 RR 隔离级别能否防止幻读？​​