Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Modérateur
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    16 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 16 013
    Points : 31 731
    Points
    31 731
    Billets dans le blog
    5

    Par défaut Enchaînement de procédures avec des commits et rollbacks.

    Bonjour,

    Soit un enchaînement de procédures pour créer un candidat qui est une personne-physique :

    Algorithmiquement dans la procédure de création du candidat :
    1) Si l'identifiant de la personne physique est null ou n'existe pas, j'appelle la procédure de création d'une personne physique.
    2) La procédure de création d'une personne physique contient une transaction qui rollback en cas d'erreur sur une clé étrangère fournie ou qui commit si tout va bien. Elle renvoie alors l'identifiant de la personne créée.
    3) On vérifie d'autres données nécessaires à la création du candidat puis on enregistre le candidat. Il peut là aussi y avoir des erreurs de clés étrangères qui sont capturées en tant qu'exception et la procédure rollback ou commit si tout va bien.

    Si je démarre la transaction dans la procédure de création du candidat, un éventuel rollback va t-il annuler la création de la personne physique ?
    Autrement dit, est-ce que je peux encapsuler les transactions par le jeu de l'enchaînement des procédures ?
    Sinon, dois-je gérer la transaction globale en dehors des procédures ?

    Dans l'application, je peux être amené à :
    - créer un utilisateur qui entraîne la création d'une personne physique ;
    - créer un candidat qui entraîne la création d'une personne physique ;
    - créer un étudiant qui entraîne la création d'un candidat qui entraîne la création d'une personne physique ;
    - créer un autre type de personne qui entraîne la création d'une personne physique...

    La procédure de création d'une personne physique ajoute d'abord une personne (qui peut aussi être une personne morale) puis une personne physique.
    J'y ai mis une transaction pour rollback la création de la personne en cas d'erreur lors de la création de la personne physique.

    En principe, je ne créerai jamais de personne physique sans passer par la création du type de cette personne (utilisateur, candidat, étudiant...) mais je n'aime pas l'idée qu'on puisse le faire vu l'existence de la procédure.

    Bref, j'aimerais avoir vos avis sur la meilleur façon de gérer ça en empêchant de créer des personnes inutiles dans la BDD.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 321
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 321
    Points : 42 824
    Points
    42 824

    Par défaut

    Il faut que tu regarde par un simple test si le modèle de gestion des transactions est symétrique ou asymétrique.
    Symétrique => autant de finalisation (COMMIT ou ROLLBACK) que de BGIN
    Asymétrique => le premier ROLLBACK met fin à l'état transactionnel

    Pour info c'est le modèle asymétrique qui a été utilisé par Microsoft SQL Server, c'est le plus sage et le plus performant. Mais il faut connaître l'état transactionnel par un moyen ou un autre. Dans MS SQL Server tu peut le savoir avec la fonction XACT_STATE() qui renvoie 0 si la transaction a été finalisée.

    Cherche dans la doc MySQL l'équivalent.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #3
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 681
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 78
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 681
    Points : 11 301
    Points
    11 301

    Par défaut

    Salut à tous.

    Citation Envoyé par CinePhil
    Si je démarre la transaction dans la procédure de création du candidat, un éventuel rollback va t-il annuler la création de la personne physique ?
    Je ne sais pas trop comment vous gérez vos transaction, mais normalement un "commit" valide tout depuis le dernier "start transaction".
    Même remarque pour le "rollback" où celui-ci détricote tout depuis le dernier "start transaction".
    En fait, la structure est la suivante :
    --> au début, on fait un "start transaction".
    --> à la fin, on fait soit un "commit" soit un "rollback".

    Ceci correspond à :
    Citation Envoyé par SQLPRO
    Asymétrique => le premier ROLLBACK met fin à l'état transactionnel
    Une remarque importante : il n'est pas possible de démarrer une transaction à l'intérieur d'une autre transaction !

    La structure de vos transactions est la suivante :
    (1) --> start transaction
    (1) --> commit ou rollback
    (2) --> start transaction
    (2) --> commit ou rollback
    (3) --> start transaction
    (3) --> commit ou rollback

    Par contre, il est interdit de faire cela :

    (1) --> start transaction
    (2) --> start transaction
    (2) --> commit ou rollback
    (1) --> commit ou rollback

    Sauf que cette explication est dans le mode de fonctionne de base.
    Il existe ce que l'on nomme un point de sauvegarde ("savepoint"), qui permet de gérer cela !
    On peut annuler tout ce qui a été fait entre ce point et le "rollback to savepoint nom_du_savepoint".
    Je ne sais pas trop si cela correspond à votre attente, à savoir l'encapsulation de vos grappes de données.
    Pour supprimer un point de sauvegarde, il suffit de faire un "release nom_du_savepoint".

    Citation Envoyé par CinePhil
    Autrement dit, est-ce que je peux encapsuler les transactions par le jeu de l'enchaînement des procédures ?
    Comme je l'ai dit précédemment, ce n'est pas possible, sauf si vous utilisez un "savepoint"."

    Citation Envoyé par CinePhil
    Sinon, dois-je gérer la transaction globale en dehors des procédures ?
    Il n'est pas nécessaire de le faire à l'extérieur d'une procédure.
    En fait, comment gérez-vous votre grappe de données ?

    Citation Envoyé par CinePhil
    Dans l'application, je peux être amené à :
    - créer un utilisateur qui entraîne la création d'une personne physique ;
    - créer un candidat qui entraîne la création d'une personne physique ;
    - créer un étudiant qui entraîne la création d'un candidat qui entraîne la création d'une personne physique ;
    - créer un autre type de personne qui entraîne la création d'une personne physique...
    Ça, c'est une grappe de données.

    Si vous rencontrez un problème sur la dernière création, vous êtes obligé de tout annuler.

    Citation Envoyé par CinePhil
    Bref, j'aimerais avoir vos avis sur la meilleur façon de gérer ça en empêchant de créer des personnes inutiles dans la BDD.
    La règle de base est de gérer votre grappe de données dans sa globalité. A savoir, on valide tout ou bien on rejette tout !

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  4. #4
    Modérateur
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    16 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 16 013
    Points : 31 731
    Points
    31 731
    Billets dans le blog
    5

    Par défaut

    Merci Artemus !
    Une remarque importante : il n'est pas possible de démarrer une transaction à l'intérieur d'une autre transaction !
    Je m'en doutais un peu mais je n'étais pas sûr, du fait que les transactions sont dans des procédures différentes.

    Il existe ce que l'on nomme un point de sauvegarde ("savepoint"), qui permet de gérer cela !
    On peut annuler tout ce qui a été fait entre ce point et le "rollback to savepoint nom_du_savepoint".
    Je ne sais pas trop si cela correspond à votre attente, à savoir l'encapsulation de vos grappes de données.
    Pour supprimer un point de sauvegarde, il suffit de faire un "release nom_du_savepoint".
    Il est bien possible que ce soit ce qu'il me faut, en effet.

    Ça, c'est une grappe de données.

    Si vous rencontrez un problème sur la dernière création, vous êtes obligé de tout annuler.
    C'est bien ça que je veux, en effet.

    En simplifiant, voici mon déroulement actuel :
    Procédure A
    ************ Emplacement potentiel d'un START TRANSACTION ***************
    => selon une condition, appelle une procédure B
    -- Procédure B
    -- Start transaction
    -- Ajoute une ligne dans une table
    -- Utilise l'ID du premier ajout pour ajouter dans une seconde table
    -- Si erreur de clé étrangère lors de l'ajout dans la seconde table -> Rollback et SIGNAL pour arrêter le déroulement
    -- Sinon -> Commit et retour à la procédure A
    ************ Emplacement potentiel d'un START TRANSACTION ***************
    Ajoute des données dans une troisième table
    Si erreur de clé étrangère lors de l'ajout dans la seconde table -> Rollback et SIGNAL pour arrêter le déroulement
    Sinon -> Commit

    L'idéal serait que le ROLLBACK de la procédure A supprime aussi ce qui a été fait dans A si on a fait des choses dans A.

    Si j'ai bien compris, le ne peux pas placer le START TRANSACTION de A au début parce qu'il y en a potentiellement un autre imbriqué par l'appel à la procédure B ?
    Et si je le mets plus loin, après l'appel éventuel à B, le ROLLBACK de A ne va pas supprimer ce qui a été fait dans B, c'est ça ?

    Si je comprends bien l'astuce du SAVEPOINT, il faudrait le mettre en début de procédure A et un ROLLBACK TO SAVEPOINT dans A permettrait d'annuler ce qu'a COMMITé la procédure B ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 321
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 321
    Points : 42 824
    Points
    42 824

    Par défaut

    En fait la technique des SAVEPOINT n'a en pratique aucun intérêt.

    Cela en aurais un dans le cas d'une programmation par contrainte ou l'algorithme de backtracking ne remonterait pas jusqu'à la racine de l'arbre de décision.

    Test ce que je t'ais dit car je met en doute les propos d'Artemus…. En effet il dit :
    Par contre, il est interdit de faire cela :

    (1) --> start transaction
    (2) --> start transaction
    (2) --> commit ou rollback
    (1) --> commit ou rollback
    Cela provoque t-il une erreur ? Si oui, laquelle ???

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  6. #6
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 681
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 78
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 681
    Points : 11 301
    Points
    11 301

    Par défaut

    Salut CINPHIL.

    Voici un exemple de procédure stockée qui gère une transaction.
    J'ai créé pour l'exemple deux procédure de maj, et où je fais le passage d'une code retour afin de voir le comportement.
    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
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    COMMIT
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE test
    ( `id`   integer unsigned NOT NULL auto_increment primary key,
      `val`  varchar(255)              default NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    DROP PROCEDURE IF EXISTS `maj1`
    --------------
     
    --------------
    CREATE PROCEDURE `maj1` (INOUT _codret integer)
    BEGIN
      insert into `test` (`val`) values ('dix'),('onze'),('douze');
     
      IF _codret <> 0 THEN
         rollback;
         SIGNAL SQLSTATE VALUE '08888' SET MESSAGE_TEXT = 'procédure "MAJ 1" rollback';
      END IF;
    END
    --------------
     
    --------------
    DROP PROCEDURE IF EXISTS `maj2`
    --------------
     
    --------------
    CREATE PROCEDURE `maj2` (INOUT _codret integer)
    BEGIN
      insert into `test` (`val`) values ('vingt'),('vingt-et-un'),('vingt-deux');
     
      IF _codret <> 0 THEN
         rollback;
         SIGNAL SQLSTATE VALUE '08888' SET MESSAGE_TEXT = 'procédure "MAJ 2" rollback';
      END IF;
    END
    --------------
     
    --------------
    DROP PROCEDURE IF EXISTS `test`
    --------------
     
    --------------
    CREATE PROCEDURE `test` (IN _maj1 integer,
                             IN _maj2 integer)
    BEGIN
      DECLARE _codret integer DEFAULT NULL;
     
      set autocommit = 0;
      start transaction;
     
      main:begin
        SET _codret = _maj1;
     
        call `maj1` (_codret);
     
        IF (_codret <> 0) THEN
           LEAVE MAIN;
        END IF;
     
        SET _codret = _maj2;
     
        call `maj2` (_codret);
     
        IF (_codret <> 0) THEN
           LEAVE MAIN;
        END IF;
     
        COMMIT;
      END main;
     
    END
    --------------
     
    --------------
    INSERT INTO test (`val`) VALUES ('un'),('deux'),('trois')
    --------------
     
    --------------
    commit
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    call test(1,0)
    --------------
     
    ERROR 1644 (08888) at line 112: procédure "MAJ 1" rollback
    --------------
    select * from test
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    call test(0,1)
    --------------
     
    ERROR 1644 (08888) at line 116: procédure "MAJ 2" rollback
    --------------
    select * from test
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    call test(0,0)
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+-------------+
    | id | val         |
    +----+-------------+
    |  1 | un          |
    |  2 | deux        |
    |  3 | trois       |
    | 13 | dix         |
    | 14 | onze        |
    | 15 | douze       |
    | 16 | vingt       |
    | 17 | vingt-et-un |
    | 18 | vingt-deux  |
    +----+-------------+
     
    Appuyez sur une touche pour continuer...
    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  7. #7
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 681
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 78
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 681
    Points : 11 301
    Points
    11 301

    Par défaut

    Salut SQLPRO.

    Est-ce que cet exemple suffit :
    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
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test`
    ( `id`   integer  unsigned  NOT NULL auto_increment primary key,
      `lib`  varchar(255)       NOT NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    INSERT INTO `test` (`lib`) VALUES
      ('un'),('deux'),('trois')
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    start transaction
    --------------
     
    --------------
    INSERT INTO `test` (`lib`) VALUES
      ('dix'),('onze'),('douze')
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    |  4 | dix   |
    |  5 | onze  |
    |  6 | douze |
    +----+-------+
    --------------
    rollback
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    rollback
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
     
    Appuyez sur une touche pour continuer...
    Le second "start transaction" se comporte comme un "commit".

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  8. #8
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 321
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 321
    Points : 42 824
    Points
    42 824

    Par défaut

    Citation Envoyé par Artemus24 Voir le message
    Salut SQLPRO.

    Est-ce que cet exemple suffit : ...
    Oui, il est suffisant, mais montre surtout que MySQL ne sait pas gérer des transactions, car il a tout simplement "oublié" la première transaction...

    Dès qu'il y a démarrage d'une transaction explicite il doit y avoir impérativement un moyen de la valider ou de la supprimer. Or le second START TRANSACTION reporte la transaction. Au final les 3 premières lignes sont "oubliées" au niveau transactionnel et se retrouvent définitivement dans la table.
    C'est évidemment un défaut, pour ne pas dire un BUG !

    Les deux modèle transactionnels symétrique et asymétrique doivent avoir les comportement suivants...

    1) SYMÉTRIQUE

    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
    49
    50
    51
    START TRANSACTION;
     
    INSERT INTO test (lib) VALUES ('un'),('deux'),('trois');
     
    select * from test;
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
     
    start transaction;
     
    INSERT INTO test (lib) VALUES ('dix'),('onze'),('douze');
     
    select * from test
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    |  4 | dix   |
    |  5 | onze  |
    |  6 | douze |
    +----+-------+
     
    rollback;
     
    select * from test;
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
     
    rollback;
     
    select * from test;
     
    +----+-------+
    | id | lib   |
    +----+-------+
    +----+-------+

    2) ASYMÉTRIQUE

    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
    START TRANSACTION;
     
    INSERT INTO test (lib) VALUES ('un'),('deux'),('trois');
     
    select * from test;
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
     
    start transaction;
     
    INSERT INTO test (lib) VALUES ('dix'),('onze'),('douze');
     
    select * from test
     
    +----+-------+
    | id | lib   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    |  4 | dix   |
    |  5 | onze  |
    |  6 | douze |
    +----+-------+
     
    rollback;
     
    select * from test;
     
    +----+-------+
    | id | lib   |
    +----+-------+
    +----+-------+
     
    rollback;
     
    --> erreur : il n'y a pas de transaction à annuler !
    Bref encore une fois MySQL montre que c'est une grosse merde au niveau transactionnel !

    Ce que confirme Wikipedia d'ailleurs :
    popular databases like MySQL[1] do not allow nesting BEGIN - COMMIT
    Le problème c'est qu'il le permet bien, mais que ça donne un résultat faux !
    Le minimum aurait été qu'il envoie une erreur au niveau du 2e START TRANSACTION...


    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  9. #9
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 681
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 78
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 681
    Points : 11 301
    Points
    11 301

    Par défaut

    Salut SQLPRO.

    Citation Envoyé par SQLPRO
    C'est évidemment un défaut, pour ne pas dire un BUG !
    Je suis d'accord avec vous !

    Mais avec un "savepoint", on gère les transactions imbriquées !
    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
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE test
    ( `id`   integer unsigned NOT NULL auto_increment primary key,
      `val`  varchar(255)              default NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    COMMIT
    --------------
     
    --------------
    start transaction
    --------------
     
    --------------
    insert into `test` (`val`) values ('un'),('deux'),('trois')
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    savepoint un
    --------------
     
    --------------
    insert into `test` (`val`) values ('dix'),('onze'),('douze')
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    |  4 | dix   |
    |  5 | onze  |
    |  6 | douze |
    +----+-------+
    --------------
    rollback to un
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    savepoint deux
    --------------
     
    --------------
    insert into `test` (`val`) values ('vingt'),('vingt-et-un'),('vingt-deux')
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------------+
    | id | val         |
    +----+-------------+
    |  1 | un          |
    |  2 | deux        |
    |  3 | trois       |
    |  7 | vingt       |
    |  8 | vingt-et-un |
    |  9 | vingt-deux  |
    +----+-------------+
    --------------
    rollback to deux
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    rollback
    --------------
     
    --------------
    select * from `test`
    --------------
     
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
    Appuyez sur une touche pour continuer...
    Là où j'ai un gros doute, c'est son utilisation dans une applications multitâches.

    Sinon, dans un traitement mono-utilisateur monotâche, oui, cela fonctionne mais je ne vois pas trop son intérêt.
    En fait, je n'ai jamais eu besoin jusqu'à présent du savepoint, ni non plus des transactions imbriquées.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  10. #10
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 321
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 321
    Points : 42 824
    Points
    42 824

    Par défaut

    Citation Envoyé par Artemus24 Voir le message
    Sinon, dans un traitement mono-utilisateur monotâche, oui, cela fonctionne mais je ne vois pas trop son intérêt.
    En fait, je n'ai jamais eu besoin jusqu'à présent du savepoint, ni non plus des transactions imbriquées.

    @+
    Parce que vous n'utilisez pas les procédures stockées...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  11. #11
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 681
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 78
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 681
    Points : 11 301
    Points
    11 301

    Par défaut

    Salut à tous.

    @ SQLPRO : c'est vrai, je ne les utilise pas.

    @ CinePhil : est-ce que mon exemple (la procédure stockée) vous convient-il ?

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  12. #12
    Modérateur
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    16 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 16 013
    Points : 31 731
    Points
    31 731
    Billets dans le blog
    5

    Par défaut

    @ CinePhil : est-ce que mon exemple (la procédure stockée) vous convient-il ?
    Je testerai ça demain en télétravail ou tout à l'heure si j'arrive à me dépêtrer d'un autre problème de modif d'un vieux programme java.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  13. #13
    Modérateur
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    16 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 16 013
    Points : 31 731
    Points
    31 731
    Billets dans le blog
    5

    Par défaut

    Voilà ce que j'ai testé...

    Extrait de la procédure de création d'une personne physique :
    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
    CREATE OR REPLACE PROCEDURE pi_ajout_personne_physique
    (
        IN nom VARCHAR(48),
        IN prenom VARCHAR(32),
    	IN id_civilite INT,
        OUT id_personne INT
    )
    BEGIN
    	-- Handler d'erreur si l'identifiant de la civilité n'existe pas dans la table des civilités tr_civilite_civ
    	DECLARE fk_exception CONDITION FOR SQLSTATE '23000'; -- Erreur de clé étrangère
    	DECLARE EXIT HANDLER FOR fk_exception
    		BEGIN
    			ROLLBACK;
    			SET AUTOCOMMIT = 1;
    			SIGNAL SQLSTATE VALUE '23000'
    			SET MESSAGE_TEXT = 'Identifiant de civilité non valide';
    		END;
     
    	/***** Vérification des données d'entrée *****/
    	-- Nom vide ou trop court
    	IF(nom IS NULL OR LENGTH(nom) < 2) THEN 
    		SIGNAL SQLSTATE '45000' 
    		SET MESSAGE_TEXT = 'Nom vide ou trop court';
    	END IF;
     
    	-- Prénom vide ou trop court
    	-- Civilité nulle => id_civilite = 0 (inconnue)
    	/***** Enregistrement des données *****/
    	SET AUTOCOMMIT = 0;
    	START TRANSACTION;
     
    	-- Insertion dans la table des personnes
        INSERT INTO te_personne_prs (prs_nom)
        VALUES (nom);
     
        -- Récupération de l'identifiant auto_incrémenté créé
        SET id_personne = LAST_INSERT_ID();
     
        -- Insertion dans la table des personnes physiques
        INSERT INTO th_personne_physique_pph (pph_id_personne, pph_id_civilite, pph_prenom)
        VALUES (id_personne, id_civilite, prenom);
     
        COMMIT;
    	SET AUTOCOMMIT = 1;
    END;
    => J'ai donc un ROLLBACK interne en cas d'erreur de clé étrangère (sur l'identifiant de la civilité)

    Extrait de la procédure de création d'un candidat (qui hérite d'une personne physique) :
    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
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    CREATE OR REPLACE PROCEDURE pi_ajout_candidat
    (
    	INOUT id_personne_physique INT, -- Identifiant du candidat
    	IN nom_usuel VARCHAR(48), -- Nom usuel du candidat
        IN prenom VARCHAR(32), -- Prénom principal candidat
    	IN id_civilite INT, -- Identifiant de la civilité du candidat
    	IN id_sexe INT, -- Identifiant du sexe du candidat
    	IN id_nationalite INT, -- Identifiant du pays de nationalité du candidat
    	IN id_ville_naissance INT, -- Identifiant de la ville de naissance du candidat
    	IN nom_naissance VARCHAR(48), -- Nom de naissance du candidat
    	IN date_naissance DATE, -- Date de naissance du candidat
    	IN autres_prenoms VARCHAR(48), -- Autres prénoms du candidat (séparés par une virgule et un espace)
    	IN num_ine CHAR(11) -- Numéro INE du candidat
    )
    BEGIN
    	DECLARE nombre INT DEFAULT 0; -- Utilisé pour les comptages
    	DECLARE msg_erreur VARCHAR(64); -- Texte du message d'erreur éventuel retourné
    	-- Handler d'erreur de clé étrangère non satisfaite
    	DECLARE fk_exception CONDITION FOR SQLSTATE '23000'; -- Erreur de clé étrangère
    	DECLARE EXIT HANDLER FOR fk_exception
    		BEGIN
    			ROLLBACK TO SAVEPOINT debut;
    			SIGNAL SQLSTATE VALUE '23000'
    			SET MESSAGE_TEXT = msg_erreur;
    		END;
     
    	-- On vérifie par les infos fournies si le candidat existe déjà
    	-- (identifiant du candidat, INE ou triplet {nom (usuel ou naissance), prénom usuel, date naissance})
    	SELECT COUNT(*) INTO nombre
    	FROM v_candidat
    	WHERE canId = id_personne_physique
    		OR canIne = num_ine
    		OR
    		(
    			canPrenomUsuel = prenom
    			AND canDateNaissance = date_naissance
    			AND 
    			(
    				canNomUsuel = nom_usuel
    				OR canNomNaissance = nom_naissance
    			)
    		);
     
    	IF nombre > 0 THEN 
    		-- Candidat existant => erreur
    		SIGNAL SQLSTATE '45000'
    		SET MESSAGE_TEXT = 'Candidat déjà existant';
    	END IF;
     
    	/***** Vérification et traitement des données *****/
    	SET AUTOCOMMIT = 0;
    	START TRANSACTION;
    	SAVEPOINT debut;
     
    	IF id_personne_physique IS NULL THEN
    	-- Si l'identifiant de la personne n'est pas fourni, on lance la procédure d'ajout d'une personne physique
    		CALL pi_ajout_personne_physique(nom_usuel, prenom, id_civilite, @id);
    		SET id_personne_physique = @id;
    	ELSE
    		-- On vérifie que l'id_personne_physique fourni existe en tant que personne physique
    		SELECT COUNT(*) INTO nombre
    		FROM th_personne_physique_pph
    		WHERE pph_id_personne = id_personne_physique;
     
    		IF nombre = 0 THEN
    			-- Personne non trouvée, on lance la procédure d'ajout d'une personne physique
    			CALL pi_ajout_personne_physique(nom_usuel, prenom, id_civilite, @id);
    			SET id_personne_physique = @id;
    		END IF;
    	END IF; -- Fin du ELSE de IF id_personne_physique IS NULL THEN
     
    	-- Vérification de l'id_sexe
    	-- Vérification de la nationalité
    	-- Vérification de la ville de naissance
    	-- Vérification du nom de naissance
    	-- Vérification de la cohérence de la date de naissance (age >= 18)
    	-- Vérification du numéro d'INE (11 caractères oblibatoires si non null ou vide)
    	-- Enregistrement du candidat
    	INSERT INTO th_candidat_can (can_id_personne_physique, can_id_sexe, can_id_nationalite, 
    		can_id_ville_naissance, can_nom_naissance, can_date_naissance, can_autres_prenoms, can_ine)
    	VALUES (id_personne_physique, id_sexe, id_nationalite, id_ville_naissance, nom_naissance, date_naissance,
    		autres_prenoms, num_ine);
     
        COMMIT;
    	SET AUTOCOMMIT = 1;
    END;
    => J'ai placé un SAVEPOINT avant tout enregistrement même via la procédure de création d'une personne physique.

    Mes tests jusqu'à présent :
    1) Cas normal avec un identifiant => OK
    2) Cas normal sans identifiant mais avec de bonnes données complètes => OK
    3) Soumission du même candidat via identifiant ou triplet de données {nom, prénom, date de naissance} => SIGNAL déclenché => OK
    4) Soumission d'un candidat sans identifiant et avec un id_civilité faux => KO :
    #1305 - SAVEPOINT debut does not exist
    L'erreur se produit dans la procédure de création d'une personne physique mais c'est le HANDLER de la procédure de création d'un candidat qui cherche à retourner à SAVEPOINT debut.
    Ou alors, le ROLLBACK de la procédure de création d'une personne physique opère normalement puis ça revient à la procédure de création d'un candidat qui récupère l'erreur et déclenche son HANDLER... qui ne trouve pas le SAVEPOINT debut alors qu'il est pourtant là ?

    En joutant un SAVEPOINT debut et un ROLLBACK TO SAVEPOINT debut dans procédure de création d'une personne physique, j'obtiens le même résultat.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  14. #14
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 321
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 321
    Points : 42 824
    Points
    42 824

    Par défaut

    Non, les savepoints n'ont pas du tout le rôle de simuler les transactions imbriquées. C'est juste de pouvoir revenir en arrière à une certaines étape. En pratique, ça sert pas vraiment !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  15. #15
    Modérateur
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    16 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 16 013
    Points : 31 731
    Points
    31 731
    Billets dans le blog
    5

    Par défaut

    Donc mon problème reste entier...

    Si j'ai une erreur dans la procédure principale, comment annuler ce qui a été fait dans la procédure secondaire ?
    Faut-il le programmer ?
    Un truc du genre DELETE FROM te_personne_prs WHERE prs_id = id_personne_physique dans le HANDLER ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  16. #16
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 321
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 321
    Points : 42 824
    Points
    42 824

    Par défaut

    Il n'y a pas de solution simple.

    Vous pouvez tenter, mais rien ne permet de garantir que le retour arrière soit possible parce que entre temps un autre utilisateur peut avoir fait une mise à jour des mêmes données !!!!

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  17. #17
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    3 681
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 78
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 3 681
    Points : 11 301
    Points
    11 301

    Par défaut

    Salut à tous.

    @ CinePhil : en fait, vous ne savez pas utiliser correctement "signal".

    Voici quelques remarques :

    1) il est inutile d'utiliser un savepoint car cela ne simule pas un "start transaction".

    2) dans votre procédure stockée, au début vous devez mettre :
    Cela doit apparaître une et une seule fois dans la procédure stockée !

    3) dans votre procédure stockée, à la fin, vous devez mettre :
    Cela doit apparaître une et une seule fois dans la procédure stockée !

    4) il y aura autant de "signal" que vous aurez de messages d'erreurs.
    Pour chaque signal, vous devez obligatoirement faire un "rollback" juste avant.
    Le signal provoque la sortir de la procédure stockée. Tout ce qui est après est ignoré.
    Pour que le signal soit compréhensible, vous devez associer un message d'anomalie avec son numéro d'identification (errno).

    5) au début de la procédure stockée, vous devez déclarer :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
      DECLARE abort CONDITION FOR SQLSTATE '23000';
      DECLARE EXIT HANDLER for abort
      BEGIN
        ROLLBACK;
        RESIGNAL;
      END;
    Le SQLSTATE que vous allez gérer aura toujours la même valeur '23000'.
    Ce n'est pas par lui que vous identifiez vos différents message, mais par MYSQL_ERRNO.

    Il est important de ne pas se tromper dans le SQLSTATE car cela provoque soit un warning ou soit un fatal error.
    Je vous laisse chercher les différentes valeurs de SQLSTATE et leur signification.

    Le "resignal" affiche le message (MESSAGE_TEXT) ainsi que son numéro (MYSQL_ERRNO). Sans resignal, le message n’apparaît pas.
    Le "rollback" va provoquer le rejet de tout ce que vous aurez fait après le "start transaction".

    6) quand vous jugerez avoir une erreur, faites ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SIGNAL abort SET MESSAGE_TEXT='bla bla bla', MYSQL_ERRNO=15;
    Le fait de mettre 'abort' va obligatoirement se référencer au HANDLER que vous avez déclaré au début de la procédure.
    Et donc, va exécuter le HANDLER, c'est-à-dire faire un "rollback" puis un "resignal".

    7) voici un exemple détaillant tout ce que je viens de dire :
    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
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    COMMIT
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE test
    ( `id`   integer unsigned NOT NULL auto_increment primary key,
      `val`  varchar(255)              default NULL
    ) ENGINE=InnoDB
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
      ROW_FORMAT=COMPRESSED
    --------------
     
    --------------
    DROP PROCEDURE IF EXISTS `test`
    --------------
     
    --------------
    CREATE PROCEDURE `test` (IN _maj1 integer,
                             IN _maj2 integer)
    BEGIN
      DECLARE fail CONDITION FOR SQLSTATE '23000';
      DECLARE EXIT HANDLER for fail
      BEGIN
        ROLLBACK;
        RESIGNAL;
      END;
     
      start transaction;
     
      insert into `test` (`val`) values ('dix'),('onze'),('douze');
     
      if _maj1 <> 0 then
         signal fail set MESSAGE_TEXT = 'Votre premier message !', MYSQL_ERRNO = 1;
      end if;
     
      insert into `test` (`val`) values ('vingt'),('vingt-et-un'),('vingt-deux');
     
      if _maj2 <> 0 then
         signal fail set MESSAGE_TEXT = 'Votre deuxième message !', MYSQL_ERRNO = 2;
      end if;
     
      COMMIT;
    END
    --------------
     
    --------------
    INSERT INTO test (`val`) VALUES ('un'),('deux'),('trois')
    --------------
     
    --------------
    commit
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    call test(1,0)
    --------------
     
    ERROR 1 (23000) at line 85: Votre premier message !
    --------------
    select * from test
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    call test(0,1)
    --------------
     
    ERROR 2 (23000) at line 88: Votre deuxième message !
    --------------
    select * from test
    --------------
     
    +----+-------+
    | id | val   |
    +----+-------+
    |  1 | un    |
    |  2 | deux  |
    |  3 | trois |
    +----+-------+
    --------------
    call test(0,0)
    --------------
     
    --------------
    select * from test
    --------------
     
    +----+-------------+
    | id | val         |
    +----+-------------+
    |  1 | un          |
    |  2 | deux        |
    |  3 | trois       |
    | 13 | dix         |
    | 14 | onze        |
    | 15 | douze       |
    | 16 | vingt       |
    | 17 | vingt-et-un |
    | 18 | vingt-deux  |
    +----+-------------+
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
    Appuyez sur une touche pour continuer...
    Cet exemple est correcte ! J'ai simulé deux erreurs, l'une sur le premier insert et l'autre sur le second insert. Le troisième cas valide les insertions.
    D'une part il y a bien le rejet de ce qui a été inséré précédemment et d'autre part l'affichage d'un message associé à l'erreur.

    8) il n'est pas nécessaire de mettre partout "set autocommit=1" ou "set autocommit=0".
    Peu importe la valeur de l'autocommit avant le "start transaction".
    Quand la transaction démarre, l'autocommit est annulé jusqu'à soit le rollback ou soit le commit.
    Après la fin de la transaction, l'autocommit reprend la valeur qu'il avait auparavant.

    9) pour gérer votre grappe des données, dans MySql, à partir de toutes vos procédures stockées, vous devez faire :

    9-a) créer une procédure stockée 'chapeau', dans laquelle, vous mettrez
    --> un seul 'start transaction' au début.
    --> un seul 'commit' à la fin.
    --> au milieu, vous mettez séquentiellement, toutes vos sous- procédures stockées.

    9-b) il va de soit que dans chaque sous-procédure stockée, vous supprimez le 'start transaction' et le 'commit' qui n'ont plus leur place.
    Par contre, vous conservez le 'rollback' associé au 'signal'.

    9-c) deux cas peuvent se présenter :
    --> une erreur va provoquer un rollback et la sortie de l'encapsulation de toutes vos procédures stockées. Donc pas de 'commit'.
    --> pas d'erreur, donc enchaînement des sous-procédures stockées et au final provoquer un 'commit'.

    10) si vous gérez indépendamment vos procédures stockées au niveau php, la structure reste la même.
    --> un 'start transaction'.
    --> pour chaque appel à la procédure stockées, vous devez tester son code retour (errno).
    --> si code retour <> 0 alors faire un rollback et sortir complètement de ce traitement.
    --> répéter cela pour chaque procédure stockées.
    --> et terminer par un 'commit'.

    Citation Envoyé par SQLPRO
    Vous pouvez tenter, mais rien ne permet de garantir que le retour arrière soit possible parce que entre temps un autre utilisateur peut avoir fait une mise à jour des mêmes données !!!!
    Vous avez raison !

    CinePhil ne nous a pas expliqué dans quel contexte il fait cette mise à jour.
    Est-ce par un traitement batch durant la nuit, c'est-à-dire en mono-utilisateur et monotâche ?
    Ou est-ce dans la journée, dans un contexte où il y a plusieurs utilisateurs avec plusieurs tâches au même moment ?

    Dans le second contexte, l'utilisateur doit se débrouiller pour être le seul à créer la grappe de données pour cette personne.
    Par exemple, créer l'identifiant de cette personne et installer un verrou dessus.
    Si pour une raison,il ne valide pas la grappe de données, alors il devra supprimer cet identifiant.

    Citation Envoyé par SQLPRO
    Il n'y a pas de solution simple.
    D'autant que les explications sont succinctes.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  18. #18
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 321
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 321
    Points : 42 824
    Points
    42 824

    Par défaut

    Citation Envoyé par Artemus24 Voir le message
    ...vous devez faire :

    9-a) créer une procédure stockée 'chapeau', dans laquelle, vous mettrez
    --> un seul 'start transaction' au début.
    --> un seul 'commit' à la fin.
    --> au milieu, vous mettez séquentiellement, toutes vos sous- procédures stockées.
    C'est à mon avis la seule solution viable. L'idéal étant de passer un paramètre booléen indiquant l'état transactionnel et un autre l'état des exception.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  19. #19
    Modérateur
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    16 013
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 16 013
    Points : 31 731
    Points
    31 731
    Billets dans le blog
    5

    Par défaut

    Citation Envoyé par Artemus24
    9) pour gérer votre grappe des données, dans MySql, à partir de toutes vos procédures stockées, vous devez faire :

    9-a) créer une procédure stockée 'chapeau', dans laquelle, vous mettrez
    --> un seul 'start transaction' au début.
    --> un seul 'commit' à la fin.
    --> au milieu, vous mettez séquentiellement, toutes vos sous- procédures stockées.

    9-b) il va de soit que dans chaque sous-procédure stockée, vous supprimez le 'start transaction' et le 'commit' qui n'ont plus leur place.
    Par contre, vous conservez le 'rollback' associé au 'signal'.

    9-c) deux cas peuvent se présenter :
    --> une erreur va provoquer un rollback et la sortie de l'encapsulation de toutes vos procédures stockées. Donc pas de 'commit'.
    --> pas d'erreur, donc enchaînement des sous-procédures stockées et au final provoquer un 'commit'.
    J'avais effectivement pensé à cette solution suite à la découverte, grâce à vos réponses à tous les deux, de l'impossibilité d'imbriquer les transactions.

    CinePhil ne nous a pas expliqué dans quel contexte il fait cette mise à jour.
    Est-ce par un traitement batch durant la nuit, c'est-à-dire en mono-utilisateur et monotâche ?
    Ou est-ce dans la journée, dans un contexte où il y a plusieurs utilisateurs avec plusieurs tâches au même moment ?
    Il me semblait pourtant que ça transparaissait dans mes messages mais bon...

    Il s'agit d'une application PHP nommée "pef" qui se connecte à la base de données "pef" avec un utilisateur MySQL/MariaDB nommé "pef" et qui a, pour le moment, tous les droits sur la base de données "pef".
    Il y aura plusieurs utilisatrices physiques qui enregistreront potentiellement au même moment des candidats dans la base de données mais chacune les siens ; il n'y aura pas deux utilisateurs qui travailleront simultanément sur le même candidat.

    Lors de la création d'un candidat, l'application va donc faire ceci (déroulement normal sans erreur ni exception) :
    - Lancement de la procédure de création d'un candidat (P1) ;
    --- La procédure P1 peut lancer la procédure de création d'une personne physique (P2) si celle-ci n'existe pas déjà ;
    --- P2 enregistre le candidat ;
    - Retour au programme applicatif qui récupère l'identifiant du candidat pour la suite des traitements et la réponse à l'utilisateur de l'application.

    Question :
    Si je crée un user MySQL/MariaDB qui a le privilège d'exécution sur P2 mais pas sur P1, mon processus ci-dessus fonctionne t-il ?
    Ceci me permettrait d'empêcher l'exécution directe de la procédure P1 et donc de sécuriser mes données en n'ayant pas des personnes physiques "fantômes" qui ne seraient ni des candidats, ni des utilisateurs, ni un autre type de personne physique prévu par la BDD (contrainte de totalité sur les héritages de th_personne_physique à respecter).
    Cette question est davantage pour ma culture personnelle, dans un premier temps, parce que de toute façon, la base de données ne sera accessible par les utilisateurs que via l'application.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  20. #20
    Modérateur

    Profil pro
    Inscrit en
    janvier 2010
    Messages
    4 989
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : janvier 2010
    Messages : 4 989
    Points : 10 224
    Points
    10 224

    Par défaut

    Bonjour,

    C'est un peu dommage d'être sous MySQL et de na pas profiter des fonctionnalités bugs mis à disposition.

    MySQL génère une erreur lorsqu'on essaie de passer le niveau d'isolation des transaction en mode REPEATABLE READ, mais uniquement si on est dans une transaction.

    On peut donc se servir de ce bug pour savoir si on est dans une transaction :
    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
     
    DELIMITER //
    CREATE FUNCTION `is_in_transaction`() RETURNS int(11)
    BEGIN
        DECLARE oldIsolation TEXT DEFAULT @@TX_ISOLATION;
        DECLARE EXIT HANDLER FOR 1568 BEGIN
            -- error 1568 will only be thrown within a transaction
            RETURN 1;
        END;
        -- will throw an error if we are within a transaction
        SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
        -- no error was thrown - we are not within a transaction
        SET TX_ISOLATION = oldIsolation;
        RETURN 0;
    END//
    DELIMITER ;
    A partir de là, on peut supprimer la transaction de la sp1, mais vérifier qu'on est bien dans une transaction (sinon génère une erreur)

    créer une sp1bis qui démarrera une transaction, appellera sp1, et validera la transaction si on est toujours dans une transaction en sortie de sp1 (la transaction aura pu être annulée dans sp1).

    Dans sp2, appeler sp1 (on sera déjà dans une transaction), et vérifier avant de continuer qu'on est toujours dans une transaction.

    un peu tordu, mais ça doit faire le job

Discussions similaires

  1. [11gR2] Portée des COMMIT et ROLLBACK dans bloc et sous-blocs
    Par ctobini dans le forum PL/SQL
    Réponses: 2
    Dernier message: 12/06/2014, 18h56
  2. Réponses: 3
    Dernier message: 01/03/2013, 11h22
  3. Réponses: 8
    Dernier message: 06/03/2008, 17h32
  4. Récupérer plusieurs lignes avec des procédures stockées
    Par GPZ{^_^} dans le forum SQL Procédural
    Réponses: 2
    Dernier message: 12/03/2007, 14h16
  5. Tables avec des relations & procédures dynamiques
    Par JustMe dans le forum Débuter
    Réponses: 5
    Dernier message: 15/12/2004, 10h58

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo