认识mysql insert into ... select的锁问题

说实话，当我第一次了解MySQL这样的嵌套查询的锁定机制时，我真的很困惑。
但经历了一些现实场景后，我逐渐掌握了窍门。
不要处理任何虚构的事情，只要按照你提到的场景进行即可。
我会解释细节。

首先我们解释一下源表S的锁定行为。
你提到了隔离级别，innodb_locks_unsafe_for_binlog参数当然很重要。
我有亲身经历。
去年我在测试环境中从事报告生成任务。
使用 REPEATABLE READ 隔离级别的 INSERT INTO...SELECT.... 导致整个系统陷入困境。
后来检查发现后台调度了一个任务也在修改源表S，此时InnoDB给S表加了共享锁。
如果另一个事务想要更新S表，它必须等待。
等待将导致僵局。
这种事情特别棘手，因为你无法确定哪个 SQL 是罪魁祸首。

有趣的是，锁定目标表T要简单得多。
无论你如何锁定源表，向目标表T插入数据时都必须加排它锁。
我去年在金融系统就遇到过这种情况。
我使用 INSERT INTO T SELECT FROM S WHERE... 运行批量导入，发现目标表的同时导入非常慢。
我检查了日志，发现T表本身锁得太紧了。
此时就需要考虑是调整T表上的索引还是批量导入。

说到解决方案，推荐的 READ COMMITTED 隔离级别当然是可靠的。
我记得在通信项目中使用 REPEATABLE READ 来执行事务，并且我记得在高峰时段有关锁等待超时的投诉数量非常多。
之后我将其更改为READ COMMITTED，问题立即得到解决。
该级别的优点是可以使用 SET TRANSACTION ISOLATION LEVEL 直接更改，无需重新启动。
我特别不喜欢需要更改配置文件或重新启动数据库的解决方案，因为它们操作和维护起来很麻烦。

至于innodb_locks_unsafe_for_binlog参数，我见过有人使用它。
我有一个使用 MySQL 5 .7 的旧项目。
团队没有勇气升级，所以他们利用这个参数来避免锁定问题。
但说实话，这个参数在MySQL 8 .0中已经被删除了，所以使用它我感到很愧疚。
我们建议遵循规范，包括使用触发器和应用层逻辑来处理数据一致性问题。

最后，我会告诉你细节。
我在做性能压力测试的时候，源表S我们发现，持有锁的时间越长，发生锁争用的机会就越高。
在一种情况下，使用函数计算查询条件，导致索引失效并锁定整个表。
在这种情况下，你可能需要优化你的SQL，比如提前计算和存储计算结果。
坦率地说，锁定问题本质上是业务逻辑和数据库锁定机制之间的冲突，需要具体情况具体分析。

我记得数据是去年测试的。
READ COMMITTED 可以将锁等待减少 7 0% 以上，尽管具体数字可能有点极端。
你应该亲自去看看。

MySQL UPDATE语句如何在大批量更新时保证性能和避免死锁？

上周在一个项目中遇到了大批量更新MySQL数据的问题。
为了保证性能并避免死锁，我们尝试了以下策略：
1 .索引优化：确保您的 WHERE 条件使用最有效的索引，例如主键或唯一索引上的 UPDATE 操作。
我们还避免了索引失效的情况，例如在索引列上使用函数或隐式类型转换。

2 批量更新：将 1 0,000 行的更新拆分为每个 1 00 到 5 00 行的较小事务，从而减少单个锁的时间和资源使用。
我们使用 LIMIT 分页来限制单次更新的数量。

3 死锁预防和处理：调整事务隔离级别，降低隔离级别，例如将REPEATABLEREAD降低为READCOMMITTED。
我们还优化了锁定策略，以固定顺序访问表和行，并使用乐观锁定控制并发。

4 架构和异步处理：引入消息队列，将大批量更新操作拆分成消息，通过后台服务异步处理。
我们还使用计划任务分片来实现负载平衡。

5 监控和调优：启用Slow_query_log来查找执行时间超过阈值的UPDATE语句并优化索引或SQL写入。
我们还评估了硬件资源，以确保服务器的 CPU、内存和 I/O 能力与数据量相匹配。

例如，在一个场景中，您需要将 1 0,000 个订单的状态从 0 更新为 1 首先我们检查 order_id 是否为主键，并且 status 字段是否有索引，然后我们批量更新它们，最后我们将任务记录到队列中以在独立服务中执行。

通过这些策略，我们成功提高了大批量 UPDATE 的性能并降低了死锁的风险。
不过这个过程还是有点复杂，我们来弄清楚吧。

mysql中insert会加锁吗

上周，我在研究MySQL中的锁定机制时，发现了一个有趣的死锁。
2 02 3 年，你遇到一张表t3 ，其结构是c1 为主键，c2 为唯一索引。
表中有几条数据，分别是(1 ,1 )、(1 5 ,1 5 )、(2 0,2 0)。

在会话1 中，您执行删除操作，这将添加Xlock以记录c2 =1 5 此时Session 2 和Session 3 同时尝试插入数据，但是由于唯一约束，它们会在(1 ,1 5 )区间添加SNext-KeyLock，即间隙锁，被Session 1 阻塞。
随着LOCK（插入意向锁）的出现，它们会互相等待，最终导致死锁。

在这种情况下，插入意向锁是一种特殊类型的锁间隙，它不会阻塞其他锁。
但会被缝隙挡住。
这解释了为什么会话 2 和会话 3 相互等待。

此外，GAPLOCK位于REA。
它还会出现在隔离级别 D-COMMITTED 下，尤其是在发生唯一约束检查​​时。

最后，我注意到锁模式和锁属性的组合可以形成一个冲突矩阵，这对于死锁优化分析很有用。