记一次死锁问题

背景：前系统跑定时任务，由于是循环插入、查询、修改同时存在，其中一个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(<generated>)
    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跑定时任务