I am using 10.3.11. I have a table:
CREATE TABLE `tmp_app_to_db_status_report` (
`site` char(2) NOT NULL,
`project` varchar(7) NOT NULL,
`app_server` varchar(15) NOT NULL,
`instance` varchar(20) NOT NULL,
`status` varchar(7) NOT NULL,
`check_time` datetime NOT NULL,
PRIMARY KEY (`site`,`project`,`app_server`,`instance`,`status`,`check_time`),
KEY `idx_tmp_app_to_db_status_report` (`site`,`project`,`app_server`,`instance`,`check_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
The table contains data of Node1 and Node2 and it have the column 'site' to indicate the data coming from which node. Therefore Node1 only inserts, deletes, updates data of Node1 and Node2 only inserts, deletes, updates data of Node2.
I have a stored procedure which loops for more than 10,000 times and read the data from the table 'tmp_app_to_db_status_report' for processing. It will takes several minutes to finish.
On Node1, I start the stored procedure to read from 'tmp_app_to_db_status_report' and process Node1's data (i.e. where site='Node1'). During the execution of the stored procedure , on Node2, I delete/insert data of Node2 only (where site='Node2') from/to 'tmp_app_to_db_status_report'. The execution of the stored procedure on Node1 sometimes is terminated with error "ERROR 1317 (70100): Query execution was interrupted" and the massive "BF lock wait long for trx" messages are logged in the mysqld.err of Node1.
I have attachment the mysqld.err for reference.
Please advise and thanks in advanced.