66 MVCC 多版本控制模型原理与间隙锁，悲观锁。乐观锁

1 MYSQL 事务隔离级别 四种隔离级别： 脏读，可重复读，幻读总结
2，详细画图分析 MVCC 多版本并发控制与UndoLog 底层原理；
3， 超详细总结行锁 for update ,间隙锁，悲观锁与乐观锁机制原理 总结
4， MySQL 性能调优与监控工具面试总结

MYSql 默认事务隔离级别； 可重复读 会发生幻读

事务隔离级别： 多个不同的session 直接事务如何管理：

读已提交：
读未提交： 脏读
可重复读： 避免脏读
串行化：

事务：
行锁： commit 或者rollback 释放
可重复读： 在同一个事务中，最开始与结束前查询到的数据都是一致的。
原理采用mvcc 多版本控制原理： 无锁方式+快照版本 Read view undolog 日志；
此时允许幻读，单不允许重复读和脏读；

mysql三种日志：
Binlog mysql 自带二进制文件 主从复制集群
readlog innodb 缓存页的日志
undolog 事务日志 底层 记忆集链

mvcc 多版本控制原理：
一张表中： 三个隐藏列：
Rowid： 隐含是自增ID(隐藏主键)，如果数据表没有主键，InnoDb 会自动以DB_ROW_ID 产生一个聚簇索引 6个字节
DB_TRX_ID： 当前全局事务id
DB_Roll_PTR: 回滚 id 修改/delete

MVCC多版本控制原理

简单回顾：MySQL InnoDB 引擎默认为REPEATABLE READ

  1. 同一个事务中，多次查询的结果还是原来数据 底层采用MVCC多版本控制机制实现，读取原来快照数据。

  2. 此时允许幻读，但不允许重复读与脏读。

MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存，就是同一份数据临时保留多版本的一种方式，进而实现并发控制

如果是传统锁的方式保证实现多版本控制，需要通过锁的方式实现，效率非常低，MVCC 使用了一种不同的手段， 无锁并发控制每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。

MVCC插入流程(insert)：

在MySQL中建表时，每个表都会有三列隐藏记录，其中和MVCC有关系的有两列

1.DB_TRX_ID

6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

2.DB_ROLL_PTR

7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

3.DB_ROW_ID

6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

MVCC新增

在插入数据的时候，假设系统的全局事务ID从1开始，

|

set tx_isolation='repeatable-read';

begin;-- 获取全局事务id

INSERT INTO mayikt_account VALUES (null, 'xiaomin', '600');

INSERT INTO mayikt_account VALUES (null, 'xiaoan', '600');

commit;

|

底层表结构

[图片上传失败...(image-64a967-1604282132901)]

插入的过程中会把全局事务ID记录到列 DB_TRX_ID 中去

MVCC删除

表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）

|

begin; -- 获得全局事务ID = 3

delete mayikt_account where id= 2;

commit;

|

MVCC修改

修改逻辑和删除逻辑有点相似，修改数据的时候会先复制一条当前记录行数据，同事标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。

|

begin;-- 获取全局系统事务ID 假设为 7

update mayikt_account set balance=700 where id=1;

commit;

|

MVCC查询

查询规则：

  1. 查找数据行版本号早于当前事务版本号的数据行记录

数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据

  1. 查找删除版本号要么为NULL，要么大于当前事务版本号的记录

这样确保查询出来的数据行记录在事务开启之前没有被删除

查询与新增原理

sessionA

|

begin;-- 获取全局事务id 1

-- select * from mayikt_account where DB_TRX_ID=1

select * from mayikt_account

commit;

|
| |

底层查询： -- select * from mayikt_account where DB_TRX_ID<=1

sessionB;

DB_TRX_ID=2；

|

begin; -- 获取全局事务id 2

INSERT INTO mayikt_account VALUES (null, 'yushengjun889', '500');

commit;

|

查询与修改原理

sessionA

|

begin;-- 获取全局事务id 1

-- select * from mayikt_account where DB_TRX_ID=1

select * from mayikt_account

commit;

|
| |

底层查询： -- select * from mayikt_account where DB_TRX_ID<=1

sessionB;

DB_TRX_ID=2；

|

begin; -- 获取全局事务id 2

update mayikt_account set balance=balance-50 where id = 1;

commit;

|

查询与delete原理

sessionA

|

begin;-- 获取全局事务id 1

-- select * from mayikt_account

select * from mayikt_account

commit;

|

sessionB

|

begin; -- 获取全局事务id 2

delete from mayikt_account where id=1;

commit;

|

底层实现原理

Redolog日志 innodb引擎数据页缓存日志

Undolog日志 事务回滚的日志 MVCC结合Undolog实现

Binlog日志 mysql 二进制文件

隐式字段、undo日志、Read View来实现；

DB_TRX_ID

6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

DB_ROLL_PTR

7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

DB_ROW_ID

6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

[图片上传失败...(image-19b8ad-1604282132900)]##### MVCC多版本控制原理

简单回顾：MySQL InnoDB 引擎默认为REPEATABLE READ

  1. 同一个事务中，多次查询的结果还是原来数据 底层采用MVCC多版本控制机制实现，读取原来快照数据。

  2. 此时允许幻读，但不允许重复读与脏读。

MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存，就是同一份数据临时保留多版本的一种方式，进而实现并发控制

如果是传统锁的方式保证实现多版本控制，需要通过锁的方式实现，效率非常低，MVCC 使用了一种不同的手段， 无锁并发控制每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照，写操作造成的变化在写操作完成之前（或者数据库事务提交之前）对于其他的读者来说是不可见的。

MVCC插入流程(insert)：

在MySQL中建表时，每个表都会有三列隐藏记录，其中和MVCC有关系的有两列

1.DB_TRX_ID

6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

2.DB_ROLL_PTR

7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

3.DB_ROW_ID

6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

MVCC新增

在插入数据的时候，假设系统的全局事务ID从1开始，

|

set tx_isolation='repeatable-read';

begin;-- 获取全局事务id

INSERT INTO mayikt_account VALUES (null, 'xiaomin', '600');

INSERT INTO mayikt_account VALUES (null, 'xiaoan', '600');

commit;

|

底层表结构

[图片上传失败...(image-762dda-1604282136437)]

插入的过程中会把全局事务ID记录到列 DB_TRX_ID 中去

MVCC删除

表格做删除逻辑，执行以下SQL语句（假设获取到的事务逻辑ID为 3）

|

begin; -- 获得全局事务ID = 3

delete mayikt_account where id= 2;

commit;

|

MVCC修改

修改逻辑和删除逻辑有点相似，修改数据的时候会先复制一条当前记录行数据，同事标记这条数据的数据行版本号为当前是事务版本号，最后把原来的数据行的删除版本号标记为当前是事务。

|

begin;-- 获取全局系统事务ID 假设为 7

update mayikt_account set balance=700 where id=1;

commit;

|

MVCC查询

查询规则：

  1. 查找数据行版本号早于当前事务版本号的数据行记录

数据行的版本号要小于或等于当前是事务的系统版本号，这样也就确保了读取到的数据是当前事务开始前已经存在的数据，或者是自身事务改变过的数据

  1. 查找删除版本号要么为NULL，要么大于当前事务版本号的记录

这样确保查询出来的数据行记录在事务开启之前没有被删除

查询与新增原理

sessionA

|

begin;-- 获取全局事务id 1

-- select * from mayikt_account where DB_TRX_ID=1

select * from mayikt_account

commit;

|
| |

底层查询： -- select * from mayikt_account where DB_TRX_ID<=1

sessionB;

DB_TRX_ID=2；

|

begin; -- 获取全局事务id 2

INSERT INTO mayikt_account VALUES (null, 'yushengjun889', '500');

commit;

|

查询与修改原理

sessionA

|

begin;-- 获取全局事务id 1

-- select * from mayikt_account where DB_TRX_ID=1

select * from mayikt_account

commit;

|
| |

底层查询： -- select * from mayikt_account where DB_TRX_ID<=1

sessionB;

DB_TRX_ID=2；

|

begin; -- 获取全局事务id 2

update mayikt_account set balance=balance-50 where id = 1;

commit;

|

查询与delete原理

sessionA

|

begin;-- 获取全局事务id 1

-- select * from mayikt_account

select * from mayikt_account

commit;

|

sessionB

|

begin; -- 获取全局事务id 2

delete from mayikt_account where id=1;

commit;

|

底层实现原理

Redolog日志 innodb引擎数据页缓存日志

Undolog日志 事务回滚的日志 MVCC结合Undolog实现

Binlog日志 mysql 二进制文件

隐式字段、undo日志、Read View来实现；

DB_TRX_ID

6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

DB_ROLL_PTR

7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

DB_ROW_ID

6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

[图片上传失败...(image-7cd910-1604282136435)]

undo日志

undo log主要分为两种：

1.insert undo log 代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

2.update undo log

事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

清除(purge)：

从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。

为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

undo日志

undo log主要分为两种：

1.insert undo log 代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

2.update undo log

事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

清除(purge)：

从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的deleted_bit，并不真正将过时的记录删除。

为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

悲观锁与乐观锁之间区别
for update定义

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

而select … for update 语句是我们经常使用手工加锁语句。在数据库中执行select … for update ,大家会发现会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。

sessionA
begin;
select * from mayikt_account where id=2 for update;
commit;

sessionB
修改id=2 直接卡出

悲观锁与乐观锁实现

悲观锁比较悲观，多线程同时对同一行数据修改的时候，最终只有一个线程修改成功。
Innodb引擎自带行锁机制。

乐观锁：比较乐观，多线程同时对同一行数据修改的时候，如果没有获取到行锁的线程，
会采用自旋的方式不断重试，一般采用cas实现。

image.png

mysql 间隙锁：
间隙锁基本概念：
间隙锁是一个在索引记录之间的间隙上的锁；

间隙锁的作用：
在mysql 的innodb 引擎中，如果操作的是一个区别的数据，会锁住这个区间所有的记录，即使这个记录不存在，这时候另一个会话去插入这个区间的数据，就必须等待上一个结束；

INSERT INTO `mayikt_account` VALUES (18, 'xiaoxiao18', '600');
INSERT INTO `mayikt_account` VALUES (22, 'xiaoxiao20', '600');

sessionA
begin;
update mayikt_account set name='mayikt6' where id>18 and id<22;
commit;
从id>18 and id <22 上了间隙锁，在没有释放锁的时候 其他的session无法对该段位做操作。
sessionB
INSERT INTO mayikt_account VALUES (19, 'mayikt19', '500');

我们也可以使用间隙锁解决幻读问题
SessionA 先执行：
update mayikt_account set name='mayikt6' where id>18 and id<22;
另外SessionB 无法insert id在18-22 段位。
行锁：提交或者回滚

主键索引或者唯一索引会使用间隙锁吗

1.如果where条件都命中的情况下，则不会发生间隙锁，只会增加记录锁
2.如果where条件部分命中或者全部没有命中的情况下，则使用间隙锁

如何避免行锁升级表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则都会从行锁升级为表锁。

sessionA

begin;
update mayikt_account set name='mayikt6' where balance='300';
commit;

修改的时候查询的条件不是索引字段，会走全表扫描 全表扫描的时候对每行数据都加上行锁
，最终形成表锁。
sessionB
无法修改该任意一条数据，直接发生表锁。

删除表锁
unlock tables;

优化注意事项
1.尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2.尽可能减少检索条件范围，避免间隙锁
3.尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,456评论 5赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,370评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,337评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,583评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,596评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,572评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,936评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,595评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,850评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,601评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,685评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,371评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,951评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,934评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,167评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 43,636评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,411评论 2赞 342