MySQL数据库事务，锁和MVCC

事务可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

一般来说，数据库事务必须支持4个特性（ACID）：原子性，一致性，隔离性和持久性。

在 MySQL 命令行的默认设置下，事务都是自动提交的。在自动提交模式下，如果没有start transaction显式地开始一个事务，那么每个sql语句都会被当做一个事务来执行并自动进行提交操作。如果要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

1. 数据库事务特性

原子性

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚。而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后才对数据库中的数据执行修改操作。

一致性

一个事务执行结束后，数据库的完整性约束没有被破坏。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。

可以说，一致性是事务追求的最终目标。前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。

隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（Read committed）、可重复读（Repeatable read）和串行化（Serializable）。

  • Read uncommitted：所有事务都可以看到其他未提交事务的更新。此种隔离级别会出现脏读的问题，即其他并发事务能够读到还未提交的数据。
  • Read committed：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别存在不可重复读（Nonrepeatable Read）的问题。不可重复读是指在一个事务中的两次查询结果集不一致，这可能是两次查询过程中间，有另外一个事务更新的原有的数据。
  • Repeatable read：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
  • Serializable：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

MySQL通过MVCC机制避免了不可重复读和幻读的问题。MVCC的原理在后文中我们会介绍。

持久性

一个事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

  1. 刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
  2. 刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

redo log与binlog

我们知道，在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。

（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。

（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。

2. 数据库锁

数据库锁从逻辑角度可以分为两类：乐观锁和悲观锁。

乐观锁：一般是用户业务中实现的锁机制。乐观锁假设数据一般情况下不会造成冲突，因此在提交更新的时候才对数据是否存在冲突进行检验。如果存在数据冲突则更新失败。具体的实现是位数据库表添加一个version字段，每次更新操作都会修改version。提交时使用CAS操作修改version，如果修改成功，则更新数据成功，否则操作失败。

悲观锁：悲观锁一般就是我们通常说的数据库层面的锁机制。一般分为表锁和行锁。MyISAM中只用到表锁，不会有死锁的问题，锁的开销也很小，但是相应的并发能力很差。innodb实现了行级锁和表锁，锁的粒度变小了，并发能力变强，但是相应的锁的开销变大，很有可能出现死锁。同时inodb需要协调这两种锁，算法也变得复杂。InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。另外，表锁和行锁都分为共享锁和排他锁，共享锁负责只读，而排他锁负责写。

2.1 共享锁和排他锁

共享锁用于读取数据。当事务A对某资源加了共享锁后，其它事务也只能对该资源加共享锁。若想加排他锁，需等待所有事务释放共享锁。

而排他锁用于修改数据。当事务A对某资源加了排他锁后，事务A可以读取和修改该资源。其它事务不能对该资源加任何锁，直到事务A释放排他锁。

下面是添加共享锁和排他锁的例子：

SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE; -- 显式加共享锁
SELECT * FROM table_name WHERE id = 1 FOR UPDATE; -- 显式加排他锁

另外，在InnoDB引擎下，所有的insert，update和delete语句都会给相关记录添加排他锁。

而select语句由于MVCC机制不用添加任何锁，因此通过MVCC能够大幅提高并发事务间的读效率。

当一个事务结束时，在事务内的添加的锁就会隐式的释放。不需要客户端显示的执行锁的释放。

3. MVCC机制

MySQL MVCC机制实现了Read committed和Repeatable Read隔离级别。如果要实现Read committed隔离级别，最简单的方案就是给事务中所有的读操作添加共享锁，所有的写操作添加排他锁。这样就能够确保所有事务内的读操作都能够读到已经提交了的更新（未提交的更新加了排他锁，因此无法读到）。

然而直接通过读锁和排他锁实现的Read committed隔离级别，并发事务读吞吐量很低。因此，MySQL通过MVCC机制，避免了对于读操作的加锁，并发竞争的概率大大降低，从而提升了并发事务间的读效率。

3.1 MVCC原理

首先每一个事务在开始时都会获得一个id（Mysql维护的递增id），每张表都拥有两个隐藏列：

  • DB_TRX_ID: 记录操作该行数据的最新事务的事务ID
  • DB_ROLL_PTR：指向上一个版本数据在undo log里的位置指针（事务开始时都会写一份undo log）

每一次事务内的update操作，都会修改该行数据的DB_TRX_ID和DB_ROLL_PTR。因此，通过每行数据的事务id和指向历史数据的指针，我们就可以根据当前事务id获得其对应版本的数据了。

Mysql MVCC具体的实现机制如下：

一个事务在第一次执行到select语句时，Mysql会为其创建一个ReadView。ReadView中包含4个重要属性：

  • trx_ids: 当前系统活跃(未提交)事务版本号集合
  • low_limit_id: 创建当前read view 时当前系统活跃的事务最大ID号
  • up_limit_id: 创建当前read view 时系统活跃的事务最小ID号
  • creator_trx_id: 创建当前read view的事务ID号

执行Select语句时，MVCC机制会根据每一条数据的DB_TRX_ID和当前事务的ReadView，来决定是否展示改行数据，具体流程如下：

  1. 如果该行数据的DB_TRX_ID小于up_limit_id（活跃事务的最小ID号），则说明该数据是在所有活跃事务开启之前就已经存在的，因此可以显示。
  2. 如果该行数据的DB_TRX_ID大于low_limit_id（活跃事务的最大ID号），则说明该数据是在所有活跃事务开始之后才创建的，所以数据不予显示。
  3. 如果该行数据的DB_TRX_ID大于up_limit_id （活跃事务的最小ID号）并且小于low_limit_id（活跃事务的最大ID号），则用该行数据事务ID与trx_ids活跃事务集合中id进行匹配，如果没有查找到，则表明该事务已经提交了，因此该行数据可以显示。另外，如果该行数据事务ID等于creator_trx_id，这说明该数据就是当前事务修改的，因此也可以直接展示。
  4. 上述规则都不满足时，通过改行数据的历史数据指针DB_ROLL_PTR，从undo log里进行查找历史数据，然后用历史数据的事务id回头再来和read view 条件匹配 ，直到找到一条满足条件的历史数据，或者找不到则返回空结果。

更多关于MVCC的实现，请参考：https://zhuanlan.zhihu.com/p/52977862?utm_source=wechat_session&utm_medium=social&utm_oi=61516120326144

注意，Read Commit隔离级别和Repeatable Read隔离级别都是通过MVCC实现。对于Read Commit隔离级别，一个事务内的每一条select语句都会创建一个最新的read view，因此会产生不可重复读的问题。而对于Repeatable Read隔离级别，只有事务内的第一条select语句开始时，才会创建一个read view，之后同一个事务内所有的select语句都会使用这个read view。

另外，MVCC在更新每行数据时（更新DB_TRX_ID和DB_ROLL_PTR属性），都会使用排他锁来进行更新。

4. 再谈数据库锁

在Repeatable read级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

不显式加『lock in share mode』与『for update』的『select』操作都属于快照读，使用MVCC，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见。下面是快照读的例子：

SELECT * FROM table WHERE ?;

而当前读的方式则是在读时显示的加上共享锁或排他锁：

select * from table where ? lock in share mode; 
select * from table where ? for update;

因此，如果想要在一个数据库事务中读取到最新的数据，要么将隔离级别设置为Read committed，要么使用加锁读（即当前读）的方式。

4.1 行锁算法

InnoDB的行锁算法主要分为三种：Record lock，Gap lock和Next-Key lock。

  • Record lock：单个行记录上的锁。
  • Gap lock：间隙锁，锁定一个范围，但不包括记录本身。注意，Gap lock并不是排他的。这意味一条记录被添加了Gap lock后仍然能添加Gap lock。但是添加了Gap lock的间隙不能再获得插入锁。从而阻止了幻读。
  • Next-Key lock：即Record lock和Gap lock的结合。即锁定一个范围，并且锁定记录本身。

注意：Gap lock和Next-Key lock的目的是为了解决幻读，仅在Repeatable read的隔离模式下有效。

对于根据主键的查询，MySQL会通过Record lock对对应的主键加锁。如果对应主键记录不存在，那么MySQL会找到该主键所在区间，然后对该区间添加Gap lock。

主键查询.png

对于唯一索引的等值查询，MySQL会通过Record lock对定位的索引和主键进行加锁。

唯一索引.png

而对于非唯一索引或者范围查询，MySQL会通过Next-Key lock对于整个范围区间加锁。

普通索引.png

注：Gap锁，锁定的是索引记录之间的间隙，是防止幻读的关键。如果没有上图中绿色标识的Gap Lock，其他并发事务在间隙中插入了一条记录如：『insert into stock (id,sku_id) values(2,103);』并提交，那么在此事务中重复执行上图中SQL，就会查询出并发事务新插入的记录，即出现幻读（select for update方式的当前读不会触发MVCC机制，因此需要靠Gap lock来保证幻读不出现）。Gap锁详解：https://blog.csdn.net/u022812849/article/details/122528923

注意：仅仅当MySQL隔离级别为Repeatable read时，InnoDB才会启用Gap lock和Next-Key lock。如果隔离级别为Read committed，那么行锁只有Record lock这一种类别。

另外，如果查询语句中不包含任何索引信息，即根据普通字段来查询，那么InndoDB就会对整颗索引树上的每一个索引节点加锁。

针对插入操作，为了增加插入操作的吞吐量，InnoDB提出了插入意向锁（Insert Intention）的概念。插入意向锁本质上是一种Gap锁。例如当前数据库存在的索引为（4，7），此时事务A插入一条主键为5的记录，事务B插入一条主键为6的记录，事务A和事务B首先获得区间（4，7）上的插入意向锁，然后再获得对应插入位置的排他锁，最后执行插入动作。因此，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。

而对于update或delete而言，如果两个事务再同一个区间内工作，那么一定会进行互相等待，因为update或delete会对行添加排他锁。

锁排他性.png

上图展示了当一个区间获得了Gap锁后，针对该区间的Insert Intention请求就会被拒绝，从而达到了禁止幻读的目的。并且Record锁是一个排他锁。

另外，对于Repeatable read隔离级别，是不支持更新的可重复读的。下图是一个例子：

修改的不可重复读.PNG

此时如果必须保证可重复读，那么应该在事务中使用当前读来代替快照读，从而形成加锁阻塞的目的。

4.2 意向锁

意向锁是针对表级别而言的，分为两种：意向共享锁和意向排他锁。一个事务在申请表锁/行锁时，必须先申请该表的意向锁。

意向锁的目的是为了加速表锁的申请。如果不存在意向锁，那么通过LOCK TABLE … WRITE申请表锁的流程如下：

  1. 判断表是否存在表锁
  2. 判断每一行是否存在行锁，如果有数据行存在行锁，那么申请表排他锁就阻塞。

因此，如果申请一个表锁要遍历所有的表记录查看是否存在行锁，那就太费时了。因此提出了表的意向锁。

一个事务在申请为一个数据行或一整张表添加共享锁时，必须先为这张表添加一个意向共享锁。同理，一个事务在申请为一个数据行或一整张表添加排他锁时，必须先为这张表添加一个意向排他锁。

此时一个事务通过LOCK TABLE … WRITE申请表锁的流程如下：

  1. 判断表是否存在表锁
  2. 判断表是否存在意向锁，如果存在，说明有事务正在对表执行读写操作，那么申请排他锁就阻塞。

5. 死锁场景

下面是一个常见死锁场景，先读取数据库判断记录是否存在，不存在则插入：

以id为主键为例，目前还没有id=22的行，数据库最大id为21

Session1:

select * from t3 where id=22 for update;

Empty set (0.00 sec)


session2:

select * from t3 where id=23  for update;

Empty set (0.00 sec)

 
Session1:

insert into t3 values(22, "Ivan2");
 

Session2:

insert into t3 values(23, "Ivan3");

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

上面的代码中session1和session2都将获得（21， +∞）的Gap lock。

因此session1在执行插入语句时，首先尝试获取insert intention lock，此时由于session2持有了Gap lock，因此等待session2释放这个Gap lock。

而session2在执行插入语句时，也同样尝试获取insert intention lock，此时由于session1持有了Gap lock，因此等待session1释放这个Gap lock。从而形成了死锁。

解决方案：使用insert into t3(xx,xx) on duplicate key update `xx`='XX';语法。或者使用select语句替换select for update语句，再最终插入时捕捉key重复的错误再返回失败或者重试。

6. 为什么大厂将默认隔离级别设置成RC

7. 解决幻读的两种方式

解决幻读有两种方式：间隙锁和MVCC。https://blog.csdn.net/m0_48847163/article/details/124082312

参考文章

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,214评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,307评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,543评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,221评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,224评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,007评论 1赞 284
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,313评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,956评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,441评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,925评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,018评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,685评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,234评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,240评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,464评论 1赞 261
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,467评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,762评论 2赞 345

推荐阅读更多精彩内容