Not the answer you need?
Register and ask your own question!

Percona XtraDB Cluster - Lock wait timeout exceeded

lear1lear1 EntrantCurrent User Role Novice
Hi, i'am doing some tests on my VMs - i installed Percona XtraDB Cluster (3 nodes). Configured it via official manual, cretead database "Percona" and table "mytable".

Everything is synchronized:
wsrep_local_state                | 4                                                        |
wsrep_local_state_comment        | Synced

There is a wsrep config:
# Path to Galera library

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node

# In order for Galera to work correctly binlog format should be ROW

# MyISAM storage engine has only experimental support

# Slave thread to use
wsrep_slave_threads= 8


# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

# Node IP address
# Cluster name

#If wsrep_node_name is not specified,  then system hostname will be used

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER

# SST method

#Authentication for SST method


Now, what's the problem? I've created a procedure which inserts a 1Milion records into "my table". I'am using DBeaver Client 6.2.0.

When i call my procedure with AUTO COMMIT OFF it runs about 35 seconds. Then i explicitly run commit. Commit is running about 5 seconds and then on all nodes in the same time i run a command "select count(*) from mytable;" and on 2 nodes it ends with error. On node 1 it shows right number of rows.

When i run the same commands after 30 seconds, it shows on all nodes same values. Why transactions fail on node 2 and node 3 when parameter innodb_lock_wait_timeout is set really high?

On Galera Cluster this is working well, without problems.

Sorry for my bad english, i hope you understand me. Thanks


  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi when you say it ends with error, do you have the error log please? And to save me asking later, if we could have the my.cnf for each node?
    And to confirm, this is a brand new install on these VMs?
  • lear1lear1 Entrant Current User Role Novice
    Open screen, there is a error message

    This is mysqld config, it's the same on all nodes. Yea all nodes are fresh installed (Centos 7.6)
    # Disabling symbolic-links is recommended to prevent assorted security risks
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    OK, thanks. Unfortunately those errors don't really tell much about what's going on under the hood.

    When the issue happens, what does this command show?

    show status like 'wsrep%';

    You might find that pt-stalk from Percona toolkit (free, open source software) will help you track down what's happening at a system level and there are a few other blog posts and forum posts that suggest what might be causing the issue if it's in the environment.

    In the meanwhile I will also check if the tech team have anything to add given the config etc you have sent through in case there is something there that is easily identifiable as causing the problem, especially if you are not experiencing the same issue with galera cluster.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.