MySQL中的锁与MVCC

1. MySQL中的锁

MySQL存储引擎：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。此处，着重InnoDB 与 MyISAM。

• MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）

• BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；

• InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

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

1.1 MyISAM

MyISAM存储引擎的表锁可分为：表读锁（共享锁）和表写锁（独占锁），读锁和写锁是互斥的，读写操作是串行的。

MyISAM表的读写操作特点

• 对MyISAM表的读操作，不阻塞其他用户对该表的读请求，但会阻塞对该表的写请求；

  读锁阻塞 写：当一个session使用LOCK TABLE命令给表加读锁后，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，其余session可以查询表中的记录，但更新就会出现锁等待。

• 对 MyISAM表的写操作，会阻塞其他用户对该表的读和写操作；

  写锁阻塞 读写：当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

• MyISAM表的读操作与写操作之间，以及写操作之间是串行的；

Q: 一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

A: 写进程先获得锁。

**本质上，MySQL中写请求优先级高于读请求。因此，即使读请求先到锁等待队列，写请求后到，写请求也会插到读请求前。**

MyISAM表不适用有大量更新操作和查询操作的场景，其原因正是：

**大量的更新操作会造成查询操作很难获得读锁，甚至可能长时间持续阻塞。**配合锁调度能有效减缓此现象。

MyISAM的锁调度

• 通过指定启动参数 low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

1.2 InnoDB

之前在MySQL索引与执行计划中已经提及InnoDB与MyISAM从底层到上层的不同，针对锁相关，主要不同之处为：

• 事务：InnoDB 支持事务； MyISAM不支持事务；
• 锁：InnoDB 提供表锁、行锁； MyISAM提供表锁； InnoDB的行锁实现在索引上，行锁过程会先锁住二级索引再锁住主键索引，而非锁在物理行记录。如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
• 读写性能：InnoDB 适合频繁更新插入，并发大； MyISAM适合频繁查询与插入，更新较少；

InnoDB的行锁实现在索引上，行锁过程会先锁住二级索引再锁住主键索引，而非锁在物理行记录。如果访问没有命中索引，也无法使用行锁，将要退化为表锁。在并发情况下查询条件列未加索引而退化为表锁，明显会出现阻塞。

1.2.1 事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

• 原子性（Actomicity）：事务是一个原子操作单元（不可分割），其对数据的修改，要么全都执行，要么全都不执行（事务回滚）。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态（从一个一致性的状态转移到另一个一致性的状态）。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B+树索引）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。一个事务的执行不能其它事务干扰。
• 持久性（Durable）：事务一旦提交，其对于数据的修改是永久性的，即使出现系统故障也能够保持。

1.2.2 并发事务带来的问题

相较于串行处理，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。

但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。

  例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。

• 脏读（Dirty Reads）：一个事务正在对一行记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；此时，另一个事务也来读取同一行记录，如果不加控制，第二个事务读取了这些“脏”的数据，并以此做进一步的处理，就会产生未提交的数据依赖关系。未提交数据被其余事务读到产生依赖影响。这种现象被形象地叫做“脏读”。

• 不可重复读（Non-Repeatable Reads）：一个事务在读取数据时其数据行已被其余事务修改或删除，造成同一事务中重复读取不一致。这种现象叫做“不可重复读”。

• 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，造成同一事务中重复读取不一致。这种现象就称为“幻读”。

1.2.3 事务隔离级别

• Read Uncommitted(读未提交) 如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据

​ 解决了更新丢失，但还是可能会出现脏读

• Read Committed(读提交) 如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

​ 解决了更新丢失和脏读问题

• Repeatable Read(可重复读取) 可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

​ 解决了更新丢失、脏读、不可重复读、但是还会出现幻读

• Serializable(序列化) 提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。

  序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。

• 总结

  四种隔离级别MySQL均支持，默认隔离级别为Repeatable Read(可重复读取)。

  mysql> SELECT @@tx_isolation;
  +-----------------+
  | @@tx_isolation  |
  +-----------------+
  | REPEATABLE-READ |
  +-----------------+
  1 row in set (0.00 sec)
  

  事务隔离级别	更新丢失	脏读	不可重复读	幻读
  Read Uncommitted(读未提交)	√	×	×	×
  Read Committed(读提交)	√	√	×	×
  Repeatable Read(可重复读取)	√	√	√	×
  Serializable(序列化)	√	√	√	√

1.2.4 InnoDB中的锁归档

InnoDB中的锁归档划分如图所示。重点关注行锁类别：记录锁、间隙锁、临键锁、插入意向锁。

共享锁与排他锁

注意，存在表级的S锁、X锁（如前文MyISAM的表锁），也有行级别的S锁、X锁；

• 共享锁（Shared Locks）：读锁、S锁，S锁间相互兼容；持锁可读；
• 排他锁（Exclusive Locks）：写锁、X锁，X锁间相互互斥；持锁可删、改；

行锁

• 记录锁（Record Locks）：锁定一个行记录；标准行锁逻辑，锁索引；

• 间隙锁（Gap Locks）：锁定一个区间；仅存在于RR模式，解决不可重复读；

• 临键锁（Next-key Locks）：记录锁 + 间隙锁，锁定行记录+区间；配合MVCC解决幻读；

• 插入意向锁（Insert Intention Locks）：插入操作时的一种间隙锁；多事务插入同一间隙的不同位置，不再相互阻塞；

表级锁

• 意向锁（Intention Locks）：控制表中并不存在的行，这些行以后可能存在；意向锁间相互兼容；
  • 意向共享锁(Intention Shared Locks, IS)，它预示着，事务有意向对表中的某些行加共享S锁；

    select * from table where ? lock in share mode; -- lock in share mode 显式加锁的读 设置意向共享锁
    

  • 意向排它锁(Intention Exclusive Locks, IX)，它预示着，事务有意向对表中的某些行加排它X锁；

    select * from table where ? for update; 	   -- for update  显式加锁的读 设置意向排它锁
    

    • 兼容性

      1）意向锁间相互兼容； 2）S锁间相互兼容； 3）X锁互斥一切；

      	S	X	IS	IX
      S	兼容	互斥	兼容	互斥
      X	互斥	互斥	互斥	互斥
      IS	兼容	互斥	兼容	兼容
      IX	互斥	互斥	兼容	兼容

• 自增锁（Auto-inc Locks）：仅出现在AUTO_INCREMENT自增列；前一个事务插入数据完成获取primary key后，其余事务才能执行，事务串行；

• 元数据锁（Meta Data Locks）：是在MySQL server层使用的一种表级别锁，并不是InnoDB引擎中实现的。使用时不需要显式声明。其兼容性同S、X，仅读-读兼容。

  • 当对表进行增删改查操作的时候，会自动加 MDL 读锁；

  • 当要对表做结构变更操作的时候，会自动加 MDL 写锁；

1.3 死锁

不同事务间相互持有对方需要的锁资源，导致事务相互都处于等待对方释放资源状态，都无法进行下去，也不会主动释放其持有的锁资源。事务间资源依赖循环导致的事务无限等待状态。

1.3.1 死锁解决策略

• 策略1：不做操作，等待锁超时，总有事务锁先超时，继而回滚事务，释放资源，其余事务则能进行下去。
• 策略2：使用wait-for graph算法主动进行发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（一般是回滚影响行最小的事务），从而释放锁让其他事务可以继续执行。将参数 innodb_deadlock_detect 设置为 on（默认on），表示开启这个逻辑。

1.3.2 避免死锁的方式

• 减小锁冲突的范围
  • 合理设计索引、使用索引，查询尽可能走索引，减小锁范围，避免索引失效导致锁升级为表锁；
  • 事务拆解，避免长事务，减少一次事务锁定的资源数量，缩短资源锁定时间；
  • 涉及事务加锁的语句尽可能置于事务最后；
  • 避免间隙锁范围过大
    • 业务要求在RR隔离级别之上，可优化缩小查询范围；
    • 业务允许不可重复读和幻读情况下，可使用RC隔离级别；
• 事务的加锁策略优化
  • 事务开启后一次性锁住所有需要资源，避免资源被其他事务抢占；
  • 结合其他事务资源需求，对事务加锁顺序进行优化；

2. MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）一种并发控制机制，在数据库中用快照多版本保存的形式，允许同一数据拥有不同版本，在查询时添加查询约束并获取对应版本数据，以此来控制并发执行的事务，达到控制事务隔离。

• 每行数据都存在一个版本，数据更新时需要更新该版本；
• 修改数据时Copy出当前版本进行随意修改，事务间隔离不可见；
• 保存数据时进行版本号比较操作，若成功（commit），则覆盖原记录；失败则放弃copy，事务回滚；

MVCC 核心思想为保存数据在某个时间点的快照。不同存储引擎的MVCC，实现是不尽相同，典型分类有乐观并发控制和悲观并发控制。MySQL 中 InnoDB 引擎是支持 MVCC的，在应对高并发事务，MVCC 比单纯的加行锁更有效，开销更小。

2.1 InnoDB下的MVCC组成

InnoDB下的MVCC核心组成为undo log 与 read_view 一致性视图

• redo log

  重做日志记录。

  存储事务操作的最新数据记录，即已成功提交事务的最新修改信息，并且会把redo log持久化到磁盘，若系统重启可通过读取redo log恢复最新数据。

• undo log

  撤回日志记录，也称版本链。

  记录了数据在变迁过程中所关联的事务ID，以及数据的多个版本的数据链；

  当前事务未提交之前，undo log保存了当前事务的正在操作的数据记录的所有版本的信息，undo log中的数据可作为数据旧版本快照供其他并发事务进行快照读。每次有其它事务提交对当前数据行的修改，都是添加到undo log中。undo log是由每个数据行的多个不同的版本链接在一起构成的一个记录“链表”。

• read_view 一致性视图

  解决事务中数据版本的可见性问题。

  保存了线程在开启一个事务之后，数据的一个快照点，记录当前事务的状态。主要是保证在多版本存储的数据中进行读取操作需要保证有且只有1个或者0个版本对事务可见，一个逻辑上的概念，不是一个物理视图的概念，具体是由MVCC来实现的，区别于MySQL中手动创建的虚拟表视图（create view）。

2.2 当前读 与 快照读

• 当前读：即加锁读最新，读取记录的最新版本，会加锁保证其他并发事务不能修改当前记录，直至获取锁的事务释放锁；

  使用当前读的操作主要包括：lock in share mode 、 for update显式加锁的读 与 标准增删改等写操作。

  ## 当前读范式
  select * from table where ? lock in share mode; -- lock in share mode 显式加锁的读 设置意向共享锁
  select * from table where ? for update; 	  -- for update  显式加锁的读 设置意向排它锁
  insert into table values (…); -- 标准增删改 默认当前读
  delete from table where ?;		-- 标准增删改 默认当前读
  update table set ? where ?;		-- 标准增删改 默认当前读
  

• 快照读：即不加锁读，读取记录的快照版本而非最新版本，通过MVCC实现；

  InnoDB默认的RR事务隔离级别下，不显式加lock in share mode与for update的读操作都属于快照读，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见；

2.3 InnoDB下的MVCC策略

InnoDB 中 MVCC 的实现方式为：每一行记录都有两个隐藏列：DATA_TRX_ID、DATA_ROLL_PTR（如果没有主键，则还会多一个隐藏的主键列）。

• DATA_TRX_ID

  事务号

  隐藏字段，6 个字节，记录最近更新这条行记录的事务 ID。

• DATA_ROLL_PTR

  回滚指针

  隐藏字段，7个字节，表示指向该行回滚段（rollback segment）的指针，InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

• DB_ROW_ID

  行标识（隐藏单调自增 ID）

  隐藏字段，6 个字节，如果表没有主键，InnoDB 会自动生成一个隐藏主键，因此会出现这个列。另外，每条记录的头信息（record header）里都有一个专门的 bit（deleted_flag）来表示当前记录是否已经被删除。

• DELETE

  BIT位，用于标识当前记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是发生在commit的时候。

Q: MVCC解决脏读问题

A：在RC隔离级别下，MVCC机制下每次查询都会创建一个 read_view 一致性视图 ，做好版本可见性控制，使事务只能看到已提交数据，从而避免看见未提交数据，解决脏读。

Q: MVCC解决不可重复读问题

A: 在RC隔离级别下 ，MVCC机制下每次查询都会创建一个 read_view 一致性视图 ，对于已提交的事务，再次进行重复读会创建一个新的 read_view 一致性视图，因此，还是可能会造成读取过程的不可重复读。在RR隔离级别下 ，事务从启动到结束只创建一个新的 read_view 一致性视图，因此不会出现不可重复读。

Q: MVCC解决幻读问题

A: 当前读 与 快照读 分情况论述，MVCC只能解决快照读的幻读，只有临键锁（Next-key Locks）加持才能解决当前读的幻读。

快照读：通过MVCC来进行控制的，不用加锁。按照MVCC中规定的“语法”进行增删改查等操作，以避免幻读。

当前读：通过临键锁（Next-key Locks）来避免幻读。

最后，推荐阅读

• MySQL锁机制全面解析