1

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

Ramin Rabii
  • 359
  • 4
  • 14
  • try using queries with no lock – Nishant Nair Feb 02 '17 at 09:11
  • Thanks but how can I prevent other users to modify the row that I have not finished working on them yet? This is a requirement ... – Ramin Rabii Feb 02 '17 at 09:46
  • I also found this [link](http://stackoverflow.com/questions/31089317/foeign-key-locking-transaction-timeout-when-a-second-database-connection-is-open?rq=1) but it did not solve the problem because I need to have separate connections to DB. – Ramin Rabii Feb 02 '17 at 09:57
  • Well, one point of transactions is that two seperate connections don't interfere, so MySQL works as intended. The first thing I would look at is if you really need two connections which is usually not the case, since you can access both dbs at the same time by one connection, even in one query (since that's what the foreign key will internally do too). But without more information (e.g. why you need two connections) it's hard to find the solution. I guess one idea would be to do the inserts to both tables in a stored procedure on ONE server (that then accesses both servers from 1 connection). – Solarflare Feb 02 '17 at 11:52
  • There were two scenarios for keeping two separate connection. 1) would that be part of possibility of future expansion to two different servers 2) I am using transaction lock on row level and commit each connection at the end of all procedure but beside having two or one connection to DBs, this error happens unless I commit the connection to first DB and then second one but in this procedure I need to confirm all at the end. – Ramin Rabii Feb 02 '17 at 14:04

1 Answers1

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

Ramin Rabii
  • 359
  • 4
  • 14