Gestion de verrou MySQL

Formation

En Ligne

Prix sur demande

Appeler le centre

Avez-vous besoin d'un coach de formation?

Il vous aidera à comparer différents cours et à trouver la solution la plus abordable.

Description

  • Typologie

    Formation

  • Méthodologie

    En ligne

Grâce à cette formation vous pourrez acquérir les connaissances nécessaires qui vous permettrons d’ajouter des compétences à votre profil et obtenir de solides aptitude qui vous offriront de nombreuses opportunités professionnelles.

Questions / Réponses

Ajoutez votre question

Nos conseillers et autres utilisateurs pourront vous répondre

À qui souhaitez-vous addresser votre question?

Saisissez vos coordonnées pour recevoir une réponse

Nous ne publierons que votre nom et votre question

Les Avis

Les matières

  • MySQL
  • Gestion

Le programme

Introduction du cours

Les bases de données permettent de stocker des informations dans des tables. Dans un système où tous les utilisateurs se connectent en lecture simple des informations et où au maximum une seule personne modifie les données à la fois, il n'y a aucun problème. Mais dans un système où plusieurs utilisateurs peuvent modifier des données sur la même ligne de la base de données en même temps, il va y avoir une collision et une incohérence des données mémorisées.

Cette image présente le cas où deux utilisateurs souhaitent modifier 2 informations différentes sur une même ligne de la base de données, celle qui contient les coordonnées de M. Dupond.
Pour résoudre ce conflit d'accès nous allons utiliser une technique de verrou.

Le principe du verrou

Le verrou est une information associée à une donnée ou un groupe de donnée qui bloque l'accès aux données verrouillées aux autres utilisateurs. Lorsqu'un utilisateur souhaite accéder à une donnée verrouillée :

  • Soit il reçoit un message d'erreur lui indiquant que la donnée est verrouillée et qu'il faut donc repasser plus tard.

  • Soit il est mis dans une file d'attente, et aura accès aux données une fois que l'utilisateur qui a posé le verrou, l'aura levé.

Ces verrous sont principalement de trois types :

  • Ils peuvent empêcher tout accès aux données aux autres utilisateurs.

  • Ils peuvent laisser uniquement un accès en lecture seule aux autres utilisateurs.

  • Le dernier utilisateur qui accède aux données en lecture pour modification prend le verrou. Quand il enregistre, il vérifie qu'il a encore le verrou. Ce type de verrouillage ne me semble pas très ergonomique pour l'utilisateur, donc nous n'allons pas l'utiliser dans ce tuto.

Le verrou "courte durée", verrou natif MySQL

MySQL possède de manière native des verrous. Ces verrous sont associés à des connexions à la base de données. Ainsi deux scripts PHP utilisant les mêmes identifiants de connexion, sont des connexions différentes. (Chaque script à sa connexion à la base de données). Ces verrous sont de deux types :

  • Les verrous READ permettent aux autres connexions d'accéder aux données verrouillées en lecture seule.

  • Les verrous WRITE empêchent tout accès aux autres connexions

Pour la suite du tuto nous allons utiliser uniquement des verrous WRITE, afin de s'assurer qu'un autre script ne vienne pas prendre des informations susceptibles d'êtres en cours de modification.

Les verrous sont automatiquement levés dès que la connexion à la base MySQL est fermée, ou dès que des verrous sont posés sur une autre table de la base par la même connexion.

Lorsqu'une connexion veut accéder à une donnée verrouillée par une autre connexion, MySQL met la requête en attente. Elle sera exécutée dès que le verrou sera levé.

Exemple pour poser un verrou WRITE sur une table MaTable

LOCK TABLE MaTable WRITE;

Quand une connexion pose un verrou sur une ou plusieurs tables, elle ne peut accéder qu'aux tables verrouillées avec les noms déclarés lors du verrou. Si cette contrainte n'est pas respectée, MySQL génère une erreur.

Exemple pour poser un verrou WRITE sur une table MaTable qui sera accédée dans les requêtes avec l'alias mt

LOCK TABLE MaTable AS mt WRITE;

Si dans les requêtes SQL, on accède à plusieurs tables ou à une même table avec plusieurs alias, il faut les verrouiller toutes en même temps.
Exemple pour poser un verrou WRITE sur une table MaTable qui sera accédés dans les requêtes avec son nom d'origine et l'alias mt, ainsi que la table AutreTable

LOCK TABLE MaTable WRITE, MaTable AS MT WRITE, AutreTable WRITE;

Pour aller plus loin sur la syntaxe de ses verrous, je vous invite à lire la documentation officielle de MySQL.

Je commence à comprendre comment on met des verrous. Mais pour les supprimer, sans fermer la connexion SQL ni poser un autre verrou, je fais comment ?

C'est simple il suffit d'utiliser la bonne requête :-° .
Elle supprime tous les verrous éventuels de la connexion courante. Il est possible de l'exécuter qu'il y ait 1, 10, ou 0 verrous...

UNLOCK TABLES; Le verrou "longue durée", verrou programmé

Nous avons vu les possibilités natives de MySQL. Cependant un verrou natif ne permet pas de verrouiller l'accès aux données plus longtemps que le temps de génération d'une page. Nous devons créer un verrou programmé qui permet de verrouiller un enregistrement pour une durée suffisante pour qu'un utilisateur puisse effectuer une saisie de formulaire.

Le temps de génération d'une page est de quelques millisecondes généralement, alors que le temps d'édition du formulaire est de quelques dizaines de secondes à quelques minutes. Nous n'allons pas bloquer l'accès complet à la table pendant tout ce temps, mais uniquement à la ligne concernées.

Comment créer ce verrou ?

Pour créer ce verrou nous ajoutons d'une part deux colonnes à chaque table de la base de données pouvant comporter des verrous et d'autre part une constante en PHP.
Voici la description de ces ajouts :

  • verrou_id : Champ en base de données qui contient l'identifiant de celui qui pose le verrou

  • verrou_heure : Champ en base de données qui contient l'heure du dépôt du verrou

  • verrou_duree : Constante PHP qui contient la durée de vie du verrou

Quelques explications supplémentaires sur les choix techniques :

  • Le champ verrou_id contient l'identifiant de la session PHP utilisée par le script posant le verrou. Ceci a l'avantage de permettre d'utiliser les verrous sur un site où aucune authentification n'est demandée, ou sur un site ou plusieurs utilisateurs possèdent les mêmes identifiants. De même nous évitons les problèmes posés par un utilisateur qui ouvre plusieurs connexions depuis différents poste avec le même login. Par contre l'utilisation de l'identifiant de session demande d'avoir une durée de vie du verrou plus courte que la durée de la session, et d'avoir un hébergeur supportant cette fonctionnalité. Quand aucun verrou n'est posé sur l'enregistrement, ce champ est à la valeur système NULL. Ce sera un CHAR32 car un identifiant de session est composé de 32 caractères alphanumériques.

  • Le champ verrou_heure contient l'instant où le verrou est posé. Ceci implique que tous les verrous accédant à la table aient la même durée de vie, puisque pour savoir si un verrou est encore valide, il va être comparé l'heure actuelle avec l'heure de création du verrou à laquelle on ajoute la durée de vie du verrou. Ce champ sera un TIMESTAMP.

  • La constante verrou_duree contient la durée de vie du verrou. Ceci assure que tout les verrous accédant à la table ait la même durée de vie, mais contraint en plus que tous les verrous de l'application aient la même durée de vie. Elle sera de type entier, et contiendra le nombre de minute de cette durée de vie.

OK, je commence à voir le concept. Mais pourquoi tu mets une durée de vie alors que le verrou MySQL n'en a pas ?

Imagine qu'il y ait un "plantage" :

  • Si c'est le script PHP qui plante, la connexion sera automatiquement fermée et le verrou MySQL sera levé.

  • Si c'est le moteur PHP, Apache, ou MySQL qui plante, les connexions seront automatiquement fermés lors du redémarrage du (des) module(s) adapté(s) et le verrou MySQL sera levé.

  • Si c'est l'utilisateur qui défaille (ou son ordinateur), Partis boire un café, happé par un collègue, ... Il faut aussi un mécanisme qui permette de faire sauter le verrou programmé, sans appeler un administrateur système. Il faut bien être conscient qu'en informatique la grande majorité des problèmes se situent "entre la chaise et le clavier". Un verrou "obsolète" ou "périmé" pourra donc être supprimé par n'importe quel utilisateur qui accède à la table.

L'articulation des verrous Natifs et programmésLe principe

Nous allons utiliser les verrous natifs de type WRITE, pour bloquer l'accès aux données à tous les autres utilisateurs pendant la durée de manipulation des verrous programmés.

Afin de faciliter les exemples et d'avoir un code compatible avec un maximum de moteurs MySQL nous allons utiliser des verrous sur les tables complètes pour les verrous natifs. Nous partons du principe qu'en fonction de l'utilisation de notre site internet bloquer une table complète pendant la durée d'exécution d'un script PHP (quelques millisecondes à quelques secondes) n'est pas critique.

Voici le scénario de l'exemple que nous allons utiliser pour la suite : Une table "test" comporte des enregistrements accessibles en écriture aux utilisateurs avec un formulaire d'édition. Voici le schéma du script d'ouverture du formulaire :

Et celui de traitement du formulaire saisi :

Nous repérons un certains nombre de fonctions (les rectangles blancs) et des groupements de fonctions communs (les rectangles gris). Nous allons programmer des fonctions qui correspondent aux rectangles blancs et dans la mesure du possible faire des fonctions plus complètes qui correspondent aux rectangles gris.
Liste des fonctions :

  • poser_verrou_MySQL : Pose dès que possible un verrou MySQL sur une table spécifié.

  • supprimer_verrou_MySQL : Supprime tous les verrous MySQL appartenant à la connexion courante.

  • poser_verrou_ligne : Pose un verrou programmé sur la ligne spécifié. Elle peut écraser un autre verrou, il faut l'utiliser après avoir vérifier qu'il n'y ait pas un autre verrou sur la ligne.

  • tester_verrou_ligne : Teste s'il y a un verrou programmé sur la ligne spécifié, et indique si la connexion courante en est propriétaire.

  • supprimer_verrou_ligne : Supprime le verrou de la ligne spécifié.

  • supprimer_verrou_obsoletes : Supprime tous les verrous obsolètes de la table spécifiée

Une bibliothèque de fonctions

Nous allons voir ici les fonctions annoncée ci dessus et nous faire un "boite à outils"

<?php /******************************************************************************/ /* Boite à outils verrous MySQL */ /* Auteur : matthieu@doncoeur.com */ /* Mise à jour : 01/12/2009 */ /* */ /* Liste des constantes */ /* - VALIDITE_VERROU Durée de validité d'un verrou en minutes */ /* */ /* Liste des méthodes */ /* - requete ($requete, $connexion = NULL) */ /* - poser_verrou_mysql ($table, $type="WRITE", $connexion = NULL) */ /* - supprimer_verrou_mysql ($connexion = NULL) */ /* - poser_verrou_ligne ($table, $id, $cle = 'id', $connexion = NULL) */ /* - tester_verrou_ligne ($table, $id, $cle = 'id', $connexion = NULL) */ /* - supprimer_verrou_ligne ($table, $id, $cle = 'id', $connexion = NULL) */ /* - supprimer_verrou_obsoletes ($table, $connexion = NULL) */ /******************************************************************************/ define('VALIDITE_VERROU', 5); // durée de validité d'un verrou en minutes /******************************************************************************/ /* requete ($requete, $connexion = NULL) */ /* Cette fonction exécute la requête sur la base */ /* $requete : requête SQL à exécuter sur la base MySQL */ /* $connexion : Peut contenir un lien vers une connexion MySQL. Si omis, la */ /* dernière connexion du script sera utilisée */ /******************************************************************************/ function requete ($requete, $connexion = NULL) { if ( empty ( $connexion) ) { $resultat = mysql_query ( $requete ); } elseif (is_ressource ($connexion) ) { $resultat = mysql_query ( $requete, $connexion ); } else { return ('erreur sur le paramètre $connexion.'); } if ( !$resultat) { die('Requête invalide : <br>'.$requete. '<br>' . mysql_error()); } else { return $resultat; } } /******************************************************************************/ /* poser_verrou_mysql ($table, $type="WRITE" $connexion = NULL) */ /* Cette fonction pose un verrou MySQL sur la ou les tables passées en */ /* paramètres */ /* $table : - si est de type STRING pose un verrou sur la table spécifiée */ /* - si est de type ARRAY pose un verrou sur les tables spécifiées. */ /* - Si le tableau est associatif, les index servent d'alias. */ /* $type : Indique le type READ ou WRITE du verrou */ /* $connexion : Peut contenir un lien vers une connexion MySQL. Si omis, la */ /* dernière connexion du script sera utilisée */ /* Retourne TRUE en cas de succès ou une chaine avec un message d'erreur */ /******************************************************************************/ function poser_verrou_mysql ($table, $type="WRITE", $connexion = NULL) { $liste_table = ''; // Liste des tables à verrouiller. $requete = ''; // Requête MySQL pour verrouiller. // verification du paramètre $type; $type = mb_strtoupper($type); if (!in_array($type, array('WRITE', 'READ'))) { return ('erreur sur le paramètre $type. "READ" et "WRITE" sont les seules valeurs admises'); } if (is_string ($table) ) { $liste_table = $table . ' ' . $type; } elseif (is_array ($table) ) { foreach ($table as $key => $value) { if ( !is_string ($value) ) { return ('erreur sur le paramètre $table.'); } if ( !empty ($liste_table) ) { $liste_table .= ', '; } $liste_table .= $value .' '; if (is_string ($key) ) { $liste_table .= 'AS ' . $key . ' '; } $liste_table .= $type; } } else { return ('erreur sur le paramètre $table.'); } $requete = 'LOCK TABLE ' . $liste_table . ';'; $resultat = requete ($requete, $connexion); if ( !$resultat) { die('Requête invalide : <br>'.$requete. '<br>' . mysql_error()); } else { return TRUE; } } /******************************************************************************/ /* supprimer_verrou_mysql ($connexion = NULL) */ /* Cette fonction supprime tous les verrous MySQL sur la ou les tables */ /* $connexion : Peut contenir un lien vers une connexion MySQL. Si omis, la */ /* dernière connexion du script sera utilisée */...

Appeler le centre

Avez-vous besoin d'un coach de formation?

Il vous aidera à comparer différents cours et à trouver la solution la plus abordable.

Gestion de verrou MySQL

Prix sur demande