SELECT…INNER JOIN…FOR UPDATE场景下行锁顺序及死锁规避方案

嘿，我来帮你梳理一下这个问题的核心点，这些都是MySQL实战中经常碰到的实用知识点：

一、SELECT...INNER JOIN...FOR UPDATE的行锁获取顺序 #

MySQL在执行这类带FOR UPDATE的关联查询时，行锁的获取逻辑是跟着查询的执行计划走的，具体细节如下：

• 首先按照执行计划中表的访问顺序处理：比如你的语句是SELECT * FROM A INNER JOIN B ON A.id = B.a_id FOR UPDATE，如果执行计划先访问A表，再访问B表，那么锁会先加在A表的匹配行上，再锁定B表对应的匹配行
• 单表内的锁顺序：如果查询用到了索引，会按照索引的遍历顺序来锁定行；如果是全表扫描，则按照数据在磁盘上的物理存储顺序来锁定
• 锁是逐步获取的：每找到一对符合JOIN条件的行，就会立刻给这两行加锁，而不是等所有匹配行都找到后再统一加锁

二、如何避免此类场景的死锁 #

死锁本质是交叉等待锁资源，针对这类JOIN加锁的场景，这些方法很实用：

• 统一加锁顺序：所有涉及多表加锁的事务，都严格按照相同的表顺序来访问（比如不管是查询还是更新，始终先操作A表再操作B表），从根源上避免交叉加锁
• 缩小锁定范围：一定要给查询语句加上合适的索引，避免全表扫描导致大量行被锁定。可以用EXPLAIN语句查看执行计划，确认是否用到了索引
• 缩短事务时长：尽量把事务拆小，减少锁的持有时间，比如不要在事务里做无关的IO操作（比如调用外部接口、写日志等）
• 调整隔离级别：如果业务允许，将事务隔离级别改为READ COMMITTED（默认是REPEATABLE READ），这个级别下MySQL的锁策略会更宽松，能降低死锁概率
• 添加重试机制：在业务代码中捕获MySQL的死锁错误码（1213），当出现死锁时自动重试事务，这是兜底的解决方案

三、Persona MySQL 5.7.21-20环境下临时表TQueue的定义 #

对应的创建语句如下（注意GROUP是MySQL关键字，所以用反引号包裹）：

CREATE TEMPORARY TABLE IF NOT EXISTS TQueue (
    ID bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    QUEUE_STATUS enum ('ADDED', 'PROCESSED', 'SUCCESS', 'ERROR') NOT NULL DEFAULT 'ADDED',
    QUEUE_TIMEOUT datetime NOT NULL,
    ACTION enum ('INSERT', 'DELETE', 'UPDATE') NOT NULL,
    REPORT_ID tinyint(4) UNSIGNED NOT NULL,
    LOGIN int(11) NOT NULL,
    `GROUP` char(16) NOT NULL,
    ENABLE int(11) NOT NULL,
    ENABLE_CHANGE_PASS int(11) NOT NULL,
    ENABLE_READONLY int(11) NOT NULL,
    -- 原语句末尾省略的字段可根据实际业务补充
    PRIMARY KEY (ID) -- 临时表通常建议显式指定主键，原语句未给出可按需调整
);

内容的提问来源于stack exchange，提问作者mr_blond