Question about ALLOW_SNAPSHOT_ISOLATION

[Vincent B]

Dear Atlassian Community,

I'm Vincent and I'm admin of Atlassian products in my Research Oragnization.

We recently add an issue with our Jira instance (Server license with version 8.13.4) on which mail queue was not flushed every 1 minute as it should be, but only from time to time, not every day and not at a consistent time.

By adding some logs in DEBUG and checking the mail queue scheduler event, I indeed saw that sometime, the scheduler was executed once in a while, and therefore mails were stukc in queue. However flushing manually the mail queue worked perfectly. 

A thread dump analysis showed that all Caesium threads were fully used by the addon Notification Assistant for JIRA and therefore not available to execute the mail scheduler. 

Disabling the NAFJ addon improved immediately performances and the mail scheduler was again executed every 1 minute, but unfortunately did not send anymore its notifications. 

When this problem occurred, we also noticed a spike in the database connection pool as well as a general slowness in Jira. The DBA also noticed that the following query was always taking a lot of CPU and executed quite often: 

SELECT CG.ID, CG.issueid, CG.AUTHOR, CG.CREATED, CI.ID, CI.groupid, CI.FIELDTYPE, CI.FIELD, CI.OLDVALUE, CI.OLDSTRING, CI.NEWVALUE, CI.NEWSTRING FROM dbo.changegroup CG INNER JOIN dbo.changeitem CI ON CG.ID = CI.groupid WHERE CG.issueid=@P0 ORDER BY CG.CREATED ASC, CI.ID ASC

The Atlassian Support explained that it was a query used to keep the change history table up to date.

Finally our DBA applied the change below that immediately solved the problem:

ALLOW SNAPSHOT ISOLATION set to TRUE

We found a lot of topic talking about Read_commited_Snapshot that must be set to true but not about this change. 

We would like to make sure that this change will not have a side effect that we could not expect, like data corruption on the database. Since "DB related queries" are not in the scope of Atlassian support, they advised to ask the question here, by hoping that someone will kindly answer.

Thanks in advance for your help.

-Vincent