IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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

Administration Oracle Discussion :

Commit et DDL : un seul Commit ou deux?


Sujet :

Administration Oracle

  1. #1
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut Commit et DDL : un seul Commit ou deux?
    Bonjour,

    Sur le site Ask Tom, un post de 2002 (oui, je sais, c'est vieux) dit que pour un DDL, un Commit est fait AVANT et APRES le DDL!

    Voici ce que dit Tom : https://asktom.oracle.com/pls/asktom...:7072180788422
    "DDL is done conceptually like this:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    begin
    COMMIT;
    do the ddl;
    COMMIT;
    exception
    when others then
    ROLLBACK;
    raise;
    end;
    "
    En cherchant sur le net, je tombe sur un post de Steven Feuerstein, le responsable chez Oracle du PL/SQL qui dit la même chose mais en 2016!
    http://stevenfeuersteinonplsql.blogspot.fr/2016/10/execution-of-ddl-in-plsql-commits-twice.html
    "You'd think that after working with Oracle Database and PL/SQL since 1990, I'd know everything.

    For example, I had long known that when you execute a DDL (data definition language) statement in PL/SQL (which must be done as dynamic SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE /EXECUTE) a commit is executed implicitly after the statement.

    What I'd somehow missed was that a commit is also performed before the DDL statement is executed. So that is the point of this post:
    Oracle Database issues a commit before a DDL statement is executed, and then afterwards as well."

    Et puis, en cherchant encore, je tombe sur la doc officielle d'Oracle qui dit la même chose.
    https://docs.oracle.com/cd/B19306_01...0/transact.htm
    "Overview of Transaction Management
    A transaction ends when any of the following occurs:
    A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction."

    Là où je suis embêté c'est que je n'arrive pas à reproduire ce comportement...
    En SQL, voilà ce que j'ai fait :
    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
    SQL> CREATE TABLE TEST_PL01 (ID NUMBER(5,2));
    Table créée.
     
    SQL> insert into test_pl01 values(1);
    1 ligne créée.
     
    -- Je provoque une erreur pour voir si un COMMIT a bien lieu avant le CREATE TABLE. Si oui, je ne pourrai pas rollbacker l'INSERT.
    SQL> CREATE TABLE TABLE (ID NUMBER(5,2));
    CREATE TABLE TABLE (ID NUMBER(5,2))
                 *
    ERREUR à la ligne 1 :
    ORA-00903: nom de table non valide
     
    SQL> select * from test_pl01;
     
            ID
    ----------
             1
     
    -- Aïe, le ROLLBACK fonctionne donc le premier COMMIT avant l'exécution du DDL n'a pas eu lieu.
    SQL> rollback;
    Annulation (rollback) effectuée.
    SQL> select * from test_pl01;
    aucune ligne sélectionnée
    Mes tests en PL/SQL ne sont pas mieux donc si quelqu'un avait une idée pour tester ce comportement, je suis preneur!
    Bon week-end à vous tous.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  2. #2
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    En fait, c'est une erreur de syntaxe ici. Donc le DDL n'a pas encore été exécuté.
    Le commit n'a lieu.
    En lançant deux DDL, un avec une erreur de syntaxe, l'autre avec une syntaxe correcte, mais une erreur sémantique, et avec le SQL_TRACE correspondant:
    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
     
    SQL> create table DEMO as select * from $TATATA;
     
     
    *** 2017-03-26T20:55:41.143262+02:00 (PDB1(4))
    CLOSE #139890934136464:c=0,e=6,dep=0,type=0,tim=14506311242
    =====================
    PARSE ERROR #140733755283744:len=43 dep=0 uid=114 oct=1 lid=114 tim=14506311473 err=911
    create table DEMO as select * from $TATATA
    create table DEMO as select * from $TATATA
                                       *
    ERROR at line 1:
    ORA-00911: invalid character
     
     
     
     
    SQL> pause
     
     
    SQL> create table DEMO as select * from TATATA;
     
     
    *** 2017-03-26T20:55:42.718908+02:00 (PDB1(4))
    XCTEND rlbk=0, rd_only=0, tim=14507886893
    =====================
    PARSE ERROR #139890934136464:len=41 dep=0 uid=114 oct=1 lid=114 tim=14507887044 err=942
    create table DEMO as select * from TATATA
    create table DEMO as select * from TATATA
                                       *
    ERROR at line 1:
    ORA-00942: table or view does not exist
     
     
    SQL> --create table DEMO2 (id number) tablespace tatata;
    SQL> --create table DEMO2 (id primary key) as select rownum from x,dual,dual;
    SQL> pause
    Seul le deuxième a un commit (XCTEND rlbk=0)

    Cordialement,
    Franck.
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  3. #3
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Merci pour la réponse Franck, je regarde ça lundi soir :-)
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  4. #4
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Voilà, c'est fait et, effectivement, on voit bien que le COMMIT, dans un cas, n'est pas fait car on peut faire un ROLLBACK.
    Je te remercie beaucoup pour ton aide Franck.

    Maintenant, la question qui tue : as-tu un lien expliquant pourquoi le premier COMMIT est fait dans un cas et pas dans l'autre? Et même, pourquoi Oracle traite les deux cas ci-dessous (erreur syntaxique et erreur sémantique) différemment?

    Voici mes tests.

    La table est vide à l'origine et j'y insère une valeur.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SQL> select * from test01;
    aucune ligne sélectionnée
     
    SQL> INSERT INTO TEST01 VALUES (1);
    1 ligne créée.
    Je crée une erreur sémantique sur un ordre DDL et non pas une erreur syntaxique.
    Dans ce cas là, le premier COMMIT est fait car la valeur insérée ne pourra pas être supprimée avec un ROLLBACK.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SQL> CREATE TABLE TEST AS SELECT * FROM TARATATA;
    CREATE TABLE TEST AS SELECT * FROM TARATATA
                                                            *
    ERREUR à la ligne 1 :
    ORA-00942: Table ou vue inexistante
    La donnée reste dans la table car si il y a eu un ROLLBACK, c'est un ROLLBACK implicite pour l'ordre DDL et non pas un ROLLBACK pour la transaction.
    Si je fais un ROLLBACK pour annuler la transaction, ça échoue car le premier COMMIT a été exécuté (puisque le ROLLBACK sur la transaction ne marche pas).
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SQL> SELECT * FROM TEST01;
            ID
    ----------
             1
     
    SQL> rollback;
    Annulation (rollback) effectuÚe.
     
    SQL> SELECT * FROM TEST01;
            ID
    ----------
             1
    Maintenant on va faire un DDL mais avec une erreur syntaxique et non pas sémantique : même le premier COMMIT n'est pas fait car l'INSERT fait avant pourra être annulé par un ROLLBACK.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    SQL> INSERT INTO TEST01 VALUES (2);
    1 ligne créée.
     
    SQL> CREATE TABLE $TEST (id number);
    CREATE TABLE $TEST (id number)
                 *
    ERREUR à la ligne 1 :
    ORA-00911: caractère non valide
    La donnée est présente dans la table car il n'y a pas encore eu de ROLLBACK de niveau transaction.
    En revanche, si je fais un ROLLBACK, la donnée insérée est supprimée de la table, ce qui prouve bien que le DDL a été annulé avant que le premier COMMIT ne soit exécuté.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SQL> SELECT * FROM TEST01;
            ID
    ----------
             1
             2
     
    SQL> rollback;
    Annulation (rollback) effectuée.
     
    SQL> SELECT * FROM TEST01;
            ID
    ----------
             1
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  5. #5
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par Ikebukuro Voir le message
    Maintenant, la question qui tue : as-tu un lien expliquant pourquoi le premier COMMIT est fait dans un cas et pas dans l'autre? Et même, pourquoi Oracle traite les deux cas ci-dessous (erreur syntaxique et erreur sémantique) différemment?
    Je dirais que avant que l'analyse syntaxique soit finie, on ne sait pas encore que c'est du DDL. Après, oui.
    Je n'ai pas de lien là dessus, mais les deux sources que tu as cité (asktom et blog) sont des media ouverts aux commentaires. Sur le premier, Chris Saxon ou Conor McDonald répondrons. Sur le deuxième Steven F.
    Mais je peux leur poser la question si tu préfères.
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  6. #6
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    Finalement, j'ai posé la question sur le blog de Steven: http://stevenfeuersteinonplsql.blogs...its-twice.html
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  7. #7
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Je pense que la réponse est dans How Oracle Database Processes DDL

  8. #8
    Membre émérite
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Avril 2013
    Messages
    1 992
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Produits et services télécom et Internet

    Informations forums :
    Inscription : Avril 2013
    Messages : 1 992
    Points : 2 498
    Points
    2 498
    Par défaut
    Je te remercie Franck pour avoir posté un commentaire sur le blog de Steven Feuerstein, ça me fait plaisir.

    Mnitu, le lien est effectivement très intéressant à lire MAIS il n'explique pas, sauf erreur de ma part, pourquoi Oracle traite différemment le Syntax error et le Semantic error.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  9. #9
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Je pense que si
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    Oracle Database processes DDL differently from DML. For example, when you create a table, the database does not optimize the CREATE TABLE statement. Instead, Oracle Database parses the DDL statement and carries out the command.
    Finalement ce test montre juste que
    • la première étape du parsing est l'analyse syntactique. Si pas d'erreur on passe à l'étape suivante
    • il émet le commit et continue avec tout sa cuisine interne.


    Dans une version plus vielle de ce paragraphe c'était également dit que
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    The execution of DDL statements differs from the execution of DML statements and
    queries, because the success of a DDL statement requires write access to the data
    dictionary. For these statements, parsing (Stage 2) actually includes parsing, data
    dictionary lookup, and execution

Discussions similaires

  1. [Mercurial] Hg et SVN: concaténer changesets en un seul commit
    Par _bat13_ dans le forum Autres DVCS
    Réponses: 1
    Dernier message: 09/11/2010, 14h15
  2. Réponses: 5
    Dernier message: 09/10/2008, 17h40
  3. Faire une seule ligne avec deux echo
    Par d10g3n dans le forum Shell et commandes GNU
    Réponses: 8
    Dernier message: 23/06/2008, 21h11
  4. un seul lien vers deux page!
    Par bassam0205 dans le forum Balisage (X)HTML et validation W3C
    Réponses: 5
    Dernier message: 07/12/2007, 17h37
  5. Une seule déclaration pour deux fiches
    Par Général03 dans le forum C++Builder
    Réponses: 12
    Dernier message: 29/08/2007, 11h29

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