lundi, 18 février 2008

De la concurrence des requêtes SQL

Sans cité Montaigne, voici un cas académique dans lequel la concurrence des requêtes SQL est souvent oubliée.

Nous avons une table hits qui contient 4 champs : une date (nommé date), une clé étrangère pointant vers un objet du système (nommée object_id) et un compteur implémenté sous ca forme la plus simple : un entier (nommé counter). Puisqu'on travail avec une framework qui implémente de l'active record on ajoute un champ id qui fera office de clé primaire.
A chaque utilisation de l'objet référencé par la clé étrangère on souhaite incrémenter le compteur et recommencer à 0 tous les minuits afin d'avoir un historique journalier de l'utilisation de l'objet. Alors comme on a quand même un peu réfléchi à la performance du système (un billet sur le sujet est en préparation), on définit un index composé des champs date et object_id. Puis on définit la fonction suivante, donnée ci-dessous en pseudo-code, qui incrémente notre compteur et insère un nouveau tuple si aucun n'existe pour le jour courant :

void function hit ( int $object_id ) {
SELECT FROM hits WHERE object_id = $object_id AND date = NOW()
if (row_exists)
UPDATE hits SET counter = counter + 1 WHERE object_id = $object_id AND date = NOW()
else
INSERT INTO hits (object_id, date, counter) VALUES ($object_id, NOW(), 1)
}
Tout se passe bien jusqu'au jour où on se rend compte qu'il y a plusieurs lignes par objet et par jour dans la base de données.

Ce problème vient du fait qu'il peut se passer un temps indéterminé entre la requête SELECT et l'INSERT. Si à 00h01 2 requêtes sont faites en même temps sur le même objet, il est fort probable que 2 requêtes SELECT soient faites avant un INSERT, et donc les 2 tests if (row_exists) vont retourner faux et 2 INSERT seront fait.

Les solutions pour résoudre ce problème sont multiples, elles passent de la redéfinition de la clé primaire en date, object_id plutôt que notre champ id, ce qui aurait comme comportement de faire échouer le 2ème INSERT, erreur qui pourrait être capturée et traitée spécifiquement. Une autre solution serait de mettre un verrou sur la fonction, chose très aisée en Java avec le mot-clé synchronized. La fonction deviendrait void synchronized function hit(int object_id), évitant ainsi ce type de problème de concurrence.

Dans tous les cas les applications web sont aussi (voir même plus) soumises aux problèmes de concurrence, et l'expérience nous montre que la technique de l'autruche de même que les phrases du style "Il ne PEUT PAS y avoir 2 requêtes en même temps" sont à bannir absolument.

4 commentaires:

MrTuTu a dit…

Une autre possibilité est de faire une transaction SQL.
En définissant correctement le niveau de "sérialisation", on empêche le problème mentionné.
Je ne connais pas le détail avec MySQL, mais il y a sûrement cette possibilité avec innoDB.

KillerWhile a dit…

La transaction ne va pas empêcher 2 inserts concurrents. Par contre au commit du deuxième insert tu aurais une erreur que tu pourrais facilement traiter.

Une solution en SQL serait de locker la table pour bloquer le select du deuxième processus, mais à mon humble avis la transaction ne résoud pas ce problème précis.

MrTuTu a dit…

oups, oui. Je viens de contrôler et le niveau le plus atomique pour innoDB est "serializable". Il permet l'accès en lecture à la ligne incriminée tant qu'aucun update n'est exécuté: cela signifie que 2 lectures simultanée sont possible.
Comme c'est un compteur, le "insert after update" n'aide pas non plus. Il faut donc faire ce que tu dis : gérer l'erreur ou modifier le code de l'application.

Anonyme a dit…

Sans compatibilité avec le standard SQL, il existe une manipulation très bien dans MySQL pour cela: INSERT INTO x,y,z VALUES(1,2,3) ON DUPLICATE KEY UPDATE x=1,y=2,z=3

Par contre intègrer cela à un framework AR existant, c'est une autre paire de manche. Je suppose qu'il faut intègrer cela dans le driver MySQL du framework, lors d'un "add" atomique (attention, "add" != "set", "add" vérifie toujours qu'il n'y a pas de données préalables, analogue au SELECT effectué avant le UPDATE)

En MyISAM il y a un table locking avec cette requête donc pas de problème (sauf quand comme moi on considère que MyISAM en soi est un problème), et en InnoDB en autocommit ca passe atomiquement.