SET IMPLICIT_TRANSACTIONS ON -- How to do that permanently

Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 4, 2013

I get deadlocks and the wiki states, that one has to SET IMPLICIT_TRANSACTIONS ON: https://confluence.atlassian.com/display/JIRAKB/Deadlock+when+running+JIRA+with+SQL+Server

But it seems this doesn't persist. How can I make that setting permanent in SQLServer?

EDIT: It is impossible to set that option at db level. It's only possible to set it as defaults for all users of the whole instance (i.e. server):

http://msdn.microsoft.com/en-us/library/ms190707(v=sql.90).aspx

http://msdn.microsoft.com/de-de/library/ms176031(v=sql.105).aspx

2 answers

1 accepted

0 votes
Answer accepted
Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 10, 2013

One needs to configure the database in dbconfig.xml in the jira home directory and append ";autoCommit=false" to the url:

<url>jdbc:jtds:sqlserver://localhost:1433/jiradb;autoCommit=false</url>

http://sourceforge.net/p/jtds/patches/109/

Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 10, 2013

Yes, it was a configuration in Atlassian's config files ;-)

Anyway, you can't deny that all documentation on Atlassian's side about that problem is wrong, even the (initial) support.

BTW: Thanks to Richie Gee [Atlassian] for investigating and finally solving that issue.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 10, 2013

Ah, good, you found it - set it for the user. Not an application setting then, and nothing to do with the code.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 10, 2013

Yes, a server setting in a config file, I know.

Atlassian support are very good, all credit to them :-)

0 votes
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 4, 2013

That's a MS-SQL Server question really. Start at http://msdn.microsoft.com/en-us/library/ms187807.aspx

Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 4, 2013

Of course I did read that page before asking here.

And from all I read, I still think, that the described solution is wrong and that the setting is session-only and as such only useful if issued by the application (JIRA) itself.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 4, 2013

No, that's the point - it's a database server setting, NOT the application.

The application could do it with the session, but then it would need more rights over the database than it needs for day-to-day running, and you'd have to do database specific coding. (i.e. bad security practice and bad coding practice)

Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 5, 2013

I would agree with you, if it was possible to set that option permanently.

But sadly this isn't possible. Even Atlassian support got this wrong and replied with this article:

http://blog.techdreams.org/2007/11/implicit-transactions-onoff-sql-server.html

Which clearly says:

"5. [...] from now onwards all the new connections opened through Management Studio/Workbench starts an implicit transactions"

Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 5, 2013

It's also not possible to do that with ALTER TABLE:

http://msdn.microsoft.com/en-us/library/bb522682(v=sql.105).aspx

Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 5, 2013

I edited the question to include my new findings. Is shows that it's impossible to set that option permanently on a db level.

That's why I say, that it's Atlassians responsibility to set that option at connection time or otherwise remove SQL-Server from the list of supported databases. Alternatively, Atlassian should require customers to use a dedicated instance for JIRA.

(I agree that SQL-Server is the worst db-alike shit in this universe, but in the current universe I have no choice).

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 5, 2013

Ok, it's not a database setting, it's a user one. http://msdn.microsoft.com/en-us/library/ms176031.aspx

I still don't think it's an Atlassian issue directly, even if you don't do it at a user level. If it needs to be set permanently, then it feels like it should be a driver setting.

I also wonder why only there's only one question about it in Answers - that implies your setup is pretty rare and there's something very different about what you've got compared with everyone else using Jira and SQL-Server - if that's the case, then having Atlassian implement somthing that could break all the other users is probably going to be a complete non-starter.

Nabil Sayegh
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 5, 2013

How could it break any other installations?

  1. Detect database
  2. If option needs to be set -> set it, otherwise do nothing

If you're still afraid then make it configurable => zero risk => another satisfied customer :-)

I guess other users either

  1. have dedicated instances just for JIRA
  2. don't know that it has to be set
  3. switch to a different database

Suggest an answer

Log in or Sign up to answer