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
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>
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ah, good, you found it - set it for the user. Not an application setting then, and nothing to do with the code.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, a server setting in a config file, I know.
Atlassian support are very good, all credit to them :-)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That's a MS-SQL Server question really. Start at http://msdn.microsoft.com/en-us/library/ms187807.aspx
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It's also not possible to do that with ALTER TABLE:
http://msdn.microsoft.com/en-us/library/bb522682(v=sql.105).aspx
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
How could it break any other installations?
If you're still afraid then make it configurable => zero risk => another satisfied customer :-)
I guess other users either
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.