# Deadlocks **URL:** \ **Category:** Percona XtraDB Cluster 5.x\ **Created:** [November 5, 2015, 10:09am UTC](https://forums.percona.com/t/deadlocks/4497 "2015-11-05T10:09:25Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Lelik1985](https://avatars.discourse-cdn.com/v4/letter/l/51bf81/32.png) [@Lelik1985](https://forums.percona.com/u/Lelik1985)\ **Post date:** [November 5, 2015, 10:09am UTC](https://forums.percona.com/t/deadlocks/4497/1 "2015-11-05T10:09:25Z")
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]](http://xxxx.xxx/c/index.html?referrer=file:///home/web/services.html%5B/url%5D) 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 ? ---
**Author:** ![jrivera](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jrivera/32/13_2.png) [@jrivera](https://forums.percona.com/u/jrivera)\ **Post date:** [November 6, 2015, 11:30pm UTC](https://forums.percona.com/t/deadlocks/4497/2 "2015-11-06T23:30:03Z")
I’ll cut to the chase and share you this link - [url][http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads[/url]](http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads%5B/url%5D), basically the reason behind what you’re seeing is Galera’s use of optimistic locking. The workaround is to send queries that create deadlocks to a single node, while non-conflicting queries can be distributed to the rest of the nodes. ---
**Author:** ![Christian.Lahme](https://avatars.discourse-cdn.com/v4/letter/c/f05b48/32.png) [@Christian.Lahme](https://forums.percona.com/u/Christian.Lahme)\ **Post date:** [November 17, 2015, 7:14am UTC](https://forums.percona.com/t/deadlocks/4497/3 "2015-11-17T07:14:09Z")
Hi Lelik, Hi J.Rivera, we investigated this issue on our cluster lab too. To us, it seems a bug coming up with Percona XtraDB Cluster 5.6.26-25.12.1. All prior versions work fine and there are no deadlocks. We upgraded to 5.6.26 two weeks ago and had those errors. After downgrading to 5.6.25-25.12.1 everything is fine again. The double check on upgrading again showed those errors again. Indicating figure is wsrep\_local\_cert\_failures. These status variable is up counting on each deadlock. We strongly advise not to use 5.6.26. Cheers Chris ---
**Author:** ![dom](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@dom](https://forums.percona.com/u/dom)\ **Post date:** [November 19, 2015, 4:52pm UTC](https://forums.percona.com/t/deadlocks/4497/4 "2015-11-19T16:52:01Z")
Ah, I thought I was going mad! I have the same problem, also as Lelik1985 describes. Cannot get any deadlock counters or information out of show engine innodb status, and hugely increased deadlocks since upgrading to 5.6.26. Increasing wsrep\_retry\_autocommit even to large values makes no difference because it’s happening in the local cert stage. Attached is a graph with the midpoint showing the time of the upgrade to 5.6.26 - edit: sorry it won’t let me upload the graph… [URL=“[Bug #1516933 “innodb\_print\_all\_deadlocks doesn't work” : Bugs : Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC](https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1516933)”][https://bugs.launchpad.net/percona-x...r/+bug/1516933[/URL]](https://bugs.launchpad.net/percona-x...r/+bug/1516933%5B/URL%5D) [URL=“[https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/43228-deadlock-logging-not-working](https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/43228-deadlock-logging-not-working)”][https://www.percona.com/forums/quest...ng-not-working[/URL]](https://www.percona.com/forums/quest...ng-not-working%5B/URL%5D) ---
**Author:** ![dom](https://avatars.discourse-cdn.com/v4/letter/d/aeb1de/32.png) [@dom](https://forums.percona.com/u/dom)\ **Post date:** [November 24, 2015, 4:24am UTC](https://forums.percona.com/t/deadlocks/4497/5 "2015-11-24T04:24:07Z")
The bug report I raised explains this well: [url][https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1516933[/url]](https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1516933%5B/url%5D) Basically there’s a difference between local and cluster deadlocks. I do agree there’s a difference with local\_cert\_failures with 5.6.26, whether that’s an intended behaviour change with the underlying galera or not, who knows… ---
**Author:** ![nan008](https://avatars.discourse-cdn.com/v4/letter/n/e9bcb4/32.png) [@nan008](https://forums.percona.com/u/nan008)\ **Post date:** [June 20, 2018, 9:09am UTC](https://forums.percona.com/t/deadlocks/4497/6 "2018-06-20T09:09:26Z")
> [@jrivera;43158](#): > > I’ll cut to the chase and share you this link - [http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads](http://www.severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads), basically the reason behind what you’re seeing is Galera’s use of optimistic locking. The workaround is to send queries that create deadlocks to a single node, while non-conflicting queries can be distributed to the rest of the nodes. @[jrivera](https://www.percona.com/forums/member/21266-jrivera) can you share how to implement the HAproxy to send the updates that create deadlocks to one node only while the rest transactions will be redistributed to other nodes as well? The article you posted is to send all traffic for writing to only one node making the cluster into master and two slaves (we have 3 nodes cluster with round robin and HAProxy)