出现死锁的原因
两个线程或者两个事务,都需要对方释放同一资源的锁,从而导致死锁。
解决策略
- 尽可能的将事务力度控制到最小
- 超时自动释放,通过SHOW VARIABLES WHERE Variable_name = 'innodb_lock_wait_timeout’语句可以查看引擎默认超时时间
- 开启死锁检测,通过SHOW VARIABLES WHERE Variable_name LIKE ‘%innodb_deadlock%’ 语句可以查看是否开启;如果发生死锁,回滚后面的事务。
死锁回滚条件
If the LATEST DETECTED DEADLOCK section of InnoDB Monitor output includes a message stating, “TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,” this indicates that the number of transactions on the wait-for list has reached a limit of 200. A wait-for list that exceeds 200 transactions is treated as a deadlock and the transaction attempting to check the wait-for list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the wait-for list.
查询数据库实例中事务相关信息
#查询进行中的事务:
SELECT * FROM information_schema.INNODB_TRX
#查询等待锁的事务
SELECT * FROM information_schema.INNODB_LOCK_WAITS
#查询获取锁的事务
SELECT * FROM information_schema.INNODB_LOCKS
参考:
https://www.cnblogs.com/sunss/p/7875012.html
https://www.cnblogs.com/lamp01/p/10751908.html