前言
我们讲了索引是什么，如何使用explain来分析索引使用情况，如何优化索引，以及show profile来分析SQL语句执行的资源消耗。今天我们来聊聊MySQL的各种锁。这里我们使用 InnoDB 作为存储引擎。
准备
创建表 tb_innodb_lock
如果存在则删除表 test_innodb_lock;
创建表 test_innodb_lock (
一个 INT (11),
b VARCHAR (20)
) ENGINE INNODB 默认字符集 = utf8;
插入test_innodb_lock值（1,'a'）；
插入test_innodb_lock值（2,'b'）；
插入test_innodb_lock值（3,'c'）；
插入test_innodb_lock值（4,'d'）；
插入test_innodb_lock值（5,'e'）；
创建索引
在 test_innodb_lock(a) 上创建索引 idx_lock_a；
在 test_innodb_lock(b) 上创建索引 idx_lock_b；
MySQL的各种锁演示先把自动提交事务改为手动提交：set autocommit=0;
我们启动两个会话窗口 A 和 B，模拟一个获得锁，另一个被阻塞。
Row Lock (Write & Read) 一个窗口执行
更新 test_innodb_lock set b='a1' where a=1;
从 test_innodb_lock 中选择 *；
我们可以看到A窗口可以看到B窗口执行的更新结果
从 test_innodb_lock 中选择 *；
我们可以看到B窗口看不到更新的结果，但是还是看到了旧的数据。这是因为a=1的行被A窗口执行的SQL语句锁住了，commit没有执行。操作。所以窗口 B 仍然看到旧数据。这是 MySQL 隔离级别中的“已提交读”。窗口A执行提交操作
COMMIT;窗口 B 查询
从 test_innodb_lock 中选择 *；
此时我们发现窗口B已经读取了最新的数据
Row Lock (Write & Write) 窗口 A 执行记录 a = 1 的更新
更新 test_innodb_lock set b='a2' where a=1;
此时没有提交，锁由窗口A持有。窗口B也对a=1的记录进行更新
更新 test_innodb_lock set b='a3' where a=1;
可以看出窗口B一直处于阻塞状态，因为窗口A还没有执行commit，仍然持有锁。窗口 B 无法获取 a = 1 行的锁，因此它一直处于阻塞和等待状态。窗口A执行提交操作
提交；窗口 B 中的更改
此时可以看到窗口B已经执行成功
表锁
当索引失败时，行锁会升级为表锁。索引失效的方法之一是自动或手动更改索引。 a 字段本身是一个整数。当我们添加引号时，它就变成了一个字符串。此时，索引将无效。窗口 A 用 a = 1 更新记录
update test_innodb_lock set b='a4' where a=1 or a=2;window B 用 a = 2 更新记录
更新 test_innodb_lock set b='b1' where a=3;
这时候发现虽然A和B窗口更新的行不同，但是B窗口还是被阻塞了表行锁定的时候能查询么，因为A窗口的索引无效，导致行锁升级为表锁，加锁整张桌子。 ，索引窗口 B 被阻塞。窗口A执行提交操作
提交；窗口 B 中的更改
此时可以看到窗口B已经执行成功
什么是间隙锁
当我们使用范围条件查询数据时，InnoDB 会锁定这个范围内的数据。比如id有4条数据：1、3、5、7，我们查找1-7范围内的数据。然后 1-7 将被锁定。 2、4、6 也在 1-7 的范围内表行锁定的时候能查询么，但是没有这些数据记录，这些 2、4、6 被称为间隙。间隙锁的危害
当搜索一个范围时，整个范围内的所有数据都会被锁定。甚至一些在这个范围内不存在的数据也会被无辜锁定。例如，如果我想 1、3、< 将 2 插入 @5、7。此时1-7被锁定，2根本无法插入。在某些场景下，它会对性能产生很大的影响。间隙锁演示
我们先将a字段的值修改为1、3、5、7、9窗口A更新a=1~7范围内的数据
更新 test_innodb_lock set b='b5' where a>1 and a
插入 test_innodb_lock 值(2, "b6");
此时发现窗口B中更新a=2的操作一直在等待，因为1~7范围内的数据被gap lock锁住了。只有窗口A执行commit，才能成功更新窗口B的a=2
行锁分析执行SQL分析命令
显示类似“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的参数考虑是否优化。如果加锁时间和加锁次数太大，那么是时候考虑优化了。优化方法请参考上一篇关于索引优化的文章。
老哥
在大厂做高级Java开发的程序员微信搜索：IT大哥，获取更多学习资料
