实例讲解MySQL与InnoDB下共享锁与排他锁

2 02 3 年5 月，一家公司的数据库管理员小王遇到了性能瓶颈问题。

小王注意到同时运行大量查询会减慢系统的响应速度。
经过一番调查，确定是数据库锁造成的。

小王了解到MySQL的InnoDB存储引擎使用共享锁（S锁）和排它锁（X锁）来控制并发访问。

共享锁（S锁）：
特点：允许多个事务同时读取相同的数据，但阻止其他事务获取排它锁。

示例：SELECT FROM table LOCK IN SHARE MODE；
注意：行锁必须命中索引。
否则，可能会升级为表锁。

特殊锁（X锁）：
特点：允许事务修改或删除数据，但阻止其他事务获取任何类型的锁。

示例：从 UPDATE 中的表中选择；
注意：InnoDB UPDATE/INSERT/DELETE 操作会自动添加排他锁。

案例分析：
情况1 ：事务A读取数据并添加共享锁。
事务B也可以读取数据并添加共享锁。
然而，当尝试添加排他锁时，事务C被阻塞。

情况2 ：事务A添加排它锁。
事务 B 在尝试添加共享锁时被阻止，但事务 C 在正常查询执行期间不受影响。

小王通过优化索引和调整事务隔离级别来减少锁争用并提高数据库性能。

MySQL 是怎么加行级锁的？为什么一会是 next-key 锁，一会是间隙锁，一会又是记录锁？

嘿嘿，说起MySQL的行级锁，这是老生常谈的话题了。
记得刚进入这个行业的时候，我对这些东西完全是一头雾水，但后来我慢慢明白了基本原理。

说实话，InnoDB引擎中的行级锁定机制确实相当复杂，但是只要掌握了规则，还是蛮有趣的。
我们以 SELECT 语句为例。
普通 SELECT 不会阻塞，但如果要执行阻塞读操作，例如使用 SELECT...FORUPDATE 或 SELECT...LOCKINSHAREMODE，则需要在事务的开头添加 BEGIN 或 START TRANSACTION。

行级锁主要有三种：记录锁、间隙锁、Next-KeyLock。
在读提交隔离级别中，只有记录锁，这意味着只有查询的记录被锁定。
但在可重复读隔离级别，除了记录锁之外，还需要添加间隙锁，主要是为了防止幻读。

我以前也遇到过类似的情况。
一位同事在查询范围时发现数据一直在变化。
最终发现间隙锁处理不当。
他当时不太明白，后来我给他解释了半天，他才恍然大悟。

Next-KeyLock实际上是记录锁和间隙锁的组合。
它不仅保护记录本身，还可以防止插入新记录。
这在相当于唯一索引的查询中特别有用。
如果记录存在，则添加记录锁；如果不存在，则添加间隙块。

要分析锁类型，可以使用SQL查询，例如SELECT FROM Performance_schema.data_locks;，这样就可以看到锁的详细信息。

不过这个东西在不同的版本和设置下可能会有所不同，但是基本原理还是类似的。
因此，了解行级锁阻塞规则对于高效管理数据库事务至关重要。
记得有一次我优化了一个项目的查询语句，把全表锁改成了行级锁。
结果，性能提高了一倍多。

简而言之，行级锁的阻塞规则取决于SQL语句、索引、查询条件和执行隔离级别。
这就像开车一样。
只有掌握了规则，才能安全、高效地驾驶。

MySql 三大知识点——索引、锁、事务

那天在咖啡馆，我旁边的那个人全神贯注于他的电脑。
屏幕上显示出错误信息，他嘴里嘟囔着什么。
乍一看，他发现自己正在调试的代码正在以慢动作重放频繁的数据库查询，每次都扫描整个表。
此时，索引的重要性不言而喻。

索引就像超市货架标签。
如果没有标签，则必须遍历每个架子才能找到牛奶盒。
如果牛奶位于冷冻区，标签尤其重要。
但由于标签太多，波特在发布产品时必须一一更新，这实际上降低了效率。
我研究了一家公司的指数优化。
去掉数百个无用索引后，写入速度直接快了3 倍。
这些索引“肯定”是我的前任老板添加的。

锁机制最麻烦的就是死锁。
去年冬天凌晨3 点，我收到订单已满的提醒。
我们发现，在幕后，两个事务正在互相持有对方的资源，并等待对方的资源被释放。
这就像两个送货员拿错了钥匙，各自等待对方先开门。
然后发现更新同一行的两条SQL语句没有被锁。
目前，我们的系统在修改操作之前检查依赖关系。

选择事务隔离级别是一种权衡。
我记得在一次大型电商促销活动中，隔离级别从RR降到了RC。
并发量确实增加了，但客户服务部门开始收到“只是更改订单然后又改回来”的抱怨。
工程部门最终妥协并维持了核心交易链路上的RR，并适当调节了外围流程。
与交通法规一样，主干道应严格监管，次干道应适当绕行。

突然发现很多分布式数据库都号称“无锁”，但实际上只是将锁分布到不同的节点上。
在一次测试中，我们使用Redis代替MySQL缓存，发现即使两个客户端同时更新同一个key，系统仍然报超时。
事实证明，分布式锁只是转发问题，并没有真正解决问题。