Description:
Based on two facts below, something wrong happens.
1. During the apply on the slave, the choice of index is hard-coded in the function sql/log_event.cc:search_key_in_table().
2. Unlike the master, the slave does not call unlock_row().
Consider a scenario with a table that lacks an explicit primary key but contains a default NULL unique key. When performing a DML operation with a NULL value for the unique key, the slave will choose to search for the unique key but will not unlock the rows that do not meet the WHERE conditions. Consequently, this results in the slave locking numerous records with NULL unique keys, potentially leading to blocking issues for subsequent DML operations.
How to repeat:
1. prepare the table and data
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL,
`col` varchar(255) DEFAULT NULL
UNIQUE KEY `uk` (`col`) USING BTREE,
KEY `sk` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
delimiter $$
create procedure auto_insert(IN times int)
BEGIN
declare i int default 1;
truncate table t1;
while(i<times)do
insert into t1 values(i,null);
set i=i+1;
end while;
END$$
delimiter ;
call auto_insert(1000);
2. Do XA trx 1 first
xa begin 'x1';
delete from t1 where id=100 and col is null;
xa end 'x1';
xa prepare 'x1';
3. Do trx 2 next:
delete from t1 where id=99 and col is null;
Result:
In the master, the second transaction will proceed without being blocked by the first. However, on the slave, the first transaction will hold many NULL unique key records and block the second transaction.