背景
insert ignore sql死锁了
排查
日志
排查了死锁日志: 我们定位到是有个表,简单来说,是有两个字段:
- id : 主键
- email_address: 唯一索引
一共有两个线程在写入,每次的操作就是批量用insert ignore写入,初步看都是很简单的sql, 后面google一下发现是和间隙锁有关: 相关blog
2023-05-03 05:06:45 0x4002719ffef0
*** (1) TRANSACTION:
TRANSACTION 218982374, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 481964, OS thread handle 70370189385456, query id 1320790141 10.4.3.228 app_cdp_0 update
insert ignore into cdp_user_email
(email_address,
clean_tag,
is_upload_emarsys,
created_at,
upload_at
)
value
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 189 page no 328322 n bits 272 index PRIMARY of table `customer_data_platform`.`cdp_user_email` trx id 218982374 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 189 page no 328322 n bits 272 index PRIMARY of table `customer_data_platform`.`cdp_user_email` trx id 218982374 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 218982373, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 481963, OS thread handle 70370189655792, query id 1320790139 10.4.3.228 app_cdp_0 update
insert ignore into cdp_user_email
(email_address,
clean_tag,
is_upload_emarsys,
created_at,
upload_at
)
value
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 189 page no 328322 n bits 272 index PRIMARY of table `customer_data_platform`.`cdp_user_email` trx id 218982373 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 189 page no 328322 n bits 272 index PRIMARY of table `customer_data_platform`.`cdp_user_email` trx id 218982373 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)修改方式
将批量insert 改成每次只写入一条