Concurrence dans les UPDATE d'une table MySql

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 ?).
 

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.