Concurrence dans les UPDATE d'une table MySql

Fermé
cocodu67... Messages postés 3153 Date d'inscription jeudi 28 janvier 2010 Statut Membre Dernière intervention 4 avril 2024 - 2 oct. 2022 à 17:54
yg_be Messages postés 22672 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 11 avril 2024 - 11 oct. 2022 à 17:57

Bonjour,

Sur mon site il y a parfois 3 à 5 personnes qui effectuent des actions provoquant la modification d'une valeur dans une ligne avec un UPDATE de cette valeur.

Tout le monde agit sur la même valeur.

D'autres personnes agissent sur une autre valeur contenue dans la même table.

Depuis quelques temps j'ai constaté que le site est extrêmement lent voir même charge à l'infini lorsque les personnes effectuent ces actions. Et même pour ces personnes le chargement des pages est extrêmement lent voir impossible.

Mon hébergeur a jeté un oeil et il a dit que c'est probablement lié au fait que mes tables sont au format MyISAM et qu'à chaque accès ça verrouille toute la table. Puisque toutes les valeurs modifiées sont dans la même table ça créé des blocages lorsque les UPDATE sont rapides et très peu espacés. Je précise que je n'ai jamais indiqué le moindre verrou dans mes requêtes mais à priori c'est le comportement par défaut. Vous confirmez ?

L'hébergeur me conseille de passer mes tables au format InnoBD et m'explique que ce format ne verrouille pas toute la table.

Je veux bien convertir au format InnoBD, mais j'ignore les conséquences que ça aura et c'est pourquoi j'écris ce message pour mieux comprendre.

En continuant de n'utiliser aucun verrou dans mes requêtes, est ce qu'au format InnoBD il risque d'y avoir un conflit avec les UPDATE ?

Exemple:
- Utilisateur 1 provoque un SELECT de la valeur.
- Utilisateur 2 provoque un SELECT de la valeur.
- Utilisateur 1 provoque un UPDATE de la valeur récupérée.
- Utilisateur 2 provoque un UPDATE de la valeur récupérée.

Le problème ici est que l'UPDATE de l’utilisateur 2 ne se fait pas sur la valeur qu'il avait récupéré, puisqu'elle a déjà été modifiée par l'utilisateur 1. Cela pose problème par exemple dans le cas d'une gestion de stock.

En effet, si l'utilisateur 1 SELECT et obtient un stock de 2 il sait qu'il peut retirer 2 du stock.

Si l'utilisateur 2 SELECT ensuite et obtient un stock de 2 il sait qu'il peut retirer 2 du stock.

L'utilisateur 1 a acheté l'objet 2 fois donc ça retire 2 du stock. Il reste 0.

Problème, l'utilisateur 2 arrive et UPDATE à son tour la valeur car il a aussi acheté l'objet 2 fois. Problème, il n'était plus en stock car l'utilisateur 1 a acheté les 2 derniers objets.

Comment faire du coup ?

Merci d'avance

 

A voir également:

2 réponses

jee pee Messages postés 39557 Date d'inscription mercredi 2 mai 2007 Statut Modérateur Dernière intervention 11 avril 2024 9 213
Modifié le 2 oct. 2022 à 19:29

Bonjour,

La gestion des accès concurrents et la notion de transaction doivent faire partie de toute formation aux bases de données ;-)

Avant même de l'aborder, on peut parfois utiliser une solution plus simple :
- lecture du stock pour affichage
- l'utilisateur achète, il peut mettre plusieurs minutes à valider
- mise à jour : update stock set qte=qte-achat where ref=$ref and qte-achat>=0, et on teste le retour, 1 ligne mise à jour, ok, pas de ligne mise à jour, au moment de la tentative d'update le stock n'était plus suffisant, on indique à l'utilisateur que sa commande n'est pas validée, plus assez de stock.

Après concernant les accès concurrents, dans ton exemple, il n'est pas acceptable de bloquer quelque chose pour une durée qui dépend d'un temps humain : l'utilisateur a le stock affiché, il réfléchit plusieurs minutes avant de valider. C'est ce qui doit entrainer les blocages de ton site.

On va plutôt faire
- lecture du stock pour affichage
- l'utilisateur achète, il peut mettre plusieurs minutes à valider
- select qte from stock where ref=$ref for update
- on vérifie que le stock est toujours suffisant
- si oui on met à jour : update stock set qte=qte-achat where ref=$ref
là on a bloqué un enregistrement le temps de la mise à jour, c'est un temps machine durant l'exécution de 3 ou 4  lignes de code.

Il existe aussi une autre façon de procéder, la transaction, un ensemble d'instructions qui s’effectuent en totalité sur un état permanent de la bdd. C'est plus pratique que le select for update quand on a plusieurs tables à mettre à jour et qu'il y a insertion d'enregistrements.
- lecture du stock pour affichage
- l'utilisateur achète, il peut mettre plusieurs minutes à valider
- start transaction
  select qte from stock where ref=$ref
  on vérifie que le stock est toujours suffisant
  si oui on met à jour : update stock set qte=qte-achat where ref=$ref
  end transaction (ou commit)

La façon de procéder, select pour update, la notion de transaction, ou autre, va dépendre du sgbd et du langage ou outil utilisé pour manipuler la bdd.

Pour mysql on peut trouver, un blocage d'enregistrement ou de table : https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

ou la gestion de transaction : https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Après il faut voir comment cela s'implémente dans le langage que tu utilises (php ?).
 


0
jordane45 Messages postés 38134 Date d'inscription mercredi 22 octobre 2003 Statut Modérateur Dernière intervention 11 avril 2024 4 646
Modifié le 2 oct. 2022 à 19:26

Bonjour jeeper je me permets juste une petite correction

update stock set qte= qte - '$qte-achat' where ref=$ref and qte-achat>='$qte-achat'

0
jee pee Messages postés 39557 Date d'inscription mercredi 2 mai 2007 Statut Modérateur Dernière intervention 11 avril 2024 9 213 > jordane45 Messages postés 38134 Date d'inscription mercredi 22 octobre 2003 Statut Modérateur Dernière intervention 11 avril 2024
2 oct. 2022 à 19:28

Exact, une donnée externe, pas une colonne de la table.

0
jordane45 Messages postés 38134 Date d'inscription mercredi 22 octobre 2003 Statut Modérateur Dernière intervention 11 avril 2024 4 646 > jee pee Messages postés 39557 Date d'inscription mercredi 2 mai 2007 Statut Modérateur Dernière intervention 11 avril 2024
Modifié le 2 oct. 2022 à 19:51

Et supérieur à la quantité achetée .. pas juste à zéro.  Car si il en acheté 5 et qu'il n'en reste que 2 .. ta condition était valide...  sauf que....

0
cocodu67... Messages postés 3153 Date d'inscription jeudi 28 janvier 2010 Statut Membre Dernière intervention 4 avril 2024 145
2 oct. 2022 à 20:00

Bonsoir,

Je vous remercie pour cette explication. Effectivement, tout est en PHP. Je trouve ça très intéressant mais j'avoue ne jamais m'être intéressé aux accès concurrents puisque ma façon de faire et en utilisant le moteur MyISAM a toujours fonctionné.

Je ne souhaite pas bloquer quoi que ce soit pendant plusieurs secondes ou plusieurs minutes. J'affiche le stock restant, mais la vérification se fait au moment de l'achat, avec un message si le stock au moment de l'achat n'est pas suffisant.

Chaque personne a des points sur son compte, chaque achat consomme un nombre de points, chaque objet n'est disponible que dans une certaine quantité.

Sous forme de phrases, voici le processus d'achat en PHP:
1) Le site récupère le nombre de points restants sur le compte.
2) Le site récupère toutes les informations nécessaires concernant l'objet à partir de son numéro (chaque objet a un numéro unique).
3) Le site vérifie si le compte possède assez de points par rapport au coût de l'objet.
4) Le site insère la "commande" (je simplifie, c'est pas vraiment une commande) dans une autre table.
5) Le site met à jour le nombre de points sur le compte de l'acheteur.
6) Le site met à jour le stock restant pour l'objet.

J'ai l'impression qu'il y a plusieurs problèmes de conception mais comme la table était verrouillée (enfin ... je suppose) il n'y avait jamais de problème de stock même avec 20 personnes qui essayent d'acheter un objet disponible que 4 fois.

Par exemple pour mettre à jour le stock restant je fais:

try {
    $req_update_quantite = $pdo_divers->prepare('UPDATE Boutique SET quantitedisponible = :NOUVELLEQUANTITE WHERE id_article = :OBJET;');
    $req_update_quantite->bindParam(':OBJET', $article , PDO::PARAM_STR, 7);
    $req_update_quantite->bindParam(':NOUVELLEQUANTITE', $nouvelle_quantite, PDO::PARAM_STR, 7);
    $req_update_quantite->execute();
} catch (PDOException $Exception) { }

Je peux modifier ça en:
UPDATE Boutique SET quantitedisponible = quantitedisponible - 1 WHERE id_article = :OBJET AND quantitedisponible > 0;
mais si je fais cela, je ne sais pas comment savoir si la requête UPDATE a réussi à modifier la ligne. Mais maintenant je me rend compte que la mise à jour du stock devrait se faire avant d'insérer la commande et avant de retirer le coût de l'objet sur le compte de la personne.

0
jee pee Messages postés 39557 Date d'inscription mercredi 2 mai 2007 Statut Modérateur Dernière intervention 11 avril 2024 9 213
2 oct. 2022 à 20:13
0
jee pee Messages postés 39557 Date d'inscription mercredi 2 mai 2007 Statut Modérateur Dernière intervention 11 avril 2024 9 213
Modifié le 2 oct. 2022 à 20:18

mais j'ai bien écrit pour la 1ère solution que cela s'aplique aux cas simples. Si tu as 2 tables à mettre à jour, et que les 2 peuvent échouer, faute de stock ou de point, la solution update ne fonctionne plus.

Il faut passer en mode transaction, et si l'une des 2 mises à jour n'est pas possible, au lieu du commit, faire un rollback de la transaction, qui restitue l'état de la bdd avant la transaction.

0
cocodu67... Messages postés 3153 Date d'inscription jeudi 28 janvier 2010 Statut Membre Dernière intervention 4 avril 2024 145 > jee pee Messages postés 39557 Date d'inscription mercredi 2 mai 2007 Statut Modérateur Dernière intervention 11 avril 2024
10 oct. 2022 à 14:25

Bonjour,

Désolé pour le retard de réponse. Je vais passer par la méthode du rowcount qui a l'air très pratique. Une seule ligne d'une seule table étant mise à jour pour le stock, cela devrait être suffisant. Je retirerais ensuite le prix de l'achat si rowcount retourne 1.

La vérification du nombre de points restants chez la personne est effectué au tout début, avant la modification du stock, puisque ça récupère en même temps l'heure du dernier achat car je limite à un achat toutes les 8 secondes.

Je retirerais 1 du stock si le stock est supérieur à 0 et si le prix de l'objet est inférieur ou égal au nombre de points possédés par la personne. Et ainsi 2 choses seront faites d'un coup ce qui va raccourcir le code.

Il faut aussi que j'étudie de près le fonctionnement du rollback car ça a l'air très pratique puisqu'on a pas besoin de garder les valeurs en mémoire juste au cas où on en a besoin. Cela pourra me servir un jour ailleurs.

J'ai également mis en place une vérification javascript côté client afin de contrôler si le dernier appuie du bouton d'achat a été effectué il y a 2 secondes et plus. Les achats étant limités à 1 toutes les 8 secondes et par personne, certains cliquaient 50 à 100 fois par minute sur le bouton d'achat pour que le prochain objet soit acheté exactement à la fin de l'attente des 8 secondes et ça provoquait des difficultés d'accès au site, même si pour le moment je n'ai pas compris pourquoi car l'hébergeur affirme que même si le CPU et la RAM montrent un pic, le serveur n'est pas surchargé pendant une vente flash. Et pourtant l'accès au site est impossible au bout de 1 à 2 minutes et ceci parfois pendant 20 minutes. Certaines personnes étant parvenues à voir les objets en vente avant les difficultés d'accès constatent un délais extrêmement long entre l'appui du bouton d'achat et la prise en compte de l'action.
J'en viens à supposer que c'est l'accès à la base de données ou le nombre de connexions qui est saturé, mais j'ai certaines pages et tâches cron qui font bien plus de 100 requêtes SQL par minute sans aucune difficulté d'accès au site pendant leur exécution ou après celle-ci.

Du coup pour résumer je dois limiter l'impacte des "cliqueurs fous" et tant pis si je n'ai pas compris pourquoi on ne peut accéder au site pendant que 2 ou 3 personnes cliquent frénétiquement sur le bouton d'achat.
 

0
yg_be Messages postés 22672 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 11 avril 2024 1 463 > cocodu67... Messages postés 3153 Date d'inscription jeudi 28 janvier 2010 Statut Membre Dernière intervention 4 avril 2024
10 oct. 2022 à 21:33

Utilises-tu des sessions?  Tu pourrais t'en servir pour limiter la fréquence des actions par utilisateur.

0
yg_be Messages postés 22672 Date d'inscription lundi 9 juin 2008 Statut Contributeur Dernière intervention 11 avril 2024 1 463 > cocodu67... Messages postés 3153 Date d'inscription jeudi 28 janvier 2010 Statut Membre Dernière intervention 4 avril 2024
10 oct. 2022 à 21:48

Tes protections par javascript peuvent facilement être contournées par les utilisateurs.

0