ISE 500 when I try to delte a project multiple times

[jakub-bochenski]

Current Behavior

"Fetch of object with id \"org.dependencytrack.model.AnalysisComment:1045\" using statement \"SELECT \"B0\".\"DETAILS\",\"B0\".\"JUSTIFICATION\",\"B0\".\"RESPONSE\",\"B0\".\"STATE\",\"B0\".\"COMPONENT_ID\",\"B0\".\"ID\",\"B0\".\"PROJECT_ID\",\"B0\".\"SUPPRESSED\",\"B0\".\"VULNERABILITY_ID\" FROM \"ANALYSISCOMMENT\" \"A0\" INNER JOIN \"ANALYSIS\" \"B0\" ON \"A0\".\"ANALYSIS_ID\" = \"B0\".\"ID\" WHERE \"A0\".\"ID\" = ?\" failed : Batched statement \"DELETE FROM \"ANALYSISCOMMENT\" WHERE \"ID\"=?\" failed. 
Batch entry 0 DELETE FROM \"ANALYSISCOMMENT\" WHERE \"ID\"=('1044'::int8) was aborted: ERROR: deadlock detected
  Detail: Process 3379476 waits for ShareLock on transaction 191339214; blocked by process 3379549.
Process 3379549 waits for ShareLock on transaction 191339213; blocked by process 3379476.
  Hint: See server log for query details.
  Where: while deleting tuple (2,129) in relation \"ANALYSISCOMMENT\"  Call getNextException to see other errors in the batch."

jakarta.ws.rs.ServerErrorException: HTTP 500 Internal Server Error
	at org.dependencytrack.resources.v1.ProjectResource.lambda$deleteProject$3(ProjectResource.java:807)
	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.resources.v1.ProjectResource.deleteProject(ProjectResource.java:787)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52)
	at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:146)
	at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:189)
	at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:176)
	at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:93)
	at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:478)
	at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:400)
	at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:81)
	at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:274)
	at org.glassfish.jersey.internal.Errors$1.call(Errors.java:248)
	at org.glassfish.jersey.internal.Errors$1.call(Errors.java:244)
	at org.glassfish.jersey.internal.Errors.process(Errors.java:292)
	at org.glassfish.jersey.internal.Errors.process(Errors.java:274)
	at org.glassfish.jersey.internal.Errors.process(Errors.java:244)
	at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:266)
	at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:253)
	at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:696)
	at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:397)
	at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:349)
	at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:358)
	at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:312)
	at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:205)
	at org.eclipse.jetty.ee10.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1379)
	at org.eclipse.jetty.ee10.servlet.ServletHolder.handle(ServletHolder.java:736)
	at org.eclipse.jetty.ee10.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1621)
	at alpine.server.filters.ContentSecurityPolicyFilter.doFilter(ContentSecurityPolicyFilter.java:225)
	at org.eclipse.jetty.ee10.servlet.FilterHolder.doFilter(FilterHolder.java:205)
	at org.eclipse.jetty.ee10.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1593)
	at alpine.server.filters.ClickjackingFilter.doFilter(ClickjackingFilter.java:93)
	at org.eclipse.jetty.ee10.servlet.FilterHolder.doFilter(FilterHolder.java:205)
	at org.eclipse.jetty.ee10.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1593)
	at alpine.server.filters.WhitelistUrlFilter.doFilter(WhitelistUrlFilter.java:166)
	at org.eclipse.jetty.ee10.servlet.FilterHolder.doFilter(FilterHolder.java:208)
	at org.eclipse.jetty.ee10.servlet.ServletHandler$Chain.doFilter(ServletHandler.java:1593)
	at org.eclipse.jetty.ee10.servlet.ServletHandler$MappedServlet.handle(ServletHandler.java:1554)
	at org.eclipse.jetty.ee10.servlet.ServletChannel.dispatch(ServletChannel.java:819)
	at org.eclipse.jetty.ee10.servlet.ServletChannel.handle(ServletChannel.java:436)
	at org.eclipse.jetty.ee10.servlet.ServletHandler.handle(ServletHandler.java:469)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:575)
	at org.eclipse.jetty.ee10.servlet.SessionHandler.handle(SessionHandler.java:717)
	at org.eclipse.jetty.server.handler.ContextHandler.handle(ContextHandler.java:1064)
	at org.eclipse.jetty.server.Server.handle(Server.java:182)
	at org.eclipse.jetty.server.internal.HttpChannelState$HandlerInvoker.run(HttpChannelState.java:662)
	at org.eclipse.jetty.server.internal.HttpConnection.onFillable(HttpConnection.java:416)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:322)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:99)
	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:480)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:443)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:293)
	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:201)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:311)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:979)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1209)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1164)
	at java.base/java.lang.Thread.run(Unknown Source)",

Steps to Reproduce

The project took a long time to delete. So I clicked "Delete" again in Web UI.

This resulted in ISE 500 and database deadlock

Expected Behavior

Concurrent requests should be handled gracefully

Dependency-Track Version

4.13.2

Dependency-Track Distribution

Container Image

Database Server

PostgreSQL

Database Server Version

No response

Browser

N/A

Checklist

• I have read and understand the contributing guidelines
• I have checked the existing issues for whether this defect was already reported

[nscuro]

I think the main issue is that deletes of all records associated with the project are currently performed on the application side. In v5 we have added proper foreign key definitions with cascading deletes, such that deadlocks like this will be less likely to happen. Also deletes will be much faster as fewer roundtrips to the database are required.

Another workaround that reduces the likelihood of deadlocks could be to add an ORDER BY clause to DELETE statements. Torn on whether it's worth adding though.