记一次mysql insert/update 语句导致锁表排查记录

最近在生产环境发现一个简单的update语句需要执行18s之久，分析慢sql日志，发现是在等待获取锁

# Time: 2024-05-20T13:00:45.103941Z
# User@Host: sky[sky] @  [172.31.19.184]  Id: 3446013
# Query_time: 18.755920  Lock_time: 18.755554 Rows_sent: 0  Rows_examined: 1
SET timestamp=1716210026;
update sky_order set status = 6 where id = 44637;

往前翻慢SQL,结合业务找到原因是因为前面有一个事务，执行时间比较长，把订单表锁住了。锁表的SQL简化如下：

UPDATE
	sky_order_rec r
INNER JOIN (
		SELECT
			sky_id,
			COUNT(*) renew_15d
		FROM
			(
				SELECT o.* FROM sky_order o
				INNER JOIN (
					SELECT sky_id FROM sky_order_rec GROUP BY
						sky_id
				) r ON o.sky_id = r.sky_id
			) a
		GROUP BY
			student_id
	) r2 ON r.sky_id = r2.sky_id
        SET
	r.renew_15d = r2.renew_15d

mysql在执行更新的过程中，会把子表相关的行给锁住，这个时候其他事务如果修改了相关的行，就需要等待释放锁

临时优化办法： 引入一张临时表，sky_order_rec_temp，将上面的update语句拆分为2句:

insert into sky_order_rec_temp (sky_id,renew_15d) 
    SELECT
            sky_id,
            COUNT(*) renew_15d
    FROM
            (
                    SELECT o.* FROM sky_order o
                    INNER JOIN (
                            SELECT sky_id FROM sky_order_rec GROUP BY
                                    sky_id
                    ) r ON o.sky_id = r.sky_id
            ) a
    GROUP BY
            student_id;
            
UPDATE
	sky_order_rec r
INNER JOIN sky_order_rec_temp r2 ON r.sky_id = r2.sky_id
   SET r.renew_15d = r2.renew_15d            

这样锁也只是锁临时表，同时要注意执行insert into 时需要将事物隔离级别改为READ_COMMITTED，可通过Spring的Transactional注解调整。 因为在可重复度隔离级别下，insert into 也会锁相关的子表。

以上只是不改业务逻辑情况下的临时解决办法，最好的办法是调整业务，正常情况下不允许在代码里面UPDATE带子查询、INSERT INTO 等复杂SQL语句，容易产生长事物，导致锁表，甚至死锁的产生。