2015-05-05 17:39:11
0
We have a script that runs at 5:15am and retrieves data from 36 remote sites. A separate instance of the script runs for each site, so all 36 run at the same time. The data ranges from 400K to 1MB.
When the data comes in from the remote site, the script tries to delete rows for the same date and site id in our MySQL database and then insert the new data. We frequently get this error:
Lock wait timeout exceeded; try restarting transaction mysql
when it tries to delete the old rows. Sometimes the script just stops responding with no error message.
Does the error mean that it can't delete or insert the data because the table is locked?
How can we fix this?
Is there a way to have the delete/insert SQL queue up and wait for the table to become unlocked?
We have many other scripts that insert data from all 36 sites, but we are only having this error with 2 of our scripts. These 2 scripts happen to bring back far more data than any other script.