Balancing does not complete due to UPDATE AO_60DB71_LEXORANK exceeding acquireLockByRowIds timeout

Summary

Lexorank Balancing does not complete due to UPDATE AO_60DB71_LEXORANK exceeding acquireLockByRowIds timeout

Steps to reproduce

1. Go to Lexorank management in your instance. type g + g + lexorank management.
2. On the balancing section, click on Balance all fields > wait for sometimes then click on Refresh

Expected Result

Lexorank balancing will be completed

Actual Result

1. Lexorank balancing does not complete
2. Problem related to this SQL query performed by com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.save(...):

   UPDATE AO_60DB71_LEXORANK SET FIELD_ID = ?, ISSUE_ID = ?, RANK = ?, TYPE = ? WHERE ID = ? AND LOCK_HASH = ? AND LOCK_TIME = ? AND LOCK_TIME >= ?

1. It cannot find any rows with such criteria specified in the WHERE clause ID = ? AND LOCK_HASH = ? AND LOCK_TIME = ? AND LOCK_TIME >= ?
   • LOCK_TIME = ? goes from acquired Lock performed by com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.acquireLockByRowIds(...):

     UPDATE AO_60DB71_LEXORANK SET LOCK_HASH = ?, LOCK_TIME = ? WHERE ID IN (?, ?) AND (LOCK_HASH IS NULL OR LOCK_TIME < ?)

• • LOCK_TIME >= ? is being calculated - System.currentTimeMillis() - 1500.

Additional Information

This would happen in large instance (with more than 1M issues)

Potential Workaround

As per JSWSERVER-15874, if currently using MySQL 5.6.x, upgrading MySQL to 5.7 might fix the problem