V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
zhanglintc
V2EX  ›  问与答

INNER JOIN 的时候,加锁的顺序是怎样的?

  •  1
     
  •   zhanglintc · 2022-09-16 09:52:27 +08:00 · 1030 次点击
    这是一个创建于 559 天前的主题,其中的信息可能已经有所发展或是发生改变。

    这两天遇到一个死锁的问题,两个程序同时使用 delete 删除两张表,A 程序使用的是T1 LEFT JOIN T2,B 程序使用的是T2 INNER JOIN T1。此时会出现死锁。

    因为两个程序的 SQL 连结表的时候,T1 、T2 表的顺序不同,所以尝试把 B 程序T2 INNER JOIN T1调整成T1 INNER JOIN T2,依然会出现死锁。

    最后把 B 程序也改成 LEFT JOIN:T1 LEFT JOIN T2,多次尝试后没有发生死锁。但是如果把 B 程序改成T2 LEFT JOIN T1的话,会非常容易发生死锁。

    而且SHOW ENGINE INNODB STATUS查看死锁信息信息的时候明确可以看到是record lock

    所以我想问的是,是不是T1 LEFT JOIN T2的时候,会优先把 T1 里的所有 record 锁上,然后再锁 T2 里的 record 。 但是T1 INNER JOIN T2的时候,顺序就不一定了。

    我猜测的是:

    • LEFT JOIN 的时候,因为 T1 表的所有数据都一定会用上,所以直接遍历上锁了,然后再去遍历 T2 ,所以 T1 中的所有数据一定先于 T2 上锁。
    • INNER JOIN 的时候是从 T1 选择一个 record 锁上,然后去遍历 T2 ,相当于一个双重 for 循环。所以 T1 、T2 中的上锁顺序不一定先后顺序如何。

    有人知道是不是这么回事吗?

    第 1 条附言  ·  2022-09-27 14:29:02 +08:00
    答案可以参考一楼回复。
    1 条回复    2022-09-27 14:27:10 +08:00
    zhanglintc
        1
    zhanglintc  
    OP
       2022-09-27 14:27:10 +08:00
    已经破案了,可以参考这篇文章: https://blog.csdn.net/mccand1234/article/details/105455626

    总结一下就是,正常情况下,`T1 INNER JOIN T2`的确是先锁左表 T1 ,然后锁右表 T2 。
    但是当 T1 表的数据量**远大于**T2 表的情况下,优化器可能会先读取 T2 表,然后读取 T1 表。此时读取顺序就跟书写顺序无关了。在我这个 case 里就表现为无论如何都会发生死锁。

    然后可以通过`STRAIGHT_JOIN`替换`INNER JOIN`来强制保证先锁左表,再锁右表。

    还有就是 LEFT JOIN 是可以保证先锁左表再锁右表的。

    最后想看锁表顺序可以通过`EXPLAIN`来看。比如`EXPLAIN SELECT T1 AS tab1 INNER JOIN T2 AS tab2`查看先读取的 T1 还是 T2 表。先读哪个就会先锁哪个。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3006 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 14:58 · PVG 22:58 · LAX 07:58 · JFK 10:58
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.