We have a huge database stored in MySQL. One of these tables has 2 million registries. Another one has 10 million registries.
We have also 300 robots on different computers doing inserts and updates on the database (both tables) everytime.
**What's our problem?
** The robot get one registry and update this registry to (robotname = name of the robot and registrystatus = running), so others 299 robots wouldn?t get the same registry. After completed the process, the robot update all the information back and set (registrystatus = completed). Our problem is when we are updating the information, some robots get error "[url removed, login to view] (0x80004005): Deadlock found when trying to get lock; try restarting transaction". We need to fix that, either tuning our MySQL server configurations or changing our proccess of retriving/updating the information.
PS: Our server has 4GB RAM and are running windows with MySQL. We need an solution ASAP.