MariaDB Galera Cluster数据库“彻底死锁”的处理过程
生产环境是三台服务器，使用MariaDB Galera Cluster，形成三节点集群，实现高可用。只是世事无绝对，对于高度繁忙的数据库来说，依然在极偶然的机会下产生彻底的死锁，也就是除了通过运行KILL -9 参数命令杀掉数据库进程之外无法解决的死锁。就我经验而言，平均1.5年1次吧。
笔者：国际认证信息系统审计师、软考系统分析师
数据库死锁表征：
所有对数据库的写操作访问都被挂起等待，进而影响到对被写的表的读操作也被挂起等待，最后所有用户的所有连接都是处于等待状态。
接到用户报告后，立即开始处理过程：
information_schema.INNODB_LOCKSinformation_schema.INNODB_LOCK_WAITS
这两个表，可以看到当前的互斥锁的记录，一般来说列出的不止1条，但从经验（所以企业内部具备有经验的DBA人员是多么重要！）可以判断到应该是哪一条是死锁，比如我的情况，找到的是这么一句：
INSERT INTO ... SELECT FROM ...
其中，INSERT操作对象是使用MyISAM引擎的临时表。倒是不清楚如果改用Aria引擎会否能避免，毕竟MyISAM确实太旧了。
information_schema.INNODB_TRX
通过这个表的内容，以及在第1步获得的锁记录id等参数，在这个表内可以找到对应的CONNECTION ID。
操作方法是使用 KILL CONNECTION 命令。这里存在两种可能：
（1）如果能杀，随后所有被阻塞的操作都得到执行，那么数据库就恢复正常。
（2）如果不能杀，则存在(a)卡死在InnoDB引擎内和(b)卡死在WSREP引擎两种情况。
不同的卡死情况直接关系到后续处理过程的风险程度。
对于(a)卡死在InnoDB引擎内的情况，杀掉几个其它有一定相关性的数据库连接，典型如锁死语句操作的表的读操作连接，一般都能恢复正常。
对于(b)卡死在WSREP引擎的情况，会在尝试杀连接时，数据库返回：
ERROR 1095 (HY000): You are not owner of thread 4226581
这样的提示。此时，无论是用 KILL CONNECTON 或者 KILL QUERY ID命令都杀不了。唯一能做的只有杀掉数据库服务。原因是 WSREP PROVIDER 程序为确保实现节点间数据同步，会短暂接管执行写操作的连接，并为连接设置一个比较高级的特权，这个特权是高于数据库的root用户，所以root用户杀不了处在这个状态的连接。
（１）首先记录好相关连接所在操作的表名，这些表很容易会被连带破坏掉，要重点检查。
（２）杀掉所有能杀的数据库连接，关闭所有访问数据库的后台服务和前台系统。如果有写入中的连接不能杀，简单忽略之。
（３）通过 systemctl 命令，对非卡死连接所在的节点数据库，尝试关闭服务。如果关不了，KILL -9 杀掉数据库进程。
（４）继续通过 systemctl 命令，对卡死连接所在的节点的数据库（以下简称为挂起节点），尝试关闭服务。如果关不了，也是 KILL -9 杀掉数据库进程。注意，此操作会必然导致 Galera Cluster 重启后的SST（State Snapshot Transfer）过程是无法避免的。
通过实践认为，在本文描述的情况下，对数据库进程执行 KILL -9 操作后，对于使用InnoDB引擎存放的数据来说，只要不影响到REDO LOG的完整性就没见到有数据丢失的问题。但对于不是InnoDB引擎的数据，比如MyISAM的，尤其是被写入引起死锁的表，就有可能会被破坏而需要重建。所以结论是只要数据有用，无论数据量多少，都应该使用InnoDB引擎。
对于（２）和（３）两步，如果设置了集群数据库是多写多读模式，则要认真考虑是否放弃挂起节点的数据，在数据库重启服务并进行SST时，选择没有出现挂起情况的节点作为数据的Donor。
但如果挂起节点是全集群的唯一写节点，则要谨慎检查和考虑重启服务进行SST时，究竟是否使用挂起节点作为Donor了。
以下内容对应的是挂起节点为唯一写节点的情况。
（５）重启服务器后，修改配置文件：
/etc/my.cnf.d/server.cnf
把配置文件中的 wsrep_on=on 改为off，然后执行
systemctl start mariadb
命令以单机模式启动节点的数据库。
注：经验认为重启服务器是必须的，考虑到内存释放重整以及网络资源释放等因素，重启服务器最简单直接和可靠。
（６）单机模式启动后，持续 tail 命令观察数据库的错误日志。，然后按（１）中记录的和死锁连接相关的表名，执行
CHECK TABLE 表名 EXTENDED;
命令进行检查。在此期间，注意观察错误日志提示。
对于检查出现错误的表，如果SELECT数据失败，可以尝试REPAIR或者直接DROP掉重新CREATE。但如果能SELECT，就不要DROP，REPAIR就好。因为极端情况下DROP了之后会CREATE不了，这种情况出现在InnoDB的Catalog有问题，或者这个表的frm（表结构定义）也被破坏掉的时候。
（７）随后对其它数据库的表进行全面的CHECK操作检查是否存在问题。但一般都没有问题，且此过程较花时间。
（８）确认数据无问题后，systemctl 关闭单机，还原配置文件的wsrep_on参数为on，然后按Galera Cluster模式用 galera_new_cluster 命令重新启动集群，等待SST过程重新同步节点，然后恢复集群服务。
后记：
1、据说可以通过设置 max_statement_time 来让数据库主动退出超长时间运行的内部进程，但对于卡死在 WSREP 引擎这种情况尚未知是否有效，要等下一次的问题出现，而且这种设置有可能和实际使用情况，比如用户就是执行了一个较长时间的查询，所冲突。参考：
2、KILL QUERY ID 操作所要给出的QUERY ID参数，要通过
SHOW ENGINE INNODB STATUS
才能看到。因为 QUERY ID 不是 PROCESS ID。
3、被堵塞的连接，其状态会停留在：Commit、committing、acquiring total order isolation，个别停留在Updating。
本站微信订阅号：
本页网址二维码：