Hi
I have a cluster with 10 nodes balanced on HAProxy as 5 nodes for read and 5 nodes for write
I noticed in apache log a lot of messages with Query failed by reason : Deadlock found when trying to get lock; try restarting transaction
[Thu Nov 05 16:12:36 2015] [error] [client xxx.xxx.xxx.xxx] Query failed by reason : Deadlock found when trying to get lock; try restarting transaction (UPDATE users SET keep_alive=NOW(), ip=‘xxx.xxx.xxx.xxx’, now_playing_type=0, just_started=0, last_watchdog=NOW() WHERE (mac=‘00:00:00:00:00:00’)), referer: [url]http://xxxx.xxx/c/index.html?referrer=file:///home/web/services.html[/url]
What i found:
If i disable all “write” nodes in haproxy and just leave one server, it is ok any Deadlock message in apache
If i enable just 2 “write” nodes in haproxy then emmidiatly i can see messages with Deadlock
I wanted to find LATEST DETECTED DEADLOCK in SHOW ENGINE INNODB STATUS
But there is anything about LATEST DETECTED DEADLOCK
Here too
mysql> SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘innodb_deadlocks’;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 117754
Current database: *** NONE ***
±---------------+
| VARIABLE_VALUE |
±---------------+
| 0 |
±---------------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE ‘innodb_deadlocks’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| Innodb_deadlocks | 0 |
±-----------------±------+
1 row in set (0.00 sec)
All nodes have the same latest version
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 135138
Server version: 5.6.26-74.0-56-log Percona XtraDB Cluster (GPL), Release rel74.0, Revision 624ef81, WSREP version 25.12, wsrep_25.12
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Can you help me to find source of problem
Cluster or haproxy ?