MVCC

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/yangguosb/article/details/79950781

MVCC是什么?

  MVCC (Multiversion Concurrency Control),即多版本并发控制技术,它使得大部分支持行锁的事务引擎,不再单纯的使用行锁来进行数据库的并发控制,取而代之的是把数据库的行锁与行的多个版本结合起来,只需要很小的开销,就可以实现非锁定读,从而大大提高数据库系统的并发性能
这里写图片描述

MVCC的作用

  读不阻塞写,提高并发性,主要是读并发性;
这里写图片描述

MVCC实现原理(Innodb引擎)

核心思想:每条数据有多个数据快照,对应不同的版本(使用事务ID标识),事务A读物的数据为事务A的ID对应的数据快照,事务B写入数据时生成新的数据快照,并用事务B的ID进行标识。

隐藏列

Innodb引擎中,每条记录有隐藏的3列:

  1. DB_TRX_ID:事务ID;
  2. DB_ROLL_PTR :回滚指针;
  3. DB_ROW_ID :列ID;

Internally, InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

    这里写图片描述

使用规则

  1. SELECT:创建事务ID <= 当前事务ID <= 删除事务ID;
  2. DELETE:更新待删除记录的删除事务ID;
  3. UPDATE:新插入一条记录,将创建事务ID设为当前事务ID,同时更新原来待更新记录的删除事务ID为当前事务ID;

具体示例请查看这里

数据快照的存储

不同版本的数据快照存储在undo.log文件中。

MVCC使用场景

Innodb引擎处理事务隔离级别为“ READ COMMITTED”和“ REPEATABLE READ”的查询语句时,默认使用MVCC,以实现非阻塞一致性读。

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

参考:

  1. https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html
  2. https://blog.csdn.net/bigtree_3721/article/details/68942273
  3. https://www.cnblogs.com/chenpingzhao/p/5065316.html
  4. https://liuzhengyang.github.io/2017/04/18/innodb-mvcc/
阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页