引言

本文介绍一个在 pt-osc 执行期间发生的形成死锁案例，其实之前的死锁文章 并发 replace into 导致 supremum X 锁与插入意向锁形成死锁 中也分析过相关案例，但由于理解不到位导致根因分析并不全面，形成因此本文进一步分析该类型的死锁死锁，包括死锁发生的形成原因与优化方法。云南idc服务商

现象

时间：2024-03-13 20:48:29

数据库版本：MySQL 5.7.21

现象：pt-osc 执行 DDL 期间多次发生死锁

分析

死锁日志

复制------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-03-13 20:48:29 0x7ff6fb2f8700 *** (1) TRANSACTION: TRANSACTION 385752159,死锁 ACTIVE 0 sec inserting mysql tables in use 2, locked 2 LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 MySQL thread id 17811400, OS thread handle 140698772580096, query id 25019811085 x.x.x.x eclp_po1_rw update REPLACE INTO `eclp_po1`.`_po_main_new` (`id`, `parent_no`, `po_no`, `unit_flag`, `unit_rule`, `bill_of_lading`, `dept_no`, `dept_id`, `dept_name`, `seller_id`, `seller_no`, `seller_name`, `org_id`, `org_no`, `org_name`, `distribution_id`, `distribution_no`, `distribution_name`, `warehouse_id`, `warehouse_no`, `warehouse_name`, `out_warehouse_no`, `out_warehouse_name`, `target_warehouse_id`, `expect_arrival_time`, `po_type`, `po_status`, `po_cancel_status`, `po_sign`, `out_source_no`, `out_po_no`, `out_seller_no`, `difference_remark`, `contacts`, `contacts_address`, `supplier_id`, `supplier_no`, `supplier_name`, `supplier_contacts`, `approval_time`, `approval_user`, `po_dl_result`, `po_dl_message`, `receive_level`, `isv_replenish_type`, `temperature`, `temperature_value`, `create_time`, `products_code`, `receive_box_number`, `products_name`, `update_time`, `create_user`, `update_user`, `yn`, `ts`, *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752159 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 45504c34343138303834393836363939; asc xxx;; 1: len 8; hex 80000404aa62df4b; asc b K;; *** (2) TRANSACTION: TRANSACTION 385752158, ACTIVE 0 sec inserting mysql tables in use 2, locked 2 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 17811470, OS thread handle 140698752878336, query id 25019811042 x.x.x.x eclp_po1_rw update REPLACE INTO `eclp_po1`.`_po_main_new` (`id`, `parent_no`, `po_no`, `unit_flag`, `unit_rule`, `bill_of_lading`, `dept_no`, `dept_id`, `dept_name`, `seller_id`, `seller_no`, `seller_name`, `org_id`, `org_no`, `org_name`, `distribution_id`, `distribution_no`, `distribution_name`, `warehouse_id`, `warehouse_no`, `warehouse_name`, `out_warehouse_no`, `out_warehouse_name`, `target_warehouse_id`, `expect_arrival_time`, `po_type`, `po_status`, `po_cancel_status`, `po_sign`, `out_source_no`, `out_po_no`, `out_seller_no`, `difference_remark`, `contacts`, `contacts_address`, `supplier_id`, `supplier_no`, `supplier_name`, `supplier_contacts`, `approval_time`, `approval_user`, `po_dl_result`, `po_dl_message`, `receive_level`, `isv_replenish_type`, `temperature`, `temperature_value`, `create_time`, `products_code`, `receive_box_number`, `products_name`, `update_time`, `create_user`, `update_user`, `yn`, `ts`, *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752158 lock_mode X locks gap before rec Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 45504c34343138303834393836363939; asc xxx;; 1: len 8; hex 80000404aa62df4b; asc b K;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752158 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 16; hex 45504c34343138303834393836363939; asc xxx;; 1: len 8; hex 80000404aa62df4b; asc b K;; *** WE ROLL BACK TRANSACTION (1)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.

其中：

REPLACE INTO _po_main_new，表明是形成 pt-osc 的 SQL，其中 SQL 截断；eclp_po1_rw，死锁显示是形成业务账号，表明对应 pt-osc 增量数据同步阶段；index po_no，WordPress模板死锁加锁索引；info bits 0，形成表明没有发生标记删除。死锁结合显示 ACTIVE 0 sec inserting，形成因此判断对应 insert，死锁不是形成 update；日志显示间隙锁与插入意向锁冲突导致死锁。

表结构

复制mysql> show create table eclp_po1.po_main \G

(责任编辑：域名)