ALTERACIONES, claves foráneas y bloqueos de metadatos, ¡oh!

Estoy seguro de que lo ha visto: inicie un ALTER y tenga el temido «esperando el bloque de metadatos». En muchos casos, esto se espera si está trabajando activamente en la mesa. Sin embargo, recientemente tuve un caso con un cliente en el que la tabla que se modificó rara vez se actualizaba y era muy pequeña (<100 filas). El ALTER solo se configura durante horas durante una prueba de carga (más sobre esto recientemente) y nunca finaliza hasta que se detiene la prueba de carga. Después del final de la prueba de carga, ALTER terminó en menos de un segundo como se esperaba. Entonces, ¿qué estaba pasando aquí?

Comprobar las claves foráneas

Mi primer instinto, cada vez que hay una cerradura extraña, es buscar claves externas. Por supuesto, esta mesa tenía algunos FK que se refieren a una mesa mucho más ocupada. Sin embargo, este comportamiento todavía parecía bastante extraño. Al realizar un ALTER en una tabla, se solicita un bloque de metadatos SHARED_UPGRADEABLE en la tabla secundaria. También hay un bloque de metadatos SHARED_READ_ONLY contra el padre y ahí es donde las cosas pueden complicarse.

Echemos un vistazo a cómo se compran los CDM para la documentación (https://dev.mysql.com/doc/refman/en/metadata-locking.html:

Si hay varios servidores para un bloqueo determinado, el La demanda de bloqueo de mayor prioridad se cumple primero, con una excepción relacionada con la variable de sistema max_write_lock_count. Las solicitudes de bloqueo de escritura tienen mayor prioridad que las solicitudes de bloqueo de lectura.

Es importante tener en cuenta que la orden de bloqueo se serializa:

Las declaraciones adquieren los bloqueos de metadatos uno por uno, no simultáneamente, y realizan la detección de bloqueo en el proceso.

Normalmente, cuando pensamos en una fila, pensamos en un proceso FIFO. Si emite las siguientes tres declaraciones (en este orden), se completan en este orden:

  1. INSERTAR EN EL PADRE…
  2. ALTER TABLE bebé…
  3. INSERTAR EN EL PADRE…

Sin embargo, como la instrucción ALTER secundaria le pide a un leer bloquear contra el padre, las dos inserciones lo completarán PRIMERO para ALTERAR a pesar de la orden. Aquí hay un ejemplo de escenario en el que esto se puede demostrar:

Configuración de tablas y población inicial:

Sesión 1:

Sesión 2:

Sesión 3:

En este punto, la sesión 1 tiene una transacción abierta y está durmiendo con un bloque de metadatos de escritura otorgados al padre. La sesión 2 tiene un bloqueo actualizable (escritura) otorgado al niño y espera un bloqueo de lectura en los padres. Finalmente, la Sesión 3 tiene un bloque escrito contra los padres:

Tenga en cuenta que la única sesión con un estado de bloqueo pendiente es la sesión 2 (ALTER). La sesión 1 y la sesión 3 (emitidas antes y después de ALTER respectivamente) recibieron bloques de escritura. Donde se rompe el orden es cuando el compromiso pasa a la sesión 1. Al pensar en una fila ordenada, uno esperaría que la sesión 2 adquiriera el bloqueo y las cosas siguieran adelante. Sin embargo, debido a la naturaleza prioritaria del sistema de bloqueo de metadatos, la sesión 2 todavía está esperando y ahora la sesión 3 tiene el bloqueo.

Si otra sesión de escritura ingresa e inicia una nueva transacción y adquiere un bloqueo de escritura en la tabla principal, incluso cuando se completa la sesión 3, ALTER seguirá bloqueado. Puedes ver a dónde va esto…

Cada vez que tengo una transacción activa que tiene un MDL en la tabla principal abierta, el ALTER en la tabla secundaria nunca se completa. Al hacer esto, dado que el bloqueo de escritura en el escritorio del niño se completó con éxito (pero la declaración completa está esperando para adquirir el bloqueo de lectura de los padres), todas las solicitudes de lectura se ingresan en el escritorio del niño. ¡El bebé será bloqueado!

Además, piense en cómo trataría normalmente de resolver una declaración que no completó. Mire las transacciones (tanto en la lista de procesos como en el estado de InnoDB) que han estado abiertas durante más tiempo. Pero como el hilo está bloqueado, ahora está más joven que el hilo ALTER, la transacción/hilo más antiguo que ve es ALTER. Tira tu cabello en una fila !!

Esto es exactamente lo que sucede en este escenario. En preparación para un lanzamiento, nuestro cliente estaba realizando sus declaraciones ALTER junto con una prueba de carga (¡una muy buena práctica!) para garantizar un lanzamiento sin problemas. El problema era que la prueba de carga mantenía abierta una transacción de escritura activa en la tabla principal. Eso no quiere decir que siguiera escribiendo, sino que había varios hilos y uno era SIEMPRE activo. Esto evitó que ALTER completara y bloqueara las solicitudes de lectura que seguían a la tabla secundaria relativamente estática.

Afortunadamente, hay una solución a este problema (algunos eliminan FK del esquema). Una variable max_write_lock_count (https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_write_lock_count) se puede usar para permitir que se concedan bloques de lectura antes que bloques de escritura después de una sucesión de bloques de escritura consecutivos. De forma predeterminada, esta variable se establece en 18 446 744 073 709 551 615. Entonces solo tienes que esperar 18 quintillones anote las preguntas a completar antes de que se conceda la lectura. Para ponerlo en perspectiva, si emites 10.000 scripts/seg contra esa tabla, tu lectura sería bloqueada por 58 millones de años.

Para evitar que esto suceda, puede reducir max_write_lock_count a un número pequeño (¿digamos 10?) Y después de que se adquieran 10 bloqueos de escritura, el subsistema MDL buscará bloqueos de lectura pendientes, otorgará uno y luego volverá a escribir. ¡Problema resuelto!

Al ser una variable dinámica, esto se puede configurar en tiempo de ejecución para permitir que se complete el ALTER en espera. En general, esto es más un punto de inflexión, ya que suele haber poco tiempo entre escribir en una mesa y leer los bloqueos que se van a obtener. Sin embargo, si su caso de uso mantiene en ejecución sesiones simultáneas que SIEMPRE tendrán una transacción en una tabla a la que se hace referencia como FK, es posible que vea crecer esta situación. Afortunadamente, la corrección es simple y se puede hacer sobre la marcha.

NOTA: esta solución de problemas ha sido posible gracias al esquema de rendimiento y al activar la tabla metadata_locks como se describe aquí: https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html

Author: Ing. Luis

A lo largo de conocer Windows y otros sistemas operativos me eh encontrado con diversos tipos de error, ahora brindo soluciones según mi experiencia-

Deja un comentario