Causas y soluciones para el bloqueo de metadatos de la tabla de optimización MySQL

En los requisitos de un determinado proyecto, es necesario agregar campos a una tabla. Sin embargo, al ejecutar la instrucción SQL para agregar campos, se bloqueó durante mucho tiempo y no se envió a Mysql para su finalización. La solicitud de servicio de la interfaz externa también se bloqueó y luego se interrumpió. Después de la declaración de modificación de la tabla bloqueada, el servicio volvió lentamente a la normalidad. Si no ha descubierto la causa raíz del problema, no agregue campos precipitadamente, ya que esto lo hará. afecta directamente al servicio

Puede ver la tabla de modificación mostrando la lista de procesos. La declaración se atascó durante la ejecución y se acumuló una gran cantidad de estados para el estado de bloqueo de metadatos de la tabla y luego el estado de bloqueo de metadatos de la tabla.

Al mostrar la lista de procesos, podemos ver que la instrucción de modificación de la tabla está bloqueada durante la ejecución y se ha acumulado una gran cantidad de registros con el estado Esperando bloqueo de metadatos de la tabla.

Luego verifique el estado actual de la transacción, ejecute seleccione * de information_schema.innodb_trx\G

Descubrí que una de las transacciones se ha estado ejecutando durante mucho tiempo y sospecho que está relacionada con esta ejecución prolongada. transacción no comprometida.

Probado usando múltiples terminales en mysql local

Sesión 1: transacción abierta, instrucción de selección ejecutada, pero no confirmada

Sesión 2: Agregar campo sql ha sido ejecutado

Ejecución bloqueada

Puede ver que el estado de la declaración de modificación de la tabla es Esperando bloqueo de metadatos de la tabla

Sesión 3: Consultar la tabla t1 nuevamente

>

También bloqueado

Seleccionar * de t1 Consultar la tabla t1 nuevamente también está Esperando el bloqueo de metadatos de la tabla, lo que indica que se bloqueará debido a la existencia de un bloqueo de metadatos. La existencia del bloqueo de metadatos hará que las consultas normales se bloqueen debido a la espera del bloqueo

Luego verifique el estado actual de la transacción:

Puede ver que la transacción de la sesión1 no ha aún no se ha confirmado, por lo que puede ver que su estado aún es En ejecución

En este momento, enviamos la transacción de la sesión1 y vemos el efecto

Podemos ver el impacto de la transacción sobre el estado de la sesión1

y ver el efecto

p>

Podemos ver los efectos de las transacciones.

Sesión 1:

Sesión 2:

Sesión 3:

Se puede observar que luego de confirmada la transacción de la sesión 1 , la sesión Tanto la sesión 2 como la sesión 3 se ejecutaron normalmente, con tiempos de finalización de 30 segundos y 7 segundos respectivamente

A través de la prueba de restauración anterior, podemos ver que debido a que la transacción no se confirmó, la tabla se bloqueado, lo que hace que la declaración de modificación posterior espere Bloqueo y bloqueo, lo que afecta las solicitudes normales posteriores.

¿Significa esto que nuestro proyecto tiene las transacciones activadas de forma predeterminada?

Continuando con la solución de problemas, este proyecto utiliza el complemento flask-sqlchemy para administrar el acceso a mysql y luego verificó la documentación.

Al crear una instancia de sqlchemy, crea un objeto de sesión para comunicarse con Mysql interacción, consulte el código fuente

Se puede ver en la definición de la clase SignallingSession que autocommit=False indica que las transacciones están habilitadas para todas las ejecuciones de SQL de forma predeterminada, es decir, incluso para declaraciones de selección pura. Tampoco es necesario bloquear la selección, nuestro proyecto requiere que las transacciones estén activadas de forma predeterminada, lo cual no es necesario para el control de versiones de Mysql MVCC.

Solución: al crear una instancia de SQLAlchemy, proporcione un parámetro para modificar el autocommit=True de la sesión:

Del sitio web oficial:

Esto significa, para garantizar Durante la ejecución en serie de transacciones, se habilita un bloqueo. Este bloqueo solo se liberará al final de la transacción, por lo que

MySQL introdujo este bloqueo de metadatos después de la versión 5.5.3 para evitar que existiera un error antes. versión 5.5.3. Introducido después de 5.3 para evitar un error antes de 5.5.3:

Cuando una sesión estaba realizando una operación DML en el repositorio principal que aún no se había confirmado, otra sesión estaba realizando una operación DDL en el mismo objeto ( como la operación ddl). Cuando una sesión realiza una operación DML (aún no confirmada) en el repositorio principal, otra sesión realiza una operación DDL (como eliminar tabla) en el mismo objeto. Dado que el binlog de MySQL se basa en el orden de envío de las transacciones, cuando se aplica al. repositorio esclavo Cuando, Q primero descarta la tabla y luego la INSERTA en la tabla, provocando un error en la aplicación desde el repositorio.