数据库的死锁

dba执行以下指令，查看死锁的机器是哪一台，和被死锁的语句。

select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

死锁后一般会报出错误

提示出现死锁：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

如果在java例就显示为以下异常

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at sun.reflect.GeneratedConstructorAccessor247.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.Util.getInstance(Util.java:381)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1045)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)

数据库实现了各种死锁探查和超时机制。像InnoDB这样复杂的存储引擎会提示循环依赖并且立即返回错误。否则死锁将会导致查询非常缓慢。其他一些不好的做法是等待超时后放弃。当前InnoDB处理死锁的方式是回滚持有最少排他行级锁的事务。

实例

http://www.kissyu.org/2017/02/19/记录一次Mysql死锁排查过程/

https://blog.csdn.net/aesop_wubo/article/details/8286215