I'm developing a mobile application whose backend is developed in Java and database is MySQL.
We have some insert and update operations in database tables with a lot of rows (between 400.000 and 3.000.000). Every operation usually doesn't need to touch every register of the table, but maybe, they are called simultaneously to update a 20% of them.
Sometimes I get this errors:
Deadlock found when trying to get lock; try restarting transaction
Lock wait timeout exceeded; try restarting transaction
I have improved my queries making them smaller and faster but I still have a big problem when some operations can't be performed.
My solutions until now have been:
- Increase server performance (AWS Instance from m2.large to c3.2xlarge)
SET GLOBAL tx_isolation = 'READ-COMMITTED';
- Avoid to check foreign keys:
SET FOREIGN_KEY_CHECKS = 0;(I know this is not safe but my priotity is not to lock de database)
- Set this values for timeout variables (
SHOW VARIABLES LIKE '%timeout%';):
But I'm not sure if these things have decreased performance.
Any idea of how to reduce those errors?
Note: these others SO answer don't help me: