数据库并发事务控制 三：mysql数据库MVCC

mysql之innodb的mvcc实现，网上找一个拿来归类，以后继续 blog.csdn.net/chen77716/article/details/6742128 Mysql到底是怎么实现MVCC的？这个问题无数人都在问，但google中并无答案，本文尝试从Mysql源码中寻找答案。 在Mysql中MVCC是在Innodb存储引擎中得

mysql之innodb的mvcc实现，网上找一个拿来归类，以后继续

blog.csdn.net/chen77716/article/details/6742128

Mysql到底是怎么实现MVCC的？这个问题无数人都在问，但google中并无答案，本文尝试从Mysql源码中寻找答案。

在Mysql中MVCC是在Innodb存储引擎中得到支持的，Innodb为每行记录都实现了三个隐藏字段：

• 6字节的事务ID（DB_TRX_ID ）
• 7字节的回滚指针（DB_ROLL_PTR）
• 隐藏的ID

6字节的事物ID用来标识该行所述的事务，7字节的回滚指针需要了解下Innodb的事务模型。

1. Innodb的事务相关概念

为了支持事务，Innbodb引入了下面几个概念：

• redo log
  redo log就是保存执行的SQL语句到一个指定的Log文件，当Mysql执行recovery时重新执行redo log记录的SQL操作即可。当客户端执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。redo log在磁盘上作为一个独立的文件存在，即Innodb的log文件。
• undo log
  与redo log相反，undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。
• rollback segment
  回滚段这个概念来自Oracle的事物模型，在Innodb中，undo log被划分为多个段，具体某行的undo log就保存在某个段中，称为回滚段。可以认为undo log和回滚段是同一意思。
• 锁
  Innodb提供了基于行的锁，如果行的数量非常大，则在高并发下锁的数量也可能会比较大，据Innodb文档说，Innodb对锁进行了空间有效优化，即使并发量高也不会导致内存耗尽。
  对行的锁有分两种：排他锁、共享锁。共享锁针对对，排他锁针对写，完全等同读写锁的概念。如果某个事务在更新某行（排他锁），则其他事物无论是读还是写本行都必须等待；如果某个事物读某行（共享锁），则其他读的事物无需等待，而写事物则需等待。通过共享锁，保证了多读之间的无等待性，但是锁的应用又依赖Mysql的事务隔离级别。
• 隔离级别
  隔离级别用来限制事务直接的交互程度，目前有几个工业标准：
  - READ_UNCOMMITTED：脏读
  - READ_COMMITTED：读提交
  - REPEATABLE_READ：重复读
  - SERIALIZABLE：串行化
  Innodb对四种类型都支持，脏读和串行化应用场景不多，读提交、重复读用的比较广泛，后面会介绍其实现方式。

2. 行的更新过程

下面演示下事务对某行记录的更新过程：

1. 初始数据行

F1～F6是某行列的名字，1～6是其对应的数据。后面三个隐含字段分别对应该行的事务号和回滚指针，假如这条数据是刚INSERT的，可以认为ID为1，其他两个字段为空。

2.事务1更改该行的各字段的值

当事务1更改该行的值时，会进行如下操作：

• 用排他锁锁定该行
• 记录redo log
• 把该行修改前的值Copy到undo log，即上图中下面的行
• 修改当前行的值，填写事务编号，使回滚指针指向undo log中的修改前的行

3.事务2修改该行的值

与事务1相同，此时undo log，中有有两行记录，并且通过回滚指针连在一起。

因此，如果undo log一直不删除，则会通过当前记录的回滚指针回溯到该行创建时的初始内容，所幸的时在Innodb中存在purge线程，它会查询那些比现在最老的活动事务还早的undo log，并删除它们，从而保证undo log文件不至于无限增长。

4. 事务提交

当事务正常提交时Innbod只需要更改事务状态为COMMIT即可，不需做其他额外的工作，而Rollback则稍微复杂点，需要根据当前回滚指针从undo log中找出事务修改前的版本，并恢复。如果事务影响的行非常多，回滚则可能会变的效率不高，根据经验值没事务行数在1000～10000之间，Innodb效率还是非常高的。很显然，Innodb是一个COMMIT效率比Rollback高的存储引擎。据说，Postgress的实现恰好与此相反。

5. Insert Undo log

上述过程确切地说是描述了UPDATE的事务过程，其实undo log分insert和update undo log，因为insert时，原始的数据并不存在，所以回滚时把insert undo log丢弃即可，而update undo log则必须遵守上述过程。

3. 事务级别

众所周知地是更新（update、insert、delete）是一个事务过程，在Innodb中，查询也是一个事务，只读事务。当读写事务并发访问同一行数据时，能读到什么样的内容则依赖事务级别：

• READ_UNCOMMITTED
  读未提交时，读事务直接读取主记录，无论更新事务是否完成
• READ_COMMITTED
  读提交时，读事务每次都读取undo log中最近的版本，因此两次对同一字段的读可能读到不同的数据（幻读），但能保证每次都读到最新的数据。
• REPEATABLE_READ
  每次都读取指定的版本，这样保证不会产生幻读，但可能读不到最新的数据
• SERIALIZABLE
  锁表，读写相互阻塞，使用较少

读事务一般有SELECT语句触发，在Innodb中保证其非阻塞，但带FOR UPDATE的SELECT除外，带FOR UPDATE的SELECT会对行加排他锁，等待更新事务完成后读取其最新内容。就整个Innodb的设计目标来说，就是提供高效的、非阻塞的查询操作。

4. MVCC

上述更新前建立undo log，根据各种策略读取时非阻塞就是MVCC，undo log中的行就是MVCC中的多版本，这个可能与我们所理解的MVCC有较大的出入，一般我们认为MVCC有下面几个特点：

• 每行数据都存在一个版本，每次数据更新时都更新该版本
• 修改时Copy出当前版本随意修改，个事务之间无干扰
• 保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道。。。，而Innodb的实现方式是：

• 事务以排他锁的形式修改原始数据
• 把修改前的数据存放于undo log，通过回滚指针与主数据关联
• 修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

二者最本质的区别是，当修改数据时是否要排他锁定，如果锁定了还算不算是MVCC？

Innodb的实现真算不上MVCC，因为并没有实现核心的多版本共存，undo log中的内容只是串行化的结果，记录了多个事务的过程，不属于多版本共存。但理想的MVCC是难以实现的，当事务仅修改一行记录使用理想的MVCC模式是没有问题的，可以通过比较版本号进行回滚；但当事务影响到多行数据时，理想的MVCC据无能为力了。

比如，如果Transaciton1执行理想的MVCC，修改Row1成功，而修改Row2失败，此时需要回滚Row1，但因为Row1没有被锁定，其数据可能又被Transaction2所修改，如果此时回滚Row1的内容，则会破坏Transaction2的修改结果，导致Transaction2违反ACID。

理想MVCC难以实现的根本原因在于企图通过乐观锁代替二段提交。修改两行数据，但为了保证其一致性，与修改两个分布式系统中的数据并无区别，而二提交是目前这种场景保证一致性的唯一手段。二段提交的本质是锁定，乐观锁的本质是消除锁定，二者矛盾，故理想的MVCC难以真正在实际中被应用，Innodb只是借了MVCC这个名字，提供了读的非阻塞而已。

5.总结

也不是说MVCC就无处可用，对一些一致性要求不高的场景和对单一数据的操作的场景还是可以发挥作用的，比如多个事务同时更改用户在线数，如果某个事务更新失败则重新计算后重试，直至成功。这样使用MVCC会极大地提高并发数，并消除线程锁。

6. 参考资料

• Mysql官网
• http://blog.chinaunix.net/link.php?url=http://forge.mysql.com%2Fwiki%2FMySQL_Internals
• Understanding MySQL Internals

########################################

上面的博文结束了，这个引出了innodb的存储格式，找了两篇放到这儿，遗憾的是看到的多数文档没有引用

MyISAM和InnoDB的行格式

MyISAM有3种行存储格式：fixed/dynamic/compressed；

其中fixed为默认格式，只有当表不包含变长字段(varchar/varbinary/blob/text)时使用，该每行都是固定的，所以很容易获取行在页上的具体位置，存取效率比较高，但是占用磁盘空间较多；

dynamic

每行都有一个行头部，包含bitmap，用以记录那些列为空(NULL列不算为空)；

相比于fixed，其有如下特性：

所有字符串列都是动态存储的，除非长度小于4；

字符类型若长度为0/数字类型为0都会不占用存储空间，由bitmap标注，NULL值不包含在内；

如果要update行，其扩展后很容易导致行链接既而产生碎片，一旦crash若link丢失则比较难恢复，fixed模式update不会产生碎片；

compressed只能通过myisampack创建且为只读；

MyISAM的索引文件包含一个flag记录基表是否正常关闭，如果mysqld启动时指定了--myisam-recover-options，则在打开表时检测并自动修复表

InnoDB行存储

Innodb plugin新引入Barracuda梭子鱼，其包含compressed/dynamic两种行格式，而之前的compact/redundant统属于antelope羚羊；

Barracuda VS antelope

由innodb_file_format(动态)参数决定，目前可选值由Antelope和Barracuda，默认为前者；要想要此参数生效，

因为共享表空间默认为Antelope，因此要想使用Barracuda为默认值，还必须先声明innodb_file_per_table；

Innodb_file_format用于控制行格式，全局变量可动态调整，5.5默认依旧是Antelope；

下面只看antelope格式

Redundant行结构

字段长度偏移列表

记录头信息

列1数据

列2数据

….

行头部为字段长度偏移信息，包括变长和非变长的, 还包含了3个隐藏列：RowID(没有主键时使用)/Transaction ID/Roll Point; 而compact只包含变长的，节约了空间；

冗余行格式没有NULL标志位；对于redundant格式，varchar为Null时不占用空间，但是char为NULL需要占用空间，因为其没有Null标志位；

记录头信息占用6个字节，比compact多1字节；

对于定长char，若为NULL依旧填充整个字段，而varchar为Null时不占用空间；

记录头信息，与compact相比，多了黑体字部分，缺失record_type

名称	长度bit	功能
Deleted_flag	1	是否被删除
Min_rec_flag	1	1则表示该记录为预先被定义的最小记录
N_owned	4	该记录拥有的总记录数
Heap_no	13	索引中该行的排序记录
N_fields	10	记录中列数量
1byte_offs_flag	1	偏移量列表是1字节还是2字节
Next_recorder	16	下一条记录相对位置
()	1	未知
()	1	未知

Create table test(t1 varchar(10), t2 varchar(10), t3 char(10),t4 varchar(10)) charset=latin1 row_format=redundant;

--该表有3个变长列

Insert into test values(‘a’,’bb’,’bb’,’ccc’);

使用hexdump –C –v test.idb查看其二进制代码

--长度偏移列表，

compact行格式

字段长度偏移列表

NULL标志位

记录头信息

列1数据

列2数据

….

5.0引入

行头存放该行内变长字段的length，当列小于255字节时占用1个字节，大于255而小于65535时占用2个字节；故varchar最大长度为2的16次方-1；

第2个指示该行是否有NULL值，占用1字节；NULL列不占用数据存储空间；

记录头信息：5个字节共计40bit，用于链接相邻的记录案的行级锁

名称	长度bit	功能
Deleted_flag	1	是否被删除
Min_rec_flag	1	1则表示该记录为预先被定义的最小记录
N_owned	4	该记录拥有的总记录数
Heap_no	13	索引中该行的排序记录
Record_type	3	行类型 0=普通 1=B+节点指针
Next_recorder	16	下一条记录相对位置
()	1	未知
()	1	未知

除此之外，每页还有两个隐含字段：

DB_TRX_ID：6字节，记录最近的一个事务标示符

DB_ROLL_ID：7字节，指向回滚日志记录

--若没有主键，则还会有DB_ROW_ID：6字节，包含在clustered索引中

创建一个compact行格式的表

Create table test(t1 varchar(10), t2 varchar(10), t3 char(10),t4 varchar(10)) row_format=compact;

--该表有3个变长列

Insert into test values(‘a’,’bb’,’bb’,’ccc’);

使用hexdump –C –v test.idb查看其二进制代码

第一行

03 02 01—变长字段长度列表(逆序)，实际顺序为01 02 03，这也是t1,t2,t4的实际长度

00—Null标志位，第一行没有NULL

00 00 10 00 2c—记录头信息，5字节，后4个字节指向下一个记录next_recorder

00 00 00 2b 68 00—6字节rowid，因为没有主键

00 00 00 00 06 05 –事务ID，6字节

80 00 00 00 32 01 10—回滚指针，7字节

61 –列1

62 62 –列2

62 62 20 20 20 20 20 20 20 20 –列3，char会填充余下部分

63 63 63 –列4

余下的为列数据，其中t3由于采用固定长度，故会填充满10个字节;

第二行

Insert into test values(‘d’,null,null,’fff’);

03 01--变长字段长度列表，逆序

06-- Null标志位，有NULL值，转换为二进制00000110，表示第2/3列为null

……

64—列1数据

66 66 66—列4数据，而第2/3列为NULL不占用存储空间

注：对于redundant格式，varchar为Null时同样不占用空间，但是char为NULL需要占用空间，因为其没有Null标志位

行溢出

Innodb表为IOT，采用了B+数类型，故每个页面至少要存储2行数据，如果行过大则会产生行溢出；

理论上mysql的varchar可存储65525字节，强于oracle的4000，但对于InnoDB其实际上限为65532，且该值为表所有varchar列长度总和；对于utf8字符集，一个字符占3个字节，则其上限又缩小为1/3；

如果强行创建varchar(65535)的字段，在sql_mode不为restricted的情况下，其会被隐式转换为mediumtext；

不论是varchar还是blob/text，只要保证一个16k的页面能容下2行数据，应该不会行溢出；

而一旦行溢出，字段前768字节依旧存放于当前页面，数据一般使用B-tree Node页，而溢出的行存放于Uncompress Blob页；

而barracuda采用了完全行溢出，即只保留字段的前20字节；

###########################

MySQL Antelope和Barracuda的区别分析

http://www.jb51.net/article/52530.htm

这篇文章主要介绍了MySQL Antelope和Barracuda的区别分析,Antelope和Barracude都是一种文件格式,需要的朋友可以参考下

Antelope是innodb-base的文件格式，Barracude是innodb-plugin后引入的文件格式，同时Barracude也支持Antelope文件格式。两者区别在于：

文件格式	支持行格式	特性
Antelope （Innodb-base）	ROW_FORMAT=COMPACT ROW_FORMAT=REDUNDANT	Compact和redumdant的区别在就是在于首部的存存内容区别。 compact的存储格式为首部为一个非NULL的变长字段长度列表 redundant的存储格式为首部是一个字段长度偏移列表（每个字段占用的字节长度及其相应的位移）。 在Antelope中对于变长字段，低于768字节的，不会进行overflow page存储，某些情况下会减少结果集IO.
Barracuda (innodb-plugin)	ROW_FORMAT=DYNAMIC ROW_FORMAT=COMPRESSED	这两者主要是功能上的区别功能上的。　另外在行里的变长字段和Antelope的区别是只存20个字节，其它的overflow page存储。 另外这两都需要开启innodb_file_per_table=1 （这个特性对一些优化还是很有用的）

备注：

这里有一点需要注意，如果要使用压缩，一定需要先使用innodb_file_format =Barracuda格式，不然没作用。

下面我们看一下区别：

复制代码 代码如下:

(testing)root@localhost [(none)]> use wubx;

Database changed

(testing)root@localhost [wubx]> CREATE TABLE t1

-> (c1 INT PRIMARY KEY)

-> ROW_FORMAT=COMPRESSED

-> KEY_BLOCK_SIZE=8;

Query OK, 0 rows affected, 4 warnings (0.01 sec)

报出来4个warnings查看一下报错：

复制代码 代码如下:

(testing)root@localhost [wubx]> show warnings;

+———+——+———————————————————————–+

| Level | Code | Message |

+———+——+———————————————————————–+

| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope. |

| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=8. |

| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. |

| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |

+———+——+———————————————————————–+

4 rows in set (0.00 sec)

从以上报错可以看出来不支持压缩。但看一下表结构如下：

复制代码 代码如下:

(testing)root@localhost [wubx]> show create table t1;

+——-+———————————————————————————————————————————————–+

| Table | Create Table |

+——-+———————————————————————————————————————————————–+

| t1 | CREATE TABLE t1 (

c1 int(11) NOT NULL,

PRIMARY KEY (c1)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |

+——-+———————————————————————————————————————————————–+

1 row in set (0.00 sec)

这个是比较坑的地方，所以在使用压缩需要注意。

复制代码 代码如下:

(testing)root@localhost [wubx]>create table t2 ( c1 int(11) NOT NULL, primary key(c1));

(testing)root@localhost [wubx]> insert into t2 select * from t1;

Query OK, 5417760 rows affected (37.12 sec)

Records: 5417760 Duplicates: 0 Warnings: 0

创建支持压缩的表：

复制代码 代码如下:

(testing)root@localhost [wubx]>SET GLOBAL 　innodb_file_per_table=1

(testing)root@localhost [wubx]>SET GLOBAL innodb_file_format=Barracuda;

(testing)root@localhost [wubx]>CREATE TABLE t3

(c1 INT PRIMARY KEY)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8;

(testing)root@localhost [wubx]> insert into t3 select * from t1;

Query OK, 5417760 rows affected (1 min 10.98 sec)

Records: 5417760 Duplicates: 0 Warnings: 0

看一下表的物理大小如下：

复制代码 代码如下:

-rw-rw—- 1 mysql mysql 8.4K Jul 5 16:58 t1.frm

-rw-rw—- 1 mysql mysql 136M Jul 5 19:40 t1.ibd

-rw-rw—- 1 mysql mysql 8.4K Jul 5 19:43 t2.frm

-rw-rw—- 1 mysql mysql 136M Jul 5 19:44 t2.ibd

-rw-rw—- 1 mysql mysql 8.4K Jul 5 19:46 t3.frm

-rw-rw—- 1 mysql mysql 96M Jul 5 19:47 t3.ibd

可见t1, t2都没进行压缩，　t3是支持压缩的