Mysql – Deadlock error in inserting bulk of records to MySQL database using trigger and Qt multi threading

deadlockmulti-threadMySQLtrigger

I have a multi thread program (In this case in Qt) which every thread at a specific time, inserts 500 records in a MySQL table (I call that as A_tbl) and also there is a trigger for A_tbl that inserts some values to other tables (I call them as B_tbls) and after that by some select queries in the trigger, I get the IDs of those inserted records in B_tbls. (I think that I have to use these select queries and last_insert_id() is not useful in my case since maybe I need the ID of the first inserted record).

But these select queries along with the last insert query lead to this error:

Deadlock found when trying to get lock; try restarting transaction QMYSQL3: Unable to execute statement

This is my trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `IpAn`.`A_tbl_BEFORE_INSERT` BEFORE INSERT ON `A_tbl` FOR EACH ROW
BEGIN

INSERT IGNORE INTO source_names (source_name) VALUES (NEW.source_name);
INSERT IGNORE INTO locations (loc1,loc2) VALUES (NEW.loc1,NEW.loc2);

SET @source_names_id = (select id from source_names USE INDEX (PRIMARY) where source_name=NEW.source_name);
SET @locations_id = (select id from locations USE INDEX (PRIMARY) where loc1=NEW.loc1 and loc2=NEW.loc2);

INSERT IGNORE INTO sections (source_names_id,locations_id) VALUES (@source_names_id,@locations_id);

...

END

If I change the threads to one, the error will not occur.

How can I solve this?

Best Answer

SET @source_names_id = (select id from source_names USE INDEX (PRIMARY) where source_name=NEW.source_name);

-->

SELECT @source_names_id := id
    FROM source_names
    WHERE source_name = NEW.source_name;

AND have

PRIMARY KEY(id)
INDEX(source_name)

Do not use USE INDEX(PRIMARY) unless it is PRIMARY KEY(source_name). Even then, there is no need for USE INDEX.

Please provide SHOW CREATE TABLE if you need to discuss this further.

Related Question