Infinite MySQL db lock causing all request cancel (Lock wait timeout exceeded)

[mrogalski1]

Hello!

We're using one of the older version of ORY Hydra v1.10.7 (October 2021). We plan to upgrade to version 2.X.X but it's a matter of months.

Recently we experience error: "Error 1205: Lock wait timeout exceeded" which caused to disaster scenario on PROD - all incoming requests started to be cancelled due to timeout.

_error=map[debug:mysql select one: context canceled_"

After an hour we rebooted MySql server which temporarily fixed the issue, but it repeated again after 1 hour.
After cleaning MySQL DB size (decreasing size from ~ 400 GB to 30 GB) problem disappeared however we're not entirely sure what was the root cause.

We're not experts but our assumption is: with such big DB tables are locked for too long during token refresh causing other concurrent calls to timeout and cancel.

I found this bug: but it was fixed in October 2018.

Can you please:

1. Help us to understand what exactly could happen?
2. We still have to maintain Hydra v1.10.7 with MySQL - can you please provide feedback what we should focus on to keep it in good shape and avoid problems?

Any feedback appreciated!!!
Thanks!!!