# Loadbalancing read/write

**URL:** <https://discourse.haproxy.org/t/loadbalancing-read-write/5620>\
**Category:** Help!\
**Created:** [August 19, 2020, 11:40am UTC](https://discourse.haproxy.org/t/loadbalancing-read-write/5620 "2020-08-19T11:40:45Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![edamber](https://avatars.discourse-cdn.com/v4/letter/e/ccd318/32.png) [@edamber](https://discourse.haproxy.org/u/edamber)\
**Post date:** [August 19, 2020, 11:40am UTC](https://discourse.haproxy.org/t/loadbalancing-read-write/5620/1 "2020-08-19T11:40:45Z")

</div>

Hello,

I have a 3 node galera cluster that I loadbalance with HAproxy. We ran into a problem with deadlock when using a certain php function on our sites that uses this setup. I know I can use check backup for db-02 and db-03 but I still want to load balance on all three, any config I can use for this?

The message I get when using the function is: Fatal error: Uncaught Zend\_Db\_Statement\_Mysqli\_Exception: Mysqli statement execute error: Got error 35 “Resource deadlock avoided” during COMMIT.

Thanks!

---

<div class="post-metadata">

**Author:** ![edamber](https://avatars.discourse-cdn.com/v4/letter/e/ccd318/32.png) [@edamber](https://discourse.haproxy.org/u/edamber)\
**Post date:** [August 21, 2020, 7:08am UTC](https://discourse.haproxy.org/t/loadbalancing-read-write/5620/2 "2020-08-21T07:08:34Z")

</div>

It works when I put check backup on db-02. So I can use HAproxy as a failover but not as loadbalancer. Can also mention that we use 3 nodes in the Galera cluster but only try to loadbalance 2 of them. Here is the config:

global  
log /dev/log local0  
log /dev/log local1 notice  
chroot /var/lib/haproxy  
stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners  
stats timeout 30s  
user haproxy  
group haproxy  
daemon

```
    maxconn 2048
    tune.ssl.default-dh-param 2048

```

defaults  
log global

```
    mode http
    option forwardfor
    option http-server-close
    stats enable
    stats uri /stats
    stats realm Haproxy\ Statistics
   

    option httplog
    option dontlognull
    timeout connect 5000
    timeout client 86400
    timeout server 86400
    errorfile 400 /etc/haproxy/errors/400.http
    errorfile 403 /etc/haproxy/errors/403.http
    errorfile 408 /etc/haproxy/errors/408.http
    errorfile 500 /etc/haproxy/errors/500.http
    errorfile 502 /etc/haproxy/errors/502.http
    errorfile 503 /etc/haproxy/errors/503.http
    errorfile 504 /etc/haproxy/errors/504.http

```

# MySQL Cluster FE configuration

frontend mysql\_cluster\_frontend  
bind \*:3306  
bind \*:3307  
mode tcp  
option tcplog  
default\_backend galera\_cluster\_backend

# MySQL Cluster BE configuration

backend galera\_cluster\_backend  
mode tcp  
option tcpka  
option mysql-check user haproxy  
balance leastconn  
server db-01 192.168.11.102:3306 check weight 1  
server db-02 192.168.11.103:3306 check weight 1

---

<div class="post-metadata">

**Author:** ![hac3ru](https://avatars.discourse-cdn.com/v4/letter/h/ee59a6/32.png) [@hac3ru](https://discourse.haproxy.org/u/hac3ru)\
**Post date:** [August 24, 2020, 8:48am UTC](https://discourse.haproxy.org/t/loadbalancing-read-write/5620/3 "2020-08-24T08:48:41Z")

</div>

Hello,

From what I understand, you’ve got a function in your app that causes a deadlock. You should first create the app so it can handle deadlocks. Still, if that’s out of the question, the config from above, with “option mysql-check user haproxy” should work properly.  
When there’s no deadlock, it will load balance among the two servers. When one is having a lock, the queries should be sent to the other server. Still, I think that this can cause an issue when the two servers will try to sync, because each will have data written in them at the same time, on the same row, basically, as db-02 is unaware that db-01 is already writing. I could be wrong though…

Still, as I said, deadlocks are a MySQL thing and apps should have a mechanism to work (retry transaction after a few seconds or something) even if they hit a lock. HAProxy’s job is to load balance / failover, not to avoid a MySQL issue, so you’re tackling the issue from the wrong perspective. If I were you, I’d tackle this from the app perspective / DB perspective (get faster disks, use more threads, etc, depending on what’s slowing the DB down).

---

<div class="post-metadata">

**Author:** ![edamber](https://avatars.discourse-cdn.com/v4/letter/e/ccd318/32.png) [@edamber](https://discourse.haproxy.org/u/edamber)\
**Post date:** [August 24, 2020, 11:12am UTC](https://discourse.haproxy.org/t/loadbalancing-read-write/5620/4 "2020-08-24T11:12:17Z")

</div>

It’s wierd though, I actually get “deadlock avoided” in the error message on the websites. I now use db-02 with check backup and the php function works great without any errors. Shame though because now our setup is only a backup instead of load balancing.

I think you are right though, this is DB related and nothing todo with HAproxy. The function we use that has errors actually copies a database entry so must be something there, the rest of the site works great no other function generates errors.