今天遇到了个mysql的问题
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at sun.reflect.GeneratedMethodAccessor77.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy106.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:54)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy104.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
... 81 more
2019-11-20 22:22:02.283 [TraceID:8ec6a237bbd44cc4b608d7c741197ff0] ERROR c.y.t.s.xhPay.XhPayService - ==信合支付查询出现异常,次数=4,直接停止查询,支付订单号=MOO201911202221461993505,e={}
org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in URL [jar:file:/usr/local/javaHome/teach_organization/teach_organiztion-0.0.1-SNAPSHOT.jar!/BOOT-INF/classes!/static/mapper/order/OrderWater.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE stu_class_tbl SET `status`=? WHERE orderNumber=?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
报了死锁的问题,应该在代码中因为事务的问题
根据异常信息来看是在
SQL: UPDATE stu_class_tbl SET `status`=? WHERE orderNumber=?
这个sql发生的死锁
死锁是指由于每个事务都持有对方需要的锁而无法进行其他事务的情况。因为这两个事务都在等待资源变得可用,所以都不会释放它持有的锁。
查看文档:
为了减少死锁的可能性,请使用事务而不是LOCK TABLES语句;保持用于插入或更新数据的事务足够小,以使其长时间不保持打开状态;当不同的事务更新多个表或大范围的行时,SELECT … FOR UPDATE在每个事务中使用相同的操作顺序(例如 );在SELECT … FOR UPDATE和 UPDATE … WHERE 语句中使用的列上创建索引。死锁的可能性不受隔离级别的影响,因为隔离级别更改了读取操作的行为,而死锁则是由于写入操作而发生的。有关避免死锁状态并从死锁状态中恢复的更多信息
应该是多次请求处理 同时执行这条sql出现的问题
分析后应该是sql的where 条件 orderNumber 不是主键,也没有索引,导致无法触发行锁 ,添加索引后执行成功