MVCC

一、sql基本命令：

存储引擎：

1、查看系统支持的存储引擎

show engines;

2、查看表使用的存储引擎

a、show table status from db_name where name='table_name';

如果已经进入到一个数据库，可以使用 mysql> show table status like 'enterprise_stock_code' \G

b、show create table l_ability_base

(有人说用第二种方法不准确（http://guaniuzhijia.blog.163.com/blog/static/16547206920114129953533/）

如果关闭掉原先默认的Innodb引擎后根本无法执行show create table table_name指令，因为之前建的是Innodb表，关掉后默认用MyISAM引擎，导致Innodb表数据无法被正确读取。（没有测试）)

3、修改表引擎方法

alter table table_name engine=innodb;

 

隔离级别：

1.查看当前会话隔离级别
select @@tx_isolation;
 
2.查看系统当前隔离级别
select @@global.tx_isolation;
 
3.设置当前会话隔离级别
set session transaction isolatin level repeatable read;
 
4.设置系统当前隔离级别
set global transaction isolation level repeatable read;

 

Mysql版本号：

select version();

 

二、MVCC是什么

MySql大多数『事务型存储引擎』的实现都不是简单的行级锁。基于提升并发性能的考虑，他们一般都同时实现了多版本并发控制（MVCC）

不仅是MySQL，包括Oracle, PostgreSQL等其他数据库系统也都实现了MVCC，但各自的实现机制不尽相同，因为MVCC没有一个统一的实现标准。
可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制所有不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

 

问题：什么是事务性存储引擎？还有什么其他类型的存储引擎？

有些存储引擎支持事务，比如InnoDB， 有些存储引擎不支持事务，比如MyIsam。当然还有其他的存储引擎

 

三、MVCC如何实现的

其实，MVCC是通过保存数据在某一时间点的快照来实现的。即，不管是否需要执行多长时间，同一个事务内看到的数据都是一致的。根据事务开始的时间不同，不通的事务对同一张表，同一时刻拿到的数据可能不一样。

MVCC只有在read commited和repeatable read的两个隔离级别下工作。read uncommitted总是读取最新的行数据，而不是符合当前事务版本的数据行。而serializable则会对所有读取的行都加锁。

 

3.1 undo log  

为了便于理解MVCC的实现原理，这里简单介绍一下undo log的工作过程

在不考虑redo log 的情况下利用undo log工作的简化过程为：

序号	动作
1	开始事务
2	记录数据行数据快照到undo log
3	更新数据
4	将undo log写到磁盘
5	将数据写到磁盘
6	提交事务

1）为了保证数据的持久性数据要在事务提交之前持久化，即2）

2）undo log的持久化必须在在数据持久化之前，这样才能保证系统崩溃时，可以用undo log来回滚事务

 

3.2、Innodb中的隐藏列

Innodb通过undo log保存了已更改行的旧版本的信息的快照。
InnoDB的内部实现中为每一行数据增加了三个隐藏列用于实现MVCC。

列名	长度(字节)	含义	作用
DB_TRX_ID	6	行的版本号	插入或更新行的最后一个事务的事务标识符。（删除视为更新，将其标记为已删除）
DB_ROLL_PTR	7	行的删除版本号	写入回滚段的撤消日志记录（若行已更新，则撤消日志记录包含在更新行之前重建行内容所需的信息）
DB_ROW_ID	6		行标识（隐藏单调自增id）

结构

 

3.3 MVCC具体实现过程

（《高性能Mysql》的1.4节）

InnoDB的MVCC，是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间（DB_TRX_ID），一个保存了行的过期时间（DB_ROLL_PTR），（存储的并不是实际的时间值，而是系统版本号）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。在REPEATABLE READ隔离级别下，MVCC具体的操作如下：

 

SELECT
InnoDB会根据以下两个条件检查每行纪录：

1. InnoDB只查找版本(DB_TRX_ID)早于当前事务版本的数据行（行的系统版本号<=事务的系统版本号,这样可以确保数据行要么是在开始之前已经存在了，要么是事务自身插入或修改过的）
2. 行的删除版本号(DB_ROLL_PTR)要么未定义（未更新过），要么大于当前事务版本号（在当前事务开始之后更新的）。这样可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的纪录，才能作为查询结果返回。

 

INSERT
InnoDB为插入的每一行保存当前系统版本号作为行版本号。

 

DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

 

UPDATE
InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为行删除标识。

 

优点：
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好。

缺点：
每行纪录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

 

四、总结

为了实现可串行化，同时避免锁机制存在的各种问题，我们可以采用基于多版本并发控制（Multiversion concurrency control，MVCC）思想的无锁事务机制。人们一般把基于锁的并发控制机制称成为悲观机制，而把MVCC机制称为乐观机制。这是因为锁机制是一种预防性的，读会阻塞写，写也会阻塞读，当锁定粒度较大，时间较长时并发性能就不会太好；而MVCC是一种后验性的，读不阻塞写，写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，从而大大提升了并发性能。

我们可以借用源代码版本控制来理解MVCC，每个人都可以自由地阅读和修改本地的代码，相互之间不会阻塞，只在提交的时候版本控制器会检查冲突，并提示merge。