Today, we experienced a consistent lock timeout with two daemon scripts after running smooth for multiple months.
Both scripts was execution bulk operations and restarting both services individually didn’t solve the issue. After troubleshooting, we found that at least one specific row was casing the troubles.
However, no other processes (
show full processlist) was apparently locking the row. At times, no other processes was running at all.
Without any further knowledge on how to trace the locking process, we restarting (and upgraded from 6.8.6 to 6.8.9) the cluster and the issue resolved itself.
In MySQL, I’m used to get further details of failing transactions with
show innodb engine status. Does MemSQL expose a similar feature?
I would appreciate any recommendations on how to troubleshoot this further if the issue should happen again. At least, I’ll be glad to gather further details for you guys to explore in case this might be a bug of some forever-hanging transaction.