MySQL深入研究–学习总结(5)

前言

接上文,继续学习后续章节。细心的同学已经发现,我整理的并不一定是作者讲的内容,更多是结合自己的理解,加以阐述,所以建议结合原文一起理解。

第20章《幻读是什么,幻读有什么问题?》

先看下幻读的定义:

在一个事务中,两次执行同一个查询SQL,后一次执行结果比前一次执行结果数量变多了,称之为幻读。

在隔离级别中的定义,我们知道RR级别是无法避免幻读的?但是在innoDB中是如果做到避免幻读问题呢?其实innoDB在RR级别下解决幻读问题也并不完美。

现有一张表t,有三个字段 id主键,普通索引C,不带索引d, 目前有数据id=1,d=5,c=5;id=4,d=5,c=5;

事务A 事务B 事务C
begin;
select * from t where d =5 for update;Q1
update t set d =5 where id =4;
select * from t where d =5 for update;Q2
insert into t values (5,5,5)
select * from t where d =5 for update;Q3
commit;

首先我们知道innoDB在RR级别下,select语句是快照读,不存在幻读问题,快照读的实现方式我们之前阐述过,通过MCVV多版本并发控制解决的,本质就是通过undo log实现的,如果还需要更详细的研究,可以参考《一文讲透MVCC原理》

Q2读到事务B的更新预警,属于当前读看到的,不属于幻读.幻读指的是新插入的行。

首先我们看下,幻读不解决可能会出现哪些问题?

1、首先如果只加行锁,执行Q1时,我们希望的是所有d=5的数据都锁定,但d=5的是id=1的数据,只会对id=1加上行锁.当事务B执行时,是对id=4进行更新操作,所以可以操作.同样的事务C也可以执行,那么就违背了,Q1的语义.

2、数据一致性问题:如果事务A在Q1时候又执行了一个按d=5条件更新语句,那么在所有事务都提交后,落入到binlog中的顺序是,事务B更新一个语句把id=4的d改成了5,事务C插入了一个id=5,d=5的数据,事务A执行了一个把d=5的数据更新。此时如果按binlog的执行顺序,则会把事务B事务C的逻辑全部改掉,造成了数据不一致的问题。

所以innoDB为了解决此类幻读问题,就引入了间隙锁(Gap lock);

顾名思义就是在行与行之间也加上锁.

比如上面例子中,有id=1和id=4两个数据,那么我不仅在行上加锁,我再(- ∞ ,1](1,4],(4, + supremum】上也加上锁,那么当你再插入数据的时候,就无缝可入了就会被阻塞住。

但需要注意的是,在行锁中,读写锁,写写锁互相冲突,当一个事务加间隙锁,另外一个事务也可以对同样的范围加间隙锁。

注意:

当我们执行更新加间隙锁时,如果where条件不是索引,那么就会对所有行加行锁和行之间数值范围加间隙锁。

如果执行的where条件是等值并且是非唯一索引列,比如id=5,那么会对id=5加行锁,以及(id=5上一个id,id=5]和(id=5,id=5的下一个id值]加间隙锁。如果是唯一索引,那么就只加行锁。

第21章《为什么只改一行代码,锁这么多》

这章节,老师总结的加锁规则,非常受用:两个原则,两个优化,一个BUG

两个原则:

1、加锁的基本单位是以next-key lock。就是左开右闭。

2、查找过程中访问的到对象才会加锁。

两个优化:

1、在索引上等值查询条件,如果是唯一索引,会优化成行锁。

2、在索引上等值查询条件,会向右遍历找到第一个不符合条件的为止,并会优化成间隙锁。

一个bug:

在唯一索引上的范围查询,会访问到不满足条件为止。

带着这些规则,下面看八个案例,以表T为背景,来实践下。

CREATE TABLE`t`(
`id`int(11)NOT NULL,
`c`int(11)DEFAULT NULL,
`d`int(11)DEFAULT NULL,
PRIMARY KEY(`id`),
KEY`c`(`c`)
) ENGINE=InnoDB;

insert intot values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一:等值查询的情况

update t set d=d+1 where id =7;

根据主键id进行更新时,由于id=7不存在。根据规则1加锁单位next-key lock :(5,10] 根据优化2: 退化成间隙锁(5,10);

此时分别执行两个SQL:

insert into t values(8,8,8) --由于间隙锁,会block
update t set d=d+1 where id =10; --不在间隙锁范围内,执行成功

经过实验,证实如此。

案例二:非唯一索引等值查询

begin;
select id from t where c=5 lock in share mode;

根据优化2 可知会在索引C间隙锁 (5,10);

但需要注意的是:根据规则2 只有访问到的对象才会加锁,由于使用了覆盖索引,所以不会再主键id上再加锁。

update t set d=d+1 where id =5; --执行不受阻
insert into t values(7,7,7); --blocked

案例三:主键索引范围查询

select * from t where id =10 for update;
select * from t where id>=10 and id <11;

首先从结果上看,这两个SQL执行结果是一样的,但是按照上面总结的加锁规则,加锁过程是不一样的。

SQL1 唯一索引等值查询,则加的是id=10的行锁。

SQL2 首先会查询到id=10这行,加锁单位next-key lock,(5,10],根据优化1,退化成行锁,只加id=10这一行,

再查询id>10,向右找到第一个不满足条件的行id=15,加next-key lock (10,15]

案例四:非唯一索引范围锁

select * from t where c>= 10 and c<11 for update;

首先找到c=10这一行,加next-key (5,10],再根据优化2,向右查询到第一个不满足条件的值,退还成间隙锁(10,15]

案例五:唯一索引范围锁bug

select * from t where id>10 and id<=15 for update;

正常id是唯一索引,找到id=15这时 ,应该就停止扫描了,但是innoDB会依然扫描到第一个不满足条件的id=20为止。

所以就会加(10,15],(15,20],导致id=20这样也会锁上,这是没有必要的。这个bug已被官方证实,但还未修复。

案例六:未唯一索引存在多个相同值的情况

--先插入一条c=10的值,这样表中存在2个c=10的行
insert into t values(30,10,30);

当我们在c=10加锁时,加锁的范围是next-key lock (5,10] next-key lock (10,10]和间隙锁 (10,15)

案例七:limit 语句加锁

delete from t where c =10 limit2;

与案例7的加锁范围不同的时,不会再加间隙锁(10,15);

因为当扫描到c=10,id=30这一行时,已经满足limit2的条件,不会再往后扫描,也就不会再加间隙锁(10,15),从而缩小了锁的范围。

所以在删除数据的时候,我们尽量加limit ,即可以控制删除数量,又可以缩小锁的范围。

案例八:一个说死锁的例子

A执行第一句SQL时,按加锁规则,会加next-key lock (5,10]和间隙锁(10,15)。

B执行时,也会加next-key lock(5,10],会被阻塞.

A再次执行插入数据(8,8,8)时,也会被阻塞,从而导致死锁。

这是为什么了?B 不是没有加锁成功吗?但是我们要注意的时,加next-key lock 本质就是加间隙锁再加行锁。由于间隙锁加之间不是互斥的,所以B加(5,10)的间隙锁时成功了,加c=10的行锁失败。

所以当A再次插入数据时,已被间隙锁阻塞。

第23章《MySQL怎么保证数据不丢失的?》

binlog写入机制

事务执行时,先把日志写到binlog cache,事务提交后再把binlog cache 写入binlog文件。

binlog_cache_size控制了每个线程的binlog cache的内存的大小。如果超过了这个参数就临时存到磁盘中。

但注意的是,binlog cache并不一定是直接落入磁盘的,是先写入binlog files,再刷入磁盘。

这个可以通过参数sync_binlog控制:

设置0,表示每次只写入binlog files,不刷磁盘

设置1,表示每次事务提交,也会刷磁盘

设置n,表示每次都写入binlog files,但是等累计了n个事务才刷磁盘

常见设置为”100-1000″,但同时也会有丢失的风险.

redo log写入机制

事务执行时,先把日志写入 redo log buffer,事务提交时,写入文件系统page cache 或者刷入磁盘.也是通过参数来设置:innoDB_flush_log_at_trx_commit

设置0,表示每次事务提交时,依然保留在redo log buffer

设置1,表示每次事务提交直接刷入磁盘

设置2,表示每次事务提交都只写到page cache.

那是不是0,2状态是不是就永不刷到磁盘了,那不是会有丢失的风险吗?

其实不是的,innoDB有一个后台线程,每隔1秒,会把redo log buffer中的日志,写到page cache,然后再调用fsync刷到磁盘.

由于这个后台线程的存在,所以也会把还未提交事务的redo log 也持久化到磁盘.

如果你的如果你的MySQLMySQL现在出现了性能瓶颈,而且瓶颈在现在出现了性能瓶颈,而且瓶颈在IO上,可以通过哪些方法来提升性能呢?上,可以通过哪些方法来提升性能呢?
针对这个问题,可以考虑以下三种方法:
1.设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数,减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的,因此可能会增加语句的响应时间,但没有丢失数据的风险。
2.将sync_binlog设置为大于1的值(比较常见是100~1000)。这样做的风险是,主机掉电时会丢binlog日志。
3.将innodb_flush_log_at_trx_commit设置为2。这样做的风险是,主机掉电的时候会丢数据。

给TA买糖
共{{data.count}}人
人已赞赏
经验教程

为 .NET 打 Call,为国产平台 Gitee 打 Call,我的 .NET/C# 开源项目清单,同步维护于 Github 和 Gitee

2021-3-16 9:43:00

经验教程

浅析MyBatis(二):手写一个自己的MyBatis简单框架

2021-3-16 9:48:00

⚠️
免责声明:根据《计算机软件保护条例》第十七条规定“为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬。”您需知晓本站所有内容资源均来源于网络,仅供用户交流学习与研究使用,版权归属原版权方所有,版权争议与本站无关,用户本人下载后不能用作商业或非法用途,需在24个小时之内从您的电脑中彻底删除上述内容,否则后果均由用户承担责任;如果您访问和下载此文件,表示您同意只将此文件用于参考、学习而非其他用途,否则一切后果请您自行承担,如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。 本站为个人博客非盈利性站点,所有软件信息均来自网络,所有资源仅供学习参考研究目的,并不贩卖软件,不存在任何商业目的及用途,网站会员捐赠是您喜欢本站而产生的赞助支持行为,仅为维持服务器的开支与维护,全凭自愿无任何强求。本站部份代码及教程来源于互联网,仅供网友学习交流,若您喜欢本文可附上原文链接随意转载。
无意侵害您的权益,请发送邮件至 momeis6@qq.com 或点击右侧 私信:momeis 反馈,我们将尽快处理。
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索