I have a setup with 3 Galera nodes on MariaDB 10.4.18.
I have blocking issue with MariaDB stability during concurrent updates on same row (this usually happens for me when multiple client applications tries to modify a record in the database in the same time).
The issues is the following:
1. Multiple customers starts the application and tries to perform an update on the same record.
2. Galera handles a few concurrencies but eventually hangs.
Now, I understand that we need to retry the transaction in the case of a deadlock, the issue is that Galera hangs before the application retries that.
1. Start Galera on 3 nodes
2. Have a ProxySQL with 3 nodes as writers
3. We have a tool that connects to Galera start a tool that sends queries continuously to the database
I'm attaching the script that we use testing and also I'm putting here he Galera configuration:
- MariaDB validation diagram.png - This is the network topology for testing
- galera-lock-replication.sql - SQLs to create the database and table for testing
- galera-lock-replication.php - the tool that connects to database and send sql update
- threads-tool.php - the tool that starts "galera-lock-replication.php" in multithreading mode to simulate concurrency
1. Create the database using the galera-lock-replication.sql script provided in attach
- the galera-lock-replication.php must be available at the following URL for the threads-tool.php to be able to work correctly:
- please configure ProxySQL endopint in galera-lock-replication.php
3. Start a first terminal with:
- this will start bulks of 100 paralel calls to the frontend webservice in a loop:
4. Wait few seconds (between 5 and 20)
5. Galera cluster should start refuse connections