16.11.10 Comment gérer les blocages de verrous?

Les blocages de verrous sont un problème classique des bases de données transactionnelles, mais ils ne sont pas dangeureux, à moins qu'ils ne se répètent si souvent que vous ne puissiez pas exécuter tranquillement certaines transactions. Normalement, vous devriez écrire vos applications de manière à ce qu'elles soient prêtes à tenter à nouveau une transaction si la transaction est annulée pour cause de blocage.

InnoDB utilise un verrouillage de lignes automatique. Vous pouvez obtenir des blocages sur une ligne, même si votre transactions ne fait que modifier ou insérer une seule ligne. Cela est dû au fait que les opérations ne sont pas réellement 'atomiques' : elles posent automatiquement des verrous (éventuellement plusieurs) sur les lignes d'index de l'enregistrement concerné.

• Utilisez la commande SHOW INNODB STATUS avec MySQL version supérieure à 3.23.52 et 4.0.3, pour déterminer la cause du dernier blocage. Cela peut vous aider à optimiser votre application pour les éviter.
• Soyez toujours prêts à tenter une nouvelle fois une transaction si elle échoue à cause d'un blocage. Les verrous ne sont pas dangeureux. Essayez juste une autre fois.
• Validez souvent vos transactions. Les petites transactions sont moins sujettes aux blocages.
• Si vous utilisez des verrous en lectures avec SELECT ... FOR UPDATE ou ... LOCK IN SHARE MODE , essayez d'utiliser un niveau d'isolation plus bas comme READ COMMITTED .
• Accédez à vos tables et lignes dans un ordre fixé. Les transactions vont alors former des queues, et non pas des blocages.
• Ajoutez de bons index à vos tables. Vos requêtes devront scanner moins souvent les tables, et poseront donc moins de verrous. Utilisez EXPLAIN SELECT pour déterminer si MySQL choisit les bons index pour vos requêtes.
• Limitez votre utilisation des verrous. Si vous pouvez vous permettre de faire retourner à une commande SELECT des données un peu anciennes, n'ajoutez pas la clause FOR UPDATE ou LOCK IN SHARE MODE . Utiliser le niveau d'isolation READ COMMITTED est bon ici, car chaque lecture cohérente dans la même transaction lira avec des données aussi fraîches que possible à chaque fois.
• En dernier recours, vous pouvez forcer les verrous avec la commande :

  LOCK TABLES t1 WRITE, t2 READ, ...; [faire quelquechose avec les tables t1 et t2]; UNLOCK TABLES;

  Les verrous de niveau de table forcent les transactions à se mettre en ligne, et les blocages sont évités. Notez que LOCK TABLES démarre implicitement une transaction, tout comme BEGIN , et UNLOCK TABLES termine une transaction avec un COMMIT .
• Une dernière solution est de créer un sémaphore auxiliaire sous la forme d'une table avec une seule ligne. Chaque transaction modifie cette table avant d'accéder aux autres tables. Dans ce cas, toutes les transactions se font en ordre séquentiel. Notez que dans cette configuration, même l'algorithme InnoDB de détection des blocages fonctionne, car le sémaphore est un verrou de ligne. Avec les verrous de niveau de table de MySQL, nous devons nous résoudre à une méthode de délai d'expiration pour résoudre un verrou.