Comment traiter l’interruption d’un traitement en raison d’un « Dead Lock » dans une base MySQL ?
Comme dans Oracle, lorsque 2 transactions entrent en conflit de verrouillages croisés sur deux ressources, le phénomène d’attente mutuelle ou « Dead lock » aboutit à l’interruption de l’une des deux transactions afin de débloquer la situation.
Lorsque le phénomène se reproduit, il est important de savoir retrouver quelle est l’application qui provoque ce blocage pour solutionner le problème de fond lorsque l’ordonnancement des mises à jour n’est pas possible.
Se connecter à la base de données en tant que « root »
mysql -u root -p
mysql@xxxxxx:~> mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2125055 Server version: 5.6.26-74.0-log Percona Server (GPL), Release 74.0, Revision 32f8dfd Copyright (c) 2009-2015 Percona LLC and/or its affiliates Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Rechercher l’emplacement des fichiers « log »
select @@datadir;
mysql> select @@datadir; +----------------------+ | @@datadir | +----------------------+ | /var/lib/mysql/data/ | +----------------------+ 1 row in set (0.00 sec)
Recherche de tous les paramètres liés aux traces
mysql> show global variables like '%log%';
+-------------------------------------------+------------------------------------------+ | Variable_name | Value | +-------------------------------------------+------------------------------------------+ [...] | log_error | /var/log/mysqld.log | [...] | sql_log_off | OFF | | sync_binlog | 1 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | +-------------------------------------------+------------------------------------------+ 81 rows in set (0.00 sec)
Recherche des instructions incriminées dans le « Dead lock »
Utiliser le fichier indiqué sur la ligne « log_error » du tableau ci-dessus.
vi /var/log/mysqld.log
2016-04-20 06:42:42 7fd0d0bae700InnoDB: transactions deadlock detected, dumping detailed information. 2016-04-20 06:42:42 7fd0d0bae700 *** (1) TRANSACTION: TRANSACTION 17066082041, ACTIVE 0 sec fetching rows mysql tables in use 6, locked 6 LOCK WAIT 1043 lock struct(s), heap size 95784, 15 row lock(s), undo log entries 8 MySQL thread id 2103907, OS thread handle 0x7fd0d071c700, query id 12083275709 10.33.128.31 uuuuuuu updating delete from xxxxxxxxxx where xxxxxxxx_id = yyyyyyyyyy_id *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 715496 page no 2182 n bits 80 index `PK1` of table `XXXXXTAB`.`XXXXXXCOL` trx id 17066082041 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 17066082054, ACTIVE 0 sec fetching rows mysql tables in use 6, locked 6 1042 lock struct(s), heap size 95784, 14 row lock(s), undo log entries 8 MySQL thread id 2103908, OS thread handle 0x7fd0d0bae700, query id 12083275745 10.33.128.31 mgr_salesportal updating delete from xxxxxxxxxx where xxxxxxxxxx_id = yyyyyyyyyyy_id *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 715496 page no 2182 n bits 80 index `PK1` of table `XXXXXTAB`.`XXXXXXCOL` trx id 17066082054 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED:<br>RECORD LOCKS space id 715496 page no 2182 n bits 80 index `PK1` of table `XXXXXTAB`.`XXXXXXCOL` trx id 17066082054 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)
