数据库 mybatis ### 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

S

q

l

S

e

s

s

i

o

n

I

n

t

e

r

c

e

p

t

o

r

.

i

n

v

o

k

e

(

S

q

l

S

e

s

s

i

o

n

T

e

m

p

l

a

t

e

.

j

a

v

a

:

440

)

a

t

c

o

m

.

s

u

n

.

p

r

o

x

y

.

SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) at com.sun.proxy.

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.

P

r

o

x

y

233.

u

p

d

a

t

e

B

y

I

d

(

U

n

k

n

o

w

n

S

o

u

r

c

e

)

a

t

c

o

m

.

b

a

o

m

i

d

o

u

.

m

y

b

a

t

i

s

p

l

u

s

.

e

x

t

e

n

s

i

o

n

.

s

e

r

v

i

c

e

.

I

S

e

r

v

i

c

e

.

u

p

d

a

t

e

B

y

I

d

(

I

S

e

r

v

i

c

e

.

j

a

v

a

:

144

)

a

t

c

o

m

.

b

a

o

m

i

d

o

u

.

m

y

b

a

t

i

s

p

l

u

s

.

e

x

t

e

n

s

i

o

n

.

s

e

r

v

i

c

e

.

I

S

e

r

v

i

c

e

Proxy233.updateById(Unknown Source) at com.baomidou.mybatisplus.extension.service.IService.updateById(IService.java:144) at com.baomidou.mybatisplus.extension.service.IService

Proxy233.updateById(UnknownSource)atcom.baomidou.mybatisplus.extension.service.IService.updateById(IService.java:144)atcom.baomidou.mybatisplus.extension.service.IService

F

a

s

t

C

l

a

s

s

B

y

S

p

r

i

n

g

C

G

L

I

B

FastClassBySpringCGLIB

FastClassBySpringCGLIB

f

8525

d

18.

i

n

v

o

k

e

(

<

g

e

n

e

r

a

t

e

d

>

)

a

t

o

r

g

.

s

p

r

i

n

g

f

r

a

m

e

w

o

r

k

.

c

g

l

i

b

.

p

r

o

x

y

.

M

e

t

h

o

d

P

r

o

x

y

.

i

n

v

o

k

e

(

M

e

t

h

o

d

P

r

o

x

y

.

j

a

v

a

:

218

)

a

t

o

r

g

.

s

p

r

i

n

g

f

r

a

m

e

w

o

r

k

.

a

o

p

.

f

r

a

m

e

w

o

r

k

.

C

g

l

i

b

A

o

p

P

r

o

x

y

f8525d18.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy

f8525d18.invoke()atorg.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)atorg.springframework.aop.framework.CglibAopProxyDynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687) at com.merchant.server.service.impl.MchUserServiceImpl

E

n

h

a

n

c

e

r

B

y

S

p

r

i

n

g

C

G

L

I

B

EnhancerBySpringCGLIB

EnhancerBySpringCGLIBbdadf6ce.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同一张表的逻辑层优化成批量操作用来解决死锁问题，大部分的改为批量可以避免多个事务造成的死锁问题。

推荐链接

 您阅读本篇文章共花了：