mysql数据库索引

MySQL数据库事务，锁和MVCC

2019-10-11  本文已影响0人  伊凡的一天

事务可以用来维护数据库的完整性，保证成批的 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）。

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），每张表都拥有两个隐藏列：

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

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

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

执行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。

注意：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来保证幻读不出现）。

注意：仅仅当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重复的错误再返回失败或者重试。

参考文章

上一篇下一篇

猜你喜欢

热点阅读