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.
我们有一个运行在凌晨5:15的脚本,并从36个远程站点检索数据。为每个站点运行一个单独的脚本实例,因此所有36个脚本同时运行。数据范围从400K到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:
当数据从远程站点进入时,脚本会尝试在MySQL数据库中删除相同日期和站点ID的行,然后插入新数据。我们经常收到此错误:
Lock wait timeout exceeded; try restarting transaction mysql
超过锁定等待超时;尝试重启事务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?
有没有办法删除/插入SQL队列并等待表解锁?
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.
我们有许多其他脚本插入来自所有36个站点的数据,但我们只有2个脚本出现此错误。这两个脚本碰巧带回的数据远远超过任何其他脚本。
1 个解决方案
#1
This isn't exactly the answer I was hoping for, but I have a workaround. When PHP tries to delete/insert the data into MySQL, it checks the return value to see if there were any errors. If it finds the lock wait error, it will retry for up to 10 minutes to insert the data. Since this change was made it appears to have fixed the issue. BTW, we use PDO for everything.
这不是我希望的答案,但我有一个解决方法。当PHP尝试删除/插入数据到MySQL时,它会检查返回值以查看是否有任何错误。如果发现锁定等待错误,它将重试最多10分钟以插入数据。由于这一变化,它似乎解决了这个问题。顺便说一下,我们将PDO用于一切。
#1
This isn't exactly the answer I was hoping for, but I have a workaround. When PHP tries to delete/insert the data into MySQL, it checks the return value to see if there were any errors. If it finds the lock wait error, it will retry for up to 10 minutes to insert the data. Since this change was made it appears to have fixed the issue. BTW, we use PDO for everything.
这不是我希望的答案,但我有一个解决方法。当PHP尝试删除/插入数据到MySQL时,它会检查返回值以查看是否有任何错误。如果发现锁定等待错误,它将重试最多10分钟以插入数据。由于这一变化,它似乎解决了这个问题。顺便说一下,我们将PDO用于一切。