DependencyTrack
Insert Statement conflicts with foreign key constraint
Current Behavior
We are getting los of foreign key constraint conflicts and deadlocks when upgrading to 4.13.5. We have deployed dependency-track via docker and are using MS SQL as database. Every Time when the ProjectMetricsUpdateTask is run, the Dependency Track API logs the following:
2025-11-03 10:15:45,968 ERROR [ProjectMetricsUpdateTask] An unexpected error occurred while updating metrics of component 97486344-94b1-47bd-8f88-2ce15dbeec9d javax.jdo.JDODataStoreException: Insert of object "org.dependencytrack.model.DependencyMetrics@64ff17bb" using statement "INSERT INTO DEPENDENCYMETRICS (COMPONENT_ID,CRITICAL,FINDINGS_AUDITED,FINDINGS_TOTAL,FINDINGS_UNAUDITED,FIRST_OCCURRENCE,HIGH,RISKSCORE,LAST_OCCURRENCE,LOW,MEDIUM,POLICYVIOLATIONS_AUDITED,POLICYVIOLATIONS_FAIL,POLICYVIOLATIONS_INFO,POLICYVIOLATIONS_LICENSE_AUDITED,POLICYVIOLATIONS_LICENSE_TOTAL,POLICYVIOLATIONS_LICENSE_UNAUDITED,POLICYVIOLATIONS_OPERATIONAL_AUDITED,POLICYVIOLATIONS_OPERATIONAL_TOTAL,POLICYVIOLATIONS_OPERATIONAL_UNAUDITED,POLICYVIOLATIONS_SECURITY_AUDITED,POLICYVIOLATIONS_SECURITY_TOTAL,POLICYVIOLATIONS_SECURITY_UNAUDITED,POLICYVIOLATIONS_TOTAL,POLICYVIOLATIONS_UNAUDITED,POLICYVIOLATIONS_WARN,PROJECT_ID,SUPPRESSED,UNASSIGNED_SEVERITY,VULNERABILITIES) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" failed : The INSERT statement conflicted with the FOREIGN KEY constraint "DEPENDENCYMETRICS_FK1". The conflict occurred in database "dtrack_prod", table "dbo.COMPONENT", column 'ID'. at org.datanucleus.api.jdo.JDOAdapter.getJDOExceptionForNucleusException(JDOAdapter.java:608) at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:702) at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:722) at org.dependencytrack.tasks.metrics.ComponentMetricsUpdateTask.lambda$updateMetrics$2(ComponentMetricsUpdateTask.java:163) at alpine.persistence.AbstractAlpineQueryManager.lambda$runInTransaction$6(AbstractAlpineQueryManager.java:564) at alpine.persistence.Transaction.call(Transaction.java:139) at alpine.persistence.AbstractAlpineQueryManager.callInTransaction(AbstractAlpineQueryManager.java:542) at alpine.persistence.AbstractAlpineQueryManager.runInTransaction(AbstractAlpineQueryManager.java:563) at alpine.persistence.AbstractAlpineQueryManager.runInTransaction(AbstractAlpineQueryManager.java:575) at org.dependencytrack.tasks.metrics.ComponentMetricsUpdateTask.updateMetrics(ComponentMetricsUpdateTask.java:155) at org.dependencytrack.tasks.metrics.ProjectMetricsUpdateTask.updateRegularProjectMetrics(ProjectMetricsUpdateTask.java:130) at org.dependencytrack.tasks.metrics.ProjectMetricsUpdateTask.updateMetrics(ProjectMetricsUpdateTask.java:79) at org.dependencytrack.tasks.metrics.ProjectMetricsUpdateTask.inform(ProjectMetricsUpdateTask.java:58) at alpine.event.framework.BaseEventService.lambda$publish$0(BaseEventService.java:110) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.base/java.lang.Thread.run(Unknown Source) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the FOREIGN KEY constraint "DEPENDENCYMETRICS_FK1". The conflict occurred in database "dtrack_prod", table "dbo.COMPONENT", column 'ID'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:276) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1787) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:688) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:607) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7745) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4700) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:321) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:253) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:549) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) at org.datanucleus.store.rdbms.SQLController.doExecuteStatementUpdate(SQLController.java:465) at org.datanucleus.store.rdbms.SQLController.executeStatementUpdateDeferRowCountCheckForBatching(SQLController.java:415) at org.datanucleus.store.rdbms.request.InsertRequest.execute(InsertRequest.java:532) at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObjectInTable(RDBMSPersistenceHandler.java:235) at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObject(RDBMSPersistenceHandler.java:211) at org.datanucleus.state.StateManagerImpl.internalMakePersistent(StateManagerImpl.java:4610) at org.datanucleus.state.StateManagerImpl.makePersistent(StateManagerImpl.java:4587) at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2077) at org.datanucleus.ExecutionContext.persistObjectInternal(ExecutionContext.java:320) at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:1925) at org.datanucleus.ExecutionContextImpl.persistObject(ExecutionContextImpl.java:1786) at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:697) ... 15 common frames omitted
Adding to this we are getting the following deadlock errors at the same time:
2025-11-03 10:15:45,865 ERROR [ProjectMetricsUpdateTask] An unexpected error occurred while updating metrics of component 8a9a00fe-cac6-4910-a1af-9cffd32881d0 javax.jdo.JDODataStoreException: Insert of object "org.dependencytrack.model.DependencyMetrics@6b0b6523" using statement "INSERT INTO DEPENDENCYMETRICS (COMPONENT_ID,CRITICAL,FINDINGS_AUDITED,FINDINGS_TOTAL,FINDINGS_UNAUDITED,FIRST_OCCURRENCE,HIGH,RISKSCORE,LAST_OCCURRENCE,LOW,MEDIUM,POLICYVIOLATIONS_AUDITED,POLICYVIOLATIONS_FAIL,POLICYVIOLATIONS_INFO,POLICYVIOLATIONS_LICENSE_AUDITED,POLICYVIOLATIONS_LICENSE_TOTAL,POLICYVIOLATIONS_LICENSE_UNAUDITED,POLICYVIOLATIONS_OPERATIONAL_AUDITED,POLICYVIOLATIONS_OPERATIONAL_TOTAL,POLICYVIOLATIONS_OPERATIONAL_UNAUDITED,POLICYVIOLATIONS_SECURITY_AUDITED,POLICYVIOLATIONS_SECURITY_TOTAL,POLICYVIOLATIONS_SECURITY_UNAUDITED,POLICYVIOLATIONS_TOTAL,POLICYVIOLATIONS_UNAUDITED,POLICYVIOLATIONS_WARN,PROJECT_ID,SUPPRESSED,UNASSIGNED_SEVERITY,VULNERABILITIES) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" failed : Transaction (Process ID 176) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at org.datanucleus.api.jdo.JDOAdapter.getJDOExceptionForNucleusException(JDOAdapter.java:608) at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:702) at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:722) at org.dependencytrack.tasks.metrics.ComponentMetricsUpdateTask.lambda$updateMetrics$2(ComponentMetricsUpdateTask.java:163) at alpine.persistence.AbstractAlpineQueryManager.lambda$runInTransaction$6(AbstractAlpineQueryManager.java:564) at alpine.persistence.Transaction.call(Transaction.java:139) at alpine.persistence.AbstractAlpineQueryManager.callInTransaction(AbstractAlpineQueryManager.java:542) at alpine.persistence.AbstractAlpineQueryManager.runInTransaction(AbstractAlpineQueryManager.java:563) at alpine.persistence.AbstractAlpineQueryManager.runInTransaction(AbstractAlpineQueryManager.java:575) at org.dependencytrack.tasks.metrics.ComponentMetricsUpdateTask.updateMetrics(ComponentMetricsUpdateTask.java:155) at org.dependencytrack.tasks.metrics.ProjectMetricsUpdateTask.updateRegularProjectMetrics(ProjectMetricsUpdateTask.java:130) at org.dependencytrack.tasks.metrics.ProjectMetricsUpdateTask.updateMetrics(ProjectMetricsUpdateTask.java:79) at org.dependencytrack.tasks.metrics.ProjectMetricsUpdateTask.inform(ProjectMetricsUpdateTask.java:58) at alpine.event.framework.BaseEventService.lambda$publish$0(BaseEventService.java:110) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.base/java.lang.Thread.run(Unknown Source) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 176) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:276) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1787) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:688) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:607) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7745) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4700) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:321) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:253) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:549) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) at org.datanucleus.store.rdbms.SQLController.doExecuteStatementUpdate(SQLController.java:465) at org.datanucleus.store.rdbms.SQLController.executeStatementUpdateDeferRowCountCheckForBatching(SQLController.java:415) at org.datanucleus.store.rdbms.request.InsertRequest.execute(InsertRequest.java:532) at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObjectInTable(RDBMSPersistenceHandler.java:235) at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.insertObject(RDBMSPersistenceHandler.java:211) at org.datanucleus.state.StateManagerImpl.internalMakePersistent(StateManagerImpl.java:4610) at org.datanucleus.state.StateManagerImpl.makePersistent(StateManagerImpl.java:4587) at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2077) at org.datanucleus.ExecutionContext.persistObjectInternal(ExecutionContext.java:320) at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:1925) at org.datanucleus.ExecutionContextImpl.persistObject(ExecutionContextImpl.java:1786) at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:697) ... 15 common frames omitted
This seems to hinder the metrics update of different components.
Steps to Reproduce
We deployed the service in 2022 and have since been using the same database instance. It seems that update after update affected the database and caused data inconsistency.
We are currently running version 4.13.5.
Expected Behavior
It should update the data in the database without issues.
Dependency-Track Version
4.13.5
Dependency-Track Distribution
Container Image
Database Server
Microsoft SQL Server
Database Server Version
15.0.4445.1
Browser
N/A
Checklist
- [x] I have read and understand the contributing guidelines
- [x] I have checked the existing issues for whether this defect was already reported
So the first error can occur when you delete a component (or a project that the component belongs to) while a metrics update is in progress. Does this happen every time metrics are being updated?
As for the deadlock, please try enabling READ_COMMITTED_SNAPSHOT transaction isolation: https://docs.dependencytrack.org/getting-started/database-support/#examples
It is recommended to set the default transaction isolation for the Dependency-Track database to READ_COMMITTED_SNAPSHOT in order to avoid deadlocks. This can be achieved by executing the following SQL statement:
ALTER DATABASE dtrack SET READ_COMMITTED_SNAPSHOT ON;
We already set the parameter READ_COMMITTED_SNAPSHOT on the database. It did not change the observation. This occurs every time the metrics are being updated. We are not deleting components or projects during the metrics update.
Ok, can you check if there are any orphaned records in the DEPENDENCYMETRICS table?
select "COMPONENT_ID", count(*)
from "DEPENDENCYMETRICS" as dm
where not exists(select 1 from "COMPONENT" where "ID" = dm."COMPONENT_ID")
group by "COMPONENT_ID";
Even if there are, it would be odd that they would cause FK violations on new inserts. But I am not too familiar with MSSQL and how it evaluates constraints.
Thank you for the query. Unfortunately there are no orphaned records.