Quelle est une bonne pratique pour les requêtes de mise à jour de gros volumes de données afin d'éviter les délais d'attente de verrouillage ?

Question

Donc en gros, j'ai actuellement cette requête :

UPDATE act AS a
INNER JOIN blok AS b
ON b.fav_pat = a.pat_id
SET a.blok_id = b.id

Actuellement expiré en raison de la grande quantité de données dont je dispose. Existe-t-il un moyen d'éviter les délais d'attente sans modifier la configuration de la base de données ?

Answer 1

Le package flyway que vous utilisez fonctionne mieux pour permettre la restauration complète de toute opération incomplète en utilisant la sémantique de transaction du SGBDR hôte. Cela signifie qu'il est conçu pour effectuer des opérations de mise à jour comme celle que vous nous avez montrée dans une seule transaction conforme à ACID.

Si les tables impliquées sont volumineuses (des millions de lignes ou plus), la transaction peut être très volumineuse. Ils peuvent faire planter votre serveur MySQL, déversant les journaux de transactions sur le disque ou le SSD. La validation de ces journaux de transactions peut prendre beaucoup de temps. Vous n'avez pas mentionné le nombre de lignes, mais si le nombre de lignes est important, Flyway n'est peut-être pas le bon outil pour le travail.

Votre délai d'expiration de verrouillage implique que vous effectuez cette opération sur une base de données qui a une autre activité simultanée. Vous souhaiterez peut-être le faire sur une base de données silencieuse pour de meilleurs résultats.

Vous pouvez augmenter le délai d'attente du verrouillage en procédant ainsi.

show variables like 'innodb_lock_wait_timeout'; -- previous vale
SET GLOBAL innodb_lock_wait_timeout = 300; -- five min

Ensuite, réessayez peut-être avant le lever du soleil pendant les vacances ou à un autre moment calme. Plus d'informations ici.

Une fois votre tâche Flyway terminée, envisagez de restaurer le délai d'expiration du verrouillage à sa valeur précédente.

Vous pouvez également envisager des mises à jour par lots, telles que la mise à jour de 1 000 lignes à la fois. Mais la voie de migration ne semble pas prendre en charge cela. Si vous suivez cette voie, vous pouvez poser une autre question.