How do I track down intermittent locks in a MySQL database?


Currently, we have a CRUD plus reporting application that talks to one MySQL database. Intermittently users will report locks when searching, currently, I can only get the approximate time of when that happens (accurate to the minute). The stored procedures on the other hand are logged to the millisecond.

So far I have not been able to trigger the issue, and the notifications by the users of the problem are not close enough to real-time to see what queries are running when the lock is happening.

One idea so far would to be to turn on all query logging the next time it happens since the locks seem to be clustered.

Are there other strategies for finding which queries are causing intermittent locks?

Why should this post be closed?


Ref. "we have a CRUD plus reporting application that talks to one MySQL database" - is there any way to use a separate database for the reporting application? Another way is to prevent the reports selects from locking the operational tables by using a "no-lock" option (SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; ), but this can lead to incorrect data. The best approach is still to have a separation between the operational database and the reporting one. ‭Alexei‭ about 1 month ago

@Alexei I don't think its the reports, but rather inserts crossing paths with the selects. The reports used to cause locks, but that was fixed by breaking the queries into chunks and then doing the calculations outside of the DB. ‭Charlie Brumbaugh‭ about 1 month ago

@Charlie Brumbaugh‭ Yes, the underlying technical issue is indeed having inserts and selects in the same time. That is why reporting typically has its own database where some aggregates can be recalculated and one almost never gets into trouble like this. ‭Alexei‭ 30 days ago

0 answers

Sign up to answer this question »