我们前几篇讲了索引是什么,如何使用explain
分析索引使用情况,如何去优化索引,以及show profiles
分析SQL
语句执行资源消耗的学习。今天我们来讲讲MySQL
的各种锁,这里存储引擎我们使用InnoDB
`drop?table?if?exists?test_innodb_lock;`
`CREATE?TABLE?test_innodb_lock?(`
`a?INT?(11),`
`b?VARCHAR?(20)`
`)?ENGINE?INNODB?DEFAULT?charset?=?utf8;`
`insert?into?test_innodb_lock?values?(1,'a');`
`insert?into?test_innodb_lock?values?(2,'b');`
`insert?into?test_innodb_lock?values?(3,'c');`
`insert?into?test_innodb_lock?values?(4,'d');`
`insert?into?test_innodb_lock?values?(5,'e');`
`create?index?idx_lock_a?on?test_innodb_lock(a);`
`create?index?idx_lock_b?on?test_innodb_lock(b);`
set autocommit=0;
`update?test_innodb_lock?set?b='a1'?where?a=1;`
``````
`SELECT?*?from?test_innodb_lock;`
我们可以看到 A 窗口可以看到更新后的结果
`SELECT?*?from?test_innodb_lock;`
我们可以看到 B 窗口不能看到更新后的结果,看到的还是老数据,这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁,并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的"读已提交"。
`COMMIT;`
`SELECT?*?from?test_innodb_lock;`
这个时候我们发现窗口 B 已经读取到最新数据了
`update?test_innodb_lock?set?b='a2'?where?a=1;`
这时候并没有 commit 提交,锁是窗口 A 持有。
`update?test_innodb_lock?set?b='a3'?where?a=1;`
可以看到,窗口 B 一直处于阻塞状态,因为窗口 A 还没有执行 commit,还持有锁。窗口 B 抢不到 a = 1 这行记录的锁,所以一直阻塞等待。
`COMMIT;`
可以看到这个时候窗口 B 已经执行成功了
当索引失效的时候,行锁会升级成表锁,索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer,我们加上引号,就变成了 String,这个时候索引就会失效了。
`update?test_innodb_lock?set?b='a4'?where?a=1?or?a=2;`
`update?test_innodb_lock?set?b='b1'?where?a=3;`
这个时候发现,虽然窗口 A 和 B 更新的行不一样,但是窗口 B 还是被阻塞住了,就是因为窗口 A 的索引失效,导致行锁升级成了表锁,把整个表锁住了,索引窗口 B 被阻塞了。
`COMMIT;`
可以看到这个时候窗口 B 已经执行成功了
当我们采用范围条件查询数据时,InnoDB 会对这个范围内的数据进行加锁。比如有 id 为:1、3、5、7 的 4 条数据,我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中,但是不存在这些数据记录,这些 2、4、6 就被称为间隙。
范围查找时,会把整个范围的数据全部锁定住,即便这个范围内不存在的一些数据,也会被无辜的锁定住,比如我要在 1、3、5、7 中插入 2,这个时候 1-7 都被锁定住了,根本无法插入 2。在某些场景下会对性能产生很大的影响
我们先把字段 a 的值修改成 1、3、5、7、9
`update?test_innodb_lock?set?b='b5'?where?a>1?and?a<7;`
`insert?into?test_innodb_lock?values(2,?"b6");`
这个时候发现窗口 B 更新 a = 2 的操作一直在等待,因为 1~7 范围的数据被间隙锁,锁住了。只有等窗口 A 执行 commit,窗口 B 的 a = 2 才能更新成功
`show?status?like?'innodb_row_lock%';`
大家可以根据 Variable\_name 这几个参数考虑是否要进行优化,如果锁定时间,锁定次数过大,那就该考虑优化了。优化手段可以参考之前索引优化的文章。
前言 当页面的html结构嵌套的盒子很多的时候,可能会导致一个页面中含有多个垂直...
转眼已经2020年,饥渴的人类不再满足于简单的文本,于是有了花里胡哨的携带各种...
具体是方法如下: css代码 复制代码 代码如下: .wap_bottom { height: 60px; wid...
设置body的宽度为window的宽度(以下脚本控制) $(body).css(width, $(window).w...
在使用织梦建站的时候如果是中英文版本的网站,分页就需要对应英文地址,该怎么...
加了junit单元测试后出现如下报错 解决方法: resources 文件夹下新建 log4j.pro...
最近看雅虎黄金34条,学习下优化网站性能的方法。其中有一条:为文件头指定Expir...
我在HTML下的默认样式中讲到,一些标签元素在HTML下有一个默认属性值,我们在写c...
浏览器引擎前缀(Vendor Prefix)是什么? Vendor prefix浏览器引擎前缀,是一些放...
一、前言 在了解加密原理前,我们来看看这样一个故事。 小红和小明是情侣,一天...