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.