MySQL a deadlock problem solved

This is the InnoDB type of deadlock!

I found a lot of things on the Internet. Various commands to view the deadlock information, the following can be used:

Show engine InnoDB status (varies by MySQL version)

This command is executed after the MySQL command line, if there is a deadlock

The following piece will appear:

------------------------
LATEST detected DEADLOCK
------------------------

Then you can find the statement that caused the deadlock based on this piece of content.

And then fix it,

My deadlock this time is because,

There is such a statement

Update table name set XX=XXX where xxx=xxx!

where xxx is not indexed!

InnoDB is a row-level lock, but the mechanism of MySQL is to use the index to find the row, if your where field is not indexed, then MySQL can not find that line, it will lock the entire table

The subsequent access will cause a deadlock!

Add an index to the xxx field

Solve the problem.

Attach 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.execute (sqlmapclienttemplate.java:212)
At Org.springframework.orm.ibatis.SqlMapClientTemplate.update (sqlmapclienttemplate.java:411)
At Com.hsd.popularize.dao.BaseDaoImpl.update (basedaoimpl.java:162)
... 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.executeUpdate (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.execute (sqlmapclienttemplate.java:209)
... 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.executeInternal (serverpreparedstatement.java:693)
At Com.mysql.jdbc.PreparedStatement.execute (preparedstatement.java:794)
At Com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute (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. $Proxy 20.execute (Unknown Source)
At Com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate (sqlexecutor.java:81)
At Com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate (generalstatement.java:200)
At Com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate (generalstatement.java:78)
... More

MySQL a deadlock problem solved