Hello everyone, I have a problem and I would like to ask you for help.
I now have a business table with the following structure
CREATE TABLE rms_pickup_step_agg (htl_cd varchar(20) DEFAULT NULL COMMENT 'Hotel number',
para_typ int(11) DEFAULT NULL COMMENT 'summary type',para_cd varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'summary code',sub_typ int(11) DEFAULT NULL COMMENT 'subtype',sub_cd varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT 'subtype code',pickup_day int(11) DEFAULT NULL COMMENT 'Pickup day',live_dt varchar(30) DEFAULT NULL,occ float DEFAULT NULL COMMENT ' Number of rental rooms',rev float DEFAULT NULL COMMENT 'Room fee income',update_dt datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Update time',
UNIQUE KEY idx01_rms_pickup_step_agg (htl_cd,para_typ,para_cd,sub_typ,sub_cd, pickup_day,live_dt) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Pickup pace table';
Now there are two threads executing statements at the same time,
Thread 1 executes replace INTO rms_pickup_step_agg (htl_cd,para_typ,para_cd,sub_typ,sub_cd,pickup_day,live_dt,occ,rev,update_dt) VALUES ('101336', '0 ', '0', '3', 'NC', '14', '2017', '7.0', '3160.40', '2017-05-29 07:31:27');
Do not submit
Thread 2 executes replace INTO rms_pickup_step_agg (htl_cd,para_typ,para_cd,sub_typ,sub_cd,pickup_day,live_dt,occ,rev,update_dt) VALUES ('101336', '0', '0', '3', 'NC ', '14', '2016', '7.0', '3160.40', '2017-05-29 07:31:27');
It was found that thread 2 was waiting for the lock.
Why does a lock occur? Thanks!
天蓬老师2017-06-06 09:54:12
Are you sure there is a deadlock? It stands to reason that there is no conflict. This unique index does not conflict. This table does not have a primary key. Please add a primary key.
为情所困2017-06-06 09:54:12
Under the default isolation level of innodb, you are replacing a unique index. In theory, only row locks should not block. Are you sure you only have these operations? I haven’t seen any updates to this kind of transaction lock in 5.7 either