/
Ranking Jira issues does not work after a deployment because of unreleased Lexorank locks

Ranking Jira issues does not work after a deployment because of unreleased Lexorank locks

Owned by Boyan Batchvarov

During an issue data deployment, CMJ locks the Lexorank table in order to rank the newly imported issues. As soon as ranking is completed, CMJ releases all locks. Lexorank locks are stored as LOCK_HASH and LOCK_TIME values in the AO_60DB71_LEXORANK.

If an SQL error occurs, CMJ does several further attempts to release the locks (until CMJ version 6.7.3 only one attempt is made). As a fail-safe, the locks automatically expire after 24 hours and are removed.

In rare occasions CMJ might not be able to release the Lexorank locks due to recurring SQL errors.



Solution:

  • To fix the issue, please clean the Lock_hash and lock_time columns in the AO_60DB71_LEXORANK table. Then try reranking a few issues through the Jira UI to make sure ranking is working normally. Side effects are not expected, but note that creating a backup is strongly recommended before doing DB updates.
    • Back up the Jira database before proceeding.
    • Shut down Jira.

    • Run the SQL command below: 

      UPDATE "AO_60DB71_LEXORANK" SET "LOCK_HASH" = NULL, "LOCK_TIME" = NULL;

    • Start Jira again.

    • Run a Full Re-index operation if the re-indexing errors in the beginning of the page were observed.
    • Run LexoRank re-balance for all fields.
  • Examine the underlying SQL exceptions, as there might be a more generic database issue. Search for errors in the Jira and Greenhopper logs, such as:

2020-12-15 20:28:32,745 pool-68-thread-1 WARN b45141 1227x3923009x1 1fbzyp6 10.5.146.141 /rest/configuration-manager/1.0/deployment/3 [greenhopper.manager.lexorank.LexoRankDaoImpl] Error while unlocking 1 entities with lock 362604de-7737-43c1-ac9c-61dce6bbdfe9.
2020-12-15 20:28:32,748 pool-68-thread-1 ERROR b45141 1227x3923009x1 1fbzyp6 10.5.146.141 /rest/configuration-manager/1.0/deployment/3 [greenhopper.manager.lexorank.LexoRankDaoImpl] ERROR: canceling statement due to user request
org.postgresql.util.PSQLException: ERROR: canceling statement due to user request


For more details about the problem and solution, please also review this KB article - https://confluence.atlassian.com/jirakb/ranking-failed-due-to-lock_hash-is-not-null-953658654.html 



PostgreSQL
There is a known issue in PostgreSQL which can cause a query to be cancelled at random:

https://stackoverflow.com/questions/13114101/postgresql-error-canceling-statement-due-to-user-request

This might happen during a CMJ deployment, while CMJ attempts to clear the LOCK_HASH and LOCK_TIME values in the AO_60DB71_LEXORANK. In this case some (or all) rows remain locked, which is why ranking may not work afterwards in Jira.



Related content