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?
This error still persists on sonar after all this time. Last weekend we upgraded sonarqube to 9.9.8 in the hope that this was something caused by the database connector or some obscure bug in sonar, however we still see the same issues;
Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 303) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The error may exist in org.sonar.db.property.PropertiesMapper
The error may involve org.sonar.db.property.PropertiesMapper.delete-Inline
The error occurred while setting parameters
SQL: delete from properties where prop_key=? and component_uuid=? and user_uuid is null
Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 303) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
…
…
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 303) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
As verified in this thread before, we indeed have READ_COMMITTED_SNAPSHOT=1 on the database, for both our enterprise and community version of sonarcube, however we only see this issue on sonarqube community 9.9.8
Any help would make this application a ton more useful to us, has anyone ran into the same issue?
I’m actually really puzzled by this specific web request.
sonar.core.startTime is a global server setting that has no business being set via Web API (like its name would lead you to believe, it’s set at start time), and certainly not tied to a specific component.
Where is this web request coming from? You should be able to hunt it down by looking at your access.log file. Do you see any other weird requests from the same origin / user agent?
What I’m trying to get at is that if something (bad actor, internal automation gone rogue) is flooding your SonarQube server with bad requests, it’s not unreasonable that some erroneous behavior would follow.
And thanks for the very fast response. As suspected, this request came from Bitbucket Datacenter v8.19.14, which uses the Mibex Include Code Quality for Bitbucket (version 7.2.0), which according to Mibex:
supports SonarQube™ compatibility:
v7.7 or newer
Community, Commercial editions and SonarCloud™ support
The request doesn’t come alone, here are some other request that happens before and after:
So you are suggesting the plugin should not expect these requests to work, and we might want to talk to Mibex about the plugin and the issue it creates?
Another thing that strikes me as odd is that the request is recieved and then gets a 400 (bad request) however, sonarcube still seems to process it, as if it was a good request, example here:
2025.01.21 17:23:46 ERROR web[AZR51aUxegWV2rC8GNMv][o.s.s.w.WebServiceEngine] Fail to process request http:///api/settings/set?component=com.company.tradestore
.enrichment%3Atradestore-enrichment-parent%3Afeature-TCAP-4205-ThroughtputTest&key=sonar.dbcleaner.daysBeforeDeletingClosedIssues&value=30
org.apache.ibatis.exceptions.PersistenceException:
Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 303) was deadlocked on lock resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.
The error may exist in org.sonar.db.property.PropertiesMapper
The error may involve org.sonar.db.property.PropertiesMapper.delete-Inline
The error occurred while setting parameters
SQL: delete from properties where prop_key=? and component_uuid=? and user_uuid is null
Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 303) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Re
run the transaction.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:196)
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:209)
If it was a completely bad request would it not be dismissed as a wrongful request?