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?
Yes, I think it’s worth mentioning to Mibex that some of these API calls make no sense.
Setting sonar.auth.saml.user.login on a project (or any component)? It’s nonsense.
Based on the timestamps, it also seems that the integration is sending a lot of requests at once—so fast that the timestamps end up out of order. SonarQube shouldn’t handle this with a deadlock, but this is still not expected input.
Why is this integration configuring your SonarQube Server settings at all?
I ran some additional tests with some unexpected results – SonarQube doesn’t actually throw away all of these requests (sonar.core.startTime is successfully set on a component, although it’s meaningless).
This is also a valid request (setting housekeeper settings on a component). But again, why would this integration be setting this?
So right now my takeaways are that:
This integration is behaving poorly, bombarding your SonarQube server with bad requests. You should reach out to Mibex.
Potentially, we have an issue with deadlocks when there’s a lot of traffic heading towards POST API/settings/set. I’ll flag this for attention, but we also have no other reports for the time being (I checked through our internal ServiceDesk as well).
First things first we would like to thank you, you highlighted a lot issues and made us start looking in a different directions that ultimately helped us find the root-cause. You were also correct about Mibex plugins calls to Sonar. We have let them know of these issues and they are now also looking into improvements to their plugin, because of your observations.
We are not over the water yet and are still working on a fix, but we figure we would take a minute to thank you.
That’s very nice of you, and I’m sorry it took so long for us to get here! I wrote this off as an issue with your DB one year ago, but seeing this in your updated report was the key to understanding that something else is going on.