MSSQL DeadLock with workerCount

SonarQube Server / Community Build
1

Hello,

We are currently using the 7.3 version (same problem with 7.4) with MSSQL 2014.
We put in sonar.properties the following value:

  • sonar.ce.workerCount = 4

When we run multiple scans at the same time, we have the following error:

### Error updating database. CAUSE: com.microsoft.sqlserver.jdbc.SQLServerException: The transaction (process ID 55) has been blocked on lock resources | communication buffer by another process and was chosen as a victim. Rerun the transaction.
### The error may involve org.sonar.db.measure.LiveMeasureMapper.update-Inline
### The error occurred while setting parameters
### SQL: update live_measures set value = ?, variation =?, Text_value =?, Measure_data =?, Update_marker =?, Updated_at =? where component_uuid =? and metric_id =?
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The transaction (process ID 55) has been blocked on lock resources | communication buffer by another process and was chosen as a victim. Rerun the transaction.

When we change the workerCount to 1, we have no problem (but the queue grows, which causes performance issues)
Do you have any idea where the problem may come from?

Thanks in advance.

Edit :
my database is configured with “READ COMMITTED SNAPSHOT ON”

KevinL

3

I deleted a unique index, which I put in non-unique:
CREATE INDEX live_measures_component ON live_measures (component_uuid, metric_id);

It’s ok now.

4

Hi,

Thanks for the followup. I’m glad you were able to work through this.

 
Ann

5

Thank you for your reply.
I had this problem because I did this step: SonarQube upgrade to 7.3 version from 6.7.5v

Do you know the impact it makes if we remove the “unique” from this index?

Thank you

6

Hello,

First, a reminder to any user of SonarQube: the database should be considered as a blackbox and should not be tampered with if you are willing to get any support from the dev team.

Second, for the record: while the solution described here may solve the reported issue, it is dropping a constraint which ensured consistency of data in the DB. This consistency not being enforced anymore, the impact on the application is undefined and other issues may arise.

Third, we have had reports of similar situations but we have no magic fix for it under our boot at the moment. We’ll update this thread when news comes on the subject.

7

Facing the same problem :
SQLServerException: Transaction (Process ID …) was deadlocked on lock resources with another process and has been chosen as the deadlock victim

With

  • SonarQube 7.3
  • MSSQL 2014 and READ_COMMITTED_SNAPSHOT ON

Any idea ?