05-mysql的存储引擎和事务

最新推荐文章于 2023-10-14 13:23:27 发布

liaoqing1999

最新推荐文章于 2023-10-14 13:23:27 发布

阅读量213

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/liaoqing1999/article/details/110874929

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

mysql的存储引擎和事务

存储引擎

我们都知道，常用的mysql引擎是InnoDB和MyISAM，下面具体讲一下他们之间的区别

InnoDB

InnoDB是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
外键：InnoDB 支持外键。而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
备份：InnoDB 支持在线热备份。
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
其它特性：MyISAM 支持压缩表和空间数据索引。
InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

如何选择

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

Mysql中的锁

行锁与表锁

表锁

锁定粒度是表

（1）意向共享锁（IS）：事务如果想要读某一个数据行，那么必须取得该表的IS锁。
（2）意向排他锁（IX）：事务如果想要对某一个数据行进行更新操作，那么必须先取得该表的IX锁。

（3）加锁方式：

意向共享锁（IS）：LOCK table tableName read;
意向排他锁（IX）：LOCK table tableName write;

（4）MyISAM引擎下的表锁分析：MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁。

行锁

锁定粒度是数据行

（1）共享锁（读锁、S锁）：事务在读某一个数据行时，如果给当前数据行加上S锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。此时只允许其他事务加共享锁，不能加排他锁，获准共享锁的事务只能读数据，不能修改数据。

（2）排他锁（写锁、X锁）：事务在对某一个数据行进行更新操作时，如果给当前数据行加上X锁，那么会禁止其他事务对相同的数据行进行任何加锁动作。获准排他锁的事务既能读数据，又能修改数据。

（3）InnoDB引擎下的行锁分析：

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
- 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
- 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。（悲观锁的实现方式)
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁（主键字段默认是有索引的）。

总结

（1）应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。

（2）InnoDB&Myisam引擎的锁机制：InnoDB支持事务，支持行锁和表锁用的比较多；Myisam不支持事务，只支持表锁。

乐观锁和悲观锁

悲观锁

（1）定义：

正如其名，悲观。实际上就是说在操作数据的时候，总是还怕此时别的事务可能也会修改数据，所以，一旦操作数据，那么就对数据加锁，一旦操作数据，就对当前的数据加锁，防止其他事务操作这个数据。

（2）实现：

select * from table where … for update （写锁）
select * from table where … lock in share mode （读锁）

乐观锁

（1）定义：

正如其名，乐观。实际上就是说在操作数据的时候，不怕别的事务也在修改，总是觉得没事的，不会这么巧的。所以在刚开始操作数据的时候并没有对数据进行加锁。而是等到提交事务时，再对数据做一个检查，判断此时数据是否修改了。如果数据被修改了，那么此时返回错误信息，让用户自己再选择怎样做，一般是回滚事务。

（2）实现：

在修改数据的时候，首先记录下数据的原始值。然后在修改的时候再判断是否这个数据发生改变了。如果改变了说明出错了。此时用户再进行相对应的操作。否则说明没有错误，继续执行。

应用

修改某字段的值时，可以使用乐观锁或者悲观锁。

eg：更新账户金额应该的sql语句写成这样子： update order_info set o.money=money-10

而不应该先查询money计算完成后再进行更新

money = money - 10 ; # 语句1

update order_info set o.money = money # 语句2

注意：如果在语句1设置money的值，那么在mysql准备执行语句2的时候，可能money的值已经被其它事务修改了。如果这样子就会导致数据出错。

间隙锁（gap锁）

定义

在RR级别下，检索某个内容时，会对其间隙加间隙锁。本质上等同于S锁。

分析

比如此时表中有记录（id=6，number=5）与（id=8，number=5），那么此时（id=6，number=5）与（id=8，number=5）之间就是存在间隙的，比如可以插入记录（id=7，number=5）。所以此时如果开启了间隙锁的机制，那么会对（id=6,number=5）与（id=8,number=5）之间的间隙加锁，禁止在间隙添加记录。

核心作用

在mysql的innodb引擎的RR级别下使用间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的。

①防止间隙内有新数据被插入；

②防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）。

innodb自动使用间隙锁的条件

①必须在RR级别下

②当前读而且检索条件必须有索引（eg：主键）

next-key锁

next-key锁其实包含了行锁和间隙锁，即锁定一个范围，并且锁定记录本身。
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）【行锁+gap锁】
分析：
假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
如下数据表与sql语句： select * from emp where empid > 100 for update;
是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
nnoDB使用间隙锁的目的：
- 防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
- 为了满足其恢复和复制的需要。

事务

事务定义

Transaction
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

案例（银行转账）

关于银行账户转账操作，账户转账是一个完整的业务，最小的单元，不可再分————————也就是说银行账户转账是一个事务

以下是银行账户表t_act(账号、余额)，进行转账操作

account	balance
1	500
2	100

转账操作

账户1转账100给账户2

update t_act set balance=400 where account=1;
update t_act set balance=200 where account=2;

以上两台DML语句必须同时成功或者同时失败。最小单元不可再分，当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存中完成的；当第二条DML语句执行成功后，和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，则清空所有的历史操作记录，要完成以上的功能必须借助事务

四大特征（ACID）

原子性(A)：事务是最小单位，不可再分
一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
隔离性(I)：事务A和事务B之间具有隔离性
持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

sql语句

开启事务：Start Transaction，标记事务开始。
事务结束：End Transaction，结束事务
提交事务：Commit Transaction，事务已经成功执行，数据已经处理妥当。
回滚事务：Rollback Transaction，数据处理过程中出错，回滚到没有处理之前的数据状态，或回滚到事务内部的保存点。
保存点：Save Transaction，事务内部设置的保存点，就是事务可以不全部回滚，只回滚到这里，保证事务内部不出错的前提下。

开启和结束标志

开启标志：任何一条DML语句(insert、update、delete)执行，标志事务的开启
结束-提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
结束-回滚：失败的结束，将所有的DML语句操作历史记录全部清空
在MySQL中，默认情况下，事务是自动提交的，也就是说，只要执行一条DML语句就开启了事物，并且提交了事务，即对于InnoDB每一条SQL语言都默认封装成事务，自动提交，

事物与数据库底层数据

在事物进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事物结束的时候，而且是成功的结束的时候，才会修改底层硬盘文件中的数据

事务sql例子

针对上述银行转账例子写下如下事务

-- 建立transfer函数
DELIMITER //
CREATE PROCEDURE transfer()
  BEGIN
	DECLARE t_error INTEGER; -- 描述一个变量
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1; -- 抛出异常时 变量设为1
	START TRANSACTION;
         		 update t_act set balance=400 where account=1;
				 update t_act set balance='200s' where account=2;  -- 异常测试 字符串‘200s’ 不能转换为 int
				 update t_act set balance=200 where account=2;
         IF t_error = 1 THEN
          ROLLBACK;  -- 发送异常回滚
         ELSE
             COMMIT;  -- 正常运行 提交事务
         END IF;
END//

CALL transfer();  -- 调用函数

脏读、不可重复读和幻影读

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
幻影读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

事务隔离

事务和事务之间存在着隔离性

隔离性级别

Read Uncommitted（读取未提交内容）
Read Committed（读取提交内容）
Repeatable Read（可重读）
Serializable（可串行化）

Read Uncommitted（读取未提交内容）

事物A和事物B，事物A未提交的数据，事物B可以读取到，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果
这里读取到的数据叫做“脏数据”
这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别，本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）

Read Committed（读取提交内容）

事物A和事物B，事物A提交的数据，事物B才能读取到
这种隔离级别高于读未提交
换句话说，对方事物提交之后的数据，我当前事物才能读取到
这种级别可以避免“脏数据”，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。
这种隔离级别会导致“不可重复读取” ，因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果
大多数数据库系统的默认隔离级别（但不是MySQL默认的）

Repeatable Read(可重读)

事务A和事务B，事务A提交之后的数据，事务B读取不到
事务B是可重复读取数据
这种隔离级别高于读已提交
换句话说，对方提交之后的数据，我还是读取不到
这种隔离级别可以避免“不可重复读取”，达到可重复读取
比如1点和2点读到数据是同一个
MySQL默认级别 , ,它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
虽然可以达到可重复读取，但是会导致“幻像读”

Serializable（可串行化)

事务A和事务B，事务A在操作数据库时，事务B只能排队等待
这种隔离级别很少使用，吞吐量太低，用户体验差
这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发
它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。

隔离级别和会出现的问题

隔离级别	脏读	不可重复读	幻影读
Read Uncommitted（读取未提交内容）	可能	可能	可能
Read Committed（读取提交内容）	不可能	可能	可能
Repeatable Read(可重读)	不可能	不可能	对InnoDB不可能， InnoDB和Falcon存储引擎通过多版本并发控制机制解决了该问题
Serializable（可串行化)	不可能	不可能	不可能

设置和查看隔离级别

在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。

  – READ-UNCOMMITTED
  – READ-COMMITTED
  – REPEATABLE-READ
  – SERIALIZABLE

  •	例如：
  [mysqld]
  transaction-isolation = READ-COMMITTED

通过命令动态设置隔离级别

隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。
其语法模式为：

  		SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
  		其中的<isolation-level>可以是：
  	–	READ UNCOMMITTED
  	–	READ COMMITTED
  	–	REPEATABLE READ
  	–	SERIALIZABLE
  	•	例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

设置隔离级别的作用范围

•	事务隔离级别的作用范围分为两种： 
–	全局级：对所有的会话有效 
–	会话级：只对当前的会话有效 
•	例如，设置会话级隔离级别为READ COMMITTED ：
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
•	设置全局级隔离级别为READ COMMITTED ： 
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

查看隔离级别

select @@global.tx_isolation; -- 查看系统当前隔离级别
select @@tx_isolation; -- 查看当前会话隔离级别

多版本并发控制机制（MVCC）

定义

多版本并发控制机制(Multi-Version Concurrency Control)-MVCC：一种并发控制机制，在数据库中用来控制并发执行的事务，控制事务隔离进行。

核心思想

MVCC是通过保存数据在某个时间点的快照来进行控制的。使用MVCC就是允许同一个数据记录拥有多个不同的版本。然后在查询时通过添加相对应的约束条件，就可以获取用户想要的对应版本的数据。

用于解决的问题

我们知道，一般是通过并发调度来实现事务隔离，对于并发调度，不同的数据库厂商有不同的实现机制，但基本原理类似，都是通过加锁来保护数据对象不同时被多个事务修改。

多版本的并发控制**(MVCC)相对于传统的基于锁的并发控制主要特点是读不上锁**，这种特性对于读多写少的场景，大大提高了系统的并发度，因此大部分关系型数据库都实现了MVCC。

大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制.事实上,他们都和MVCC–多版本并发控制来一起使用.

大家都应该知道,锁机制可以控制并发操作,但是其系统开销较大,而**MVCC可以在大多数情况下代替行级锁,**使用MVCC,能降低其系统开销.

MVCC是通过保存数据在某个时间点的快照来实现的. 不同存储引擎的MVCC. 不同存储引擎的MVCC实现是不同的,典型的有[悲观并发控制和乐观并发控制.

基本数据结构

redo log

重做日志记录。存储事务操作的最新数据记录，方便日后使用。

undo log

撤回日志记录,也称版本链。当前事务未提交之前，undo log保存了当前事务的正在操作的数据记录的所有版本的信息，undo log中的数据可作为数据旧版本快照供其他并发事务进行快照读。

每次有其它事务提交对当前数据行的修改，都是添加到undo log中。undo log是由每个数据行的多个不同的版本链接在一起构成的一个记录“链表”。

read_view（快照）

定义

会对数据在每个时刻的状态拍成照片记录下来。那么之后获取某时刻的数据时就还是原来的照片上的数据，是不会变的。其实也可以简单理解为是一个版本链的集合，只不过在这里的版本链是经过筛选的。

基本结构

# read_view的基本结构：
read_view->creator_trx_id = 当前事务id; # 当前的事务id
read_view->up_limit_id = 12654;        # 当前活跃事务的最小事务id
read_view->low_limit_id = 12659;       # 当前活跃事务的最大事务id
read_view->trx_ids = [12654, 12659];   # 当前活跃的事务的id列表，又称活跃事务链表。表示在记录当前快照时的所有活跃的、未提交的事务
read_view->m_trx_ids = 2;              # 当前活跃的事务id列表长度

注意：

read_view中包含了活跃事务链表，这个链表表示此时还在活跃的事务，指的是那些在当前快照中还未提交的事务。（注意：新建事务(当前事务)与正在内存中commit 的事务不在活跃事务链表）。
read_view中不会显示所有的数据行，只会显示“可见”的记录。

什么是可见的？

前提：DATA_TRX_ID 表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

读某一行数据的时候，如果发现他的事务id < up_limit_id (活跃id列表最小值)，可见。
如果发现数据的事务id>low_limit_id（ReadView时系统中最大的事务id），不可见。
如果发现数据的事务id在列表范围内
- 如果是id集合中的，不可见
- 如果不在id集合中，可见

更新方式

仅分析RC级别和RR级别，因为MVCC不适用于其它两个隔离级别。

对于Read Committed级别的：
- **基本描述：**每次执行select都会创建新的read_view，更新旧read_view，保证能读取到其他事务已经COMMIT的内容（读提交的语义）；
- **详细分析：**假设当前有事务A和事务A+1并发进行。在当前级别下，事务A每次select的时候会创建新的read_view，此时可以简单理解为事务A会提交，也就是让事务A执行完毕，然后创建一个新的事务比如是事务A+2。这样子的话，因为事务A+2的事务ID肯定是比事务A+1的ID大，所以就能够读取到事务A+1的更新了。那么便可以读取到在创建这个新的read_view之前事务A+1所提交的所有信息。这是RC级别下能读取到其他事务已经COMMIT的内容的原因所在。
对于Repeatable Read级别的：
- 第一次select时更新这个read_view，以后不会再更新，后续所有的select都是复用这个read_view。所以能保证每次读取的一致性，即都是读取第一次读取到的内容（可重复读的语义）。

例子

根据上面的规则，以Repeatable Read举个例子吧，要求读一个值，一直读都是同一个值。
- 这种隔离级别下，开启事务的时候开启一个ReadView，在整个事务过程中都用这个ReadView。
- 我开启是事务是10，ReadView是（4，8, 10），【我自己也活跃】，up_limit_id=10，low_limit_id=4
- 如果我读到一个数据的事务id是1，小于活跃列表的最小值，可见。
  - 为什么？事务开启的时候生成的ReadView，除了4，8，10，其他都已经提交了，所以其实版本<4，以及5、6、7、9，都是肯定在我开启的时候已经提交了。这些版本的的数据，再怎么读都不会变。放心读吧。
- 如果我读到一个数据的事务id是12，说明他在我创建ReadView之后提交的，我不应该看见这个值，应该去undolog里找这个数据的前面的版本，如果找到<4，或者5、6、7、9都是安全的，可以读。
- 如果我读到一个数据事务id在活跃列表的范围内
  - 如果就是活跃的事务之一，比如说是8，说明这个数据在我开启事务之后提交了，这个我不能看见，应该去undo log中找上一个版本来读，假设说是7。
  - 7也是在这个活跃范围里，但是并不是活跃事务之一，这个版本是在开启事务之前提交的，这个我可见。
再举个读已提交RC的例子。
- 这个隔离级别是每次读都采用新的ReadView。
- 开启的事务是10。
- 读一个数据，事务id是9，ReadView活跃id是（4，8，10），按照规则，可见。
- 过一会再读这个数据，发现事务id变成了11，活跃的id还是（4，8，10），但是因为开启了新的ReadView，当前系统最大事务id>11(因为我们已经读到11了嘛)(比如活跃的id已经变成了（4，8，12）)，判断规则是：不在活跃id列表，所以可见。这回就读到了这个数据的新版本了

在mysql中的实现

在mysql中，在实现MVCC时，会为每一个表添加如下几个隐藏的字段：

字段名	大小	说明
DATA_TRX_ID	6字节	标记了最新更新这条行记录的transaction id，每处理一个事务，其值自动设置为当前事务ID（DATA_TRX_ID只有在事务提交之后才会更新）；
DATA_ROLL_PTR	7字节	一个rollback指针，指向当前这一行数据的上一个版本，找之前版本的数据就是通过这个指针，通过这个指针将数据的多个版本连接在一起构成一个undo log版本链；
DB_ROW_ID	6字节	隐含的自增ID，如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引。这是一个用来唯一标识每一行的字段；
DELETE_BIT	1位(1字节）	用于标识当前记录是否被删除，这里的不是真正的删除数据，而是标志出来的删除。真正意义的删除是在commit的时候。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q8simj8g-1618299479743)(https://note.youdao.com/yws/api/personal/file/C1017FF2F23F4E51BA088DB5E2973134?method=download&shareKey=052f32e3f43e1fc0690016519d7dffbb)]

insert

设置新记录的DATA_TRX_ID为当前事务ID，其他的采用默认的。

delete

修改DATA_TRX_ID的值为当前的执行删除操作的事务的ID，然后设置DELETE BIT为True，表示被删除

update

update<==> insert+ delete
用X锁锁定该行（因为是写操作）；
记录redo log：将更新之后的数据记录到redo log中，以便日后使用；
记录undo log：
- 将更新之后的数据记录到undo log中，设置当前数据行的DATA_TRX_ID为当前事务ID，
- 回滚指针DATA_ROLL_PTR指向undo log中的当前数据行更新之前的数据行，
- 同时设置更新之前的数据行的DATA_TRX_ID为当前事务ID，并且设置DELETE BIT为True，表示被删除。

select

如果当前数据行的DELETE BIT为False，只查找版本早于当前事务版本的数据行(也就是数据行的DATA_TRX_ID必须小于等于当前事务的ID)，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行；
如果当前数据行的DELETE BIT为True，表示被删除，那么只能返回DATA_TRX_ID的值大于当前事务的行。获取在当前事务开始之前，还没有被删除的行。

注意：

a、此时就是要去查找read_view，判断其中是否有需要的记录；

b、就算在当前事务提交的时候，也不会读取到DATA_TRX_ID大于当前事务ID的数据记录（而默认情况下，RR隔离级别下，当前事务一commit，就能够读取到其他事务的commit）。这也是MVCC能够解决幻读的原因。

使用MVCC核心优势

（1）在mysql中，使用MVCC本质上是为了在进行读操作的时候代替加锁，减少加锁带来的负担。

（2）在mysql的InnoDB引擎，并且是在RR隔离级别下，通过使用MVCC和gap锁来解决幻读问题（详细解决方式参见下方的第7点）。

MVCC与四大隔离级别的关系的分析：

分析了在MVCC的控制之下，如何实现四大隔离级别。

Read Uncimmitted级别

由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC。原因是MVCC的DATA_TRX_ID只有在事务提交之后才会更新，而在Read uncimmitted级别下，由于是读取未提交的，所以说MVCC在这个级别下是不适用的。

Read Committed级别

查找操作：

分析：假设当前有事务A、事务A+1、数据B（DATA_TRX_ID为A-1）。

事务A进行查找，此时找出事务ID小于它本身的，所以此时数据B可以被找到；
如果在事务A还没有执行完毕的时候，事务A+1对数据B进行了更新操作，那么此时数据B的undo log则被更新为“数据B（DATA_TRX_ID为A+1）-> 数据B（DATA_TRX_ID为A-1）”；
此时如果事务A再次进行查找操作，会更新read_view。更新旧的read_view，并且开启新的事务A+2。那么根据MVCC的规定，就能够找到数据B（DATA_TRX_ID为A+1），可以找到更新之后的。这样子的话就等价于能够读取到别的事务commit的最新的数据记录。这就符合RC级别的语义。

Repeatable Read级别

查找操作：

分析：假设当前有：事务A、事务A+1，数据B（DATA_TRX_ID为A-1）。

事务A进行查找，此时找出事务ID小于它本身的，所以此时数据B可以被找到；
如果在事务A还没有执行完毕的时候，事务A+1对数据B进行了更新操作，那么此时数据B的undo log则被更新为“数据B（DATA_TRX_ID为A+1）-> 数据B（DATA_TRX_ID为A-1）”；
此时如果事务A再次进行查找操作，那么根据MVCC的规定，还是只能找到数据B（DATA_TRX_ID为A-1）（因为B（DATA_TRX_ID为A+1）的事务ID比当前事务A的事务ID大，所以不会被找到），不会找到更新之后的。这样子的话就等价于只能够读取到事务A开始时读取到的数据记录。这就符合RR级别的语义。

Serialization级别

串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题

**总结：**通过上面的分析可得：MVCC只适用于MySQL隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）

MVCC、gap锁解决幻读问题的分析

前提：InnoDB引擎、RR隔离级别（gap锁只存在于这个级别下）

快照读和当前读

快照读：读快照，可以读取数据的所有版本信息，包括旧版本的信息。其实就是读取MVCC中的read_view，同时结合MVCC进行相对应的控制；

select * from table where ?;

当前读：读当前，读取当前数据的最新版本。而且读取到这个数据之后会对这个数据加锁，防止别的事务更改。（分析：在进行写操作的时候就需要进行“当前读”，读取数据记录的最新版本）

select * from table where ? lock in share mode;  # 读锁
select * from table where ? for update;          # 写锁
insert into table values (…); 
update table set ? where ?; 
delete from table where ?;

RC和RR隔离级别下的快照读和当前读

RC隔离级别下，快照读和当前读结果一样，都是读取已提交的最新；
RR隔离级别下，当前读结果是其他事务已经提交的最新结果，快照读是读当前事务之前读到的结果。RR下创建快照读的时机决定了读到的版本。

解决幻读问题

对于快照读：通过MVCC来进行控制的，不用加锁。按照MVCC中规定的“语法”进行增删改查等操作，以避免幻读。（MVCC的具体内容参见上方第1点到第4点的分析）
对于当前读：通过next-key锁（行锁+gap锁）来解决问题的。（next-key锁的分析：mysql中的锁）

特殊语句分析

“MVCC不能根本上解决幻读的情况？”

分析：这句话的含义是指对于快照读，那么是可以通过MVCC来解决的；但是对于当前读，则必须通过next-key锁（行锁+gap锁）来解决。

悲观并发控制和乐观并发控制

一个锁定系统，可以阻止用户以影响其他用户的方式修改数据。

悲观并发控制

如果用户执行的操作导致应用了某个锁，只有这个锁的所有者释放该锁，其他用户才能执行与该锁冲突的操作，这种就是悲观并发控制。它主要用于数据争用激烈的环境中，以及发生并发冲突时用锁保护数据的成本低于回滚事务的成本的环境中。

乐观并发控制
在乐观并发控制中，用户读取数据时不锁定数据。

当一个用户更新数据时，系统将进行检查，查看该用户读取数据后其他用户是否又更改了该数据。如果其他用户更新了数据，将产生一个错误。

一般情况下，收到错误信息的用户将回滚事务并重新开始。这种方法之所以称为乐观并发控制，是由于它主要在以下环境中使用：数据争用不大且偶尔回滚事务的成本低于读取数据时锁定数据的成本。

两者区别

首先假设A和B需要在SCC（Source Code Control）上修改同一个文件

悲观并发控制：假设，那么在A锁定这个文件并修改的过程中，B无法修改这个文件，他只能等待A解锁文件后，他才能修改。由此可见，悲观并发控制是强调控制在前，确保整个过程不会出现文件版本的冲突。这样做会使得系统效率损耗在加锁机制上，尤其是加锁机制需要用到低速的外部存储（比如FileLocking）时，然而这样做就降低了事务的并发性，尤其是事务之间本来就不存在冲突的情况下。例如在A修改数据的时候，B就是只是读，也只能等待。

由此可见，悲观并发控制通过使用显式的加锁机制或者时间戳，对每一个事务进行增量同步校验。如果加锁机制的成本较高的话，悲观并发控制就会出现一些弊端。首先就是效率问题，尤其是使用低效率的外部存储系统实现加锁机制时，这样的问题会更加突出。其次，在不会出现冲突的事务处理（例如只读型事务）中，使用加锁机制就显得没有必要了，这样做只能增加系统负载。再次，这种方式降低了系统的并发性。

乐观并发控制：首先A和B他们都将这个文件获取到自己的机器上，A修改完以后，就把文件上传到SCC上了，此时B也修改完了，当他也打算将文件上传时，系统会告知B，已经有人上传了，并出现一个错误。剩下的问题只能由B手动解决，例如B可以在SCC上将文件中更改的内容再次复制一遍。乐观并发控制使得系统效率损耗在事务的后期处理中，比如B必须手动的去修改他已经修改过的东西，然而这种控制方式在极少出现冲突的多事务处理中显得十分高效。

乐观并发控制将事务分为三个阶段：读取阶段、校验阶段以及写入阶段。在读取阶段，事务将数据写入本地缓冲（如上所述，A和B将文件都获取到自己的机器上），此时不会有任何校验操作；在校验阶段，系统会对所有的事务进行同步校验（比如在A或者B打算，但还没有，往SCC上写入更改后的文件时）；在写入阶段，数据将被最终提交。在完成读取阶段以后，系统会对每个事务分派一个时间戳。

悲观并发控制中一个常见的问题就是死锁。例如A在修改文件T1，B在修改文件T2，他们分别锁定了这两个文件，假设T1和T2内容相关，B在修改T2的时候发现他还需要修改T1，可是T1却被A锁定；与此同时，A在修改T1的时候也发现了他还需要修改T2，可是T2又被B锁定了，这样就出现了死锁。当然，在实际操作中，这种情况可以由A和B协商解决，但是在错综复杂的多事务处理环境中，死锁将使得问题变得非常复杂。

liaoqing1999

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
05-mysql的存储引擎和事务

mysql的存储引擎和事务存储引擎我们都知道，常用的mysql引擎是InnoDB和MyISAM，下面具体讲一下他们之间的区别InnoDBInnoDB是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大
复制链接

扫一扫