java ibatis 锁表,记一次死锁问题

背景：前系统跑定时任务，由于是循环插入、查询、修改同时存在，其中一个log表中数据存在500多万，做了修改操作，但是并没有做索引，通过执行计划查看为全表操作。从而造成死锁。

日志: (Deadlock found when trying to get lock; try restarting transaction)

2020-10-14 16:05:26 [ myScheduler-5:6558809650 ] - [ ERROR ] 按小时统计统计客流数据出错

org.springframework.dao.DeadlockLoserDataAccessException:

### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)

at com.sun.proxy.$Proxy22.update(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)

at com.sun.proxy.$Proxy47.updateDataState(Unknown Source)

at com.huameng.springmvc.service.DeviceVistorsJobService.statisticsVisitors(DeviceVistorsJobService.java:91)

at com.huameng.springmvc.service.DeviceVistorsJobService$$FastClassBySpringCGLIB$$b733868e.invoke()

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)

步骤，排查sql：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程(如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程)

show processlist

update t_camera_log set state=1 where DATE_FORMAT(data_time,'%Y-%m-%d %H')='2020-10-12 22' and camer

当前6825750线程一直存在，表明update一直未结束，可能死锁，进行杀除

3.杀死进程id(就是上面命令的id列)

kill id(kill 6825750)

4.查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

5.杀死进程id(就是上面命令的trx_mysql_thread_id列)

kill 线程ID (kill 6826259 & kill)6826201

3.删除log部分数据(例：2019前数据全部清除)

4.在update语句的查询条件加索引

update t_camera_log set state=1 where DATE_FORMAT(data_time,'%Y-%m-%d %H')=#{hourTime} and camera_code=#{cameraCode} and state=0

ALTER TABLE `t_camera_log` ADD INDEX idx_camera_code ( `camera_code` )

后续数据订正sql

select * from t_camera_log where data_time >= DATE_FORMAT('2020-10-10','%Y-%m-%d 00') and data_time < DATE_FORMAT('2020-10-13','%Y-%m-%d 24') and state=0

select * from t_device_visitors where data_time >= DATE_FORMAT('2020-10-10','%Y-%m-%d 00') and data_time < DATE_FORMAT('2020-10-13','%Y-%m-%d 24') and device_type=1

/**

1. 修改状态为0

*/

update t_camera_log set state=0 where data_time >= DATE_FORMAT('2020-10-10','%Y-%m-%d 00') and data_time < DATE_FORMAT('2020-10-13','%Y-%m-%d 24');

/**

2. 删除device_type=1的数据用来重新生成

*/

delete from t_device_visitors where data_time >= DATE_FORMAT('2020-10-10','%Y-%m-%d 00') and data_time < DATE_FORMAT('2020-10-13','%Y-%m-%d 24') and device_type=1

/**

3. 改代码

*/

本地连接线上sql跑定时任务