背景：前系統(tǒng)跑定時(shí)任務(wù)，由于是循環(huán)插入、查詢、修改同時(shí)存在，其中一個(gè)log表中數(shù)據(jù)存在500多萬(wàn)，做了修改操作，但是并沒(méi)有做索引，通過(guò)執(zhí)行計(jì)劃查看為全表操作。從而造成死鎖。

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

2020-10-14 16:05:26  [ myScheduler-5:6558809650 ] - [ ERROR ]  按小時(shí)統(tǒng)計(jì)統(tǒng)計(jì)客流數(shù)據(jù)出錯(cuò)
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.查詢進(jìn)程（如果您有SUPER權(quán)限，您可以看到所有線程。否則，您只能看到您自己的線程）
show processlist

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

當(dāng)前6825750線程一直存在，表明update一直未結(jié)束，可能死鎖，進(jìn)行殺除
3.殺死進(jìn)程id（就是上面命令的id列）
kill id（kill 6825750）
4.查看下在鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

5.殺死進(jìn)程id（就是上面命令的trx_mysql_thread_id列）
kill 線程ID （kill 6826259 & kill）6826201

3.刪除log部分?jǐn)?shù)據(jù)（例：2019前數(shù)據(jù)全部清除）
4.在update語(yǔ)句的查詢條件加索引

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` ) 

后續(xù)數(shù)據(jù)訂正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. 修改狀態(tài)為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的數(shù)據(jù)用來(lái)重新生成
*/
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跑定時(shí)任務(wù)