MySql transaction lock timeout (1205) when insert into two databases which have foreign key on primary

Question

I have two database in mysql running with innoDB and the second db has foreign key related to primary key of the first one.

In my code I need to insert in the first DB and get id for that and insert to the second one.

All goes well unless I start transaction and that locks the first DB for the new ID and second one cannot insert that ID and I am getting SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction. When I remove foreign key then it goes fine.

I need to mention that I use PHP PDO to connect to MySql and I create separate connection to each DB and I also cannot commit the first db transaction so that second one goes through because I need to make sure all transactions to all dbs goes fine in whole process and then start committing them.

Thanks Ramin

Answer 1

It seems that the only solution would be either committing the first DB and start transaction on the second one or removing the foreign key between to DBs.

Please give solutions if you see better one and I would accept that as the answer any time in future.

Thanks Ramin