样运行的--下查询优化explainoptimizer_trace

Pool缓冲池Buffer

Pool的存储结构空闲页存储--free链表脏页修改后的数据存储--flush链表

使用Buffer

日志redo日志简述恢复时保证原子性写入过程redo日志设置参数

ID增删改对应的undo日志INSERT操作对应的undo日志DELETE操作对应的undo日志UPDATE操作对应的undo日志

存储undo日志状态查看Undo页面链表单个事务中的Undo页面链表多个事务中的Undo页面链表

回滚段

事务隔离级别与MVCC-读事务并发的三个问题事务四种隔离级别MVCC原理版本链ReadViewREAD

COMMITTED和REPEATABLE

optimizer_traceenabledon;查看具体的优化过程

SELECT

information_schema.OPTIMIZER_TRACE;这个OPTIMIZER_TRACE表有4个列分别是

QUERY表示我们的查询语句。

TRACE表示优化过程的JSON格式文本。

MISSING_BYTES_BEYOND_MAX_MEM_SIZE由于优化过程可能会输出很多如果超过某个限制时多余的文本将不会被显示这个字段展示了被忽略的文本字节数。

INSUFFICIENT_PRIVILEGES表示是否没有权限查看优化过程默认值是0只有某些特殊情况下才会是1我们暂时不关心这个字段的值。

打开optimizer

information_schema.OPTIMIZER_TRACE;

trace功能关闭

optimizer_traceenabledoff;化过程大致分为了三个阶段

prepare阶段

于单表查询来说我们主要关注optimize阶段的rows_estimation这个过程

对于多表连接查询来说我们更多需要关注considered_execution_plans这个过程

Pool缓冲池

InnoDB的设计者为了存磁盘中的页在MySQL服务器启动的时候就向操作系统申请了一片连续的内存他们给这片内存起了个名叫做Buffer

默认情况下Buffer

Pool只有128M大小。

当然如果你嫌弃这个128M太大或者太小可以在启动服务器的时候配置innodb_buffer_pool_size参数的值是以字节为单位的

Pool也不能太小最小值为5M(当小于该值时会自动设置成5M)。

Buffer

我们就把每个页对应的控制信息占用的一块内存称为一个控制块吧控制块和缓存页是一一对应的它们都被存放到

Buffer

把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中这个链表也可以被称作free链表

为了管理好这个free链表特意为这个链表定义了一个基节点里边儿包含着链表的头节点地址尾节点地址以及当前链表中节点的数量等信息。

这里需要注意的是链表的基节点占用的内存空间并不包含在为Buffer

Pool申请的一大片连续内存空间之内而是单独申请的一块内存空间。

有了这个free链表之后事儿就好办了每当需要从磁盘中加载一个页到Buffer

Pool中时就从free链表中取一个空闲的缓存页并且把该缓存页对应的控制块的信息填上就是该页所在的表空间、页号之类的信息然后把该缓存页对应的free链表节点从链表中移除表示该缓存页已经被使用了

Pool中某个缓存页的数据那它就和磁盘上的页不一致了这样的缓存页也被称为脏页我们创建一个存储脏页的链表凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的所以也叫flush链表数据结构与free链表一样

使用Buffer

页号作为key缓存页作为value创建一个哈希表在需要访问某个页的数据时先从哈希表中根据表空间号

页号看看有没有对应的缓存页如果有直接使用该缓存页就好如果没有那就从free链表中选一个空闲的缓存页然后把磁盘中对应的页加载到该缓存页的位置。

2、修改数据时脏页处理

从flush链表中异步同步脏页数据这种刷新页面的方式被称之为BUF_FLUSH_LIST

从LRU链表的冷数据中刷新一部分页面到磁盘方式被称之为BUF_FLUSH_LRU

3、查看状态

STATUS语句来查看关于InnoDB存储引擎运行过程中的一些状态信息其中就包括Buffer

简单设计

只要我们使用到某个缓存页就把该缓存页调整到LRU链表的头部这样LRU链表尾部就是最近最少使用的缓存页喽

1、预读功能

这两种情况可能并不是常用数据但是会导致很多BufferPool的数据被删掉

设计InnoDB的大佬把这个LRU链表按照一定比例分成两截分别是

一部分存储使用频率非常高的缓存页所以这一部分链表也叫做热数据或者称young区域。

另一部分存储使用频率不是很高的缓存页所以这一部分链表也叫做冷数据或者称old区域。

我们可以通过查看系统变量innodb_old_blocks_pct的值来确定old区域在LRU链表中所占的比例

SHOW

innodb_old_blocks_pct;默认情况下old区域在LRU链表中所占的比例是37%

针对上面两种情况

Pool中的某个缓存页时该缓存页对应的控制块会被放到old区域的头部。

这样针对预读到Buffer

Pool却不进行后续访问的页面就会被逐渐从old区域逐出而不会影响young区域中被使用比较频繁的缓存页。

2、针对全表扫描时在进行全表扫描时虽然首次被加载到Buffer

Pool的页被放到了old区域的头部但是后续会被马上访问到每次进行访问的时候又会把该页放到young区域的头部

在对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间如果后续的访问时间与第一次访问的时间在某个时间间隔内那么该页面就不会被从old区域移动到young区域的头部否则将它移动到young区域的头部。

时间间隔由一下参数决定

innodb_old_blocks_time;更进一步优化LRU链表只有被访问的缓存页位于young区域的1/4的后边才会被移动到LRU链表头部这样就可以降低调整LRU链表的频率从而提升性能。

ACID

事务是原子的要么全部执行成功要么全部失败。

如果事务中的任何一部分失败系统应该回滚到事务开始前的状态以保持数据库的一致性。

事务的执行应该相互隔离一个事务的执行不应该影响其他事务的执行。

这是通过隔离级别来控制的如读未提交、读提交、可重复读和串行化。

Consistency事务执行前后数据库应该保持一致性。

这包括应用事务中定义的业务规则和完整性约束。

例如数据库中的主键、外键和其他约束条件在事务执行前后都应该得到满足。

Durability一旦事务被提交对数据库的更改应该是永久性的并且在数据库发生故障或系统崩溃时也应该能够恢复。

部分提交

BEGIN语句代表开启一个事务后边的单词WORK可有可无。

开启事务后就可以继续写若干条语句这些语句都属于刚刚开启的这个事务。

或者START

TRANSACTION语句和BEGIN语句有着相同的功效都标志着开启一个事务

READ

ONLY标识当前事务是一个只读事务也就是属于该事务的数据库操作只能读取数据而不能修改数据。

READ

WRITE标识当前事务是一个读写事务也就是属于该事务的数据库操作既可以读取数据也可以修改数据。

WITH

TRANSACTION或者BEGIN语句开启了一个事务或者把系统变量autocommit的值设置为OFF时事务就不会进行自动提交。

definition

language缩写为DDL。

当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时隐式使用或修改mysql数据库中的表事务控制或关于锁定的语句使用LOCK

TABLES、UNLOCK

TABLES等关于锁定的语句加载数据的语句比如我们使用LOAD

DATA语句来批量往数据库中导入数据时关于MySQL复制的一些语句START

SLAVE、STOP

redo日志是InnoDB存储引擎的一部分用于保证事务的持久性和恢复能力。

在InnoDB中redo日志是以物理方式记录的而不是直接写入磁盘的数据文件。

它包含了对数据库所做的所有修改操作包括插入、更新和删除等。

redo日志的作用是在发生故障或崩溃时通过重做日志中的操作来恢复数据库到最新的一致状态。

redo日志存储在InnoDB的日志文件中通常称为redo日志文件或者事务日志文件。

在MySQL的数据目录下可以找到以ib_logfile开头的文件例如ib_logfile0、ib_logfile1等。

这些文件就是InnoDB的redo日志文件。

redo日志文件是循环写入的当写满一个日志文件后会继续写入下一个日志文件。

当所有的日志文件都写满后InnoDB会将最旧的日志文件复用覆盖其中的内容。

因此redo日志文件的大小和数量可以通过配置参数进行调整以适应不同的需求。

Pool之后才可以访问如果一个事务对于数据做了修改也是现在BufferPool中生效后面再刷到磁盘中但是如果还没有刷到磁盘中导致在事务提交之后出现了事故那么可能会产生一致性问题。

因此提出了redo日志也叫重写日志。

原理是在提交事务之前把修改了哪些东西记录一下系统奔溃重启时需要按照记录的步骤重新更新数据页即可。

redo日志有两个优势

重启进行恢复时也要保证原子性因此在redo日志中必须要以一个类型为MLOG_MULTI_REC_END结尾在系统奔溃重启进行恢复时只有当解析到类型为MLOG_MULTI_REC_END的redo日志才认为解析到了一组完整的redo日志才会进行恢复。

否则的话直接放弃前面解析到的redo日志。

当然如果只有一条语句那么将会以type字段的第一个比特位为1代表该需要保证原子性的操作只产生了单一的一条redo日志不需要关注MLOG_MULTI_REC_END否则表示该需要保证原子性的操作产生了一系列的redo日志。

通过mtr生成的redo日志都放在了大小为512字节的页中称为block。

结构如下

bufferredo日志缓冲区我们可以通过启动参数innodb_log_buffer_size来指定log

buffer的大小默认是16M刷入到缓存就会找时机将缓存数据刷到磁盘中。

redo日志刷盘时机

下默认有两个名为ib_logfile0和ib_logfile1的文件log

参数调节

我们说过事务需要保证原子性也就是事务中的操作要么全部完成要么什么也不做。

但是偏偏有时候事务执行到一半会出现一些情况比如

情况一事务执行过程中可能遇到各种错误比如服务器本身的错误操作系统错误甚至是突然断电导致的错误。

情况二程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前的事务的执行。

我们要对一条记录做改动时这里的改动可以指INSERT、DELETE、UPDATE都需要留一手

把回滚时所需的东西都给记下来,把这些为了回滚而记录的这些数据称之为撤销日志英文名为undo

InnoDB存储引擎在实际进行增、删、改一条记录时都需要先把对应的undo日志记下来。

一般每对一条记录做一次改动就对应着一条undo日志但在某些更新记录的操作中也可能会对应着2条undo日志。

一个事务在执行过程中可能新增、删除、更新若干条记录也就是说需要记录很多条对应的undo日志这些undo日志会被从0开始编号也就是说根据生成的顺序分别被称为第0号undo日志、第1号undo日志、…、第n号undo日志等这个编号也被称之为undo

这些undo日志是被记录到类型为FIL_PAGE_UNDO_LOG的页面中这些页面可以从系统表空间中分配也可以从一种专门存放undo日志的表空间也就是所谓的undo

事务id

服务器会在内存中维护一个全局变量每当需要为某个事务分配一个事务id时就会把该变量的值当作事务id分配给该事务并且把该变量自增1每当这个变量的值为256的倍数时就会将该变量的值刷新到系统表空间的页号为5的页面中一个称之为Max

Trx

ID的属性处这个属性占用8个字节的存储空间。

当系统下一次重新启动时会将上面提到的Max

Trx

ID属性加载到内存中将该值加上256之后赋值给我们前面提到的全局变量因为在上次关机时该全局变量的值可能大于Max

Trx

聚簇索引的记录除了会保存完整的用户数据以外而且还会自动添加名为trx_id、roll_pointer的隐藏列如果用户没有在表中定义主键以及UNIQUE键还会自动添加一个名为row_id的隐藏列。

就是某个对这个聚簇索引记录做改动的语句所在的事务对应的事务id而已此处的改动可以是INSERT、DELETE、UPDATE操作

查看Table

information_schema.innodb_sys_tables;增删改对应的undo日志

InnoDB设计了一个类型为TRX_UNDO_INSERT_REC的undo日志它的完整结构如下图所示

undo

no在一个事务中是从0开始递增的也就是说只要事务没提交每生成一条undo日志那么该条日志的undo

no就增1。

如果记录中的主键只包含一个列那么在类型为TRX_UNDO_INSERT_REC的undo日志中只需要把该列占用的存储空间大小和真实值记录下来如果记录中的主键包含多个列那么每个列占用的存储空间大小和对应的真实值都需要记录下来图中的len就代表列占用的存储空间大小value就代表列的真实值。

插入两条数据

no为0记录主键占用的存储空间长度为4真实值为1。

画一个示意图就是这样

no为1记录主键占用的存储空间长度为4真实值为2。

画一个示意图就是这样与第一条undo日志对比undo

no和主键各列信息有不同

mark删除标记是InnoDB存储引擎中的一种操作用于标记数据行被删除的状态。

它是InnoDB实现MVCC多版本并发控制的关键机制之一。

在InnoDB中当执行DELETE操作时并不会立即删除数据行而是通过设置一个特殊的删除标记来标记该行为已删除状态。

这个删除标记称为delete

Delete

mark的作用是在事务的隔离级别为可重复读REPEATABLE

READ或更高级别时保证在事务执行期间其他事务不会读取到已删除的数据行。

当一个事务需要读取数据时InnoDB会根据事务的隔离级别判断是否需要忽略已经被删除的数据行。

如果事务的隔离级别为可重复读或更高级别InnoDB会使用delete

mark来判断数据行是否可见。

如果数据行被标记为已删除则InnoDB会忽略这些数据行不将其包含在查询结果中。

mark并不是永久性的删除数据行而是在事务提交后由后台的purge线程负责清理已删除的数据行。

purge线程会根据事务的提交时间和MVCC的版本链来判断哪些数据行可以被永久删除。

总结起来delete

mark是InnoDB存储引擎中的一种操作用于标记已删除的数据行。

它是实现MVCC的关键机制之一用于保证在事务的隔离级别为可重复读或更高级别时其他事务不会读取到已删除的数据行。

删除标记并不是永久性的删除数据行而是在事务提交后由purge线程清理。

插入到页面中的记录会根据记录头信息中的next_record属性组成一个单向链表我们把这个链表称之为正常记录链表

被删除的记录其实也会根据记录头信息中的next_record属性组成一个链表只不过这个链表中的记录占用的存储空间可以被重新利用所以也称这个链表为垃圾链表Page

Header部分有一个称之为PAGE_FREE的属性它指向由被删除记录组成的垃圾链表中的头节点。

删除流程如下

1、仅仅将记录的delete_mask标识位设置为1其他的不做修改

正常记录链表中的最后一条记录的delete_mask值被设置为1但是并没有被加入到垃圾链表。

也就是此时记录处于一个中间状态

在删除语句所在的事务提交之前被删除的记录一直都处于这种所谓的中间状态。

2、当该删除语句所在的事务提交之后会有专门的线程后来真正的把记录删除掉。

所谓真正的删除就是把该记录从正常记录链表中移除并且加入到垃圾链表中然后还要调整一些页面的其他信息比如页面中的用户记录数量PAGE_N_RECS、上次插入记录的位置PAGE_LAST_INSERT、垃圾链表头节点的指针PAGE_FREE、页面中可重用的字节数量PAGE_GARBAGE、还有页目录的一些信息等等。

设计InnoDB的大佬把这个阶段称之为purge。

1、在对一条记录进行delete

mark操作前需要把该记录的旧的trx_id和roll_pointer隐藏列的值都给记到对应的undo日志中来

删除一条记录

这条undo日志是id为100的事务中产生的第3条undo日志所以它对应的undo

mark操作时记录的trx_id隐藏列的值是100本次事务的id所以把100填入old

trx_id属性中。

然后把记录的roll_pointer隐藏列的值取出来填入old

roll_pointer属性值找到最近一次对该记录做改动时产生的undo日志。

由于undo_demo表中有2个索引一个是聚簇索引一个是二级索引idx_key1。

只要是包含在索引中的列那么这个列在记录中的位置pos占用存储空间大小len和实际值value就需要存储到undo日志中。

对于主键来说只包含一个id列存储到undo日志中的相关信息分别是

posid列是主键也就是在记录的第一个列它对应的pos值为0。

pos占用1个字节来存储。

lenid列的类型为INT占用4个字节所以len的值为4。

len占用1个字节来存储。

value在被删除的记录中id列的值为1也就是value的值为1。

value占用4个字节来存储。

在执行UPDATE语句时InnoDB对更新主键和不更新主键这两种情况有截然不同的处理方案。

1、就地更新in-place

mark操作而是真正的删除掉也就是把这条记录从正常记录链表中移除并加入到垃圾链表中

1、将旧记录进行delete

2、根据更新后各列的值创建一条新记录并将其插入到聚簇索引中需重新定位插入的位置。

存储undo日志

undo日志默认存储在数据目录下的系统表空间文件中。

系统表空间文件的默认命名为ibdata1。

从MySQL

5.7.6版本开始InnoDB引入了多表空间的概念可以将Undo表空间存储在独立的文件中而不再与系统表空间混合。

这样可以更好地管理Undo表空间的大小和回收。

表空间的具体数据都是以页的形式存储的undo日志也不例外被称为FIL_PAGE_UNDO_LOG的页

状态查看

Status部分并查找到TRANSACTIONS标签下的History

list

因为一个事务可能包含多个语句而且一个语句可能对若干条记录进行改动而对每条记录进行改动前都需要记录1条或2条的undo日志所以在一个事务执行过程中可能产生很多undo日志这些日志可能一个页面放不下需要放到多个页面中这些页面就通过我们上面介绍的TRX_UNDO_PAGE_NODE属性连成了链表

在一个事务执行过程中可能混着执行INSERT、DELETE、UPDATE语句也就意味着会产生不同类型的undo日志。

但是我们前面又强调过同一个Undo页面要么只存储TRX_UNDO_INSERT大类的undo日志要么只存储TRX_UNDO_UPDATE大类的undo日志反正不能混着存所以在一个事务执行过程中就可能需要2个Undo页面的链表一个称之为insert

undo链表另一个称之为update

另外设计InnoDB的大佬规定对普通表和临时表的记录改动时产生的undo日志要分别记录我们稍后阐释为什么这么做所以在一个事务中最多有4个以Undo页面为节点组成的链。

刚刚开启事务时一个Undo页面链表也不分配。

当事务执行过程中向普通表中插入记录或者执行更新记录主键的操作之后就会为其分配一个普通表的insert

undo链表。

当事务执行过程中删除或者更新了普通表中的记录之后就会为其分配一个普通表的update

undo链表。

当事务执行过程中向临时表中插入记录或者执行更新记录主键的操作之后就会为其分配一个临时表的insert

undo链表。

当事务执行过程中删除或者更新了临时表中的记录之后就会为其分配一个临时表的update

总结一句就是按需分配什么时候需要什么时候再分配不需要就不分配。

为了尽可能提高undo日志的写入效率不同事务执行过程中产生的undo日志需要被写入到不同的Undo页面链表中。

基本流程

Header每一个Undo页面链表都对应着一个段称之为Undo

Log

一个事务在向Undo页面中写入undo日志时的方式是十分简单暴力的就是直接往里怼写完一条紧接着写另一条各条undo日志之间是亲密无间的。

写完一个Undo页面后再从段里申请一个新页面然后把这个页面插入到Undo页面链表中继续往这个新申请的页面中写。

Undo页面链表的第一个页面在真正写入undo日志前其实都会被填充Undo

Page

undo链表中只存储类型为TRX_UNDO_INSERT_REC的undo日志这种类型的undo日志在事务提交之后就没用了就可以被清除掉。

所以在某个事务提交后重用这个事务的insert

undo链表这个链表中只有一个页面时可以直接把之前事务写入的一组undo日志覆盖掉从头开始写入新事务的一组undo日志

3、update

undo链表中的undo日志也不能立即删除掉这些日志用于MVCC。

所以如果之后的事务想重用update

undo链表时就不能覆盖之前事务写入的undo日志。

这样就相当于在同一个Undo页面中写入了多组的undo日志

回滚段

原因一个事务只能读到另一个已经提交的事务修改过的数据并且其他事务每对该数据进行一次修改并提交后该事务都能查询得到最新值

原因个事务先根据某些条件查询出一些记录之后另一个事务又向表中插入了符合这些条件的记录原先的事务再次按照该条件查询时能把另一个事务插入的记录也读出来

严重性脏写

UNCOMMITTED未提交读。

可能发生脏读、不可重复读和幻读问题。

READ

COMMITTED已提交读可能发生不可重复读和幻读问题但是不可以发生脏读问题。

REPEATABLE

READ可重复读可能发生幻读问题但是不可以发生脏读和不可重复读的问题。

SERIALIZABLE可串行化各种问题都不可以发生。

Oracle就只支持READ

对于使用InnoDB存储引擎的表来说它的聚簇索引记录中都包含两个必要的隐藏列row_id并不是必要的我们创建的表中有主键或者非NULL的UNIQUE键时都不会包含row_id列

trx_id每次一个事务对某条聚簇索引记录进行改动时都会把该事务的事务id赋值给trx_id隐藏列。

roll_pointer每次对某条聚簇索引记录进行改动时都会把旧的版本写入到undo日志中然后这个隐藏列就相当于一个指针可以通过它来找到该记录修改前的信息。

随着更新次数的增多所有的版本都会被roll_pointer属性连接成一个链表我们把这个链表称之为版本链版本链的头节点就是当前记录最新的值。

另外每个版本中还包含生成该版本时对应的事务id

ReadView

UNCOMMITTED隔离级别的事务来说由于可以读到未提交事务修改过的记录所以直接读取记录的最新版本就好了

2、对于使用SERIALIZABLE隔离级别的事务来说设计InnoDB规定使用加锁的方式来访问记录

3、对于使用READ

READ隔离级别的事务来说都必须保证读到已经提交了的事务修改过的记录也就是说假如另一个事务已经修改了记录但是尚未提交是不能直接读取最新版本的记录的

为了完成上述的不同阶段的读取要求需要判断一下版本链中的哪个版本是当前事务可见的提出了一个ReadView的概念这个ReadView中主要包含4个比较重要的内容

m_ids表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。

min_trx_id表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id也就是m_ids中的最小值。

max_trx_id表示生成ReadView时系统中应该分配给下一个事务的id值。

creator_trx_id表示生成该ReadView的事务的事务id。

这四个属性的作用如下m_ids用于存储当前活跃的读写事务的id这些事务可能正在修改数据因此需要排除在当前事务之外。

min_trx_id用于确定当前事务能够看到的最早的版本即所有事务id大于min_trx_id的事务所做的修改对于当前事务都是不可见的。

max_trx_id用于确定当前事务的id如果一个事务的id小于max_trx_id那么这个事务在当前事务开始之前就已经提交了所以对当前事务来说是可见的。

creator_trx_id就是用来标识这个可见版本是由哪个事务创建的。

如果这个事务已经提交了那么creator_trx_id就是这个事务的ID如果这个事务还没有提交那么creator_trx_id就是这个事务的ID加上一个特殊的值表示这个版本是由一个未提交的事务创建的。

当一个事务要访问某个数据时它会检查这个数据的版本链只需要按照下面的步骤判断记录的某个版本是否可见如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同意味着当前事务在访问它自己修改过的记录所以该版本可以被当前事务访问。

如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值表明生成该版本的事务在当前事务生成ReadView前已经提交所以该版本可以被当前事务访问。

如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值表明生成该版本的事务在当前事务生成ReadView后才开启所以该版本不可以被当前事务访问。

如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间那就需要判断一下trx_id属性值是不是在m_ids列表中如果在说明创建ReadView时生成该版本的事务还是活跃的该版本不可以被访问如果不在说明创建ReadView时生成该版本的事务已经被提交该版本可以被访问。

如果某个版本的数据对当前事务不可见的话那就顺着版本链找到下一个版本的数据继续按照上面的步骤判断可见性依此类推直到版本链中的最后一个版本。

如果最后一个版本也不可见的话那么就意味着该条记录对该事务完全不可见查询结果就不包含该记录。

READ

READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

在READ

COMMITTED隔离级别中ReadView是在每个SQL语句执行前都会生成的。

这意味着在事务中的同一个查询可能会返回不同的结果因为每次查询都会看到最新的数据。

而在REPEATABLE

READ隔离级别中ReadView是在事务开始时就生成的并且会一直保持到事务结束。

这就保证了在同一个事务中所有的查询都将看到同样版本的数据。

事务A

COMMITTED隔离级别下执行那么事务B可能会在事务A提交后才看到新插入的数据。

但是如果这两个事务在REPEATABLE

READ隔离级别下执行那么事务B将始终看不到新插入的数据因为它的ReadView是在事务开始时生成的。

Concurrency

READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程这样子可以使不同事务的读-写、写-读操作并发执行从而提升系统性能。

READ

COMMITTD、REPEATABLE

READ这两个隔离级别的一个很大不同就是生成ReadView的时机不同READ

COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView而REPEATABLE

READ只在第一次进行普通SELECT操作前生成一个ReadView之后的查询操作都重复使用这个ReadView就好了。

锁-写

怎么解决脏读、不可重复读、幻读这些问题呢其实有两种可选的解决方案

一致性读

事务利用MVCC进行的读取操作称之为一致性读或者一致性无锁读有的地方也称之为快照读。

所有普通的SELECT语句plain

SELECT在READ

一致性读并不会对表中的任何记录做加锁操作其他事务可以自由的对表中的记录做改动。

锁定读Locking

Locks简称S锁。

在事务要读取一条记录时需要先获取该记录的S锁。

也称为读锁

Locks简称X锁。

在事务要改动一条记录时需要先获取该记录的X锁也称为写锁

两者兼容性如下

对一条记录做DELETE操作的过程其实是先在B树中定位到这条记录的位置然后获取一下这条记录的X锁然后再执行delete

mark操作

如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化则先在B树中定位到这条记录的位置然后再获取一下记录的X锁最后在原记录的位置进行修改操作。

其实我们也可以把这个定位待修改记录在B树中位置的过程看成是一个获取X锁的锁定读。

如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化则先在B树中定位到这条记录的位置然后获取一下记录的X锁将该记录彻底删除掉就是把记录彻底移入垃圾链表最后再插入一条新记录。

这个定位待修改记录在B树中位置的过程看成是一个获取X锁的锁定读新插入的记录由INSERT操作提供的隐式锁进行保护。

如果修改了该记录的键值则相当于在原记录上做DELETE操作之后再来一次INSERT操作加锁操作就需要按照DELETE和INSERT的规则进行了。

INSERT

一般情况下新插入一条记录的操作并不加锁不过InnoDB通过一种称之为隐式锁的东西来保护这条新插入的记录在本事务提交前不被别的事务访问

多粒度锁

前面提到的锁都是针对记录的也可以被称之为行级锁或者行锁对一条记录加锁影响的也只是这条记录而已我们就说这个锁的粒度比较细其实一个事务也可以在表级别进行加锁自然就被称之为表级锁或者表锁

意向共享锁英文名Intention

Lock简称IS锁。

当事务准备在某条记录上加S锁时需要先在表级别加一个IS锁。

这样可以避免其他事务在获取同一张表中某些记录的排他锁X锁从而提高了并发性能。

意向独占锁英文名Intention

Exclusive

Lock简称IX锁。

当事务准备在某条记录上加X锁时需要先在表级别加一个IX锁。

这样可以避免其他事务在获取同一张表中某些记录的共享锁或独占锁从而提高了并发性能。

假设我们有一个表T有两行数据A和B。

如果事务T1想要获取A和B的共享锁它需要在表级别加一个IS锁。

如果此时另一个事务T2想要获取A或B的独占锁它必须等待T1释放IS锁后才能获取IX锁。

这样可以防止两个事务同时修改同一行数据从而保证了数据的一致性。

IS、IX锁是表级锁它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁以避免用遍历的方式来查看表中有没有上锁的记录也就是说其实IS锁和IX锁是兼容的IX锁和IX锁是兼容的

记录锁

当一个事务锁定了一条记录时其他事务不能修改或删除这条记录直到第一个事务提交或回滚其更改。

记录锁是基于索引的也就是说当对某个索引上的数据行进行操作时MySQL会自动获取或释放相应的记录锁

对于使用非唯一索引的数据行InnoDB引擎使用的是共享锁Shared

对于唯一索引的数据行InnoDB引擎使用的是排他锁Exclusive

Gap

Locks是一种锁定机制用于防止幻读。

当一个事务执行范围查询例如SELECT

WHERE

UPDATE时InnoDB会对查询结果集中的每个行加上一个gap

虽然有共享gap锁和独占gap锁这样的说法但是它们起到的作用都是相同的

Next-Key

Lock是一个组合锁将记录锁和间隙锁结合在一起。

记录锁Record

Lock用于锁定查询范围内的已存在的数据行以确保其他事务无对数据行进行修改或删除。

间隙锁Gap

Lock用于锁定查询范围之间的间隙以防止其他事务在范围内插入新的数据行。

Next-Key

Lock可以同时锁定一个范围内的数据和间隙以保证查询结果的一致性和避免幻读Phantom

根据范围查询的起始点和结束点InnoDB会自动获取和释放Next-Key

它既能保护该条记录又能阻止别的事务将新记录插入被保护记录前面的间隙。

插入意向锁

锁所在的事务信息不论是表锁还是行锁都是在事务执行过程中生成的哪个事务生成了这个锁结构这里就记载着这个事务的信息。

索引信息对于行锁来说需要记录一下加锁的记录是属于哪个索引的。

锁行锁信息

Space

n_bits对于行锁来说一条记录就对应着一个比特位一个页面中包含很多记录用不同的比特位来区分到底是哪一条记录加了锁。

为此在行锁结构的末尾放置了一堆比特位这个n_bits属性代表使用了多少比特位。

type_mode这是一个32位的数被分成了lock_mode、lock_type和rec_lock_type三个部分锁的模式lock_mode占用低4位可选的值如下

LOCK_AUTO_INC十进制的4表示AUTO-INC锁。

锁的类型lock_type占用第58位不过现阶段只有第5位和第6位被使用

LOCK_TABLE十进制的16也就是当第5个比特位置为1时表示表级锁。

LOCK_REC十进制的32也就是当第6个比特位置为1时表示行级锁。

行锁的具体类型rec_lock_type使用其余的位来表示。

只有在lock_type的值为LOCK_REC时也就是只有在该锁为行级锁时才会被细分为更多的类型LOCK_WAIT十进制的256

也就是当第9个比特位置为1时表示is_waiting为true也就是当前事务尚未获取到锁处在等待状态当这个比特位为0时表示is_waiting为false也就是当前事务获取锁成功。

摘录链接