MySQL锁机制揭秘：共享锁、排他锁、意向锁与间隙锁

共享锁（S锁）：
读取共享内容，写入块。
对于
选择...共享应用程序。

适合报告查询。

私人锁（X锁）：
私下写。
块读取。

默认添加INSERT/UPDATE/DELETE。

明确包含在 SELECT ... UPDATE 中。

折扣适用于订单。

目标锁定（IS/IX 锁定）：
表级锁协调。

IS 锁：安装在 S 级锁之前。

IX 锁：安装在 X 级锁之前。

IX 与 IX 兼容。

IS 反对 IX。

适合阅读和写作相结合。

间隙锁：
锁定指数差距。

范围查询在递归读取下启动。

块输入。

适合防止幻读。

隔离级别：
读提交：仅记录锁；仅读取可能的幻象。

重复阅读：空白锁+下一键，防止幻读。

SERIALIZABLE：表级锁；完全隔离；性能低下。

死锁排查：
显示引擎的状态。

INFORMATION_SCHEMA.INNODB_TRX，INNODB_LOCKS。

查看 INNODB_LOCK_WAITS 等待链。

优化策略：
短交易。

优化索引。

修复了锁定顺序。

降低隔离级别。

正锁+再试一次。

缩小间隙锁定范围。

间隙锁问题：
阻止并发条目。

调试比较复杂。

性能影响。

建议：
选择隔离级别。

监控锁定。

优化索引。

标准化锁定方案。

MySQL中的共享锁 排他锁 意向锁、 架构与存储引擎

抱歉，MySQL 的锁定机制说起来很复杂。
2 02 2 年，我刚刚开始接触这个：桌面块、行块、共享块和独占块。
起初我很困惑。
共享块是多个事务可以同时读取但不能写入的块。
这就像图书馆里的一本书，每个人都可以阅读，但不能借阅。
独占锁意味着一旦你拿到一笔交易，其他人就不能再碰它，就像你借了一本书，其他人就不能再借了。

我也感觉独占块看起来很强大，但是我发现虽然可以读写，但是其他事务已经无法进行了。
我记得有一次测试，我使用了某个城市的数据库，有数十万个数据点。
我特意给一行数据加了排他锁，然后另一个事务想要更新它，结果却等到了锁被释放。

共享块和独占块之间的区别实际上只是一个字，请阅读。
共享锁用于读，而独占锁用于读和写。
当时我还尝试了使用 select...lockinsharemode 添加共享锁和 select...forupdate 添加独占锁。
结果是显而易见的。
添加唯一区块数据后，其他交易将无法再进行。

让我们谈谈意图块。
这个东西就是InnoDB的表级锁，它告诉你接下来这个事务会加什么锁。
意向共享锁（IS）和意向独占锁（IX）就像在图书馆门上贴了一张小纸条，告诉你接下来要借阅这本书。

表级锁是指整个表的锁定，读锁和写锁。
读锁是LOCKTABLE table_nameREAD，写锁是LOCKTABLE table_nameWRITE。
这在 MyISAM 和 InnoDB 中都有使用。
创建区块的成本低，并且不存在死锁。
但并发度较低，适合对大量数据进行GROUPBY操作的场景。

MySQL的架构和存储引擎，我后来才体会到，分为两层，SQLLayer和StorageEngineLayer。
SQLLayer负责解析SQL语句、管理用户权限等。
StorageEngineLayer是存储引擎，和InnoDB、Myisam一样。

InnoDB和Myisam，这两种存储引擎，各有各的特点。
InnoDB 支持事务并具有存储过程、视图和行级锁定等高级功能。
但在高并发的情况下，性能比Myisam要好。
至于Myisam，它简单、快速，但不支持事务。

后来想了想，可能是我有偏见吧。
每个存储引擎都有它的位置。
关键是根据您的业务类型选择合适的。
就像选车一样，不是最好的车就是最好的，而是最适合自己的才是最好的。

MySQL中的共享锁 排他锁 意向锁、 架构与存储引擎

记得我和同事小王曾经测试过数据库的行锁功能。
那天下午我们选择了公司内部的一个小项目，决定用它来模拟排障的效果。
在项目数据库中，有一个员工表，其中包含员工的姓名、年龄和职位信息。

我们首先在小王的电脑上打开一个窗口，执行一条更新语句，更改了一名员工的年龄。
此时，我同时在另一台计算机上打开一个窗口并尝试读取同一员工的信息。
这阻止了我，无法读取数据。
这就验证了排它锁（X锁）的效果：一旦一个事务开始修改数据，就会对该数据行加排它锁，其他事务不能同时读取或修改它。

然后我们做了另一个测试。
这次我在计算机上执行了 Select 语句并读取了同一员工的信息。
随后小王试图在我的电脑上更改该员工的信息。
这也会阻止他的更新语句。
这说明Select语句默认添加了共享锁（S锁），可以防止其他事务修改数据。

我们还进行了其他组合测试，例如比如同时使用共享锁和排它锁，或者一个事务加了排它锁而另一个事务试图读取数据。
无论如何，我们看到了锁定机制的预期效果。

通过这些测试，我们对MySQL的行锁机制有了更深入的了解。
但是等等，我突然想到：当业务场景既有大量的数据插入或更新，又有频繁的查询操作时，我该如何选择存储引擎呢？

MySQL中的共享锁 排他锁 意向锁、 架构与存储引擎

哦，这件事让我头晕。
说实话。

我想当时，我刚接手我们公司的旧系统时，服务器就是那台旧机器，CPU只有两个核心，内存是5 1 2 GB。
MyISAM 被用作数据库。
后来，客户不断抱怨数据有时不符。
当我检查时，天哪，这都是MyISAM的错。
想一想，MyISAM的锁定机制在写入数据时会锁定整个表，而在读取时可以添加共享锁。
但如果同时有人想改的话，那就只能等了，那就惨了。
当时我们在处理订单，有时当订单量增大时，后端就慢得像蜗牛一样。
后来老板做出决定，全部换成InnoDB。
嘿，结果立竿见影！ InnoDB中的行级锁定不影响读取期间的写入。
锁定在键入时更改的行。
这是非常清楚的。
我记得双十一期间，请求大小比平常大了三到四倍，而数据库仍然完好无损，这要归功于InnoDB。

回到我提到的锁：
1 共享锁：简单来说就是每个人都可以看到它，但是没有人可以动它。
就像我们办公室的公共打印机，很多人都可以使用，但是你不能打印和修改里面的零件，对吧？我记得当时有系统维护，某个表索引要重建。
如果使用MyISAM，则必须先锁定表，整个表无法使用，工作会停止很长时间。
最好使用InnoDB。
创建索引时，添加共享锁。
其他查询可以正常运行，但数据无法写入。
这给我们省去了很多麻烦。

2 独占锁：这是最严格的，任何人都不能碰它，任何人都不能看它。
如果更新数据，必须先添加私有锁。
当别人过来看到你在改变的时候，他们就会等着看书、写字。
我记得有一次我正在运行一个测试，我想为我的用户分数添加 1 00，但我忘记添加独占锁。
旁边传来一个查询，结果是积分加到了2 00，我顿时惊呆了，赶紧扫了全表，又改了一遍。
很尴尬。
InnoDB在数据修改时默认添加排他锁，这样就比较容易担心了。

3 意向锁：我没有太多联系有了这件事，我就不陷入太多的陷阱，也不敢乱说。
据资料显示，是在InnoDB中使用的。
为了提高效率，告诉系统“我接下来可能需要添加共享锁”或“我接下来可能需要添加私有锁”。
系统收到消息后就会知道它是什么，从而避免在真正需要加锁时检测到冲突。
这就像一个预警：“嘿，我马上要去看书了，谁都不能碰我的桌子。
”怎么用，还得请教懂的人。

4 MySQL架构：我还没有深入研究过各层。
我记得以前有查询缓存模块，后来发现没用，就把它删除了。
该系统实际上更快。
现在我们主要关注SQL层如何分发命令以及存储引擎层如何访问数据。
就像盖房子一样，一旦绘制了设计图（SQL层），用于覆盖它们的材料（存储引擎层）就非常重要。

5 存储驱动器：这一点应该强调。
InnoDB现在已经成为主流，每个从事数据库工作的人都应该了解事务和行级锁。
当时我们的电商系统订单、库存、支付都必须使用InnoDB，否则无法保证数据一致性。
MyISAM查询速度很快，但是一旦并发量增加就会崩溃。
有一个客户，经营一个论坛，但是数据量不大，数据量也不大。
我们用 MyISAM 进行了尝试。
查询速度实际上比InnoDB快很多，而且当内存足够大时，查询缓存仍然可以使用，节省了大量的硬盘IO。
但这只能在某些特定场景下使用。

总结：不要只阅读文档，你应该结合实际实践来使用它。
当时我们有一个项目，数据量不大。
对于纯读，我们使用MyISAM，并且还开启了查询缓存，速度非常快。
后来数据量增大，开始更新，我就立即切换到InnoDB。
虽然CPU有时会达到1 00%，但系统稳定，业务运行顺利。
因此，您选择的锁和使用的电机取决于您正在做什么。
不要盲目使用，否则真的会堵塞系统。