Mysql存储引擎，事务与锁

最新推荐文章于 2021-11-14 16:10:04 发布

OnlyloveCuracao

最新推荐文章于 2021-11-14 16:10:04 发布

阅读量373

点赞数 3

分类专栏： MySQL

本文链接：https://blog.csdn.net/OnlyloveCuracao/article/details/104603660

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文的主要范围如下：

Mysql常用存储引擎

InnoDB事务特性

InnoDB事务日志redo log、undo log

事务并发的三大问题（读一致性问题）：脏读、不可重复读、幻读

数据库读一致性问题解决方案：事务隔离机制

事务隔离解决方案：锁机制（LBCC）、多版本控制详解（MVCC）

锁分类：共享锁、排它锁、意向锁

数据库锁，到底锁住了什么？

锁算法：记录锁、间隙锁、临界锁

InnoDB引擎中RR隔离级别，是怎么样解决幻读的？

存储引擎

Mysql的存储引擎有很多，不同的存储引擎保存数据和索引的方式是不一样的，每一种存储引擎都有它的优势和劣势。本文只介绍几种常见的存储引擎。

1、MyIsAM

MyISAM采用的是索引与数据分离的形式，将数据保存在三个文件中.frm（表结构）.MYD（数据）.MYI（索引）

提供高速的存储和检索以及全文搜索能力，基于非聚簇索引进行存储的

不支持事务的，使用表锁，读取时对表加上共享锁，在写入时对表加上排他锁，所以在并发写入时，效率会比较低。

表压缩：进行压缩后的表是不能进行修改的，但是压缩表可以极大减少磁盘占用空间，因此也可以减少磁盘IO，从而提供查询性能。

适用场景：

做非常多的count计算

查询非常频繁但是修改非常少

2、InnoDB

默认的存储引擎，在没有特殊情况下，一般都使用InnoDB存储引擎

使用InnoDB时，会将数据表分为.frm（表结构）和 .idb（表数据和索引）两个文件进行存储

InnoDB是典型的事务型存储引擎，具有崩溃恢复能力

使用行锁，也能使用表锁，InnoDB采用MVCC(多版本并发控制，后面有说到)来支持高并发

基于聚簇索引进行存储，聚簇索引对主键的查询有很高的性能，不过它的二级索引(secondary index，非主键索引)中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此表上的索引较多的话，主键应当尽可能的小。

外键约束，MySQL支持外键的存储引擎只有InnoDB

适用场景：

更新和查询都相当的频繁，多重并发

要求事务，或者可靠性要求比较高

3、MEMORY（HEAP）引擎

数据保存在内存中，拥有极高的插入、更新和查询效率，但是不是其并不稳定，重启以后数据都会丢失

不支持事务。使用表锁，因此并发写入的性能较低

memory不支持变长的数据类型（BLOB，TEXT），但VARCHAR除外，因为VARCHAR在MySQL内部当做CHAR类型，所以可以使用

支持HASH和B-Tree索引，默认使用HSAH索引，但是HASH索引，在范围查询时，表现有点不尽人意。

事务

首先，什么是事务？

百度百科上面的解释是：事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

对于这句话抓重点，逻辑单元表示事务是最小的，不可以再拆分的东西，有限的数据库操作序列构成，表示事务可能包含一个或者多个DML语句。

对于事务就不得不提一下事务的四大特性（ACID）

原子性（Atomicity）：这就是事务解释中最小的逻辑单位，表示一个事务要么全部成功要么全部失败。

一致性（Consistent）：指数据库的完整性约束没有被破坏，操作前后都是一个合法的状态，其中完整性约束包含：实体完整性、参照完整性、用户自定义完整性

隔离性（Isolation）：在并发情况下，各个事务之间是互相不干扰的、都是透明的，你做你的我做我的

持久性（Durable）：对数据的修改的操作，只要事务提交成功了，那么这个结果必然是永久性的，不可能说因为重启或者什么原因而回到事务之前的状态

InnoDB事务日志

在数据库中不会把数据直接写入到磁盘，主要因为io的操作效率太低了，对于数据的操作，会先在内存里面修改完成，然后再写入到磁盘，当内存里面的数据跟磁盘里面的数据不一致的时候，我们把这种数据页叫做脏页，把内存的数据同步到磁盘时，我们把这种操作叫做刷脏

redo log：该日志主要帮助事务完成持久性特性。当数据还驻留在内存中时，系统重启了，这个时候不是会出数据丢失的情况？事务已经提交了，但是磁盘却没有数据。这个时候事务是如何保证一致性的呢？对了，就是依靠redo log日志，当在内存中修改数据时，同样会把数据在redo log日志文件中保存一份（持久化），当系统发生奔溃重启后，再利用redo log日志写入到磁盘，就可以保证事务的持久性

undo log：该日志主要完成事务的原子性特性。在操作任何数据之前，首先将数据备份到undo log，然后进行数据的修改，如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态

缺点：每个事务提交前将数据和undo log写入磁盘，这样会导致大量的磁盘io，因此性能很低。如果能够将数据缓存在内存一段时间，就能减少io提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即上面提到的redo log

redo log和undo log一起组成InnoDB事务日志

Mysql事务

基于Mysql5.7（保持默认配置，未更改，存储引擎InnoDB）

如何开启事务？

如果执行以下SQL会开启事务吗?

update student set sname = '张三' where id = 1

答案：有事务，这条语句会自动开启事务并且提交事务

1、自动开启事务，当执行增删改操作时会自动开启事务，输入以下SQL查看是否自动开启事务

2、通过BEGIN;开启事务，COMMIT;或者ROLLBACK;关闭事务，中间带若干条SQL语句

事务的并发

当多个事务同时操作一张表时，如果没有事务的隔离性特性，那么会出现什么情况呢？

没错，会出现：脏读、不可重复读、幻读

脏读：

事务A执行

select * from user where id = 1，结果

id	name	age
1	张三	16

事务B突然执行update user set age = 18 where id = 1，但是并没有commit

事务A再次执行

select * from user where id = 1

id	name	age
1	张三	18

脏读定义：在同一个事务里面，相同的查询得到了不同的结果，是因为读取到了其它事务修改并且未提交的数据而造成的，这种情况我们把它称之为脏读，前面也说过了，修改的数据，会先在内存中修改，我们把这种数据叫做脏数据

不可重复读：

事务A执行

select * from user where id = 1，结果

id	name	age
1	张三	16

事务B突然执行update user set age = 18 where id = 1，并且commit提交了

事务A再次执行

select * from user where id = 1

id	name	age
1	张三	18

不可重复读定义：一个事务的两次一样的执行，却得到不一样的结果，导致不能回到以前的状态，我们称之为不可重复读。它和脏读的区别就是，脏读是未提交，而不可重复读是已提交的数据，导致不一样的结果。（这个时候事务B已经commit了，所以这个数据就不能被叫做脏数据了）

幻读：

事务A执行

select * from user where age>=15，结果

id	name	age
1	张三	16

事务B突然执行insert into user values(2,'李四',22); commit;

事务A再次执行

select * from user where age>=15

id	name	age
1	张三	16
2	李四	22

幻读定义：一个事务两次相同的操作，第二次操作出现了新的数据，就像幻影一样，我们把这种情况叫做幻读。

幻读和不可重复读的区别：不可重复读只针对update和delete语句，幻读只针对insert语句（这里可能有的人把delete语句也归纳为幻读，这个可以去SQL92标准官网上面看，里面有很清晰的定义）

总结：事务并发带来的三大问题，其实都是数据库的读一致性问题，这个必须由数据库提供一定的事务隔离机制来解决

事务隔离级别

在SQL92标准中定义了4种隔离级别，不同的数据库会有不一样的隔离级别，并不会完全按照标准来

Read Uncommitted（RU）：未提交读，可以读取到其它事务未提交的数据，没有解决三大问题

Read Committed（RC）：已提交读，可以读取到其它事务已提交的数据，解决脏读

Repeatable Read（RR）：可重复读，解决脏读、不可重复读

Serializable：串行化，通过锁机制让每个事务排队执行，解决事务并发的所有问题（但性能太低）

在Mysql的InnoDB引擎下，对这四种隔离级别都支持，并且，在Repeatable Read中也解决了幻读问题，即保证了数据库并发安全，也保证了性能（后面会讲到InnoDB引擎RR事务是如何解决幻读问题的）

那么如果要解决读一致性的问题，保证一个事务中前后两次读取数据结果一致，实现事务隔离，应该怎么做呢？

第一种：在读取数据前，对其加锁，阻止其他事务对数据进行修改（LBCC）

第二种：生成一个数据请求时间点的一致性数据快照，并且用这个快照来提供一定级别（语句级或者事务级）的一致性读取（多版本控制MVCC）

多版本控制MVCC

MVCC建立一个快照，到底是怎么样建立的呢？又是怎么样保证事务隔离性的呢？

假设我们的表是这样的

在Mysql，InnoDB引擎的表中，为每一行都实现了三个隐藏的字段

DB_ROW_ID:占6字节，是一个行标识，一般是主键，如果没有主键则是其它列的唯一索引，如果都没有，则是默认的int自增型

DB_TRX_ID:6字节，插入或更新行的最后一个事务的事务ID，自动递增（创建版本号）

DB_ROLL_PTR:7字节，回滚指针（删除版本号）

id	name	DB_ROW_ID	DB_TRX_ID	DB_ROLL_PTR
1	张三	1	01	NULL

我们可以这样子查看一下DB_ROW_ID

好了，现在开始模拟一下MVCC的执行过程

1、开启第1个事务初始化数据，假设事务ID为1

begin;
insert into user values(1,'张三');
insert into user values(2,'李四');
commit;

表
id	name	创建版本（DB_TRX_ID）	删除版本（DB_ROLL_PTR）
1	张三	1	undefined
2	李四	1	undefined

2、开启的第2个事务，不断进行相同的查询，并不提交

begin;
select * from user; 


select * from user；
    。
    。
    。

3、在事务2的第一次查询后，开启事务3，执行以下SQL语句（插入）

begin;
insert into user values(3,"王五");
commit;

表
id	name	创建版本	删除版本
1	张三	1	undefined
2	李四	1	undefined
3	王五	3	undefined

再次执行事务2的查询，查询到以下数据（和上次查询一样）

这是因为，id为3的语句，它的创建版本为3（创建它的事务版本ID为3），而事务2的查询，事务的版本为2，该事务只能查询到事务版本比它低的SQL语句，所以比2低的，就只有id为1和2的，比2低，说明是在本身事务开启之前就存在的

4、开启事务4，执行以下SQL语句（删除）

begin;
delete from user where id = 2;
commit;

表
id	name	创建版本	删除版本
1	张三	1	undefined
2	李四	1	4
3	王五	3	undefined

再次执行事务2的查询，查询到以下数据（和上次查询一样）

这是因为，事务2的查询语句，除了上面的创建版本有要求，还有删除版本也有要求，事务的查询，只能查询到删除版本为空或者大于自己查询版本的数据，事务2的ID为2，比2大的删除版本，说明是在事务2创建之后删除的，所以可以查询到

5、开启事务5，执行以下SQL（修改）

begin;
update user set name="张小三" where id = 1;
commit;

id	name	创建版本	删除版本
1	张三	1	5
2	李四	1	4
3	王五	3	undefined
1	张小三	5	undefined

再次执行事务2的查询，查询到以下数据（和上次查询一样）

这是因为，对于修改前id=1的数据来说，满足创建版本小于自身的查询版本，所以可以查询到，但是对于修改后id=1，它的创建版本大于自身查询的版本了，所以就查不到。

到此MVCC分析完毕，对于上面事务的ID，全都是为了便于理解随便起的，这个时候可能有些同学就有疑问了，在事务5中，出现了两个id=1的数据，这样子不是冲突了？其实并不是的，这里只是我便于大家理解将它们写在一起，对于那些数据，一般都是存在事务日志里面的，也就是前面说的undo log，便于回滚操作，这不就和前面知识的联系起来了

现在说完了MVCC，就该说一下LBCC（锁）

锁

锁的粒度：表锁，行锁

锁定力度：表锁 > 行锁

加锁效率：表锁 > 行锁

冲突概率：表锁 > 行锁

并发性能：表锁 < 行锁

在MySQL5.7的官网里面有以下几种常用的锁

Shared and Exclusive Locks：共享锁和排它锁

Intention Locks：意图锁

行锁的算法

Record Locks：记录锁

Gap Locks：间隙锁

Next-key Locks:临界锁

行锁：

共享锁（Shared Locks，也称为S锁）：又可以叫做读锁，共享锁就是多个事务对同一数据可以共享一把锁，都可以访问到数据，但是不能修改

加锁方式：select * from user where id = 1 LOCK IN SHARE MODE;

释放锁：事务结束就会释放锁，commit/rollback;

验证一下（如果是用dos窗口，直接开多个dos窗口就可以，如果是可视化工具就更简单了）

我们对刚才的user表的id字段加上共享锁，有打印说明加锁成功了，而且我们并没有commit，这个事务还没有结束

我们再在另外一个dos窗口执行同样的SQL语句，同样有打印，说明加锁成功。结论是正确的

排它锁（Exclusive Locks，也称为X锁）：又称为写锁，排它锁不能与其他锁共存，如一个事务获取了一个数据行的排它锁，其它事务就不能再获取改行的任何锁（共享锁，排它锁），只有该获取了排它锁的事务可以对数据行进行读取和修改

加锁方式：手动和自动

自动：delete/update/insert语句，默认加上排它锁

手动：select * from user where id = 1 FOR UPDATE;

释放锁：事务结束就会释放锁，commit/rollback;

验证一下：

我们对刚才的user表的id字段加上排它锁，有打印，说明加锁成功

我们在另外一个dos窗口，执行相同的SQL语句，没有打印信息，说明加锁失败，操作被阻塞了（默认等待50秒，超出则停止等待）。结论是正确的

表锁：

意向锁：意向锁是由数据库引擎自己维护的，用户无法手动操作意向锁，它的作用就相当于一个标志信息一样。

意向共享锁（IS锁）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁之前必须先取得该表的IS锁

意向排它锁（IX锁）：表示事务准备给数据行加入排它锁，事务在一个数据行加排它锁前必须先取得该表的IX锁

那么为什么需要意向锁呢？

其实非常简单，如果有一个表，你需要给这个表加上表锁，那么，它肯定会先逐行去扫描看看其中有没有行锁，如果没有才能加上表锁，但是当数据量非常大时，这个加表锁的效率就太低了，所以，引入意向锁，在加表锁之前，先看看有没有意向锁，如果有，则加表锁失败，如果没有，加表锁成功。相当于一个标志的作用，提高加表锁的效率。

可以通过这个方式个给表加上表锁

锁，到底锁住了什么？

我们所加的锁，到底是锁住了什么东西，是一行数据，还是一个字段？

我们下面来做个试验，检验一下锁，到底锁住了什么

我们创建三张表

1、不使用索引 t1

2、主键索引 t2

3、唯一索引 t3

t1表结构：没有索引

我们开启一个事务对id=3这一行加上行锁，有打印信息，加锁成功

我们再开启一个事务，对id=3这一行加锁，加锁失败

我们再开启一个事务，对id = 1，这一行加锁，发现也失败了

这就很奇怪了，为什么我们对不同行加锁也会失败呢？

当表中没有索引的时候，对表的加行锁的操作，会变成一个表锁，锁定整张表

t2表结构，存在主键索引

开启一个事务，对id = 3的行加上一个行锁，加锁成功

再开启一个事务，对id = 3加上一个行锁，加锁失败

再开启一个事务，对id = 1加上一个行锁，加锁成功

存在主键索引的时候，对不同行加锁是都可以成功

t3表结构，存在主键索引，name列存在唯一索引

开启一个事务，锁定id = 1，加锁成功

开启一个事务，锁定同一行的name = 1，验证是不是锁定一个字段，加锁失败

在加锁的时候，其实锁住的是索引

那么问题来了，1、第一张表（t1）没有索引，那还锁个鬼的索引啊？2、第三张表（t3）有两个索引，为什么我锁住id，再次去锁name，也会失败呢？

首先回答第一个问题

1、一张表有没有可能会没有索引呢？在InnoDB引擎中，前面我们也说过，使用的是聚集索引，聚集索引决定了数据的存放顺序，索引的逻辑顺序和数据的物理顺序一致。一、当一张表有主键索引时，那么它一定是聚集索引；二、当一张表没有主键索引，但是存在唯一索引，且唯一索引没有空值（not null）时，会将第一个唯一索引当做聚集索引来决定数据的存放顺序；三、也就是我们的t1表，既不存在主键索引，也不存在唯一索引，那么会使用数据库表中隐藏的列_rowid，当做聚集索引，所以当你没有使用索引去访问数据的时候，数据库就会把所有的rowid锁住，也就变成了锁表的操作了。也就回答了那个问题，一张表中是不可能没有索引的。

2、两个索引，为什么会冲突呢？这个我们又要回到前面说InnoDB存储引擎的时候，非主键索引必须包含主键索引列，也就是说主键索引包含整个数据行，而非主键索引，除了要包含自身以外，还要包含主键索引列，当我们按照非主键索引列进行查询时，一旦需要其它列的数据，就必须要回到主键索引上，我们把这个操作叫做回表，所以当你锁住name时，它还是会回到id列上面去拿数据，所以就会将id列也锁起来。