Upgrade from SonarQube 8.9.9 to 9.9.2 causes MSSQL deadlocks

Hi,

After upgrading from SonarQube 8.9.9 Community to 9.9.2 we see MSSQL deadlocks in our logs:

"### SQL: delete from properties where prop_key=? and component_uuid=? and user_uuid is null

Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

We are running SonarQube on Debian Bullseye11.5, which is using OpenJDK Runtime Environment (build 17.0.6+10-LTS)
We use MSSQL 2016 as our database server.

We followed your best practices to enable is_read_committed_snapshot_on=true on the database, suggested in your installation, In another article (MSSQL DeadLock with workerCount ) a user suggested that setting sonar.ce.workerCount=1 had helped him getting rid of these errors, but after doing that we still see these errors. What makes me scratch my head is “delete from properties where prop_key=? and component_uuid=? and user_uuid is null” which in my opinion would never delete anything, and when i asked my DBA what such a query would do he responded “I would expect it to fail due to syntax error. Query parser can not parse that statement with question marks”.

Is sonarqube failing to log the correct values in the query, or might our database have been corrupted during the upgrade?

Any insight would help us tremendously

Kind regards,

Jonas

Hey there.

Those question marks bit misleading – they aren’t literally being passed to the database server like that.

Is READ_COMMITTED_SNAPSHOT set on your SQL server as documented? This not being set is the most common cause of deadlocks.

Hi,

Yes, it is indeed.

Kind regards

Jonas

Thanks. I actually missed that part in your first message.

  • Is your SQL Server performing well? Regarding CPU, memory, disk space…
  • Are you database indexes/statistics up-to-date?

And to be super clear, you have READ_COMMITTED_SNAPSHOT set to 1, not is_read_committed_snapshot_on set to true, correct?

Nope, thats a typo on my behalf.

Just to be sure i will verify it with the DBAs

I can confirm from the DBA who did the change, that it’s indeed set correctly:

READ_COMMITTED_SNAPSHOT=1

On that database.

Kind regards,

Jonas