CDA deadlock issues when deleting locations

[jbatterman]

Describe the bug
Using Swagger or a python script, CDA almost always deadlocks when attempting to delete a location. Locations were confirmed to have no children (no rating curves, location levels, time series, etc.). Sometimes locations will delete, probably less than 10% of the time on average in my testing.

Deadlock also occurred using SQLDeveloper on at least one location when attempting to delete without CDA. Cannot delete the locations in CWMSVue.

Deadlock errors have also occurred when attempting to modify time series groups, but not as often.

Expected behavior
Expect no deadlock errors to occur.

To Reproduce
Steps to reproduce the behavior:

1. Attempt to delete a location.
2. See error

Logs/Incident Identifier

22-Apr-2026 13:21:57.337 INFO [https-openssl-nio-155.77.185.11-8243-exec-21562] cwms.cda.data.dao.AuthDao.prepareContextWithUser Validated Api Key for user=G0PDRJAB [CONTEXT ratelimit_period="5 SECONDS" ]
22-Apr-2026 13:22:06.659 WARNING [https-openssl-nio-155.77.185.11-8243-exec-21562] cwms.cda.ApiServlet.lambda$init$20 error on request[-8818300043231349053]: /nwdm-data/locations/Bear_Creek-2350-IDLC2
  org.jooq.exception.DataAccessException: SQL [{ call "CWMS_20"."CWMS_LOC"."DELETE_LOCATION" (?, ?, ?) }]; ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "CWMS_20.CWMS_LOC", line 3246
ORA-06512: at "CWMS_20.CWMS_LOC", line 3220
ORA-06512: at line 1

    at org.jooq_3.18.7.ORACLE18C.debug(Unknown Source)
    at org.jooq.impl.Tools.translate(Tools.java:3470)
    at org.jooq.impl.Tools.translate(Tools.java:3458)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:802)
    at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:611)
    at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:405)
    at org.jooq.impl.Tools.attach(Tools.java:1610)
    at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:374)
    at usace.cwms.db.jooq.codegen.packages.CWMS_LOC_PACKAGE.call_DELETE_LOCATION(CWMS_LOC_PACKAGE.java:1441)
    at cwms.cda.data.dao.LocationsDaoImpl.lambda$deleteLocation$0(LocationsDaoImpl.java:217)
    at org.jooq.impl.DefaultDSLContext.lambda$connection$12(DefaultDSLContext.java:697)
    at org.jooq.impl.DefaultDSLContext.connectionResult(DefaultDSLContext.java:658)
    at org.jooq.impl.DefaultDSLContext.connection(DefaultDSLContext.java:696)
    at cwms.cda.data.dao.JooqDao.connection(JooqDao.java:734)
    at cwms.cda.data.dao.LocationsDaoImpl.deleteLocation(LocationsDaoImpl.java:211)
    at cwms.cda.api.LocationController.delete(LocationController.java:392)
    at io.javalin.apibuilder.CrudFunction$5.invoke$lambda-0(CrudHandler.kt:34)
    at io.javalin.apibuilder.CrudFunctionHandler.handle(CrudHandler.kt)
    at cwms.cda.security.CdaAccessManager.manage(CdaAccessManager.java:37)
    at io.javalin.http.JavalinServlet.addHandler$lambda-0(JavalinServlet.kt:96)
    at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)
    at io.javalin.http.JavalinServlet$lifecycle$2$1$1.invoke(JavalinServlet.kt:43)
    at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:99)
    at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
    at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:995)
    at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2137)
    at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)
    at io.javalin.http.JavalinServletHandler.executeNextTask$lambda-11$lambda-10(JavalinServletHandler.kt:119)
    at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:616)
    at java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:628)
    at java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:1996)
    at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)
    at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
    at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:995)
    at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2137)
    at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)
    at io.javalin.http.JavalinServletHandler.executeNextTask$lambda-11$lambda-10(JavalinServletHandler.kt:119)
    at java.util.concurrent.CompletableFuture.uniApply(CompletableFuture.java:616)
    at java.util.concurrent.CompletableFuture.uniApplyStage(CompletableFuture.java:628)
    at java.util.concurrent.CompletableFuture.thenApply(CompletableFuture.java:1996)
    at io.javalin.http.JavalinServletHandler.executeNextTask(JavalinServletHandler.kt:119)
    at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$lambda-1(JavalinServletHandler.kt:85)
    at java.util.concurrent.CompletableFuture.uniComposeStage(CompletableFuture.java:995)
    at java.util.concurrent.CompletableFuture.thenCompose(CompletableFuture.java:2137)
    at io.javalin.http.JavalinServletHandler.queueNextTaskOrFinish$javalin(JavalinServletHandler.kt:85)
    at io.javalin.http.JavalinServlet.service(JavalinServlet.kt:89)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
    at cwms.cda.ApiServlet.service(ApiServlet.java:1018)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:623)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:197)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:142)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:142)
    at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:129)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:142)
    at org.apache.catalina.filters.CorsFilter.handleNonCORS(CorsFilter.java:334)
    at org.apache.catalina.filters.CorsFilter.doFilter(CorsFilter.java:161)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:142)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:166)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:88)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:596)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:643)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:83)
    at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:240)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:72)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:398)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:935)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1831)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:973)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:491)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
    at java.lang.Thread.run(Thread.java:750)
  Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

CURL Commands

curl -X 'DELETE' \
  'https://HOST:PORT/nwdm-data/locations/GRAI?office=NWDM' \
  -H 'accept: */*'

CDA Version (please complete the following information):

[rma-rripken]

If deadlock occurs in SQLDeveloper this must be a database issue. The delete is probably executing a trigger that then tries to use the same rows that are locked by the delete.

[jbatterman]

Does this need to be moved to a different project other than CDA? It's preventing us from cleaning up our data and adding things like agency aliases, etc.

[MikeNeilson]

Do you know the exact schema version your on?

Or alternatively can you try that deletion on CWBI-test which we know is newer.

I may have missed a database commit in this PR: HydrologicEngineeringCenter/cwms-database#34, but I want to confirm first whether you're using a version that includes that change. And it looks like it was merge in after the previous release. So it is likely fixed in the next schema release, which CWBI-test already contains.

[jbatterman]

We have not updated our schema yet due to the ongoing issues with it. I think this is what we are on if we found the right call:

53 G6CWMSS1 CWMS 25 7 1 01-JUL-25 19-AUG-25 CWMS Database Release 25.7.1 Updated from 24.12.4

I tested the deletion in CWBI-Test and I'm getting a different error that makes somewhat more sense - child record found. However, I see no child records on any CWMS-Vue tabs.

2026-04-29 09:01:12.219 
error 
https://docs.oracle.com/error-help/db/ora-02292/ 

2026-04-29 09:01:12.219 
 
		at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:718) 

2026-04-29 09:01:12.219 
 
		at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:618) 

2026-04-29 09:01:12.219 
error 
https://docs.oracle.com/error-help/db/ora-02292/ 

2026-04-29 09:01:12.219 
 
		at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:83) 

2026-04-29 09:01:12.219 
 
		at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1372) 

2026-04-29 09:01:12.219 
error 
	Caused by: Error : 2292, Position : 0, SQL = BEGIN "CWMS_20"."CWMS_LOC"."DELETE_LOCATION" (:1, :2, :3) ; END;, Original SQL = { call "CWMS_20"."CWMS_LOC"."DELETE_LOCATION" (?, ?, ?) }, Error Message = ORA-02292: integrity constraint (CWMS_20.AT_A2W_TS_CODES_BY_LOC_R01) violated - child record found 

2026-04-29 09:01:12.219 
warn 
29-Apr-2026 16:01:12.218 WARNING [http-nio-7000-exec-642] cwms.cda.ApiServlet.lambda$init$8 error on request[5055ca3f-b22a-451e-a8c8-faf7e935de1e]: /cwms-data/locations/Chatfield-1500-PWPC2 

2026-04-29 09:01:12.219 
 
		at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:726)