Discussion :

[Paul TOTH]

Bonjour,

J'aimerai avoir un avis éclairé sur le fonctionnement des transactions sous Firebird

j'ai créé des objets Delphi pour utiliser l'API FB qui fonctionnent suivant le principe suivant

Code :

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

1
2
3
4
5
client = nouveau client FB
transaction = client.StartTransaction
query = transaction.PrepareSQL
query.Execute
transaction.commit

jusque là tout va bien, je peux même faire ceci

Code :

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

1
2
3
4
5
6
7
8
9
client = nouveau client FB
transaction = client.StartTransaction
query = transaction.PrepareSQL('SELECT ...')
update = transaction.PrepareSQL('UPDATE ...')
while Query.Fetch
  update.Params[0] = query.Fields[0]
  update.Execute
end
transaction.commit

mais que se passe-t-il si je veux commiter mes updates DANS la boucle ?

Code :

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

1
2
3
4
5
6
7
8
9
client = nouveau client FB
transaction = client.StartTransaction
query = transaction.PrepareSQL('SELECT ...')
update = transaction.PrepareSQL('UPDATE ...')
while Query.Fetch
  update.Params[0] = query.Fields[0]
  update.Execute
  transaction.commitRetaining
end

cela perturbe-t-il le SELECT ? faut-il ouvrir une nouvelle transaction pour l'UPDATE ?!

NB: la transaction possède les attributs isc_tpb_read_committed + isc_tpb_no_rec_version
transaction.commit

l'objet est ici de traiter une série d'enregistrement en attente de traitement et de mettre à jour un champ Etat, exemple SELECT ... WHERE Etat = 0, UPDATE ... Etat = 1

[TryExceptEnd]

Mais pourquoi une boucle ? alors qu'il suffit d'écrire une seule requête qui fait tout le boulot en une seule passe.

[Paul TOTH]

Mais pourquoi une boucle ? alors qu'il suffit d'écrire une seule requête qui fait tout le boulot en une seule passe.

merci pour cette remarque très pertinente...peux-tu s'il te plait me donner l'instruction SQL qui permet d'envoyer un SMS pour toutes les lignes qui on un Etat à 0, et passer cet Etat à 1 si l'envoie se déroule sans problème ?

Merci

[TryExceptEnd]

merci pour cette remarque très pertinente...peux-tu s'il te plait me donner l'instruction SQL qui permet d'envoyer un SMS pour toutes les lignes qui on un Etat à 0, et passer cet Etat à 1 si l'envoie se déroule sans problème ?

Merci

Envoyer un SMS par sql avec Firebird ? faut pas rêver !
et tu ne l'a pas mentionné dans ton post.
Effectivement dans ce cas il te faut une boucle ou tu commit chaque "update" avec une transaction indépendante de celle du "select".

[Paul TOTH]

Envoyer un SMS par sql avec Firebird ? faut pas rêver !
et tu ne l'a pas mentionné dans ton post.
Effectivement dans ce cas il te faut une boucle ou tu commit chaque "update" avec une transaction indépendante de celle du "select".

peu importe, la question était générique, mais merci pour la réponse.

[Artemus24]

Salut Paul TOTH.

mais que se passe-t-il si je veux commiter mes updates DANS la boucle ?
cela perturbe-t-il le SELECT ? faut-il ouvrir une nouvelle transaction pour l'UPDATE ?!

Admettons que le update vienne à perturber le select.
C'est une hypothèse de travail, ce n'est pas nécessaire la réalité.

Comment procéder ?

Pour résoudre ce problème, il faut faire deux boucles, une interne et une autre externe.

1) La boucle interne va gérer la taille de la grappe qui va subir le commit.
Pour ce faire, tu fais un select en retournant par exemple que seulement 50 lignes à chaque fois.
50 ligne est la taille que tu as choisi pour la grappe devant subit le commit.
Faire cela ligne par ligne est idiot car un commit prend du temps et risque de rallonger la durée de ton traitement.

Or tu dis dans le post #3, que l'état passe de 0 à 1, quand tu as fait le update.
Il va de soi que le select devra sélectionner les lignes ayant l'état à 0.
C'est une condition nécessaire au bon fonctionnement de ton select.

A la fin de la boucle interne, et bien tu fais ton "commit".
J'espère que tu comprends le principe de cette boucle.

2) que se passe-t-il entre le premier select qui va extraire tes premières 50 lignes et le suivant ?
Si ton update s'est bien passé (ce qui est le cas normal), le deuxième select ne doit pas donner les mêmes lignes.
En fait, il donne les première ligne ayant l'état à 0.
C'est-à-dire, les 50 lignes suivantes ayant l'état à 0, vis-à-vis du premier select.
Ce qui fait qu'à chaque fois que tu fais un update sur tes 50 lignes, l'état passe de 0 à 1.
Et de cela, le nouveau select va extraire les autres lignes ayant l'état à 0, et non celles qui viennent d'être traitées.

3) à quoi sert la première boucle externe ?
Comme la boucle interne traite, au maximum, que 50 lignes, il faut bien faire en sorte de passer au 50 lignes suivantes.

4) quand va-t-on terminer le traitement ?
Quand tu fais, dans la boucle interne, un select et que celui-ci retourne aucun ligne, alors tu as fini le traitement.
Dans ce cas, tu sors définitivement, de la boucle interne, mais aussi de la boucle externe.

5) est-ce que le commit va perturber ton select ?
Même si c'est le cas, tu t'aperçois que tu vas traiter une grappe de 50 lignes à chaque fois.
Donc s'il y perturbation, cela va se faire que sur les 50 premières lignes traités.
Et n'aura aucun impacte sur le reste de la table.

Au deuxième passage, donc après fait le premier commit, ton select va te donner les 50 premières lignes ayant l'état à 0.
En fait il va te donner les 50 lignes suivante, car les 50 premières lignes seront passées à l'état 1.

6) à quoi sert de procéder ainsi ?
C'es le traitement qui va gérer un plantage avec un point de reprise.
Imagine que dans ton traitement, tu plantes l'exécution quand tu te retrouves dans un cas que tu ne sais pas traiter.
Tu le signales par un beau message d'erreur, en spécifiant la nature exacte du problème.

Un utilisateur habilité intervient et va corriger cela.
Si le traitement est bien conçu, il ne va pas recommencer les modifications qui ont déjà été validées.
Il va commencé, juste après le dernier commit.

Imagine un seul instant que ton traitement dure plusieurs heures.
Est-ce que tu es d'accord pour recommencer depuis le début, surtout si le plantage s'est produit, disons cinq minutes avant la fin du traitement ?

Voilà le principe de la gestion d'un point de reprise.

@+

[Barbibulle]

Bonjour Paul,

cela perturbe-t-il le SELECT ?

Non, ce qui est lu ne changera pas (sauf si tu utilises le même cache local comme certains composants) mais dans tous les cas le nombre d'enregistrement ne changera pas car le Where est executé coté serveur.

faut-il ouvrir une nouvelle transaction pour l'UPDATE ?!

C'est une possibilité qui a ses avantages et inconvénients...

Tu liras un peu partout qu'il est préconisé d'utiliser une transaction longue en read (pour l'affichage de liste, ou d'écran utilisateur) et pour la mise à jour une transaction courte séparée.

L'avantage c'est :

• Une charge moins importante pour le serveur (maintenir une transaction longue contenant des mises à jour c'est plus lourd en milieu fortement concurrentiel)
• Moins de conflits lors des updates

Mais il faut savoir ce que ça impacte.
Notamment cela veut dire que c'est le dernier qui met a jour qui gagne. Sans précautions, la mise à jour se fera même si l'enregistrement mis à jour ne correspond pas à ce qui a été lu dans la transaction longue...

Pour ton traitement cela peut impliquer que tu envoies plusieurs fois le même SMS. (En imaginant que tu lances ton traitement plusieurs fois).

Concernant la transaction si tu n'utilises qu'une seule transaction pour la lecture et la mise à jour

Code :

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

1
2
3
read_committed 
no_rec_version
nowait

ne va pas empêcher non plus l'envoie de SMS en double si tu fais un CommitRetaining après chaque update. (toujours dans le cas d'un lancement concurrentiel de ton traitement).
Donc pour éviter qu'une seconde instance ne lise les données de la première il suffit de faire le commit qu'en fin de traitement. En effet une seconde instance voulant lire les SMS non envoyé lèvera une exception grâce à no_rec_version qui exige la dernière version des données. Cette dernière version étant en cours de modification (non commité) une exception est levée immédiatement (grâce au nowait).
On a bien ainsi un mécanisme empêchant l'envoie en double de SMS. Un et un seul traitement pourra travailler.
Nb : Il peut rester un risque d'envoie en double pour le premier SMS si on l'envoie avant l'update... Conclusion il faut commencer par un update (sans commit) envoyer le SMS et si besoin refaire un update si le SMS n'a pas été envoyé.

Cependant ce n'est pas très satisfaisant, voir même handicapant s'il y a beaucoup de SMS à envoyer, il n'y a qu'une instance qui pourra le faire.

Imaginons qu'on veuille pouvoir lancer plusieurs instances de notre programme, sans que cela envoie des SMS en double.
Voyons en utilisant deux transactions.

Comment définir la transaction de lecture qui va alimenter notre traitement ?
Cette liste est donc potentiellement traitée par d'autres instance, inutile donc de la faire échouer simplement parce qu'une autre instance est en train de faire des mises à jours.... On va donc assurer cette lecture en réclamant la dernière version "stable" des enregistrements => rec_version

Code :

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

1
2
3
4
read
read_committed
rec_version
nowait

Comment donc empêcher l'envoie en double ? Tout va se passer dans la seconde transaction de mise à jour unitaire.
On va utiliser le mécanisme de "locking conflict" pour "réserver" un SMS.
L'idée est donc de faire l'update juste avant l'envoie du SMS. Si l'update génère une exception (un lockconflict) c'est qu'une autre instance est en train d'envoyer ce sms. On passe donc au SMS suivant...

Un autre cas à traiter, le SMS a déjà été envoyé (ce n'est pas intuitif car on a demandé la liste des SMS non envoyé, mais il faut comprendre que cette liste on ne la rafraîchi pas...)

Pour traiter ce second cas il suffit de lire le SMS à traiter regarder si l'état est bien a 0 et faire l'update.
Mais il y a mieux, plus simple et sécurisé. Il suffit de modifier la clause where de l'update en y ajoutant "AND etat=0" et tester le nombre d'enregistrement mis à jour (si zéro mise à jour c'est que le SMS a déjà été envoyé (ou supprimé mais peux importe)).

La seconde transaction sera donc avec l'option no_rec_version :

Code :

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

1
2
3
read_committed
no_rec_version
nowait

et l'update :

Code :

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

1
2
3
4
update SMS S
set S.ETAT = 1
where (S.ID = :ID)
  and (S.ETAT=0);

Ainsi si l'update ne génère pas d'exception et le résultat de l'update nous indique bien une mise à jour on va envoyer le SMS si l'envoie échoue on Rollback sinon on commit.

Petit programme de test :
Attention j'ai utilisé les IBX dans cet exemple car je n'ai que ça à l'instant où j'écris mais le principe est le même et fonctionnera avec n'importe quelle connecteur permettant un paramétrage des transactions spécifique à FB.

Donc sous D7/IBX créer un nouveau projet, placer les composants
IBDatabase et le relier à la base de données,
deux IBTransaction (IBtransaction1 et IBTransactionUpdateEtat)
deux IBSQL (IBSQL1 et IBSQLUpdateEtat)
un Memo
et un bouton

IBSQL1 contient la requête

Code :

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

1
2
3
select S.ID, S.SMS_MESSAGE, S.ETAT, S.SMS_NUMERO
from SMS S
where s.etat=0;

et IBSQLUpdateEtat

Code :

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

1
2
3
4
update SMS S
set S.ETAT = 1
where (S.ID = :ID)
  and (S.ETAT=0);

Double clic sur IBtransaction1 et copier : (Attention ici pas de préfixe "isc_tpb_")

Code :

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

1
2
3
4
read
read_committed
rec_version
nowait

double clic sur IBTransactionUpdateEtat et copier :

Code :

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

1
2
3
read_committed
no_rec_version
nowait

Sur le clic du bouton :

Code PASCAL :

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
var i                    ,
    IndiceDernierMessage : integer;
begin
  Memo1.Clear;
 
  IBDatabase1.Connected := true;
 
  IBTransaction1.StartTransaction;  // transaction en lecture seule paramétrée comme suit :
  // read
  // read_committed
  // rec_version (et pas no_rec_version sinon il y a un gros risque d'avoir un lock conflict alors qu'on ne souhaite que "lire")
  // nowait
 
 
  IBSQL1.ExecQuery; // récupération de la liste de SMS à envoyer
 
  i := 1;
  while not IBSQL1.Eof do
  begin
 
    IndiceDernierMessage := Memo1.Lines.Add('i : ' + IntToStr(i) + ' - idSMS : ' + IBSQL1.FieldByName('ID').AsString);
    Application.ProcessMessages;
    try
      // ouverture de la transaction de mise à jour de l'état du SMS
      IBTransactionUpdateEtat.StartTransaction;
      // Transaction en écriture et cette fois on demande qu'un exception
      // soit levée si une autre transaction est en train de modifier notre
      // enregistrement. ->
      // read_committed
      // no_rec_version
      // nowait
 
      IBSQLUpdateEtat.ParamByName('ID').AsInt64 := IBSQL1.Fields[0].AsInt64;
      // Attention ici l'ordre SQL on va mettre dans la clause
      // WHERE en plus de l'ID du SMS la condition "AND ETAT=0"
      // car rien ne me dit que ce SMS n'a pas été traité par une autre instance
      // de ce traitement.
      IBSQLUpdateEtat.ExecQuery; // si le SMS est en cours d'envoie par un autre process il y aura une exception de levée
 
      if IBSQLUpdateEtat.RowsAffected=1 then // c'est que l'update c'est fait reste plus qu' a envoyer le SMS et a commit si Ok sinon rollback
      begin
        if SMSEnvoye( IBSQL1.FieldByName('SMS_NUMERO').AsString, IBSQL1.FieldByName('SMS_MESSAGE').AsString) then
        begin
          Memo1.Lines[IndiceDernierMessage] := Memo1.Lines[IndiceDernierMessage] + ' => SMS envoyé';
          IBTransactionUpdateEtat.Commit;
        end
        else
        begin
          Memo1.Lines[IndiceDernierMessage] := Memo1.Lines[IndiceDernierMessage] + ' => MS envoyé => ECHEC !!!';
          IBTransactionUpdateEtat.Rollback;
        end;
      end
      else
      begin
        Memo1.Lines[IndiceDernierMessage] := Memo1.Lines[IndiceDernierMessage] + ' => SMS déjà envoyé par un autre processus';
        IBTransactionUpdateEtat.Commit; // on a rien mis a jour mais on préfèrera le commit au rollback qui est plus lourd pour le serveur
      end;
    except
      on exception do // pour bien faire il faudrait mettre le bonne exception (flemme)
      begin  // si on arrive ici c'est qu'on a essayé de mettre à jour l'état du SMS en même temps qu'un autre processus (avant que celui-ci ne fasse le commit)
        if IBTransactionUpdateEtat.InTransaction then
          IBTransactionUpdateEtat.Rollback; 
        Memo1.Lines[IndiceDernierMessage] := Memo1.Lines[IndiceDernierMessage] + ' => SMS en cours d''envoi par un autre processus';
      end;
    end;
    IBSQL1.Next;
  end;
  IBTransaction1.Commit;
 
end;

Pour simuler l'envoi de SMS j'ai créer une fonction qui va durrer plus ou moins longtemps et renvoyer un boolean de valeur aléatoire qui indiquera la réussite ou non

Code PASCAL :

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

1
2
3
4
5
function SMSEnvoye(numeroSMS, messageSMS: string): boolean;
begin
  Sleep(random(1000)); // simulation d'une durée pouvant aller jusqu'à une seconde histoire qu'on puisse voir ce qui se passe...
  result := ( random(10) < 2); // simulation d'échec d'envoi de SMS.
end;

Coté base de données de test j'ai créé une table SMS comme suit :

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
CREATE GENERATOR GEN_SMS_ID;
 
CREATE TABLE SMS (
    ID    INTEGER NOT NULL,
    SMS_MESSAGE  VARCHAR(155),
    SMS_NUMERO varchar(15),
    ETAT  SMALLINT
);
 
 
ALTER TABLE SMS ADD CONSTRAINT PK_SMS PRIMARY KEY (ID);
 
 
SET TERM ^ ;
 
 
/* Trigger: SMS_BI */
CREATE OR ALTER TRIGGER SMS_BI FOR SMS
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_sms_id,1);
end
^
 
SET TERM ; ^

Que j'ai rempli d'une centaine de SMS avec un etat =0.

Voilà on compile, on lance deux instances ou plus et on appuie sur les boutons de chacune d'elle pour lancer les traitements concurrents.
Et on constate que les SMS sont effectivement envoyés qu'une seule fois (ou pas du tout si c'est la procédure d'envoie du SMS qui a échoué).

Merci de m'avoir lu, jusqu'ici