# PXC8 locks when adding users to different nodes at the same time

**URL:** <https://forums.percona.com/t/pxc8-locks-when-adding-users-to-different-nodes-at-the-same-time/15033>\
**Category:** Percona XtraDB Cluster 8.x\
**Created:** [April 1, 2022, 11:54am UTC](https://forums.percona.com/t/pxc8-locks-when-adding-users-to-different-nodes-at-the-same-time/15033 "2022-04-01T11:54:45Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mikhail\_Ermolaev](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikhail_ermolaev/32/2953_2.png) [@Mikhail\_Ermolaev](https://forums.percona.com/u/Mikhail_Ermolaev)\
**Post date:** [April 1, 2022, 11:54am UTC](https://forums.percona.com/t/pxc8-locks-when-adding-users-to-different-nodes-at-the-same-time/15033/1 "2022-04-01T11:54:45Z")

</div>

I am using pxc8 (1:8.0.26-16-1.focal) in docker on two nodes.

OS Ubuntu 20.04

When I add users in DB in the different nodes at the same time my queries are not receive the result and hang.

In log i see the row:  
[Note] [MY-000000] [WSREP] MDL conflict db=mysql table=columns\_priv ticket=4 solved by abort

If I will see processlist for two nodes I see:  
| 10 | system user | | | Sleep | 61 | Waiting for table metadata lock | CREATE USER IF NOT EXISTS ‘test’@‘%’ IDENTIFIED BY | 61227 | 0 | 0 |  
| 264 | root | localhost | NULL | Query | 61 | wsrep: initiating TOI for write set (-1) | CREATE USER IF NOT EXISTS ‘test’@‘%’ IDENTIFIED BY | 61296 | 0 | 0 |

In this state I can not create database or alter tables.

How can I fix the situation?

Configs:  
**/etc/mysql/my.cnf**

```auto
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

bind-address = 127.0.0.1
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

log_error = /var/log/mysql/error.log
log_error_verbosity = 3
binlog_expire_logs_seconds = 864000

tls_version=TLSv1.2
pxc_encrypt_cluster_traffic = OFF

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

!includedir /etc/mysql/conf.d/

```

**/etc/mysql/conf.d/node.cnf**

```auto
[mysqld]

default_authentication_plugin=mysql_native_password
datadir=/var/lib/mysql/mysql_data
socket=/tmp/mysql.sock
skip-host-cache

default_storage_engine=InnoDB
binlog_format=ROW

innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_autoinc_lock_mode=2

bind_address = 0.0.0.0

wsrep_applier_threads=8
wsrep_cluster_address=gcomm://
wsrep_provider=/usr/lib/galera4/libgalera_smm.so

wsrep_cluster_name=Theistareykjarbunga

wsrep_log_conflicts
transaction_write_set_extraction = OFF
pxc_strict_mode=ENFORCING

wsrep_sst_method=xtrabackup-v2

[sst]
encrypt=0
progress=/var/lib/mysql/sst_in_progress

```

---

<div class="post-metadata">

**Author:** ![Michael\_Coburn](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/michael_coburn/32/18_2.png) [@Michael\_Coburn](https://forums.percona.com/u/Michael_Coburn)\
**Post date:** [April 1, 2022, 2:18pm UTC](https://forums.percona.com/t/pxc8-locks-when-adding-users-to-different-nodes-at-the-same-time/15033/2 "2022-04-01T14:18:55Z")

</div>

Hi @Mikhail_Ermolaev , welcome back to the Percona forums!

You should generally be writing to only one PXC instance at a time. What you’re hitting are network based deadlocks and they are not automatically resolving on their own. I would avoid this behaviour of writing to multiple instances.

---

<div class="post-metadata">

**Author:** ![Mikhail\_Ermolaev](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mikhail_ermolaev/32/2953_2.png) [@Mikhail\_Ermolaev](https://forums.percona.com/u/Mikhail_Ermolaev)\
**Post date:** [April 1, 2022, 2:56pm UTC](https://forums.percona.com/t/pxc8-locks-when-adding-users-to-different-nodes-at-the-same-time/15033/3 "2022-04-01T14:56:54Z")

</div>

If we use proxysql to access the database and make queries from different connections, then the situation may repeat.

---

<div class="post-metadata">

**Author:** ![Michael\_Coburn](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/michael_coburn/32/18_2.png) [@Michael\_Coburn](https://forums.percona.com/u/Michael_Coburn)\
**Post date:** [April 1, 2022, 2:58pm UTC](https://forums.percona.com/t/pxc8-locks-when-adding-users-to-different-nodes-at-the-same-time/15033/4 "2022-04-01T14:58:16Z")

</div>

Even with ProxySQL you should set up your architecture so that writes are sent to a hostgroup which contains a single member.