Add lock JIRA table to fast track transition code

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.

2 answers

1 accepted

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.

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

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.

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

0 vote

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)

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.

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 :-)

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,296 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot