lordk911 opened a new issue, #10251: URL: https://github.com/apache/dolphinscheduler/issues/10251
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar issues. ### What happened when want to delete a process which have run many many times, the delete operation is too expensive ，the transaction do too mush operation , finally casue MySQLTransactionRollbackException . also between the transaction , other operation like 'insert into process_instance' also will be faild . Finally the connection pool many be exhaust，no more new process instance can be run. ``` [ERROR] 2022-05-25 18:05:25.073 org.apache.dolphinscheduler.api.exceptions.ApiExceptionHandler:[46] - 删除工作流定义错误 org.springframework.dao.CannotAcquireLockException: ### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction ### The error may exist in org/apache/dolphinscheduler/dao/mapper/ProcessDefinitionMapper.java (best guess) ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: DELETE FROM t_ds_process_definition WHERE id=? ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction ; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) at com.sun.proxy.$Proxy101.delete(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:303) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:68) at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61) at com.sun.proxy.$Proxy104.deleteById(Unknown Source) at sun.reflect.GeneratedMethodAccessor1105.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) ``` ### What you expected to happen need some mechanism to periodically delete the history data about process instance \task instance ### How to reproduce just delete a long ago and keep daily running process. ### Anything else _No response_ ### Version 2.0.5 ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
