mysql如何创建两个会话_究竟是哪个会话造成了"Waiting for table metadata lock"等待...

       曾经是否有遇到过，数据库出现一大片的”Waiting for table metadata lock“元数据锁等待，但是却不知道到底是哪个会话占有了该对象的元数据锁。 我们曾在5.7上遇到过这个现象，现在在8.0上做同样的实验，以便我们在遇到类似问题的时候，而不是措手不及。 

实验方法如下：

Session 1 :

mysql> create table xcy( id int primary key , name varchar(5));

Query OK, 0 rows affected (0.44 sec)

mysql> insert into  xcy values(1,'asdf');

Query OK, 1 row affected (0.06 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into  xcy values(2,'asdfsssss');

ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> 

Session 2：对这个表加索引,无法加成功

alter table xcy add index idx_name(name);

Session 3: 查询这个表，无法返回结果。

select * from xcy;

通过show processlist 命令，查询数据库当前会话状态:

发现增加索引的会话出现了“Waiting for table metadata lock“等待，查询语句"select * from xcy" 也出现同样的等待，其他会话都是sleep 状态。 

      我们该怎么去排查到底是谁持有了表的元数据锁， 有经验的DBA会果断地认为：当前数据库应该/“肯定”存在未提交的事务。于是通过show engine innodb status 命令去检查，命令输出如下图：

并没有发现存在活跃的事务。那到底是谁在持有表的metadata锁？

查看information_schema.innodb_trx表，输出同样为空。

mysql> select * from information_schema.innodb_trx;

Empty set (0.00 sec)

mysql> 

陷入僵局。。。。。。。。。

       好在，MySQL 8.0的performance_schema默认就开启了metadata lock的监控。我们查询performance_schema.metadata_locks 表----有发现，问题已经定位。信息如下图：

(请放大图片)，我们发现有两个会话想获取表xcy的metadata 锁，但处于pending状态，而thread id 为67 的会话持有了表xcy的metadata 锁，所以是该会话造成了堵塞。 

我们把这个会话杀掉，就可以释放该锁。于是，执行kill 67 这个命令。

mysql> kill 67;

ERROR 1094 (HY000): Unknown thread id: 67

mysql> 

这个thread id 为67的会话不存在？见鬼了。命令输出中明明显示表xcy的metadata lock的持有者id (OWNER_THREAD_ID列) 为67. 为什么这个会话就不存在呢？

        下面将从源码的层面，来定位这个id为67的会话为什么不存在，以及它对应会话id到底是多少。

       通过阅读代码发现， performance_schema.metadata_locks表中的 OWNER_THREAD_ID 列，是内部ID ,代码中用m_thread_internal_id 变量表示，与show processlist 命令输出的id不是同一个id , show processlist 命令输出的是processlist_id, 代码中用m_processlist_id变量表示， 且kill 命令，只认processlist 命令输出的id. 

      m_thread_internal_id 的生成代码如下，在创建会话的时候生成。

通过thread_internal_id_counter.m_u64 自加1产生新的m_thread_internal_id.

 而m_processlist_id的值的产生，则由下面的代码生成

实际上是调用get_new_thread_id 函数生成。继续往下看：

可以看到，processlist id 通过thread_id_counter++ 自加得到。

这两个值是建立会话连接的时候产生的，而且都是递增的，同步加1， 更重要的，这两个计数器(thread_id_counter与thread_internal_id_counter.m_u64)只加不减，所以，对于同一个会话来说，这两值的差值应该是固定的。 

   现在回到刚才的示列中(见上面出现的图)，表performance_schema.metadata_locks 显示的处理pengding状态的，正在增加索引的线程号为68，它对应的process list 的id 为11， 中间的差值为68-11=57.  同样，我们认为：表xcy的metadata 锁的持有者OWNER_THREAD_ID为67, 而它对应的process id 应该为67-57 ,即为10.  我们认为，在show processlist 命令的输出中，thread_id 为10的会话，就是表xcy的持有者。  通过执行kill 10 命令，就可以该会话没有释放的元数据锁释放掉，从而能够正常为该表增加索引。  

     所以，通过查询表performance_schema.metadata_locks 得到的owner_thread_id, 经过减57换算，即可得到show processlist 命令输出中的id,  然后可以确定锁的持有者的会话ID . 进一步，通过kill命令，就可以将该会话持有的锁释放。

        当前8.0.20版本，这两个值的差值是57.  对于其他版本的数据库，可能差值会不太一样，但计算方式应该是通用的。请大家在自己的数据库版本上验证。 另外，5.7版本的数据库，performance_schema默认是不记录metadata lock信息的，需要开启相应的开关，metadata_locks表才会有相应的信息，否则为空。