Frequent Deadlock and lock timeout

ERPNext
1

Team,

To improve performance, we recently upgraded the server from 2 core-8GB to 4 core-16GB. we increased the gunicorn workers from 5 to 9. Our App Server and DB Server are in the same EC2 Instance.

Post Upgrade, we are getting to see frequent locks in database and users are not able to complete the transaction.

Any guidance on how to approach this issues?

Any temporary fix or method to clear the deadlocks in DB so that user can continue. ?

image

Thanks,
Saravana
Digixr Technologies

2

Hi,

The server upgrade by itself should not cause the problem, though the increase in gunicorn workers may be a contributing factor. If you are using a stock MariaDB setup this link came up when Googling the error message:
https://severalnines.com/database-blog/how-fix-lock-wait-timeout-exceeded-error-mysql

3

Thanks @smino for the response.

We see that in mariadb innodb_autoinc_lock_mode parameter is set to Consecutive Lock Mode. In this mode for bulk insert/update table level lock will occur.

We are suspecting that table level lock might be happening resulting in Lock Timeout.

Can we change the innodb_autoinc_lock_mode to Interleaved Lock Mode?

Will it improve the db performance? Will it have any implications?

Any suggestion on this?

Thanks,
Saravana

4

I have not tried to modify database parameters and cannot say if it will solve the problem. Before trying that approach , I might reduce the gunicorn workers to 5 to see if that was the cause of the problem. I will defer to someone with knowledge of database settings for advice on those.

5

Sure @smino. Also have you done any additional indexes on the fields to improve the performance?

Have raised an specific query on the same. Any inputs on these will be really helpful.

Thanks,
Saravana
DigixrTechnologies