mysql报错：
发现问题
最近在补以前数据的时候程序突然报如下错误：
[2017-02-10 13:12:06.678] [INFO] mysqlLog - update tbl_playerdata_error: { [Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction]
code: 'ER_LOCK_DEADLOCK',
errno: 1213,
sqlState: '40001',
index: 0 }
⼀看就是mysql出现了死锁问题，其实上⾯跑的程序在测试服跑了好久都没什么问题，为什么在正式服上会出现mysql的死锁问题呢，第⼀反应是不是数据量太⼤（3百多万条），可是也不可能啊，再说
死锁和这些有什么鸡⽑的关系，看来要好好解决下了。
问题分析
我的分析是：由于现在处理的是正式服的数据，⽽正式服还有许多⽤户在操作，应该是在⽤户查询，或者是其他操作的时候，和我这边的数据更新产⽣了死锁（⾸先说明使⽤的是：InnoDB存储引擎。由
mysql需要安装documentation于⽤户那边的查询或者其他操作锁定了我需要的资源，⽽我这边更新也锁定了⽤户操作的⼀部分资源，两边都等着对⽅释放资源，从⽽导致死锁）。
解决⽅法
知道错误code之后，先来查看mysql的说明，关于上⾯的 Error: 1213 SQLSTATE: 40001，参见：
Message: Deadlock found when trying to get lock; try restarting transaction
InnoDB reports this error when a transaction encounters a deadlock and is automatically rolled back so that your application can take corrective action. To recover from this error, run all the operations in this transaction again. A deadlock occurs when requests 上⾯有两句：
To recover from this error, run all the operations in this transaction again<br><br>If you encounter frequent deadlocks, make the sequence of locking operations (<code class="literal">LOCK TABLES</
code>, <code class="literal">SELECT ... FOR UPDATE</这两句也就道出了处理死锁的⽅法了，我就是在死锁错误发⽣的时候，使⽤定时器再重新做⼀次更新操作，这样就避免了上⾯出现的问题。
One easy trick that can help with most deadlocks is sorting the operations in a specific order.
You get a deadlock when two transactions are trying to lock two locks at opposite orders, ie:
connection 1: locks key(1), locks key(2);
connection 2: locks key(2), locks key(1);
If both run at the same time, connection 1 will lock key(1), connection 2 will lock key(2) and each connection will wait for the other to release the key -> deadlock.
Now, if you changed your queries such that the connections would lock the keys at the same order, ie:
connection 1: locks key(1), locks key(2);
connection 2: locks key(1), locks key(2);
it will be impossible to get a deadlock.
So this is what I suggest:
Make sure you have no other queries that lock access more than one key at a time except for the delete statement. if you do (and I suspect you do), order their WHERE in (k1,k2,..kn) in ascending order.
Fix your delete statement to work in ascending order:
Change
DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
To
DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;
Another thing to keep in mind is that mysql documentation suggest that in case of a deadlock the client should retry automatically. you can add this logic to your client code. (Say, 3 retries on this particular error before giving up).
总结
以上就是这篇⽂章的全部内容了，希望本⽂的内容对⼤家的学习或者⼯作能带来⼀定的帮助，如果有疑问⼤家可以留⾔交流，谢谢⼤家对的⽀持。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。