### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException:

Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction### The error may exist in com/merchant/server/mapper/MchUserMapper.java (best guess)### The error may involve com.merchant.server.mapper.MchUserMapper.updateById-Inline### The error occurred while setting parameters### SQL: UPDATE mch_user SET is_admin=? WHERE id=?### 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，堆栈信息：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/merchant/server/mapper/MchUserMapper.java (best guess)### The error may involve com.merchant.server.mapper.MchUserMapper.updateById-Inline### The error occurred while setting parameters### SQL: UPDATE mch_user SET is_admin=? WHERE id=?### 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:263) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)atcom.sun.proxy.$Proxy171.update(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:65) at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96) at com.sun.proxy.$Proxy233.updateById(UnknownSource)atcom.baomidou.mybatisplus.extension.service.IService.updateById(IService.java:144)atcom.baomidou.mybatisplus.extension.service.IService$$FastClassBySpringCGLIB$$f8525d18.invoke(<generated>)atorg.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)atorg.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687) at com.merchant.server.service.impl.MchUserServiceImpl$$EnhancerBySpringCGLIB$$bdadf6ce.updateById() at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

死锁发生了。Lock wait timeout exceeded; 超过锁定等待超时，也就是死锁了，两个线程同时争夺资源，没有先后顺序，这就造成了死锁。产生死锁的原因有很多，更容易在多线程、线程池、多条sql操作同一张表分开来写。有必要对这些容易产生死锁的接口用jemet性能测试。

文中告诉你，错误来自UPDATE mch_user SET is_admin=? WHERE id=?### 这条，当然sql语句没有错，id是主键索引，is_admin就一普通字段。

它说错误可能存在com.merchant.server.mapper.MchUserMapper.updateById这里，MchUserMapper只看到的是mybatis-plus BaseMapper的updateById

实际上我用的是IService里面的updateById，从而间接的使用到了BaseMapper的updateById
具体框架可以看到

default boolean updateById(T entity) {    return SqlHelper.retBool(this.getBaseMapper().updateById(entity));}

查看mysql事务和锁的情况
show processlist;
select * from information_schema.innodb_trx;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

发现两个事务都要锁住用户表的主键

同一个线程，涉及到了两个事务，sql获取的sqlsession和同一个线程的其他sql不一致，两个sqlsession对应不是一个事务

最后处理的结果是把两个update同一张表的逻辑层优化成批量操作用来解决死锁问题，大部分的改为批量可以避免多个事务造成的死锁问题。

来源地址：https://blog.csdn.net/weixin_43206161/article/details/126732919