Update MYSQL

[xanthius]

Bonsoir,
voilà maintenant deux ans qu'Air Carrier est ouvert. Ce dernier poursuit son bonhomme de chemin avec quelques mises à jours par ci par là.
Nous en avons même une en préparation pour le 15 juin mais là n'est pas l'objet du message.
Si vous écris ce soir c'est pour avoir un peu d'aide sur la manière de procéder avec les UPDATE MYSQL mais aussi avec le fonctionnement des lock.
Pour ceux étant choqué, oui, je ne m'y connais pas énormément, d'où ce POST. Qui sait, il en aidera d'autres.

Bref,
deux intérogrations.
Pour vos jeux, quand vous devez boucler puis mettre à jours les données de vos joueurs, comment procédez quoi ?
Imaginons que nous devons boucler sur les combats à venir, mettre à jours les vaisseaux restants (table 1), les ressources perdues ou gagnée (table 2), les points de stat (table 3).
Il s'agit simplement d'un exemple, je veux simplement vous illustrer le cas où l'on doit boucler et mettre à jour plusieurs tables différentes.

Seconde interrogation,
lorsque l'on met à jour une ligne, l'on bloque l'accès, les updates se font à la suites avec le blocage des prochains process "waiting for table level lock" du coup le temps d'exécution s'allonge. Le fait de bloquer, pénalise les accès (?) du coup lors du traitement, l'accès à la table est plus longue (là je n'ai pas de connexion à la base et tout passe sans latence, ce qui n'est pas le cas avec mon cron en arrière plan..)

D'où mes interrogations, comment faites vous dans pareille situation ?

[Xenos]

Salut,

Pour vos jeux, quand vous devez boucler puis mettre à jours les données de vos joueurs, comment procédez quoi ?

Perso, c'est rare. Souvent, une seule requête suffit. Eventuellement, une table d'integers peut aider pour cela (perso, j'ai toujours une table "integers" composée d'une seule colonne "n INT UNSIGNED" remplie avec tous les entiers de 0 à ~1.000.000; ça ne coûte rien en stockage, mais cela permet d'émuler quelques trucs que MySQL ne sait pas faire nativement [nota: MySQL 8, sorti depuis peu, propose quelque chose de similaire via les window functions et ROW_NUMBER de mémoire])

Dans les cas où c'est nécessaire, je ne lock pas la table (c'est une catastrophe en terme de concurrences). Au lieu de cela, j'encapsule dans une transaction et basta (souvent largement suffisant, étant donné que MySQL est censé fournir une lecture consistante des données).
Quand des SELECT sont à faire, j'utilise également FOR UPDATE or LOCK IN SHARE MODE suivant le cas d'utilisation (cf doc https://toile.reinom.com/les-astuces-et-...ql-queries )

waiting for table level lock

Ce n'est pas normal: tu devrais plutôt avoir un ROW lock et non un TABLE lock. Nota que MyISAM ne permet pas le lock des rows: passe en InnoDB si tu étais dessus.

du coup le temps d'exécution s'allonge. Le fait de bloquer, pénalise les accès [...] l'accès à la table est plus longue

Tout à fait. Tant qu'une ROW est "lock", les autres clients SQL ne peuvent y accéder. Si une requête a besoin de verrouiller une ligne (ie: lors d'un UPDATE dans une transaction) alors cette requête attend que la ligne ne soit plus verrouillée en écriture, puis elle pose son verrou, l'édite, et jusqu'à ce que le verrou soit relâché, les autres requêtes poireautent (sauf éventuellement si elles ont demandé des options type "zappe les verrous et ignore ces lignes"). Note qu'une lecture (SELECT) ne pose pas de verrou en écriture (une requête peut faire son SELECT sans bloquer une autre requête), sauf si la première requête SELECT a préciser de verrouiller la ligne (FOR UPDATE)

Bon, après, pour nos usuels petits jeux web à 100 voire 1000 joueurs actifs (et encore), les temps de traitement et de lock SQL sont censés être très courts. Si ce n'est pas le cas, c'est que tu as probablement mal construit ta requête SQL.

[xanthius]

Bonsoir,
merci d'avoir pris le temps de me répondre même si je l'avoue que j'ai parfois du mal à cerner. Je ne suis pas aussi pointu que toi, du coup des choses qui pour toi, limpides, sont pour moi totalement abstraites...
Pour mon cas, je fais un SELECT sur 6 tables, par la suite je met à jour les informations du joueur. En soit, ce qu'il a gagné, l'évolution de ses stats ..
Du coup pour ce fichier j'ai une méthode de fonctionnement assez sale, je le reconnais. Je boucle et j'update à l'intérieur or il y a plusieurs Update mais surtout d'après ce que j'ai pu lire (si toutefois c'est juste) c'est une pratique à éviter..

Comme tu le dis pour nos petits jeux, nous devrions avoir des temps assez courts mais au final pour certains joueurs, les plus gros, les délais sont longs..
d'où ma venue pour trouver une solution.

Pour le waiting, toutes mes tables sont en MYISAM et j'ai bien des waiting for table. Dans mes process j'avais une belle liste en attente (les update de ma boucle).
Tu parles d'Unlock, peut être ai je mal compris mais tu évites que MYSQL ne bloque les UPDATE suivant, pour qu'il exécute tout d'un bloc ?
Lorsque l'on effectue sur une table X un update d'une ligne mais qu'on veut récupérer cette même ligne via un Select, les performances ne seront pas dégradées ?

[Xenos]

Dur de résumer toutes ses connaissances des 10 dernières années en 5 lignes, mais:

- Encapsule toutes les manipulations de chaque page et de chaque CRON dans une transation (dont le principe est d'assurer que personne ne viendra modifier les données pendant que tu les traites). $pdo->beginTransaction pour la démarrer / $pdo->commit pour la terminer et valider les données / $pdo->rollback pour la terminer mais annuler les modifications [de mémoire, aka les noms des méthodes sont peut-être un poil différents). START TRANSACTION / COMMIT / ROLLBACK si tu es en SQL pur (ie dans des procédures stockées)

- Passe toutes tes tables MyISAM en InnoDB. D'une part, tu auras la possibilité de faire des FOREIGN KEY (je te laisse chercher les tutos du net pour savoir ce que c'est puis voir la doc MySQL pour vraiment comprendre et bien les utiliser) mais tu auras également les lock par ligne et non par table (actuellement, si tu fais un UPDATE sur 1 ligne de ta table MyISAM, alors toute la table sera lockée jusqu'à ce que la transaction soit terminée, ce qui est ingérable)

- Dans le cas de tes SELECT, je pense que tu n'as pas besoin d'un FOR UPDATE/LOCK IN SHARE MODE. Donc on va s'en passer (mais jette un oeil sur la doc pour comprendre ce que cela fait; sur le principe, cela lock la ligne de sorte que les autres transactions ne puissent ni la lire ni l'écrire dans le 1er cas, et juste pas l'écrire mais pouvoir la lire dans le 2nd, de mémoire)

- Dans l'idéal, il faudrait voir le traitement que tu fais, car il y a 99% de chances que ce soit mal goupillé et ultra-fat pour rien. Partage le code en question si tu veux de l'aide là dessus. Cela allègera le temps de calcul.

- Ce n'est pas normal que tu ne scale pas bien avec la "grosseur" du joueur. Je pense que l'algo que tu as est très mauvais (sans être vexant). Partage le code pour qu'on puisse aider (via GitHub ou Mercurial-assimilé au mieux, ou dans un post au pire)

- Non, je veux dire que MyISAM verrouille toute la table quand il modifie 1 seule ligne, et personne (= aucune autre transaction) ne peut y accéder. Ce qui va donc bloquer les autres pages du jeu (= les autres joueurs)

- Non, dans la même transaction, UPDATE & SELECT sur une même ligne peuvent se faire. C'est même souvent indispensable. En revanche, l'UPDATE va "bloquer" la ligne de sorte que d'autres pages ne puissent pas y accéder tant que la transaction n'est pas terminée. Sinon, imagine, tu fais un SELECT dans ton CRON, tu récupère des data, une autre page s'exécute à ce moment-là et met la ligne à jour, puis ton CRON mets à jour cette ligne: ton CRON aurait alors écrasé les modifications de la page (cela s'appelle des accès concurrents). Les transactions sont là pour ça, et MyISAM ne les gère pas convenablement (voire pas du tout peut-être...)

[xanthius]

Bonjour,
merci pour ta réponse ! Déjà de un, je l'assume entièrement, mon code n'est pas mauvais mais plutôt dégueulasse. Je ne serais donc pas vexé par tes remarques. J'aurai juste un peu honte mais bon on fera avec.
Le seul point où j'aimerai réellement insisté c'est la différence de compétence entre toi et moi, peut être pour toi que se sont des notions que toutes personnes devraient avoir mais pour ma part, je ne les connais pour certaines (transaction) que de nom, tu m'avais fais un post il y a quelques mois voir peut être année ^^. D'où le fait que je puisse prendre un peu de temps à comprendre tes explications.

Pour mon code, nul besoin de copier, il est assez long mais dans les faits, voici la structure :

Code PHP :

<?php 

$liste = sql('SELECT .. ');

foreach($liste as $vols){

  sql('UPDATE TAB1');

  sql('UPDATE TAB2');

  sql('UPDATE TAB3');

  sql('UPDATE TAB4');

  sql('UPDATE TAB5');

} 

[Ter Rowan]

ce que tu as décrit n'aide pas
liste c est quoi, est ce qu on peut pas faire des update de masse (tout d'un coup sur une table) etc.. faut plus de précision

[xanthius]

liste est la requête principale sur laquelle je boucle.
Elle me renseigne sur les vols terminés. Je récupère les infos du vol en question (heure, appareil, destination), les infos du joueur, les infos de la destinations, les infos de l'appareil provenant de la jointure avec la table vol.
Quand j'ai récupéré toutes les informations du vols,
je met à jour,
les fonds du joueur
le résultat du jour du joueur (sont renseignés les recettes/dépenses pour chaque jour),
le résultat du vol combien il a transporté, pour combien, le revenu du vol,
les nouvelles caractéristique de l'appareil découlant de la réalisation du vol (heure de vol, CA, usure)
l'insertion dans son journal des mouvements de compte provenant du vol réalisé (relevé bancaire).
la mise à jour de ses équipages (temps de travail, risque de grève)
l'envoi de message si problème avec le vol

[Xenos]

Si je passe l'ensemble des tables en InnoDB, aurai je affaire à des lectures plus lentes de mes tables ou bien une baisse de perf quelconque ? Il doit bien y avoir une contrepartie négative quelque part .. ou pas

Non, c'est simplement un engine plus récent et le plus travaillé par MySQL de nos jours. Il n'y a pas de contrepartie négative. Les éventuelles (baisse de perf" dont tu peux entendre parfois parler ne sont que des racondarts.

Oui, mais sans le code exact, il est impossible de t'aider spécifiquement. Avec ce que tu présentes, très (trop) générique, on ne peux que te répondre des choses génériques, à savoir: travaille de manière ensembliste... Ce qui risque de ne pas beaucoup t'avancer. Ou alors, je peux te dire que ce genre de structure ressemble fortement à un CURSOR en SQL, et qu'il faut donc appliquer les patterns appropriés pour transformer cela en une seule query...

Si tu débute pour ce qui est du SQL? je te (re?) conseille de t'acheter un bouquin, type "Programmer avec MySQL (C Soutou)" [ou version plus récente, le mien date un peu]. Ou, de manière générale, tous les bouquins de chez Eyrolles (couverture noire avec une couleur spécifique à chaque langage). Ca t'aidera énormément, et tu y trouveras des aides et guides de qualité (contrairement à la plupart des tutos du web, que je trouve lamentablement torchés avec le c*l par des débutants en mal de reconnaissance...)

[xanthius]

Ok, dans ce cas je passerais en InnoDB, en espérant que tout se passe bien ..
Le code en question fait 1500 lignes et j'avoue que ça me met un peu mal de tout balancer ..
je pensais que ça aurait été suffisant car je fais réellement une boucle dont je fais des UPDATE à l'intérieure d'où la structure présentée plus haut.
En gros vous allez lire 1500 lignes où il y a une condition ou plusieurs conditions et dans ces conditions un ou plusieurs UPDATE.
Exemple : si l'avion est un avion loué alors on mettra à jour les infos du bailleur ainsi que ceux du loueur. Si l'équipage sur l'appareil a effectuer le quota maximal d'heure de travail alors on update la ligne de l'équipage dans la table équipage avec un warning (en gros la dernière date où il y a eu un rappel) et si lorsqu'on repasse dessus au prochain vol il y a toujours le warning alors grève, dans ce cas c'est une autre condition et là insert + update etc.

Je voulais simplement (bon le terme est un peu fort) savoir comment vous, vous procédiez. Admettons qu'on prenne Ogame, ce dernier à la fin d'un combat, il ne va pas boucler sur tous les combats ayant eu lieu et faire des UPDATE à tout va ..

[Xenos]

En gros vous allez lire 1500 lignes où il y a une condition ou plusieurs conditions et dans ces conditions un ou plusieurs UPDATE.

Oui, mais ce sont justement ces conditions qui doivent être intégrées à ta requête principale pour éviter l'anti-pattern du "foreach each SELECTED row do UPDATE"

On peut, en revanche, procéder dans l'autre sens, et te sortir un exemple mal codé et son équivalent bien codé (tiens, je vias peut-être même le faire en article, histoire que cela puisse servir à d'autres, et histoire d'essayer de commencer à diffuser sur Human Coder... Allez, c'est parti!)