站长手记 - 站长手记
打印】【收藏此页
MariaDB Galera Cluster数据库死锁的处理过程
作者:本站:苏信东  来源:WaveCN.com  发布日期:2021-10-28  最后修改日期:2021-10-28  

同样的事情发生了两次,可以小结一下了。

 

表征:

所有对数据库的写操作访问都被挂起等待。

 

处理过程:

1、检查:

information_schema.INNODB_LOCKS

information_schema.INNODB_LOCK_WAITS

这两个表,可以看到当前的互斥锁的记录,一般来说列出的不止1条,但从经验(!)可以判断到应该是哪一条是死锁,比如这次还是INSERT INTO ... SELECT FROM ...,INSERT操作对象是使用MYISAM引擎的临时表(不清楚如果改用ARIA引擎会否能避免)

2、检查

information_schema.INNODB_TRX

表,通过第1步获得的锁记录id等参数,在这个表内可以找到对应的CONNECTION ID。

3、尝试按 CONNECTION ID 杀掉访问数据库的连接。这里存在两种可能:

(1)如果能杀,就可以恢复正常。

(2)如果不能杀,则存在卡死在INNODB引擎内和卡死在WSREP引擎两种情况。对于INNODB的,杀多几个相关的数据库连接一般都能恢复正常。

(3)对于卡死在WSREP引擎的情况,在杀连接时,会出现“ERROR 1095 (HY000): You are not owner of thread 4226581”这样的提示,无论是KILL CONNECTON 或者KILL QUERY ID都杀不了。这种情况只能关数据库。原因是WSREP PROVIDER为实现节点间数据同步而会短暂接管连接,并为连接设置一个比较高级的特权,高于数据库的root,所以杀不了。此时,记录好相关连接所在操作的表名,这些表很容易会被连带破坏掉,要重点检查。

 

接下来都是应对卡死在WSREP引擎的操作:

4、杀掉所有能杀的数据库连接,关闭所有访问数据库的后台服务或者前台系统。如果有写入中的连接不能杀,简单忽略之。

5、SYSTEMCTL 方式关闭非卡死连接所在的节点数据库。如果关不了,KILL -9杀掉进程。

6、SYSTEMCTL 方式关闭卡死连接所在的节点的数据库(以下简称为挂起节点),如果关不了,KILL -9杀掉进程。注意,此时Galera Cluster重启后的SST过程是无法避免的。

KILL -9对于INNODB引擎来说,只要不影响到REDO LOG则没啥大事。另外,对于5和6两步,如果设置了集群数据库是多写多读模式,则考虑放弃挂起节点的数据。如果挂起节点是全集群的唯一写节点,则要谨慎考虑是否要保留此节点数据,并把数据备份准备好。以下内容对应的是挂起节点为唯一写节点的情况:

 

7、修改/etc/my.cnf.d/server.cnf,其中的 wsrep_on=on 改为off,然后 systemctl 以单机模式启动节点的数据库。

8、启动时,持续 tail 数据库的错误日志,然后直接按在第三步中记录的和死锁连接相关的表进行检查。在此期间,注意观察错误日志提示。

对于检查出现错误的表,如果SELECT数据失败,可以尝试REPAIR或者直接DROP掉重新CREATE。但如果能SELECT,就不要DROP,REPAIR就好。因为极端情况下DROP了之后会CREATE不了,这种情况出现在INNODB的CATALOG有问题,或者这个表的frm(表结构定义)也被破坏掉的时候。

9、随后对其它数据库的表进行全面的CHECK操作检查是否存在问题。但一般都没有问题,且此过程较花时间,检查期间可能会导致用户的写操作挂起。

10、重新同步节点,恢复集群。

 

后记:

1、据说可以通过设置 max_statement_time 来让数据库主动退出超长时间运行的内部进程,但未见有实际使用的网帖。参考:

https://jira.mariadb.org/browse/MDEV-12008

https://mariadb.com/kb/en/aborting-statements/

 

2、KILL QUERY ID 的QUERY ID要通过 SHOW ENGINE INNODB STATUS 才能看到。QUERY ID 不是PROCESS ID。

 

3、被堵塞的内部进程,状态会停留在:Commit、committing、acquiring total order isolation,个别停留在Updating。