mysql错误码 1213_webgame中Mysql Deadlock ERROR 1213 (40001)错误的排查历程_MySQL

bitsCN.com

案例发现:

从我们正在运营的一款webgame的异常日志中看到一些程序执行MYSQL 语句的报错信息。比较多的是“Deadlock found when trying to get lock; try restarting transaction”,少部分是“Error number: 1205:Lock wait timeout exceeded; try restarting transaction”，如下：001 --> 2012-11-22 06:05:36 --> ERROR -->system/database/Driver.php--777--log--Debug002 --> 2012-11-22 06:05:36 --> ERROR -->system/database/Driver.php--295--error--JV_Driver003 --> 2012-11-22 06:05:36 --> ERROR -->system/database/ActiveRecord.php--947--query--JV_Driver004 --> 2012-11-22 06:05:36 --> ERROR -->server/models/MRoleMonster.php--84--update--JV_ActiveRecord005 --> 2012-11-22 06:05:36 --> ERROR -->server/daemon/update.php--392--kill--MRoleMonster006 --> 2012-11-22 06:05:36 --> ERROR --> DATABASE: xxx_roles_xxx(10.1.1.75) --> Error number: 1205:#####Lock wait timeout exceeded; try restarting transaction##### --> Error Message: #####db_query_error --> Query Error: UPDATE `monster` SET `kills` = kills + 1 WHERE `id` = '30036' AND `role_id` = '19863'.##### --> query elapsed counter: 184293;time 590.4272678 --> Database Connection has be closed:dbwRole001 --> 2012-11-28 15:59:47 --> ERROR -->system/database/Driver.php--777--log--Debug002 --> 2012-11-28 15:59:47 --> ERROR -->system/database/Driver.php--295--error--JV_Driver003 --> 2012-11-28 15:59:47 --> ERROR -->system/database/ActiveRecord.php--948--query--JV_Driver004 --> 2012-11-28 15:59:47 --> ERROR -->server/models/MRole.php--1143--update--JV_ActiveRecord005 --> 2012-11-28 15:59:47 --> ERROR -->server/daemon/update_other.php--283--updateRoleState--MRole006 --> 2012-11-28 15:59:47 --> ERROR --> DATABASE: xxx_roles_xxx(10.1.1.72) --> Error number: 1213:#####Deadlock found when trying to get lock; try restarting transaction##### --> Error Message: #####db_query_error --> Query Error: UPDATE `role_state` SET `state` = 1WHERE `role_id` = '53016'.##### --> query elapsed counter: 4972;time 4.2417307 --> Database Connection has be closed:dbwRole007 --> 2012-11-28 15:59:47 --> ERROR -->system/database/Driver.php--616--log--Debug008 --> 2012-11-28 15:59:47 --> ERROR -->server/daemon/combat_update.php--308--transComplete--JV_Driver009 --> 2012-11-28 15:59:47 --> ERROR --> DB Transaction Failure.

从报错的英文上理解，大约是发生了“死锁”，以及“事务锁等待超时”两个错误异常。而且，都是我们后台PHP常驻进程遇到的问题。异常的代码对应行数上&#