MYSQL 8 从metadata开始到如何获得语句由于获取锁失败的错误

最近有同学提出，你的文字还OK，就是排版有问题，嗯，的确。我的排版的确是很烂，我也想改变，可能基于工作中的时间问题，以及学习的速度，让我实在是没有能力在排版上下功夫。希望哪位好心的同学，可以给我一个方法来提高排版，并且不要花太多的心思在这里面，谢谢。

接着上期，metadata lock 到底是一个什么东西，首先metadata lock 是MYSQL 来管理一致性访问以及确认数据一致性所做得一个工作。metadata lock不光是应用于表的层面，同时也应用于schema, triiger,scheduled,function等层面。

Metadata锁的意义在于MYSQL 不会随便让数据写入到metadata 中，他要做的是维护数据在表中的一致性，举例当有表的操作在修改 metadata 中的数据的情况下，未提交的事务，或者是回滚的事务都需要等待metadata lock中的锁释放后，才能进行后续的工作。

那么接下来的问题是，metadata lock的锁会在什么时候在MYSQL 中工作的问题，

1 创建或删除索引

2 修改表结构

3 在对表进行optimize table , repair table ,delete table , table lock 生效时，这些都触发了metadata lock

select concat('kill ',i.trx_mysql_thread_id,';') 
from information_schema.innodb_trx i,   
(select  id, time      
from  information_schema.processlist      
where    time = (select  max(time)             
 from   information_schema.processlist              
where  state = 'Waiting for table metadata lock'                      
and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p   
where timestampdiff(second, i.trx_started, now()) > p.time   and i.trx_mysql_thread_id  not in (connection_id(),p.id);

通过这个方式可以将长时间等待metadata lock 不工作的事务从数据库中找出来, 并产生一个kill 的语句。

那么下面有一个问题，如果对一个表的锁定的解锁顺序是如何的，当我们针对一个表进行了 X锁的加持，后面我们先进行了一个插入的操作，然后在进行对表的rename的操作， 此时真正的顺序应该是

1 X 锁定标

2 INSERT

3 RENAME

————————

1 解除X锁

2 RENAME

3 INSERT

另外在MYSQL 8.013后MYSQL prepare 事务的问题，在客户端和数据库失联的情况下，用户的prepare状态会被保持直到XA_COMMIT 或者 XA_ROLLBACK

除了这个问题以外，就是关于如何发现曾经MYSQL 发生过错误，一般的情况MYSQL 5.X我们都是去找到ERROR LOG ，里面去找寻可能发生的信息，但是MYSQL 8 我们在performance_schema 中已经有了 events_errors 系列，这些表可以让你从各个层面来了解MYSQL 在最近都发生过什么错误。

1 event_error_summary_global_by_error

通过这个表，我们查看这个表可以记录的错误的种类有 5017种

select count(distinct error_name) from performance_schema.events_errors_summary_global_by_error;

select * from performance_schema.events_errors_summary_global_by_error where error_name IN  ('ER_LOCK_WAIT_TIMEOUT','ER_LOCK_DEADLOCK','ER_LOCK_TABLE_FULL')\G

在这个表里面分别有三个记录与我们日常所有关的方向，

'ER_LOCK_WAIT_TIMEOUT', 发生过程序block的情况

'ER_LOCK_DEADLOCK', 发生过程序死锁的情况

'ER_LOCK_TABLE_FULL' 发生过全表扫描的情况

select * from events_errors_summary_by_user_by_error where last_seen is not null; 以上的这个表，主要是从访问数据库的用户的角度来出发，查看这个用户曾经发生过什么样的错误，我们可以改写一下这个查询的语句，来更精确的对这个账号发生过什么错误进行判断。

select USER,ERROR_NAME,last_seen,sum_error_raised from events_errors_summary_by_user_by_error where SUM_ERROR_RAISED > 0 and error_name in ('ER_LOCK_WAIT_TIMEOUT','ER_LOCK_DEADLOCK','ER_LOCK_TABLE_FULL');

上面的语句可以发现你最后一次产生查询问题的账号以及问题的原因，和发生的次数的递增，这里可以做定期的信息收集，然后对比同一个时期的信息差，来发现更多的问题。