Solve a deadlock problem in mysql _ MySQL

Here we are talking about innodb deadlocks! There are a lot of other commands on the Internet to view the deadlock information, which can be used later: showengineinnodbstatus; (different from mysql version) if a deadlock occurs after the command is executed in the mysql command line: ---- innodb deadlock is used here!

I have found a lot of other commands on the Internet to view the deadlock information. this can be used later:

Show engine innodb status; (varies with the mysql version)

If there is a deadlock after this command is executed on the mysql command line

The following section appears:

------------------------
LATEST DETECTED DEADLOCK
------------------------

Then you can find the statement that causes the deadlock based on the content.

Then solve it,

My deadlock this time is because,

There is such a statement

Update table name set xx = xxx where xxx = xxx!

Xxx has no index!

Innodb is a row-level lock, but mysql uses indexes to locate the row. if your where field does not have an index, mysql cannot find the row and locks the entire table.

In this way, subsequent access will lead to deadlocks!

Add an index to the xxx field

Solve the problem.

Attached exception information:

Caused by: org. springframework. dao. DeadlockLoserDataAccessException: SqlMapClient operation; SQL [];
--- The error occurred while applying a parameter map.
--- Check the point. updatePointAccount-InlineParameterMap.
--- Check the statement (update failed ).
--- Cause: com. mysql. jdbc. exceptions. MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction; nested exception is com. ibatis. common. jdbc. exception. NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the point. updatePointAccount-InlineParameterMap.
--- Check the statement (update failed ).
--- Cause: com. mysql. jdbc. exceptions. MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
At org. springframework. jdbc. support. SQLErrorCodeSQLExceptionTranslator. translate (SQLErrorCodeSQLExceptionTranslator. java: 300)
At org.springframework.orm.ibatis.SqlMapClientTemplate.exe cute (SqlMapClientTemplate. java: 212)
At org. springframework. orm. ibatis. SqlMapClientTemplate. update (SqlMapClientTemplate. java: 411)
At com. hsd. popularize. dao. BaseDaoImpl. update (BaseDaoImpl. java: 162)
... 87 more
Caused by: com. ibatis. common. jdbc. exception. NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the point. updatePointAccount-InlineParameterMap.
--- Check the statement (update failed ).
--- Cause: com. mysql. jdbc. exceptions. MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
At com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.exe cuteUpdate (GeneralStatement. java: 91)
At com. ibatis. sqlmap. engine. impl. SqlMapExecutorDelegate. update (SqlMapExecutorDelegate. java: 505)
At com. ibatis. sqlmap. engine. impl. SqlMapSessionImpl. update (SqlMapSessionImpl. java: 90)
At org. springframework. orm. ibatis. SqlMapClientTemplate $10. doInSqlMapClient (SqlMapClientTemplate. java: 413)
At org.springframework.orm.ibatis.SqlMapClientTemplate.exe cute (SqlMapClientTemplate. java: 209)
... 89 more
Caused by: com. mysql. jdbc. exceptions. MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
At com. mysql. jdbc. SQLError. createSQLException (SQLError. java: 941)
At com. mysql. jdbc. MysqlIO. checkErrorPacket (MysqlIO. java: 2870)
At com. mysql. jdbc. MysqlIO. sendCommand (MysqlIO. java: 1573)
At com. mysql. jdbc. ServerPreparedStatement. serverExecute (ServerPreparedStatement. java: 1169)
At com.mysql.jdbc.ServerPreparedStatement.exe cuteInternal (ServerPreparedStatement. java: 693)
At com.mysql.jdbc.PreparedStatement.exe cute (PreparedStatement. java: 794)
At com.mchange.v2.c31_impl.newproxypreparedstatement.exe cute (NewProxyPreparedStatement. java: 989)
At sun. reflect. GeneratedMethodAccessor51.invoke (Unknown Source)
At sun. reflect. DelegatingMethodAccessorImpl. invoke (DelegatingMethodAccessorImpl. java: 43)
At java. lang. reflect. Method. invoke (Method. java: 606)
At com. ibatis. common. jdbc. logging. PreparedStatementLogProxy. invoke (PreparedStatementLogProxy. java: 62)
At com.sun.proxy.w.proxy20.exe cute (Unknown Source)
At com.ibatis.sqlmap.engine.execution.SqlExecutor.exe cuteUpdate (SqlExecutor. java: 81)
At com. ibatis. sqlmap. engine. mapping. statement. GeneralStatement. sqlExecuteUpdate (GeneralStatement. java: 200)
At com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.exe cuteUpdate (GeneralStatement. java: 78)
... 93 more