I have a multi thread program (In this case in Qt) which every thread at a specific time, writes 500 records in a MySQL table (I call that as A_tbl) and also there is a trigger that inserts some values to other tables (I call them as B_tbl) and after that by some select queries in the trigger, I get the IDs of those inserted records in B_tbl.
But these select queries 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 (loc1,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);
...
END
If I change the threads to one, the error will not occur.
How can I solve this?
