insert select发生死锁的业务场景及分析

insert select发生死锁的业务场景及分析

前两天，甲方DBA反馈数据库发生了死锁，于是我们要来死锁日志并对其进行分析

SQL死锁日志如下（敏感数据已模糊）

*** (1) TRANSACTION:
TRANSACTION 4309749954, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 14824011, OS thread handle 139701529425664, query id 18948460170 xxx.xxx.xxx.xxx Sending data
insert into 表A ()
        select XXX from 表B where 主键 = 'xxxxxxx'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6297 page no 370294 n bits 96 index PRIMARY of table 表A trx id 4309749954 lock mode S locks rec but not gap waiting
Record lock, heap no 24 PHYSICAL RECORD: n_fields 48; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 4309749953, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 14928905, OS thread handle 139701347866368, query id 18948460173 xxx.xxx.xxx.xxx updating
delete
        from 表B
        where 主键 = 'xxxxxx'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6297 page no 370294 n bits 96 index PRIMARY of table 表A trx id 4309749953 lock_mode X locks rec but not gap
Record lock, heap no 24 PHYSICAL RECORD: n_fields 48; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6299 page no 3190 n bits 120 index PRIMARY of table 表A trx id 4309749953 lock_mode X locks rec but not gap waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 33; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

业务场景

本地上报数据，需要根据表B的数据在表A中插入数据后再删除表B的数据， 这是一个事务，但在不同的情况下有不同的上报，走了不同的方法，其中方法A（事务1）是使用的insert into 表A () select from 表B where 主键，然后delete from 表B where 主键；方法B（事务2）是先select from 表B where 主键，然后insert into 表A，然后delete from 表B where 主键。在正常情况下，这两个方法不会在短时间内同时接收到本地上报并执行，但由于是第三方的本地，实际生产过程中出现了两个方法的并发执行。

两种锁

共享锁

共享锁（S锁）是一种允许多个事务同时读取同一资源的锁。当一个事务获得了一个共享锁后，其他事务仍然可以获得共享锁，但不能获得排他锁（X锁），直到该共享锁被释放。共享锁通常用于读取操作，因为读取操作不会改变数据，因此可以允许多个事务同时读取同一资源，提高并发性能。

排他锁

排他锁（Exclusive Lock，X锁）是一种独占锁，它会阻止其他事务同时对同一资源进行修改。在MySQL中，当一个事务获取了排他锁时，其他事务无法获取到任何类型的锁（包括共享锁和排他锁），直到该事务释放了锁。在给定的时间点，只有一个事务可以获取到排他锁。一般情况下，排他锁用于写操作，比如更新、插入或删除数据。

错误分析

根据MySQL的死锁日志和业务场景，以及Kibana中的业务日志，可以分析到出现死锁的业务流程情况如下：
（1）方法B接收到上报，执行语句select from 表B where 主键，此时没问题
（2）方法B继续执行语句insert into 表A，获取了表A的排他锁
（3）方法A接收到上报，执行语句insert into 表A () select from 表B where 主键，获取了表B的共享锁，并等待表A的排他锁释放
（4）方法B继续执行语句delete from 表B where 主键，由于表B存在共享锁，所以获取不到表B的排他锁，等待表B的共享锁释放
为啥（3）会获取表B的共享锁呢，正常select是不会拿共享锁的，问题就出现在insert into select上：
MySQL的insert select语句会自动加行级共享锁（S锁）到from子句中的所有源表上，以确保在插入新数据之前不会有其他事务对源数据进行修改或删除操作。这些S锁将一直保持到整个insert select语句执行完毕并提交。另外，在向目标表插入数据时，MySQL会为目标表加上行级排他锁（X锁）来避免其他事务对目标表进行修改操作，直到当前事务执行完insert select语句并提交为止。总之，MySQL的insert select语句会自动加上S锁和X锁来确保数据的一致性和完整性。
这两个事务操作的都是一条数据，主键是同一个，所以发生死锁也不足为奇了。

优化方案

将方法A的insert into 表A () select from 表B where 主键改为和方法B一样的先select from 表B where 主键再insert into 表A，不让它获取表B的共享锁，那么死锁就不会发生啦。

小结

在实际业务中，insert select 语句是一种非常常见和有用的 SQL 操作。它可以根据一定的条件从一个表中选择数据，然后将这些数据插入到另一个表中，从而实现数据的复制或迁移。insert select 语句的好处是可以提高数据处理的效率，避免了多次单独的 SQL 操作，因此在某些情况下非常推荐使用，例如：
（1）在需要将大量数据从一个表复制到另一个表时，使用 insert select 可以显著提高性能，减少系统负载。
（2）在需要对大量数据进行筛选、聚合、计算等操作时，使用 insert select 可以避免使用多次 SQL 查询，从而提高效率。
但是，在实际应用中，insert select 语句也存在一些问题，例如：
（1）在使用 insert select 进行大量数据迁移时，需要注意对目标表的锁定，防止对其他操作的影响。
（2）insert select 语句不能同时更新源表和目标表，因此在需要对数据进行复杂处理时，可能需要使用多条 SQL 语句来完成。
因此，在实际业务中是否推荐使用 insert select 语句，需要根据具体的情况进行评估和选择。