Solución de errores en bases de datos causados por bloqueos de metadatos - ApsaraDB RDS

Bajo ciertas condiciones, un bloqueo de metadatos (MDL) puede impedir las operaciones posteriores sobre una tabla. Este tema describe cómo resolver este problema mediante DMS.

Contexto

MySQL 5.5 introdujo el bloqueo de metadatos (MDL) para garantizar la coherencia entre las operaciones del lenguaje de definición de datos (DDL) y del lenguaje de manipulación de datos (DML). Sin embargo, en algunos escenarios se producen bloqueos; por ejemplo, cuando se ejecuta una operación ALTER durante una operación DML o cuando se intenta ejecutar una operación ALTER mientras hay activa una consulta de larga duración.

Escenarios

Cree o elimine un índice.
Modifique la estructura de una tabla.
Ejecute operaciones de mantenimiento de tablas, como optimize table o repair table.
Elimine una tabla.
Obtenga un bloqueo de escritura de tabla.

Causas

Hay una consulta de larga duración activa en la tabla.
Una transacción iniciada de forma explícita o implícita no se confirmó ni se revirtió. Por ejemplo, una consulta termina, pero su transacción permanece abierta.
Existe una transacción con una consulta con errores en la tabla.

Pasos

Log on to an ApsaraDB RDS database using DMS.
En SQL Console, ejecute el comando show full processlist para consultar el estado de todos los hilos de la base de datos.
Compruebe si la columna State contiene una gran cantidad de valores Waiting for table metadata lock. Si aparece Waiting for table metadata lock, existe un bloqueo.
Busque el identificador de la sesión que causa el bloqueo.
1. Para una sesión con el estado Waiting for table metadata lock, revise la columna Info e identifique la tabla a la que intenta acceder, por ejemplo, sbtest2.
2. Revise la columna Info de las demás sesiones para encontrar la que opera sobre la tabla sbtest2. Anote el valor de Id de esa sesión.
  
  Nota
  Debe buscar la sesión que retiene activamente el bloqueo sobre la tabla, no las sesiones que lo esperan. Las sesiones en espera muestran el estado Waiting for table metadata lock. Analice las columnas State y Info para identificar la sesión que causa el bloqueo.
  
  Por ejemplo, si una sesión tiene el valor Waiting for table metadata lock en la columna State, el comando de su columna Info indica que necesita operar sobre la tabla sbtest2. Entre las demás sesiones que también requieren operar sobre la tabla sbtest2, la columna State de la sesión con Id igual a 267 muestra que esta última opera actualmente sobre la tabla sbtest2 y provoca el bloqueo.
  
  Nota
  El estado Sending data es solo un ejemplo. El estado real de una sesión que causa un bloqueo puede variar.
  También puede ejecutar la siguiente consulta para localizar transacciones de larga duración. Si el usuario que inició el comando que causa el bloqueo es distinto del usuario actual, inicie sesión con ese usuario para finalizar la sesión.
```
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', 'trunc'))) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  and i.trx_mysql_thread_id  not in (connection_id(),p.id);
```
Ejecute kill <session ID> (por ejemplo, kill 267) para finalizar la sesión y liberar el bloqueo de metadatos.

Recomendaciones

Ejecute operaciones como crear o eliminar índices fuera del horario de mayor demanda.
Habilite la confirmación automática (autocommit).
Establezca el parámetro lock_wait_timeout en un valor más bajo.

Considere usar un evento para finalizar automáticamente las transacciones de larga duración. Por ejemplo, el siguiente evento finaliza cualquier transacción que se haya ejecutado durante más de 60 minutos.

create event my_long_running_trx_monitor
on schedule every 60 minute
starts '2015-09-15 11:00:00'
on completion preserve enable do
begin
  declare v_sql varchar(500);
  declare no_more_long_running_trx integer default 0; 
  declare c_tid cursor for
    select concat ('kill ',trx_mysql_thread_id,';') 
    from information_schema.innodb_trx 
    where timestampdiff(minute,trx_started,now()) >= 60;
  declare continue handler for not found
    set no_more_long_running_trx=1;
  open c_tid;
  repeat
    fetch c_tid into v_sql;
 set @v_sql=v_sql;
 prepare stmt from @v_sql;
 execute stmt;
 deallocate prepare stmt;
  until no_more_long_running_trx end repeat;
  close c_tid;
end;