Third-party apps that create multiple subtasks as part of workflow operations may trigger LexoRank deadlocks

Summary

Whenever user uses a third-party plugin to create several subtasks during workflow operation (for example ScriptRunner) JIRA Software's ranking system, LexoRank, will deadlock if those workflow operation being performed on two (or more) issues at the same time.  

Steps to Reproduce

Let's say we want to create 5 subtasks during workflow transition.

Whenever sub-task is created LexorankIssueEvenListener is invoked and it performs initial ranking. During that ranking, LexoRankOperation goes into synchronized block and acquires a lock in database by performing an update over several rows in lexorank table. Due to transaction opened by workflow from now on lexorank table (or desired set of records) is locked until transaction is completed. After that LexoRankOperation completes for first subtask and returns control to workflow. It is ready to create second subtask.

At the same time another thread performs creation of 5 subtasks for another issue through same workflow operation. That thread goes into synchronized block and tries to acquire lock in database:

synchronized (rankFieldIdLocks.get(rankFieldId))
            {
                // find new available entity
                LexoRankRow[] unlockedRankRows = dao.getMaximumMarkerRowAndPreviousRow(rankFieldId);
                LockOutcome lockOutcome = dao.acquireLock(unlockedRankRows);

It stuck here because to acquire that lock it needs to update lexorank table which is locked in another transaction. So it waits for exclusive lock in database and holds lock in java.

At the same time first thread with workflow operation ready to create second subtask. It creates it and invokes LexoRankIssueEventListener which tries to perform initial ranking. it goes to rankInitially() method and stuck there waiting for possibility to acquire a lock to enter synchronized block.

So we have second thread waiting for lock in database and holding a lock in java and we have first thread waiting for lock in java and holding lock in database. Then somebody trying to perform workflow operation on third issue.... Eventually, jira hangs.

Expected Results

Workflow creation works fine. JIRA is stable.

Actual Results

JIRA hangs and the following error is thrown in the atlassian-jira.log:

2016-08-27 12:49:57,057 http-nio-127.0.0.101-8080-exec-109 ERROR username 940x1974177x4 1no823r 127.0.0.1 /secure/QuickCreateIssue.jspa [c.a.g.manager.lexorank.LexoRankDaoImpl] Lock wait timeout exceeded; try restarting transaction
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) 

Workaround

Disable third-party plugin subtasks creation or follow steps from one of the comments below for Script Runner plugin.

• This has also been found to occur with the Create on Transition for JIRA plugin