MariaDB Galera集群InnoDB状态死锁信息过时问题咨询
Hey there, let's dig into this deadlock problem you're hitting with your 3-node Galera cluster running MariaDB 10.1.32. It’s super frustrating when syslog keeps spitting out deadlock errors, but SHOW ENGINE INNODB STATUS only serves up stale details—here’s how to get the real info you need to fix this:
First, Make Sure You’re Checking the Right Node
Deadlock errors in syslog are node-specific. If you’re running SHOW ENGINE INNODB STATUS on a different node than where the error was logged, you’ll only see that node’s local deadlock history (which might be outdated). Double-check which node is throwing the errors in syslog, then run the status command directly on that node.
Enable Persistent Deadlock Logging
The SHOW ENGINE INNODB STATUS output only keeps track of the very last detected deadlock, which gets overwritten fast if you’re dealing with frequent deadlocks. To capture every deadlock in detail, add these settings to your my.cnf/my.ini on all cluster nodes, then restart MariaDB:
innodb_print_all_deadlocks = 1 log_error = /var/log/mysql/error.log # Adjust this path to match your system's setup
This will log full deadlock details—including the transactions involved, locks held, and locks requested—directly to your error log. No more relying on the easily overwritten entry in innodb status.
Check for Galera-Specific Replication Conflicts
Galera’s multi-master setup can cause conflicts that look like deadlocks, even if InnoDB doesn’t flag them locally. Scan your error logs for messages starting with WSREP: Conflict detected—these point to write conflicts between nodes that might be triggering the deadlock errors.
Also, check if your nodes are lagging on replication:
SHOW STATUS LIKE 'wsrep_local_recv_queue_avg';
If the average queue size is high, the node is struggling to apply replication events in time, which increases the chance of timing-related deadlocks.
Temporarily Log All Queries (Carefully)
If you need to catch the exact transactions causing deadlocks right now, you can enable the general query log temporarily (note: this generates a lot of data, so don’t leave it on permanently in production):
general_log = 1 general_log_file = /var/log/mysql/general.log
This will log every query run on the node, so you can cross-reference the timestamps with your syslog deadlock errors to find the problematic transactions.
Consider Upgrading Your MariaDB Version
MariaDB 10.1.32 is quite old (released back in 2018), and newer versions (like 10.6 or 10.11, which are still supported) include improved deadlock detection, better Galera integration, and bug fixes for multi-master concurrency issues. Upgrading might resolve the root cause of your frequent deadlocks.
内容的提问来源于stack exchange，提问作者Florian
