Discussion :

[Mages]

Bonjour,

Je modélise un système de réservation en PHP + PostGreSQL.
Je n'arrive pas à trouver de solution propre pour un problème d'accès concurrents.

Description de mon problème:

J'ai un système de réservation dans lequel une réservation pour une personne vaut un coupon.
Pour réserver pour n personnes l'utilisateur doit utiliser n coupons.

Une table représente les achats de coupons: achat_coupon.
Une autre table représente les remboursements de coupons: remboursement_coupon.
Une autre table représente les paiements des réservations (utilisation de coupons): paiement_reservation.

Pour connaître le solde de coupons je fais la somme des achats de coupons - la somme des paiements de réservations (utilisation de coupons) - les remboursements de coupon.

Contrainte: Le solde doit toujours être positif.

Avant de finaliser une réservation je vérifie qu'il y a assez de coupons pour l'effectuer (grâce à l'opération de calcul du solde).

Problème:
Lorsque je finalise la réservation en enregistrant un nouveau paiement de réservation, rien ne m'assure qu'aucun changement dans les tables achat_coupon et surtout remboursement_coupon n'a été effectué en parallèle. Donc mon solde pourrait être négatif à l'issue de cette opération.

Plusieurs solutions auxquelles j'ai pensé:

(1) Vérifier a posteriori que le solde est positif et annuler le paiement de réservation (DELETE) si solde négatif. C'est bourrin et pas forcément safe si jamais j'ai une exception ou un crash de la BDD avant de faire la vérification. Mon paiement serait alors enregistré alors que le solde est négatif.

(2) Utiliser les transactions pour effectuer le paiement puis la vérification a posteriori. Cela permet de rendre l'opération atomique. Donc je n'ai plus le problème d'exception ou de crash en plein milieu d'une opération. Tant que ce n'est pas commité rien n'est enregistré en BDD.
Problème: même en SERIALIZABLE, rien n'empêche d'autres transactions de faire des INSERTS dans les tables qui influent sur le solde de coupons. Donc à la fin de ma transaction je peux faire un commit pensant que le solde est suffisant alors qu'il a été changé par une opération dans une autre transaction.
J'en reviens à mon problème de départ... .

Voilà où j'en suis pour le moment. J'imagine que les systèmes de réservation sont légions et qu'il y a donc des solutions éprouvées à ce problème de validité des données au moment de finaliser une opération basée sur d'autres données de la BDD qui peuvent changer (par INSERT, UPDATE, etc...)

Merci pour votre aide.

[SQLpro]

Vous pouvez rester au niveau d'isolation REPEATABLE READ voire READ COMMITTED, et encapsulant l'ensemble de la logique conditionnelle dans le WHERE de chacun des ordres de mise à jour.

Postez l'intégralité du DDL de vos tables ainsi qu'un jeu d'essais et le résultat attendu et nous vous aiderons a finaliser la procédure.
Votre problématique est très proche d'un exercice que je donnais en cours au CNAM !

A +

[Mages]

Merci pour ton aide.

Je n'ai pas d'ordre de mise à jour à proprement parler (UPDATE). Seulement des INSERTs qui, de fait, sont une sorte de mise à jour car de ces INSERTs dépend le calcul du nombre de coupons restant.

Cela-dit, je dois pouvoir faire une sorte d'INSERT conditionnel de la manière suivante (en "pseudo code", :valeur correspond à une valeur constante renseignée par le code PHP à travers DBAL au moment de la préparation de la requête):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO paiement_reservation
(id_paiement, id_reservation, nb_coupons)
SELECT :idPaiement, :idReservation, :nbCoupons
WHERE (nombre_de_coupons_restants >= :nbCoupons )
RETURNING id_paiement;

(*) nombre_de_coupons_restants pouvant être une clause "WITH" qui calcule le nombre de coupons restants.

Dans mon code PHP je fetche le résultat. S'il n'y en a pas c'est que l'opération d'INSERT a échoué et je peux alors faire un ROLLBACK de ma transaction pour préserver mes données.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
if($fetchResult === false)
{
    $this->connection->rollBack();
}

De même en cas d'exception je fais un ROLLBACK.

Je peux répéter ceci pour toutes les opérations de "mise à jour" de la base de données.

Est-ce cela que tu voulais dire par?

"encapsulant l'ensemble de la logique conditionnelle dans le WHERE de chacun des ordres de mise à jour."

Note:
En testant en mode console sur un set de test je me suis rendu compte que quel que soit le niveau d'isolation, une transaction voit les modifications commitées par d'autres transactions concurrentes. Alors que je pensais (en SERIALIZABLE) que la base faisait un snapshot au début de la transaction et que seules ces valeurs gelées pouvaient être vues par la transaction.

Du coup je pensais qu'utilisation d'une TRANSACTION et accès aux dernières données COMMITées de la base n'était pas possible.

*******************************************************************************************************************************************
EDIT car j'ai complètement divagué... la fatigue sûrement.

Mon problème reste en fait toujours plus ou moins le même.

Même si j'ai vérifié pour chaque opération qu'elle pouvait s'effectuer selon la condition globale, juste avant de faire le COMMIT rien n'empêche une transaction concurrente de faire un INSERT (sur par exemple un remboursement de coupons) et donc mon solde de coupons de devenir négatif.

Je vais donc COMMIT-er des donnée erronées...

Same player shoot again.

[SQLpro]

Non, sit u démarre une transaction et qu'à chaque ordre SQL tu encapsule dans ta clause WHERE l'ensemble des conditions, il n'y aura pas de problème.

PostGreSQL ne permet malheureusement pas de piloter les transactions dans les routines SQL. Mais voici ce qu'il faudrait faire (je donne cet exemple sous MS SQL Server)...
Table des vols

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE T_VOL
(VOL_ID               INT PRIMARY KEY,
 VOL_DATE             DATE NOT NULL,
 VOL_SIEGES_LIBRE     SMALLINT NOT NULL);
GO
 
INSERT INTO T_VOL VALUES (1, '2015-08-15', 4);
INSERT INTO T_VOL VALUES (2, '2015-08-15', 89);
INSERT INTO T_VOL VALUES (3, '2015-08-16', 45);

Table des hotels

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE T_HTL --> Hotel
(HTL_ID               INT PRIMARY KEY,
 HTL_DATE             DATE NOT NULL,
 HTL_CHAMBRES_LIBRE   SMALLINT NOT NULL);
GO
 
INSERT INTO T_HTL VALUES (100, '2015-08-15', 3);
INSERT INTO T_HTL VALUES (200, '2015-08-15', 28);
INSERT INTO T_HTL VALUES (300, '2015-08-16', 16);

Un voyage = réservation de vol + hotel. Voici la procédure stockée transactionnée en Transact SQL (MS SQL Server) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE PROCEDURE P_RESERVATION @NOMBRE SMALLINT, 
                               @VOL_ID INT,
							   @HTL_ID INT
AS
 
BEGIN TRANSACTION;
 
BEGIN TRY
 
    -- prise de sièges dans le vol
	UPDATE V
	SET    VOL_SIEGES_LIBRE = VOL_SIEGES_LIBRE - @NOMBRE
	FROM   T_VOL AS V
		   CROSS JOIN T_HTL AS H
	WHERE  V.VOL_ID = @VOL_ID
	  AND  V.VOL_SIEGES_LIBRE >= @NOMBRE
	  AND  H.HTL_ID = @HTL_ID
	  AND  H.HTL_CHAMBRES_LIBRE > = @NOMBRE;
 
	-- si aucune ligne mise à jour alors, pas de place sur le vol  
	IF @@ROWCOUNT = 0
	   RAISERROR('Plus de place sur le vol n°%s', 16, 1, @VOL_ID)
 
    -- prise de chambre dans l'hotel
	UPDATE T
	SET    HTL_CHAMBRES_LIBRE = HTL_CHAMBRES_LIBRE - @NOMBRE
	FROM   T_VOL AS V
		   CROSS JOIN T_HTL AS H
	WHERE  V.VOL_ID = @VOL_ID
	  AND  V.VOL_SIEGES_LIBRE >= @NOMBRE
	  AND  H.HTL_ID = @HTL_ID
	  AND  H.HTL_CHAMBRES_LIBRE > = @NOMBRE;
 
	-- si aucune ligne mise à jour alors, pas de place dans l'hotel 
	IF @@ROWCOUNT = 0
	   RAISERROR('Plus de place dans l''hotel n°%s', 16, 1, @HTL_ID)
 
	COMMIT;
 
END TRY
 
BEGIN CATCH
 
	IF XACT_STATE() <> 0 -- la transaction est toujours "vivante"
	   ROLLBACK;
	RAISERROR('Le voyage considéré n''a pu être vendu', 16, 1);
 
END CATCH;

Réservation pour 4 sur vol 1 et hotel 100 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC P_RESERVATION 4, 1 100

A +

[Mages]

Merci pour l'exemple.

Tu peux m'expliquer pourquoi le fait de mettre toutes les conditions dans le WHERE empêche des enregistrement concurrents?

Dans ton exemple je peux le comprendre si la transaction est SERIALIZABLE car la transaction va voir le verrou posé par un UPDATE de l'autre transaction sur la même table (ou le même enregistrement) et donc attendre que le verrou soit relâché lors du COMMIT avant de continuer.

Mais dans mon cas, je ne vois pas puisque aucun verrou n'est posé lorsqu'un INSERT est effectué.
Je vais conditionner mon INSERT sur la clause WHERE mais celle-ci ne voit de toute façon pas les modifications faites par l'autre transaction concurrente.
Donc les deux transactions sont aveugle vis-à-vis des INSERTs de l'autre.

Je débute sur les transaction dans les SGBD donc je n'ai peut-être pas tout saisi... .

A+

[SQLpro]

Merci pour l'exemple.

Tu peux m'expliquer pourquoi le fait de mettre toutes les conditions dans le WHERE empêche des enregistrement concurrents?

Dans un SGBDR il n'est pas d'usage d'interdire les accès concurrents mais d'en limiter les effets. Et cela se base sur 3 choses :
1) la logique fonctionnelle
2) la transaction
3) le niveau d'isolation

Suivant les SGBDR les transactions sont pilotés de différentes manières :
1) implicite (cas d'Oracle) : dès connexion au serveur, une transaction est démarrée.
2) explicite (cas de SQL Server) : nécessite d'exprimer l'ordre de démarrage de la transaction par BEGIN TRANSACTION (ou BEGIN WORK)
3) autocommit (cas de SQL Server) : chaque ordre SQL est une transaction
PostGreSQL travaille en autocommit, mais permet dans les applications (hélas pas dans les routines) de démarrer des transactions explicites.

Le niveau d'isolation permet d'éviter certaines anomalies transactionnelles.
Au niveau 0 (READ UNCOMMITTED) toutes les anomalies transactionnelles sont possibles (lecture sale, lectures non répétables, ligne fantôme...) .
Au niveau 1 (READ COMMITTED) l'anomalie de lecture sale est évitée
Au niveau 2 (REPEATABLE READ) l'anomalie de lecture sale et de lecture non répétable est évitée
Au niveau 3 (SERIALIZABLE) toutes les anomalies sont évitées, y compris l'apparition de lignes fantôme

Toute autre anomalie doit être considérée au niveau fonctionnel par des mécanismes adéquats,, tel que la pré-réservation ou la vérification simultanée

À noter, certains SGBDR fonctionnent en verrouillage optimiste par défaut (Oracle, PostGreSQL) cela permet que les lectures ne bloquent pas les écritures au moyen d'un versionnement des lignes qui possède l'inconvénient de rajouter une anomalie non contournable : la perte de mise à jour.

Dans ton exemple je peux le comprendre si la transaction est SERIALIZABLE car la transaction va voir le verrou posé par un UPDATE de l'autre transaction sur la même table (ou le même enregistrement) et donc attendre que le verrou soit relâché lors du COMMIT avant de continuer.

Dans mon exemple, point n'est besoin d'une transaction au niveau SERIALIZABLE. Le niveau 1 (généralement par défaut) suffit. En sus, le relâchement tardif d'un verrou est aussi obtenu par le niveau REPEATABLE READ (lecture répétable).

Mais dans mon cas, je ne vois pas puisque aucun verrou n'est posé lorsqu'un INSERT est effectué.

Bien sur que si !!!

Je vais conditionner mon INSERT sur la clause WHERE mais celle-ci ne voit de toute façon pas les modifications faites par l'autre transaction concurrente.
Donc les deux transactions sont aveugle vis-à-vis des INSERTs de l'autre.

mais il ne faut pas faire 2 transaction, mais une seule !!!

Je débute sur les transaction dans les SGBD donc je n'ai peut-être pas tout saisi... .

Je confirme !!!!

A +