背景:
报错信息:
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 com/datamesh/tissue/checklist/provider/mapper/CheckListPointDeviceTaskMapper.java (best guess)
### The error may involve com.datamesh.tissue.checklist.provider.mapper.CheckListPointDeviceTaskMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE cloud_checklist_point_device_task SET tenant_id=?, point_device_id=?, task_plan_id=?, task_id=?, skip_scan=?, device_id=?, create_time=?, update_time=?, exception_skip=?, temp_dispatch=?, check_duplication=?, device_status=? WHERE id=? AND del_flag=0
### 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) ~[spring-jdbc-5.3.26.jar!/:5.3.26]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.26.jar!/:5.3.26]
数据库死锁
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: dtcs_tenant_cn_206 - Connection is not available, request timed out after 10000ms.
### The error may exist in com/datamesh/tissue/tenant/provider/mapper/user/UserLogMapper.java (best guess)
### The error may involve com.datamesh.tissue.tenant.provider.mapper.user.UserLogMapper.insert
### The error occurred while executing an update
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: dtcs_tenant_cn_206 - Connection is not available, request timed out after 10000ms.
优化思路:
1、加索引
2、加缓存
3、多线程调用
批处理 rewriteBatchedStatements=true
数据库连接失败及数据库死锁优化解决