并发执行相同的update语句产生的死锁

[yanwuyang]

CREATE TABLE adword_1 (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键编号',
bu_id int(11) NOT NULL COMMENT '站点',
dimension_type smallint(6) NOT NULL COMMENT '维度类型',
dimension_value varchar(100) NOT NULL COMMENT '维度值',
ad_type smallint(6) NOT NULL COMMENT '类型',
ref_id varchar(200) DEFAULT NULL COMMENT '关联业务',
creator varchar(100) DEFAULT NULL COMMENT '操作人',
created datetime NOT NULL COMMENT '创建时间',
updated datetime NOT NULL COMMENT '修改时间',
yn smallint(6) NOT NULL DEFAULT '1' COMMENT '是否有效:0=无效,1=有效',
PRIMARY KEY (id),
KEY inxex_bu_id_dimension_value (bu_id,dimension_value) USING BTREE,
KEY inxex_updated (updated) USING BTREE,
KEY index_buid_dv_dt_at_yn (bu_id,dimension_value,dimension_type,ad_type,yn)
) ENGINE=InnoDB AUTO_INCREMENT=7371662 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMMENT='';

---

LATEST DETECTED DEADLOCK

2022-06-17 08:44:01 0x7fa14eb9f700
*** (1) TRANSACTION:
TRANSACTION 7812220241, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 6626379, OS thread handle 140355400283904, query id 9145445661 11.43.237.206 adword_28_rw Searching rows for update
UPDATE adword_244
SET yn = 0,

        updated = now()
     

    where
    yn = 1
    AND dimension_value = '10051846311262'
    AND bu_id = 301
     
        AND dimension_type = 1
     
     
     
        AND ref_id = 'sg:289138'
     
     
        AND ad_type = 2

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 274 page no 98798 n bits 304 index index_buid_dv_dt_at_yn of table adword_28.adword_244 trx id 7812220241 lock_mode X waiting
Record lock, heap no 229 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 80000000006eeebd; asc n ;;

*** (2) TRANSACTION:
TRANSACTION 7812220240, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4996
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 6627449, OS thread handle 140330787272448, query id 9145445660 11.40.39.203 adword_28_rw updating
UPDATE adword_244
SET yn = 0,

        updated = now()
     

    where
    yn = 1
    AND dimension_value = '10051846311262'
    AND bu_id = 301
     
        AND dimension_type = 1
     
     
     
        AND ref_id = 'sg:289138'
     
     
        AND ad_type = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 274 page no 98798 n bits 304 index index_buid_dv_dt_at_yn of table adword_28.adword_244 trx id 7812220240 lock_mode X
Record lock, heap no 229 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 80000000006eeebd; asc n ;;

Record lock, heap no 232 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 8000000000704355; asc pCU;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 274 page no 98798 n bits 304 index index_buid_dv_dt_at_yn of table adword_28.adword_244 trx id 7812220240 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 229 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 80000000006eeebd; asc n ;;

*** WE ROLL BACK TRANSACTION (1)