Distribuer les lignes d'une table SQL entre plusieurs applications multi-instanciées

Les bases de données SQL offrent aujourd'hui de très nombreux outils afin de nous permettre de manipuler nos données au mieux.

C'est notamment le cas lorsque l'on souhaite verouiller des lignes au niveau de nos tables. D'autant plus, c'est bien mieux lorsque le moteur de la base de données gère cela pour vous.

La mécanique que je vais vous présenter dans cet article est disponible sur les moteurs MySQL, MariaDB et PostgreSQL.

Cas d'utilisation

Pour les besoins de cet article, nous allons imaginer une application multi-instanciée dont chaque application va régulièrement poller des jobs dans une table SQL et mettre à jour leur statut.

Prennons donc la table SQL nommée jobs suivante :

La problématique est que lorsque l'application A s'occupe de mettre à jour le statut du job 1, il ne faut pas que ce même job 1 soit mis à jour par une application B entre temps.

Pour résoudre cette problématique, j'ai utilisé un mécanisme SQL permettant de locker des lignes en base de données tant qu'une transaction n'a pas encore été modifiée grâce à la syntaxe SELECT ... FOR ....

Utilisation des transactions

Pour résoudre cette problématique, nous allons également avoir besoin des transactions, donc petit rappel sur les transactions si vous ne connaissez pas encore.

Les transactions permettent d'éviter les erreurs d'écriture et de lecture liées à des requêtes concurrentes.

En effet, les requêtes sont dites "atomiques", ce qui signifie que l'état dans votre base de données sera modifié uniquement lorsque vous aurez envoyés une requête COMMIT (ou ROLLBACK).

La mécanique est très simple à utiliser, il suffit en effet de spécifier START TRANSACTION (ou BEGIN) et de faire suivre vos requêtes SQL, comme suit :

BEGIN;

UPDATE jobs SET status = 'pending' WHERE id = 1;
UPDATE jobs SET status = 'error' WHERE id = 2;
-- Ajoutez les requêtes que vous souhaitez ...

COMMIT;

Utilisation de SELECT ... FOR ...

La mécanique SELECT ... FOR ... est disponible au moins chez les trois bases de données suivantes :

• MySQL : https://dev.mysql.com/doc/refman/5.7/en/select.html
• MariaDB : https://mariadb.com/kb/en/select/
• PostgreSQL : https://www.postgresql.org/docs/9.0/sql-select.html

La syntaxe apporte les mots clés suivants :

• FOR UPDATE : Permet de verouiller les lignes sur la lecture, la mise à jour et la suppression,
• FOR SHARE : Permet de verouiller la mise à jour et la suppression uniquement, la valeur pourra cependant toujours être lue.

Lorsque ces options sont spécifiées, la prochaine requête SQL qui essaiera d'interagir avec une de ces lignes se verra attendre que la transaction lancée soit bien terminée. Attention aux deadlocks.

Des options peuvent cependant être ajoutées à la suite de cette syntaxe, notamment :

• NOWAIT : Permet de ne pas attendre que la transaction ayant verouillée la ligne soit terminée et de renvoyée une erreur tout de suite,
• SKIP LOCKED : Permet de ne pas attendre que la transaction ayant verouillée la ligne soit terminée mais ne renvoie pas les lignes verouillées dans le résultat.

Dans notre cas, c'est ce dernier mot clé qui va nous intéresser pour la suite de cet article : SKIP LOCKED.

Gestion de la distribution des lignes

Ainsi, couplé à des transactions, le mécanisme à mettre en place est le suivant :

Chaque application ayant sa propre session SQL, la première application va effectuer les requêtes suivantes :

START TRANSACTION;

-- Sélection des lignes (non verouillées par une autre application) à traiter
SELECT * FROM jobs WHERE status = 'created' ORDER BY created_at ASC LIMIT 2 FOR UPDATE SKIP LOCKED;

-- Plus tard, l'application 1 met à jour les jobs
UPDATE jobs SET status = 'pending' WHERE id = 1;
UPDATE jobs SET status = 'error' WHERE id = 2;

COMMIT;

Ainsi, dans ce cas, l'application 2 ne traitera jamais les jobs avec l'identifiant 1 et 2 tant que l'application 1 n'aura pas terminée de les traiter (COMMIT).

Conclusion

Notre problématique est donc résolue et nous pouvons désormais scaler sereinement nos applications, elles ne se marcheront pas sur les pieds lorsqu'elles traiteront les données.

De plus, inutile de mettre en place une logique compliquée dans notre application, le moteur de base de données gère ceci bien mieux que nous !