Libérer les verrous de métadonnées avec DMS - ApsaraDB RDS

Dans certaines conditions, un verrou de métadonnées (MDL) peut bloquer les opérations ultérieures sur une table. Cette rubrique explique comment résoudre ce problème à l'aide de DMS.

Contexte

MySQL 5.5 a introduit le verrou de métadonnées (MDL) afin de garantir la cohérence entre les opérations DDL (Data Definition Language) et DML (Data Manipulation Language). Toutefois, des blocages peuvent survenir dans certains scénarios, par exemple lorsqu'une opération ALTER est exécutée pendant une opération DML, ou lorsqu'une tentative d'opération ALTER intervient alors qu'une requête longue est en cours d'exécution.

Scénarios

Création ou suppression d'un index.
Modification de la structure d'une table.
Maintenance de table, telle que optimize table ou repair table.
Suppression d'une table.
Acquisition d'un verrou en écriture au niveau de la table.

Causes

Une requête longue est active sur la table.
Une transaction démarrée explicitement ou implicitement n'a pas été validée ni annulée. Par exemple, une requête se termine mais sa transaction reste ouverte.
Une transaction incluant une requête ayant échoué existe sur la table.

Étapes

Se connecter à une base de données ApsaraDB RDS à l'aide de DMS.
Dans la SQL Console, exécutez la commande show full processlist pour consulter l'état de tous les threads de la base de données.
Vérifiez si la colonne State contient un grand nombre de valeurs Waiting for table metadata lock. Si l'état Waiting for table metadata lock apparaît, un blocage est en cours.
Identifiez l'ID de session de la session qui bloque l'accès.
1. Pour une session dont l'état est Waiting for table metadata lock, examinez la colonne Info afin d'identifier la table à laquelle elle tente d'accéder, par exemple sbtest2.
2. Parcourez la colonne Info des autres sessions pour trouver celle qui opère sur la table sbtest2. Notez l'Id de cette session.
  
  Remarque
  Vous devez identifier la session qui détient activement le verrou sur la table, et non les sessions en attente du verrou. Les sessions en attente affichent l'état Waiting for table metadata lock. Vous pouvez identifier la session bloquante en analysant les colonnes State et Info.
  
  Par exemple, pour une session dont l'State est Waiting for table metadata lock, vous pouvez déterminer, à partir de la commande présente dans sa colonne Info, que cette session doit opérer sur la table sbtest2. Parmi les autres sessions qui doivent également opérer sur la table sbtest2, vous constatez, en examinant la colonne State de la session dont l'Id est 267, qu'elle est actuellement en train d'opérer sur la table sbtest2 et provoque le blocage.
  
  Remarque
  L'état Sending data est donné à titre d'exemple. L'état réel d'une session bloquante peut varier.
  Exécutez également la requête suivante pour identifier les transactions longues. Si l'utilisateur qui a initié l'instruction de blocage diffère de l'utilisateur actuel, connectez-vous en tant que cet utilisateur pour terminer la session.
```
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);
```
Exécutez kill <session ID> (par exemple, kill 267) pour terminer la session et libérer le verrou de métadonnées.

Recommandations

Effectuez les opérations telles que la création ou la suppression d'un index pendant les heures creuses.
Activez l'autocommit.
Définissez le paramètre lock_wait_timeout sur une valeur plus faible.

Envisagez d'utiliser un événement pour terminer automatiquement les transactions longues. Par exemple, l'événement suivant termine toute transaction en cours d'exécution depuis plus de 60 minutes.

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;