[MySQL5][innoDB][table lockée?]Lock wait timeout exceeded

[hansaplast]

Bonjours,

je travaille sur un relativement gros site d'e-commerce, et on a un énorme pb!

tout d'abors le contexte :
- le code n'a pas ete changé depuis hier
- seule la taille des données stockée en base a (forcement augmanté)

maintenant, le pb :
- soudainement, est apparut le bug suivant (en production!)
l'appel a la procedure stockée suivante :

Code :

creerCaddie( <params> );

provoque l'erreur suivante :

Lock wait timeout exceeded;

voici le code de la proc sto :

Code :

1
2
3
4
5
6
7
8
9
10
11
DELIMITER $$;
CREATE DEFINER=`user`@`%` PROCEDURE `creerCaddie`(    <params>     )
BEGIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                               SET AUTOCOMMIT=0;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                               START TRANSACTION;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                       INSERT INTO Caddie (  <tables>  )                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
                       VALUES (     <ParamEntree>  );                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
                       COMMIT;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                       SELECT LAST_INSERT_ID() AS panierid;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
END$$
DELIMITER ;$$

nous avons eu ce pb durant plussieures heures, le seul moyen de le resorber etait de redémarrer le serveur MySQL. Mais il reaparaissait.
il a disparut tout seul, depuis, nous avons supprimés la transaction inutile.
le code de la procedure stockée etait fonctionnel et inchangé depuis janvier.

Mon intérogation est la suivante :
apres renseignements sur internet, avec innoDB, seules les row liés a des requetes d'insert / update sont lockés dans la/les table concernée.


donc, comment un insert / update peut etre bloqué par une transaction qui, de plus, effectue bien un commit a sa fin ??


la table concerné fait 5.5Mo, avec 10Mo d'indexes, et compte ~30 000 enregistrements. ca ne me semble pas enorme...
elle possede en outre 2 foreign key pointants vers des tables relativement plus legeres.



la resultante de ce bug est que toute reservation est impossible lorsqu'il survient!
si quelqu'un a une piste... :'(


edit : sous mySQL Administrator, on pouvait voir des threads d'ecriture sur cette table en attente lorsque le bug survenait, leut etat etait 'locked'

[hansaplast]

le bug est revenu, a priori, il apparait lorsque les stats sont lancées sur le site.

nos stats lancent de grosses procedures stockées qui lancent de grosses fonctions sur les elements des selects...

sauf que... un select meme dans une prc sto qui lance des fonctions ne peut pas provoquer de lock, non?

ps : en fichier joint : un print screen d'un thread bloquant et des threads bloqués

[eomer212]

bonjour, j'ai eu le meme probleme furtif sur ma base, un peu le meme contexte, de forts acces concurrentiels, moteur innodb.
il etait impossible d'ajouter un enregistrement à un numero specifique, qui etait pourtant le premier disponible dans l'index.
faire un insert sur l'enregistrement 25812 etait impossible, ca passait tout seul sur le 25813 et suivants..
aprés une bonne torture de tete, on s'est apercu qu'une session d'insert precedente sur la table , avec begin, insert, commit our rollback avait ete coupé par l'utilisateur. donc l'enregistrement lui meme pour ce numero etait bloqué, attebndant toujours un commit ou un rollback pour s'arreter. il a fallu killer les sessions pour forcer les utilisateurs à se reconnecter, et le systeme à accepter de nouveaux insert.
genant, d'autant que ca a demandé une intervention manuelle.
est-ce qu'il y a un moyen de detecter ce type de bloquage avant de faire une requete d'insert.? d'autant que je dois le controler, j'ai deux tables en liaison à alimenter lors de cet insert.
donc si la premiere fonctionne, je fais la deuxieme, si la deuxieme fonctionne aussi, je commit, sinon, rollback global.
merci pour tout renseignement..

[hansaplast]

tu veut dire quoi par coupé par l'utilisateur?

[eomer212]

he bien, les requetes sont envoyées sur la base par un executable, qui envoie d'abord le begin, les deux inserts succesifs et le rollback ou commit, selon le resultat.
hors, il y a eu une surcharge reseau à un moment, ou un cable debranché, et la liaison a ete coupée.juste apres l'envoi des requetes d'insertion. le commit ou le rollback n'ont jamais pu etre envoyés...
et la base est restyée à attendre la fin de la transaction , bloquant du meme coup toute autre insertion d'information pour l'index qui etait en cours d'utilisation..