Forum Moderators: open
I am writing a rather complex web application by using PHP/mySQL combination, and am having some problems with mySQL DEADLOCK occurring on the system. Tried Googling a bit but with no real results ..
The deal is that I have a largish (~400 mb) database to which users make changes from the web interface, and then there is an automated task run on the background every half hours or so, which also makes queries to the database (select, update, delete, insert .. everything). A couple of the queries in the automated script are a bit complex with many joins and so on (but they work just fine anyway).
Problem is that every now and then, let's say once a week, the automated script fails to run because of a Deadlock error in one of the big SQL queries. Specific error is 'Deadlock found when trying to get lock; try restarting transaction'.
There is no real error in the queries as most of the time they run just fine. So I suspect this happens when the system tries to perform a large query but at the same time some user is also doing something (normal) at the web interface which also makes a query to the same table, and thus the other query fails for some reason.. This is just my guesswork though.
So questions - what causes this phenomena, and what is the best way to avoid this? Should I try perhaps using the 'lock table ...' before excecuting the large query at the automated script (and 'unlock' it after that), or something else? Making the query more simple could also help but really don't prefer that at this time if there is another solution.
Unfortunately, I do not remember its exact name.
You probably should check my.cnf options in that direction.
