共计 2455 个字符，预计需要花费 7 分钟才能阅读完成。
前言
咱们前几篇讲了索引是什么，如何应用 explain 剖析索引应用状况，如何去优化索引，以及 show profiles 剖析 SQL 语句执行资源耗费的学习。明天咱们来讲讲 MySQL 的各种锁，这里存储引擎咱们应用InnoDB
筹备工作
创立表 tb\_innodb\_lock
`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);`
MySQL 各种锁演示
- 先将主动提交事务改成手动提交：
set autocommit=0; - 咱们启动两个会话窗口 A 和 B，模仿一个抢到锁，一个没抢到被阻塞住了。
行锁（写 & 读）
- A 窗口执行
`update test_innodb_lock set b='a1' where a=1;`
``````
`SELECT * from test_innodb_lock;`
咱们能够看到 A 窗口能够看到更新后的后果
- B 窗口执行
`SELECT * from test_innodb_lock;`
咱们能够看到 B 窗口不能看到更新后的后果，看到的还是老数据，这是因为 a = 1 的这行记录被 A 窗口执行的 SQL 语句抢到了锁，并且没有执行 commit 提交操作。所以窗口 B 看到的还是老数据。这就是 MySQL 隔离级别中的 ” 读已提交 ”。
- 窗口 A 执行 commit 操作
`COMMIT;`
- 窗口 B 查问
`SELECT * from test_innodb_lock;`
这个时候咱们发现窗口 B 曾经读取到最新数据了
行锁（写 & 写）
- 窗口 A 执行更新 a = 1 的记录
`update test_innodb_lock set b='a2' where a=1;`
这时候并没有 commit 提交，锁是窗口 A 持有。
- 窗口 B 也执行更新 a = 1 的记录
`update test_innodb_lock set b='a3' where a=1;`
能够看到，窗口 B 始终处于阻塞状态，因为窗口 A 还没有执行 commit，还持有锁。窗口 B 抢不到 a = 1 这行记录的锁，所以始终阻塞期待。
- 窗口 A 执行 commit 操作
`COMMIT;`
- 窗口 B 的变动
能够看到这个时候窗口 B 曾经执行胜利了
表锁
当索引生效的时候，行锁会升级成表锁，索引生效的其中一个办法是对索引主动 or 手动的换型。a 字段自身是 integer，咱们加上引号，就变成了 String，这个时候索引就会生效了。
- 窗口 A 更新 a = 1 的记录
`update test_innodb_lock set b='a4' where a=1 or a=2;`
- 窗口 B 更新 a = 2 的记录
`update test_innodb_lock set b='b1' where a=3;`
这个时候发现，尽管窗口 A 和 B 更新的行不一样，然而窗口 B 还是被阻塞住了，就是因为窗口 A 的索引生效，导致行锁升级成了表锁，把整个表锁住了，索引窗口 B 被阻塞了。
- 窗口 A 执行 commit 操作
`COMMIT;`
- 窗口 B 的变动
能够看到这个时候窗口 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
- 窗口 A 更新 a = 1~7 范畴的数据
`update test_innodb_lock set b='b5' where a>1 and a<7;`
- 窗口 B 在 a = 2 的地位插入数据
`insert into test_innodb_lock values(2, "b6");`
这个时候发现窗口 B 更新 a = 2 的操作始终在期待，因为 1~7 范畴的数据被间隙锁，锁住了。只有等窗口 A 执行 commit，窗口 B 的 a = 2 能力更新胜利
行锁剖析
- 执行 SQL 剖析命令
`show status like 'innodb_row_lock%';`
- Variable\_name 阐明
- Innodb\_row\_lock\_current\_waits：以后正在期待锁定的数量。
- Innodb\_row\_lock\_time：从系统启动到当初锁定的时长。
- Innodb\_row\_lock\_time\_avg：每次期待锁所花均匀工夫。
- Innodb\_row\_lock\_time\_max：从系统启动到当初锁期待最长的一次所花的工夫。
- Innodb\_row\_lock\_waits：系统启动后到当初总共期待锁的次数。
结语
大家能够依据 Variable\_name 这几个参数思考是否要进行优化，如果锁定工夫，锁定次数过大，那就该思考优化了。优化伎俩能够参考之前索引优化的文章。
IT 老哥
