Lock wait timeout exceeded; try restarting transaction

这个错误，应该是锁等待超时，事务长时间没有提交，导致了回滚。

1.错误如下：

org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO industrial_output         VALUES (null,?,?,         ?,?,?,         ?,?,?,         ?,?,NOW(),NULL)
### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:259)
    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:371)
    at com.sun.proxy.$Proxy19.insert(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:46)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
    at com.sun.proxy.$Proxy95.add(Unknown Source)

2.发生场景：

//类上有@Transactional注解
    @Override
    public void upload(String platFrom,String yearAndMonth, File file) throws Exception{
        //删除历史数据
        industrialOutputDao.delete(platFrom,yearAndMonth);
        fileUploadHistoryDao.delete(platFrom,yearAndMonth+"-01",FILE_TYPE);
        logger.info("==========>删除历史数据");

        List<IndustrialOutput> industrialOutputList = this.parseExcel(file, platFrom, yearAndMonth);

        if(CollectionUtils.isNotEmpty(industrialOutputList)){
            industrialOutputList.parallelStream().forEach(industrialOutput -> {
                industrialOutputDao.add(industrialOutput);//出错位置！！！
            });
        }
        logger.info("===========>工业产值表格解析完毕，数据写入完毕");
    }

3.解决方案：

方法级别添加事务，并指定事务的传播级别：

@Transactional(propagation= Propagation.SUPPORTS)

---

1.查询等待锁的进程

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

2.查询所有被锁的

select * from information_schema.innodb_trx;