Análisis detallado de la solución del bloqueo USE DB en Mysql

Al encontrarse con un fallo, a menudo pensamos en cómo resolverlo, en lugar de reflexionar sobre la causa raíz del fallo. Este resultado solo nos da el pescado, pero perdemos la red. Hoy, compartiremos un caso de estudio de reflexión desencadenado por un fallo de bloqueo de USE DB.

Descripción del fallo

Hoy, un amigo se encontró con un fallo grave en la base de datos, el entorno de fallo es el siguiente:

MYSQL 5.6.16

RR隔离级别

GITD关闭

La manifestación es la siguiente:

use db no puede ingresar a la base de datos

show table status no puede consultar la información de la tabla

schema.processlist muestra una gran cantidad de Waiting for table metadata lock

En una situación de emergencia, mató a una gran cantidad de hilos pero no pudo recuperarse, finalmente mató a una transacción que no se había presentado a tiempo y恢复正常. Solo dejó una captura de pantalla como se muestra a continuación:

Extracción de información de fallo

Volvamos al gráfico anterior, podemos resumir el tipo de sentencias como sigue:

1、CREATE TABLE A AS SELECT B

Su STATE es sending data

2、DROP TABLE A

Su STATE es Waiting for table metadata lock

3、SELECT * FROM A

Su STATE es Waiting for table metadata lock

4、 SHOW TABLE STATUS[like 'A']

Su STATE es Waiting for table metadata lock

Análisis de información

No es fácil analizar este caso de estudio, ya que es una combinación de MDL LOCK en la capa MYSQL y el modo de lock innodb row lock, y debemos ser muy sensibles al STATE de schema.processlist.

Se recomienda leer primero los siguientes artículos míos para aprender sobre MDL LOCK:

https://es.oldtoolbag.com/article/131383.htm

Esta sección trata sobre la verificación del MDL LOCK utilizando dos métodos a continuación:

方式一：El autor agrega salida de registro en la función de bloqueo del código fuente de MDL LOCK, si se desea analizar el tipo de MDL LOCK de varias declaraciones, solo se puede usar este método, porque el bloqueo de MDL LOCK a menudo pasa rápidamente, y performance_schema.metadata_locks no puede observarlo.

方式二：En situación de bloqueo5.7Versión de performance_schema.metadata_locks para observar.

El método de apertura de monitoreo mdl en P_S es el siguiente:

Análisis sobre CREATE TABLE A AS SELECT B para B table sending data

Sobre el estado de sending data, realmente puede representar muchos significados, desde mi comprensión actual, es un nombre común utilizado por el nivel superior de MYSQL para este tipo de declaración SELECT en el momento de la interacción de datos entre el nivel INNODB y el nivel MYSQL, por lo que su aparición puede incluir:

Realmente necesita acceder a una gran cantidad de datos, es posible que necesite optimización.

Debido a que la obtención de row lock en la capa INNODB requiere esperar, como el SELECT FOR UPDATE común.

Al mismo tiempo, también debemos prestar atención a que en el modo RR, la forma de bloqueo de SELECT B es la misma que INSERT...SELECT, y no se repetirá.

Desde su reacción, porque él mató un evento de transacción no comprometida a largo plazo, por lo que debido a la situación2. Y debido a que algunas bases de datos en la tabla B están bloqueadas, la declaración CREATE TABLE A AS SELECT B no puede obtenerse, lo que hace que toda la declaración esté en estado de sending data.

Análisis sobre SHOW TABLE STATUS[like 'A'] Waiting for table metadata lock

Este es el punto más importante de este caso, SHOW TABLE STATUS[like 'A'] resulta estar bloqueado, su STATE es Waiting for table metadata lock, y preste atención, aquí es table porque el tipo de MDL LOCK se divide en muchos. En el artículo sobre MDL que mencioné, mencioné que cuando se describe una tabla, se aplica MDL_SHARED_HIGH_PRIO(SH), y también en el momento de SHOW TABLE STATUS se aplica MDL_SHARED_HIGH_PRIO(SH) a esta tabla.

方式一

方式二

Ambas formas pueden observar la existencia de MDL_SHARED_HIGH_PRIO(SH), y yo simulo una situación de bloqueo.

Pero MDL_SHARED_HIGH_PRIO(SH) es un tipo de MDL LOCK con una prioridad muy alta, que se manifiesta de la siguiente manera:

Compatibilidad:

Prioridad de la cola de bloqueo:

Las condiciones para que se bloquee, además de que se bloquee por MDL_EXCLUSIVE(X), no hay otra posibilidad. Por lo tanto, esto es un punto de partida muy importante.

Tres, análisis sobre CREATE TABLE A AS SELECT B sobre el candado MDL de la tabla A

Este es algo que no sabía antes, y es el lugar que más tiempo me llevó en este caso, como se analizó anteriormente, para que una declaración como SHOW TABLE STATUS[like 'A'] que solo pondrá MDL_SHARED_HIGH_PRIO(SH) MDL LOCK se bloquee en MDL LOCK, solo hay una posibilidad, es que la tabla A tenga MDL_EXCLUSIVE(X).

Entonces, comencé a sospechar que esta declaración DDL pondría MDL_EXCLUSIVE(X) en la tabla A antes de que la declaración terminara, y luego hice una prueba real, como se esperaba, fue así:

方式一

方式二

Es un poco lamentable que en performance_schema.metadata_locks no se muestre MDL_EXCLUSIVE(X), y se muestre como MDL_SHARED(S) podemos verlo en los registros de salida que hice aquí se realizó una operación de actualización, MDL_SHARED(S) se actualizó a MDL_EXCLUSIVE(X). Y según la lista de compatibilidad anterior, solo MDL_EXCLUSIVE(X) bloqueará MDL_SHARED_HIGH_PRIO(SH). Por lo tanto, deberíamos poder confirmar que realmente se realizó la operación de actualización, de lo contrario, SHOW TABLE STATUS[like 'A'] no estaría bloqueado.

Cuatro, análisis sobre SELECT * FROM A Waiting for table metadata lock analysis

Tal vez algunos crean que SELECT no pondrá candados, pero eso es en el nivel innodb, en MYSQL se pondrá MDL_SHARED_READ(SR) como sigue:

方式一

方式二

Se puede ver que realmente existe MDL_SHARED_READ(SR), que está en estado de bloqueo en la actualidad

La compatibilidad es la siguiente:

Obviamente, MDL_SHARED_READ(SR) y MDL_SHARED_HIGH_PRIO(SH) son incompatibles y necesitan esperar.

Cinco, análisis sobre DROP TABLE A Waiting for table metadata lock

Este punto es fácil de analizar porque la tabla A tiene un candado X y DROP TABLE A necesariamente pondrá el candado MDL_EXCLUSIVE(X), que por supuesto es incompatibles. Como sigue:

方式一

方式二

Donde EXCLUSIVE es lo que llamamos MDL_EXCLUSIVE(X), realmente existe y está bloqueado en la actualidad

六、为何use db也会阻塞？

如果使用mysql客户端不使用-A选项(或者 no-auto-rehash)在USE DB的时候至少要做如下事情：

1、 对db下每个表上MDL (SH) lock如下（调用MDL_context::acquire_lock 这里给出阻塞时候的信息）

方式一

方式二

可以看到USE DB确实也因为MDL_SHARED_HIGH_PRIO(SH) 发生了阻塞。

2、对每个表加入到table cache，并且打开表(调用open_table_from_share())

那么这种情况就和SHOW TABLE STATUS[like 'A']被阻塞的情况一模一样了，也是由于MDL 锁不兼容造成的。

分析梳理

有了前面的分析那么我们可以梳理这个故障发生的原因如下：

有一个在B表上长期未提交的DML
语句会在innodb层对B表某些数据加innodb row lock。

由步骤1引起了CREATE TABLE A AS SELECT B的阻塞
因为RR模式下SELECT B必然对B表上满足的数据上锁，因为步骤1已经加锁所以触发等待，STATE为sending data。

由步骤2引起了其他语句的阻塞
因为CREATE TABLE A AS SELECT B在A表建立完成之前会上MDL_EXCLUSIVE(X),这把锁会阻塞其他所有关于A表的语句，包括DESC/SHOW TABLE STATUS/USE DB(非-A) 这种只上MDL_SHARED_HIGH_PRIO(SH)MDL LOCK的语句。STATE统一为Waiting for table metadata lock。

模拟测试

测试环境：

5.7.14

GITD关闭

RR隔离级别

使用脚本：

步骤如下：

session1 session2 session3 session4------use test;---use test;begin; delete from b;------------use test;create table a asselect * from b;(由于b表innodb row lock堵塞)------------show table status like 'a';(由于a表MDL LOCK堵塞)------------use test(由于a表MDL LOCK堵塞)

最终我们看到的等待状态如下：

De esta manera, hemos simulado perfectamente el estado en línea, si matamos la sesión1de las cosas, naturalmente, se desbloquearán todas, volvamos a ver la salida de performance_schema.metadata_locks:

Podemos ver la salida como se muestra anteriormente, pero debemos prestar atención a que LOCK_TYPE: SHARED no puede bloquear LOCK_TYPE: SHARED_HIGH_PRIO (puede referirse al apéndice o a mis artículos anteriores de análisis de MDL LOCK). Como se analizó anteriormente, aquí se realizó una operación de nivel superior, que se actualizó a MDL_EXCLUSIVE(X).

Resumen

En el modo RC, aunque la tabla B en CREATE TABLE A SELECT B no adquirirá ningún LOCK de ROW INNODB, si la tabla B es muy grande, la tabla A también estará bajo la protección de MDL_EXCLUSIVE(X), por lo que también se desencadenará la situación de espera de USE DB\SHOW TABLE STATUS.

Si se activa GTID, no se puede usar la sentencia CREATE TABLE A SELECT B.

Para DML/El sistema que mezcla DDL debe prestar atención a la concurrencia, como en el ejemplo, si se nota la situación bajo alta concurrencia, se pueden encontrar formas de evitarla.

Este caso de estudio Again ilustra que las cosas no presentadas a tiempo pueden desencadenar desastres, por lo que se recomienda monitorear las transacciones que no finalicen después de N segundos.

Apéndice

TIPO DE LOCK MDL

Matriz de Compatibilidad

Matriz de Prioridad de Cola de Espera

Declaración: El contenido de este artículo se obtiene de Internet, pertenece al propietario original, el contenido se contribuye y carga de manera autónoma por los usuarios de Internet, este sitio no posee los derechos de propiedad, no ha sido editado por humanos y no asume ninguna responsabilidad legal. Si encuentra contenido sospechoso de copyright, le invitamos a enviar un correo electrónico a: notice#oldtoolbag.com (al enviar un correo electrónico, reemplace # con @) para denunciar, y proporcione evidencia relevante. Una vez confirmado, este sitio eliminará inmediatamente el contenido sospechoso de infracción.