Query locking issue when updating a large number of rows in Percona XtraDB Cluster

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi,

I am experiencing a query locking issue on my Percona XtraDB Cluster (version 8.0.36-28.1 for Linux, WSREP version 26.1.4.3).

To resolve the issue, I am running the cluster on a single node

I am running the following update query:

UPDATE products SET productBrand = 'TEST' WHERE customerID = '25117' AND productID IN (25555346, ..., 25747112);

The total number of rows affected by this query is 8763. When I run the query with productID 8763, it completes successfully in about 4 seconds. However, if I add one more productID (so there are 8764 rows), the query does not complete and locks the entire table.

Could this be due to a limitation in the Percona XtraDB Cluster configuration? If so, what would be the relevant limit that would affect this?

Thanks in advance for any suggestions or guidance.

2

Hey there @emretorx,
Sounds like you don’t have good indexes. InnoDB does not do table-level locking; it does row-level locking, but it can only lock rows it found from an index. Looking at your query, you should have an index on (customerID, productID)

This is not an issue with PXC; you would see this same behavior in standard MySQL/Percona.

Use EXPLAIN and EXPLAIN ANALYZE to understand how your queries perform and why they are slow.