Add lock JIRA table to fast track transition code

[Alexey I_ Kiyashko]

Hello!

We run JIRA 5.0.3 and use Script Runner plugin 2.0.7

Is there a way to lock JIRA tables when executing the "fast track transition" post function? I can do it in SQL, but I need to use this in the groovy script.

[Jozef Kotlár · Accepted solution]

It seems to me that you need to dive into MS SQL concurrency control (isolation level) - the default level is way too pessimistic - at least for JIRA.

Did you followed installation steps for MS SQL? Step 5.

[Alexey I_ Kiyashko]

Jozef - it was not me who installed JIRA, but I see this in dbconfig:

<jdbc-datasource>
<url>jdbc:jtds:sqlserver://jiradbserver:1433/jiradb</url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
<username>username</username>
<password>password</password>
<pool-size>15</pool-size>
</jdbc-datasource>

So I guess that the step 5 of installation is done correctly with jdbc connection

[Jozef Kotlár]

Sorry - Step 3.5

[Alexey I_ Kiyashko]

Thank you for suggestion, Jozef!

READ_COMMITTED_SNAPSHOT option is OFF

I'll turn it on when the working day is over and then try to test.

[Alexey I_ Kiyashko]

It seems that the problem was there - no more deadlocks in logs. Thank you for your suggestion!

[Nic Brough -Adaptavist-]

Why do you think you need to lock the tables? It's pointless if things are working properly, which suggests you're breaking something, or you've got an issue and you're reaching for some solution (and in this case, you're reaching for the wrong one)

[Alexey I_ Kiyashko]

The point is that things are not working properly :( Look in the logs:

2014-05-26 10:54:13,355 Thread-1382 ERROR user_1 649x201397x1 g5v7ch 192.168.128.17 /secure/WorkflowUIDispatcher.jspa [atlassian.jira.transaction.TransactionSupportImpl] Unable to rollback transaction : Error occurred while rolling back transaction. (Connection net.sourceforge.jtds.jdbc.ConnectionJDBC3@25222683 is closed.)

2014-05-26 10:54:13,527 Thread-1382 ERRORuser_1 649x201397x1 g5v7ch 192.168.128.17 /secure/WorkflowUIDispatcher.jspa [groovy.canned.utils.WorkflowUtils] Errors: {}
Error Messages: [Could not find workflow instance #59759: root cause: SQL Exception while executing the following:SELECT NAME, INITIALIZED, STATE FROM OS_WFENTRY WHERE ID=? (Connection net.sourceforge.jtds.jdbc.ConnectionJDBC3@25222683 is closed.)]

2014-05-26 10:54:18,238 Thread-1385 ERROR user_1 652x201664x8 g5v7ch 192.168.128.17 /secure/QuickCreateIssue.jspa [atlassian.jira.workflow.OSWorkflowManager] Caught exception while attempting to perform action 211 from workflow 59761 on issue 'ISSUE-21'
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:ChangeItem][newvalue,null][field,assignee][oldstring,user_2][id,274798][fieldtype,jira][oldvalue,username_2][newstring,null][group,186910] (SQL Exception while executing the following:INSERT INTO changeitem (ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING) VALUES (?, ?, ?, ?, ?, ?, ?, ?) (Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.))

I think this occurs because I have a lot of these post functions running at same time - that's why I concluded I need to lock the table.

[Nic Brough -Adaptavist-]

I'm afraid that is, as I feared, the wrong answer. You should not lock the table, you need to correct the settings on the server side. Jozef already dug that up for you :-)