Se préserver des accès concurrents dans un système de réservation

Version imprimable

23/06/2015, 13h09
Mages

Se préserver des accès concurrents dans un système de réservation

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.
23/06/2015, 16h21
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 +
23/06/2015, 17h46
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:

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:

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?

Citation:

"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.

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:

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:

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:

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:

EXEC P_RESERVATION 4, 1 100

A +

24/06/2015, 11h54
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+
24/06/2015, 13h53
SQLpro

Citation:

Envoyé par 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 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.

Citation:

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).

Citation:

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

Bien sur que si !!!

Citation:

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 !!!

Citation:

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

Je confirme !!!!

A +
24/06/2015, 13h56
SQLpro

Poste le DDL de tes tables, un jeu d'essais et le résultat attendue.

A +

Prenons un exemple concret, tu as raison, car là on ne se comprend pas je pense.

Je prends l'exemple du paiement d'une réservation décrit plus haut (fait par un INSERT dans la table booking_payment).

Dans une première transaction:
J'ai fait comme tu as dit la vérification sur le solde suffisant du nombre de coupons pour effectuer le paiement dans une clause WHERE dans l'INSERT.

Dans un seconde transaction (et avant que la première ne se termine) :
je simule un remboursement de coupon par le même utilisateur. Les accès sont donc concomitants.

Je COMMIT-e alors la première transaction.

A noter que j'ai simplifié le problème à un seul INSERT pour l'illustration du problème mais qu'en réalité je fais plusieurs INSERTs qui doivent être atomiques.
Donc le passage en COMMIT manuel est là pour illustrer le propos.

Le premier exemple est inspiré de ce que tu m'as dit de faire (ou du moins de ce que j'en ai compris).
Les autres exemples en sont dérivés en rajoutant des niveaux d'isolation supérieurs + des verrous sur les lignes qui sont importantes pour valider les INSERTS grâce à SELECT ... FOR UPDATE.

En résumé: seule la combinaison SERIALIZABLE + SELECT ... FOR UPDATE fonctionne.

Détail ci-dessous:

Initialisation de ma base:

Code:

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
DROP TABLE coupon_purchase;
DROP TABLE coupon_refund;
DROP TABLE booking_payment;
 
CREATE TABLE coupon_purchase(
coupon_purchase_id SERIAL PRIMARY KEY,
user_id INT,
nb INT);
 
CREATE TABLE coupon_refund(
coupon_refund_id SERIAL PRIMARY KEY,
user_id INT,
nb INT);
 
CREATE TABLE booking_payment(
booking_payment_id SERIAL PRIMARY KEY,
user_id INT,
booking_id INT,
nb INT);
 
INSERT INTO coupon_purchase
(user_id, nb) VALUES
(1, 1),
(1, 5);
 
INSERT INTO coupon_refund
(user_id, nb) VALUES
(1, 3);

Exemple 1: ISOLATION LEVEL READ COMMITTED

// Faire initialisation de la base

Transaction 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
BEGIN;
 
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
INSERT INTO booking_payment
(user_id, booking_id, nb)
SELECT 1::INT, 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);
INSERT 0 1

Transaction 2

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
INSERT INTO coupon_refund
(user_id, nb)
SELECT 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);
INSERT 0 1

Transaction 1

Code:

1
2
COMMIT;
COMMIT

Résultat en base:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select * from booking_payment;
 booking_payment_id | user_id | booking_id | nb 
--------------------+---------+------------+----
                  1 |       1 |          1 |  3
(1 row)
 
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
SELECT * FROM coupon_balance;
 total 
-------
    -3
(1 row)

La contrainte de balance > 0 n'est pas respectée car les deux transactions ont eu lieu.

Exemple 2 : ISOLATION LEVEL READ COMMITTED avec SELECT ... FOR UPDATE

Ici j'ai introduit les verrous sur les lignes qui rentrent dans le calcul du solde de coupons.

// Réinitialisation de la base comme pour le premier exemple

Transaction 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
BEGIN;
 
WITH 
purchased_coupons AS
(SELECT nb FROM coupon_purchase WHERE user_id='1' FOR UPDATE),
refund_coupons AS
(SELECT nb FROM coupon_refund WHERE user_id='1' FOR UPDATE),
booking_payment AS
(SELECT nb FROM booking_payment WHERE user_id='1' FOR UPDATE),
coupon_balance AS 
(SELECT
  ((SELECT COALESCE(SUM(nb),0) FROM purchased_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM refund_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM booking_payment)) AS total
)
INSERT INTO booking_payment
(user_id, booking_id, nb)
SELECT 1::INT, 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);
INSERT 0 1

Transaction 2

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH 
purchased_coupons AS
(SELECT nb FROM coupon_purchase WHERE user_id='1' FOR UPDATE),
refund_coupons AS
(SELECT nb FROM coupon_refund WHERE user_id='1' FOR UPDATE),
booking_payment AS
(SELECT nb FROM booking_payment WHERE user_id='1' FOR UPDATE),
coupon_balance AS 
(SELECT
  ((SELECT COALESCE(SUM(nb),0) FROM purchased_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM refund_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM booking_payment)) AS total
)
INSERT INTO coupon_refund
(user_id, nb)
SELECT 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);

// La transaction est bloquée ici en attente du COMMIT ou ROLLBACK de la transaction concurrente qui a mis les verrous sur les lignes.

Transaction 1

Code:

1
2
COMMIT;
COMMIT

Transaction 2

Code:

1
2
3
INSERT 0 1
COMMIT;
COMMIT

// L'insert pour le remboursement de coupons a quand même eu lieu.

Résultat en base:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select * from booking_payment;
 booking_payment_id | user_id | booking_id | nb 
--------------------+---------+------------+----
                  1 |       1 |          1 |  3
(1 row)
 
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
SELECT * FROM coupon_balance;
 total 
-------
    -3
(1 row)

La contrainte de balance > 0 n'est pas respectée car les deux transactions ont eu lieu.

Exemple 3 : ISOLATION LEVEL REPEATABLE READ avec SELECT ... FOR UPDATE

// Réinitialisation de la base comme pour le premier exemple

Transaction1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
BEGIN ISOLATION LEVEL REPEATABLE READ;
 
WITH 
purchased_coupons AS
(SELECT nb FROM coupon_purchase WHERE user_id='1' FOR UPDATE),
refund_coupons AS
(SELECT nb FROM coupon_refund WHERE user_id='1' FOR UPDATE),
booking_payment AS
(SELECT nb FROM booking_payment WHERE user_id='1' FOR UPDATE),
coupon_balance AS 
(SELECT
  ((SELECT COALESCE(SUM(nb),0) FROM purchased_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM refund_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM booking_payment)) AS total
)
INSERT INTO booking_payment
(user_id, booking_id, nb)
SELECT 1::INT, 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);
INSERT 0 1

Transaction 2

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
BEGIN ISOLATION LEVEL REPEATABLE READ;
 
WITH 
purchased_coupons AS
(SELECT nb FROM coupon_purchase WHERE user_id='1' FOR UPDATE),
refund_coupons AS
(SELECT nb FROM coupon_refund WHERE user_id='1' FOR UPDATE),
booking_payment AS
(SELECT nb FROM booking_payment WHERE user_id='1' FOR UPDATE),
coupon_balance AS 
(SELECT
  ((SELECT COALESCE(SUM(nb),0) FROM purchased_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM refund_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM booking_payment)) AS total
)
INSERT INTO coupon_refund
(user_id, nb)
SELECT 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);

// Transaction bloquée en attente du COMMIT ou ROLLBACK de la transaction 1.

Transaction 1

Code:

1
2
COMMIT;
COMMIT

Transaction 2

Code:

1
2
3
INSERT 0 1
COMMIT;
COMMIT

// L'insert pour le rembousement de coupons a quand même eu lieu.

Résultat en base:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select * from booking_payment;
 booking_payment_id | user_id | booking_id | nb 
--------------------+---------+------------+----
                  1 |       1 |          1 |  3
(1 row)
 
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
SELECT * FROM coupon_balance;
 total 
-------
    -3
(1 row)

La contrainte de balance > 0 n'est pas respectée car les deux transactions ont eu lieu.

Exemple 4 : ISOLATION LEVEL SERIALIZABLE avec SELECT ... FOR UPDATE

// Réinitialisation de la base comme pour le premier exemple

Transaction 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
BEGIN ISOLATION LEVEL SERIALIZABLE;
 
WITH 
purchased_coupons AS
(SELECT nb FROM coupon_purchase WHERE user_id='1' FOR UPDATE),
refund_coupons AS
(SELECT nb FROM coupon_refund WHERE user_id='1' FOR UPDATE),
booking_payment AS
(SELECT nb FROM booking_payment WHERE user_id='1' FOR UPDATE),
coupon_balance AS 
(SELECT
  ((SELECT COALESCE(SUM(nb),0) FROM purchased_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM refund_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM booking_payment)) AS total
)
INSERT INTO booking_payment
(user_id, booking_id, nb)
SELECT 1::INT, 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);

Transaction 2

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
BEGIN ISOLATION LEVEL SERIALIZABLE;
 
WITH 
purchased_coupons AS
(SELECT nb FROM coupon_purchase WHERE user_id='1' FOR UPDATE),
refund_coupons AS
(SELECT nb FROM coupon_refund WHERE user_id='1' FOR UPDATE),
booking_payment AS
(SELECT nb FROM booking_payment WHERE user_id='1' FOR UPDATE),
coupon_balance AS 
(SELECT
  ((SELECT COALESCE(SUM(nb),0) FROM purchased_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM refund_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM booking_payment)) AS total
)
INSERT INTO coupon_refund
(user_id, nb)
SELECT 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);

// Transaction bloquée en attente du résultat de la première transaction;

Transaction 1

Code:

1
2
COMMIT;
COMMIT

Transaction 2

Code:

1
2
3
4
5
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.
COMMIT;
ROLLBACK

Et en effet

Code:

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
 
SELECT * FROM booking_payment;
 booking_payment_id | user_id | booking_id | nb 
--------------------+---------+------------+----
                  1 |       1 |          1 |  3
(1 row)
 
WITH                     
purchased_coupons AS
(SELECT nb FROM coupon_purchase WHERE user_id='1' FOR UPDATE),
refund_coupons AS
(SELECT nb FROM coupon_refund WHERE user_id='1' FOR UPDATE),
booking_payment AS
(SELECT nb FROM booking_payment WHERE user_id='1' FOR UPDATE),
coupon_balance AS 
(SELECT
  ((SELECT COALESCE(SUM(nb),0) FROM purchased_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM refund_coupons) -
  (SELECT COALESCE(SUM(nb),0) FROM booking_payment)) AS total
)
SELECT * FROM coupon_balance;
 total 
-------
     0
(1 row)

Le booking payment a bien été effectué, le remboursement de coupons annulé et le solde est bien 0.

Donc la solution à mon probléme semble être un niveau d'isolation à SERIALIZABLE + des verrous sur les lignes du calcul du solde à l'aide de SELECT ... FOR UPDATE.

25/06/2015, 09h54
estofilo

Oui ça peut fonctionner avec le niveau d'isolation SERIALIZABLE et la logique de réessai des transactions.

Mais comme apparemment il s'agit de faire une exclusion mutuelle pour un user_id donné, il y a beaucoup plus simple.

Au tout début de la transaction de toute mise à jour des tables qui influent sur le solde, faire un UPDATE du style:

Code:

UPDATE table_users set date_modif=now() where user_id=le_user_id_en_question;

user_id étant la clef primaire de cette table.

Du fait du verrou au niveau ligne nécessaire à cet UPDATE, une transaction qui veut mettre à jour le solde sera bloquée à ce niveau là s'il y en a déjà une autre en train de mettre à jour, jusqu'au COMMIT ou ROLLBACK de cette transaction.

On appelle aussi ça une section critique : https://fr.wikipedia.org/wiki/Section_critique

@Estofilo:

Nice and easy. J'aime cette solution.

Je connais les sections critiques et la façon de les gérer dans le monde applicatif mais je ne connaissais pas ce "truc" pour le faire en BDD.
Ça paraît tellement évident maintenant que je comprends mieux les transactions et le principe d'isolation.
Par contre on est bien d'accord que je dois avoir une table par section critique à gérer?

Donc la solution complète à mon problème avec ce mécanisme donne:

Code:

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
DROP TABLE coupon_purchase;
DROP TABLE coupon_refund;
DROP TABLE booking_payment;
DROP TABLE lock_coupon_transaction;
 
CREATE TABLE coupon_purchase(
coupon_purchase_id SERIAL PRIMARY KEY,
user_id INT,
nb INT);
 
CREATE TABLE coupon_refund(
coupon_refund_id SERIAL PRIMARY KEY,
user_id INT,
nb INT);
 
CREATE TABLE booking_payment(
booking_payment_id SERIAL PRIMARY KEY,
user_id INT,
booking_id INT,
nb INT);
 
CREATE TABLE lock_coupon_transaction (
user_id INT,
timestamp TIMESTAMP
); 
 
INSERT INTO coupon_purchase
(user_id, nb) VALUES
(1, 1),
(1, 5);
 
INSERT INTO coupon_refund
(user_id, nb) VALUES
(1, 3);
 
INSERT INTO lock_coupon_transaction
(user_id, timestamp) VALUES
(1, current_timestamp);

Transaction 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
BEGIN;
 
UPDATE lock_coupon_transaction SET timestamp=current_timestamp WHERE user_id='1';
 
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
INSERT INTO booking_payment
(user_id, booking_id, nb)
SELECT 1::INT, 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);
INSERT 0 1

Transaction 2

Code:

1
2
3
4
5
BEGIN;
 
UPDATE lock_coupon_transaction SET timestamp=current_timestamp WHERE user_id='1';
 
// Transaction bloquée ici en attendant un COMMIT ou ROLLBACK de la transaction 1.

Transaction 1

Code:

1
2
COMMIT;
COMMIT

Transaction 2

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// Relâchement du verrou sur UPDATE de la transaction 1 donc la transaction 2 peut continuer
 
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
INSERT INTO coupon_refund
(user_id, nb)
SELECT 1::INT, 3::INT
FROM coupon_balance
WHERE (total::INT >= 3::INT);
INSERT 0 0 
 
COMMIT;
COMMIT

Vérification :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH coupon_balance AS (
SELECT
  t1.nb_purchased_coupons -
  t2.nb_refunded_coupons -
  t3.nb_booking_payment_coupons AS total
FROM 
(SELECT COALESCE(SUM(nb),0) AS nb_purchased_coupons FROM coupon_purchase WHERE user_id='1' ) t1,
(SELECT COALESCE(SUM(nb),0) AS nb_refunded_coupons FROM coupon_refund WHERE user_id='1' ) t2,
(SELECT COALESCE(SUM(nb),0) AS nb_booking_payment_coupons FROM booking_payment WHERE user_id='1' ) t3
)
SELECT * FROM coupon_balance;
 total 
-------
     0
(1 row)

La transaction 2 n'a pas pu procéder au remboursement par un INSERT dans la table coupon_refund car le solde en coupons était bien mis à jour et commité par la transaction 1. La contrainte est donc respectée!

Et ça marche dès le niveau d'isolation READ COMMITED.

Merci pour votre aide! :applo:

25/06/2015, 15h03
SQLpro

Et donc tu as résolu avec un mixte de fonctionnel et de transactionnel.

Le problème ici posé est intéressant car PG fonctionne nativement en mode optimiste de verrouillage.

Avec SQL Server, qui fonctionne nativement en verrouillage pessimiste, tu n'aurais pas eu besoin de faire cet UPDATE préventif. Il aurait suffit d'utiliser le REPEATABLE READ, sauf si tu avais placé la base en verrouillage optimiste ou entamer ta transaction avec le niveau d’isolation SNAPSHOT....

A +