bonjour,
 
une simple question:
 
peut-on faire un COMMIT dans une boucle FOR d'un Curseur ?

oui, à condition d'y faire avant un begin trans
 
une boucle FOR étant obligatoirement transactionnelle (comme tout code PL/SQL) elle sera par contre implicitement inclue dans une transaction générale dont la portée sera le FOR, la procédure, voir le script entier par contre.

tu es sure Arjuna.
Est ce que le commit ne provoque pas la fermeture du Curseur? en tt cas c'est ce qu'on ma dit l'an passé. Et je pense que ça serait logique aussi que le curseur se ferme au commit ou au rollback.

d'où la notion de transaction imbriquée.
 
en "clair", en pseudo code :
 

 
Mettons que le FOR s'éxécute 3 fois (grâce à un curseur ou une variable)
 
On a :
toto = 1 (a)
toto = 2 (b)
-- commit 2
toto = 3 (b)
-- commit 2
toto = 4 (b)
-- commit 2
toto = 8 (c)
--- commit 1
au final : toto = 8
 
Maintenant, à la fin, à la place du "commit transaction 1" on fait un "rollback transaction 1" :
 
toto = 1 (a)
toto = 2 (b)
-- commit 2
toto = 3 (b)
-- commit 2
toto = 4 (b)
-- commit 2
toto = 8 (c)
--- rollback 1
 
au final on n'a pas de ligne toto dans la table, car la transaction 1 a été rollbackée. malgré les commit de la transaction imbriquée, les valeurs "flushées" par cette dernière ont été rollbackées aussi.
 
si, à la troisième boucle du for, à la place du "commit transaction 2" on a "rollback transaction 2" :
 
toto = 1 (a)
toto = 2 (b)
-- commit 2
toto = 3 (b)
-- commit 2
toto = 4 (b)
-- rollback 2
=> toto = 3
toto = 6 (c)
-- commit 1
 
Au final, on a toto = 6
 
Règles :
-> On ne peut pas faire un commit ou un rollback d'une transaction d'un niveau suppérieur. SQL Server par exemple, permet la syntaxe "commit transaction ###" et "rollback transaction ###". C'est uniquement pour la lisibilité. Si le numéro indiqué ne correspond pas à la transaction courante, ça plante.
-> Une transaction imbriquée, quoi qu'elle fasse, ne peut pas impacter les données d'une transaction globale si elle est rollbackée (mon dernier exemple)
-> Une exception, quelle qu'elle soit, si elle n'est pas trappée "à la main" va rollbacker implicitement la transaction courante, et se propager à la transaction parente, et ainsi de suite jusqu'à l'arrêt du script, ou l'activation exception handler compatible.
 
Après, ceci est à vérifier avec Oracle. En tout cas, c'est ce fonctionnement qui est décrit dans la norme SQL92, et nombre de SGBD suivent rigoureusement ça.
 
A noter aussi que dans une transaction, on peut généralement faire des savepoint : ça équivaut à une sous-transaction suivie d'un commit : on peut rollbacker la suite jusqu'au niveau de ce savepoint. (ça porte un autre nom, mais je ne m'en souviens plus )

A noter aussi que je ne suis pas sûr, tout comme il est interdit de commiter une transaction ouverte avant un FOR à l'intérieur de ce dernier, il est normalement interdit de rollbacker aussi à l'intérieur de ce FOR.
Par contre, on peut très bien jouer avec une transaction déclarée à l'intérieur d'n FOR. Elle sera automatiquement commitée (ou rollbackée, ça dépend de la config du SGBD) si on sort du FOR (ou si on boucle)sans avoir explicité un commit ou rollback.

A priori, les transactions imbriquées (ou nested transactions) sont une "nouveauté" apparue dans la version 8i (il était temps, SQL Server 7 le faisait déjà des années avant)
http://www.devx.com/gethelpon/10MinuteSolution/16609
 
Sinon, une petite démonstration "basique" des transactions imbriquées implicites.
 
Tu as une PS qui fait plein de merdes dans ta base.
 
Tu l'appelles depuis un FOR.
 
Ben si elle plante en plein milieu et que tu gère correctement les erreurs depuis la partie appelante, alors tu n'as pas à te soucier du bordel qu'il y a dans la table : ça a tout bien rollbacké ce que faisait la PS avant de planter, et pourtant tes curseurs et autres sont encore ouvert, tu peux continuer à boucler dans ton FOR.
La raison : un begin transaction et commit sont automatiquement ajoutés en début et fin de n'improte quelle procédure stockée.
De la même manière que les transaction atomiques qui se déroulent à chaque UPDATE/DELETE/INSERT : si ça merde en plein milieu d'une de ces requêtes, tu n'as pas à te poser si la moitié des lignes impactées sont restées dans un état stable ou non : elles le sont forcément. Idem, il s'agit toujours de transactions imbriquées

on peut faire des commit dans des boucles FOR et cela ne ferme pas les curseurs (encore heureux !).
par contre je suppose que tu dois faire de l'insertion ou de la mise à jour en masse, ne commit surtout pas à chaque ordre mais fais des commit en masse pour éviter de générer trop de redo et trop d'undo.
 

eh bien je vous remercie pour vos lumières :-)
 
merci bien à tous

moonboot > t'es sûr qu'on peut faire un commit d'une transaction qui a été commencée en dehors du FOR depuis l'intérieur du FOR ?
parcequ'une fois le commit effectué, normalement la transaction n'existe plus...
 
depuis un script PL lancé depuis SQL+ directement, en effet, ça peut marcher, car un transaction implicite est recréé aussitôt.
 
mais pour un vrai bloc PL (donc une procédure stockée), je doute fortement que ce soit possible...

 
non mais tu peux rencontrer des problèmes de type "snapshoot to old"

 
la transaction est ouverte à la connection et fermée lors du commit et/ou rollback donc pas de soucis
 
La boucle n'ouvre pas une nouvelle transaction à moins d'appeler une procédure en AUTONOMOUS_TRANSACTION

c'est justement ce que je fis vin dieux
 
si tu fais :
 
for truc much
begin
   commit
end
 
je vois pas comment ça peut marcher à moins que ce que tu appelles "AUTONOMOUS_TRANSACTION" soit activé, c'est à dire, en temps normal, uniquement dans sql+, toad ou autre. jamais depuis une PS.
 
là l'exemple ci-dessus va shooter la transaction courrante, et au second passage, tu va te bouffer un message genre "impossible de commiter une transaction qui n'est pas ouverte", puisque t'as plus de transaction...

pas sous Oracle    
 

 
C'est ainsi qu'on fait des commit intermédiaire

t'es peut-être pas dans sql+ là par hasard ?
 
le "AUTONOMOUS_TRANSACTION" machin est actif sous sql+

fait le même code dans une PS, tu vas voir ce que tu vas avoir comme résultat

 
  absolument pas, l'autonomous_transaction est une option à indiquer en entête de procédure :
 

 
je te répéte que la transaction dure de la connection au COMMIT et/ou rollback
 
http://didier.deleglise.free.fr/in [...] ansactions

hmmmm, chelou, en effet, même dans une ps ça plante pas. (je t'ai pas attendu pour tester )
c'pas normal ça...
 
oracle est vraiment pourri de chez pourri, y'a pas moyen d'immaginer plus pourri comme truc
 
on n'a jamais vu ça : commiter des transactions qui n'ont jamais été commencées... trop fort cet orcale...

mais la transaction commence dés la connection... pourquoi tu dis qu'elle ne commence pas ?
 
je ne vois pas comment tu voudrais que ce soit géré
 

un commit dans une transaction met fin à cette dernière.
si tu ne veux pas y mettre fin, au lieu de faire un commit, tu fais un snapshot.
 
donc faire un commit dans une boucle, sans ouvrir une nouvelle transaction à chaque fois, c'est une hérésie.
 
y'a que Oracle à ma connaissance qui le permet...

mais c'est quoi le problème de mettre fin à ta transaction ??? J'vois pas en quoi tu pourrais avec besoin de ne pas mettre fin à la transaction.
 
C'est quoi pour toi une transaction ?

hmpf...
 
si ce document c'est vrai, alors c'est moi qui ne suis pas SQL92 compliant, et Oracle qui l'est
 
http://www.firstsql.com/tutor5.htm
 
En tout cas, je trouve ce fonctionnement particulièrement étrange, et interdit en SQL92 les transactions imbriquées.
 
Si on veut pouvoir imbriquer des transactions, ce fonctionnement doit être abandonné, sinon on ne sais jamais dans quelle transaction on est au moment d'un COMMIT ou d'un ROLLBACK

je ne comprends toujours pas l'intérêt d'avoir différentes transactions selon toi    
 
avoir des transactions imbriquées pour moi c'est surtout un excellent moyen de faire des deadlocks

Pour moi, une transaction, c'est un lot déterminé d'instruction SQL qui sont protégées contre les erreurs d'intégrités.
 
C'est à dire que tant qu'une transaction n'est pas commitée, toutes les données qu'elle a impactée sont totalement INVISIBLE pour les autres connections, même au sein de la même session.
 
Au moment d'un COMMIT, toutes les données impactées par la transaction sont flushées en même temps. Ca se traduit par un LOCK EXCLUSIF de TOUS les objets de la base durant cette mise à jour, de façon à ce que :
1) Les connections ne soient pas impactées par des données à moitié commitée (genre, j'insère des ligne dans la table A et dans la table B. si pendant que je commit, un autre PS a un curseur ouvert sur A, pour jouer sur les données de B, alors mon commit va attendre que ce traitement // soit terminé pour être effectif, afin de ne pas permettre au traîtement concurrent de jouer avec des données en cours de mise à jour (ici, B serait à jour, mais pas le curseur contenant les donnés de A)
2) En cas de crash du serveur au moment du commit, l'ensemble des modifications ne doivent pas être prises en compte, même si elles avaient commencée à être flushées, afin de conserver un état totalement stable des données, et non pas la moitiée flushée, et le reste dans la nature.
3) Un ROLLBACK doit permettre d'annuller l'ensemble des modifications de la transaction en cours, sans impacter les parentes.
4) Un COMMIT doit faire la même chose.
=> A noter que les transactions imbriquées sont utiles uniquement dans le cas où au sein d'une même connection on a deux traîements en parallèle : un commit dans une transaction imbriquée doit être visible depuis le thread concurrent, s'il tourne dans la transaction parente de celle commitée.
5) Tout bloc PL/SQL doit être au sein d'une transaction implicite. C'est à dire que quels que soient les traîtements effectués par un bloc défini entre un BEGIN et un END, sans indication de transaction implicite, doit être invisible des autres traîtements (y compris au sein de la même transaction) tant qu'il n'aura pas atteinds le END. Si une erreur se produit durant ce bloc, alors un ROLLBACK doit automatiquement être activé, afin de retrouver les données dans le même état qu'avant le début du bloc.
 
C'est de la logique pure, et c'est de cette façon que les transactions sont maintenant implémentées dans beaucoup de SGBD

Ca permet, comme expliqué dans mon post précédent, d'utiliser la même connection pour des traîtements assynchrones.
Notamment la possibilité de faire des traîtements non séquentiels dans des données non-encore commitées.
 
Si Oracle implémente réellement aussi mal que ça les transactions, alors il est en effet impossible d'obtenir ce résultat avec.
Ca expliquerais les 25 pages de la doc ADODB détaillant les limitations des transactions imbriquées sous Oracle, qui sont arrivées très tard après l'implémentation de ses derniers sous Oracle (j'ai jamais pris le temps de les lire)
 
Visiblement, Oracle ne fait pas de différence entre un snapshot et une transaction imbriquée. C'est pourtant PAS DU TOUT la même chose (même si pour 99% des cas d'utilisation des transactions imbriquées, ça ne change rien (on ne fait pas souvent des traîtements assynchrone au sein d'une même transaction, et même dans ces cas, on ne les exploite pas souvent)

Sinon, pour le coup des DEADLOCKS, y'a pas plus de risque avec des transactions imbriquées que dans des transactions tout court.
 
C'est justement là que ta vision des transactions est faussée par la vision très réductrice d'Oracle.
Une transaction imbriquée fait des verroux "de plus haut niveau" que ceux de la transaction mère. Ainsi, une transaction imbriquée peut accéder à des données lockées par la mère, même si c'est un verrou exclusif.

 
c'est bien comme ça sous Oracle (sauf le point 5 qui est au choix du développeur)... décidément j'arrive pas à comprendre
 
Par ailleurs, il est possible de désynchroniser la transaction via une procédure autonome pour écrire dans une table d'erreur par exemple sans commiter la transaction qui a généré la-dite erreur.
 

 
les verrous sont très précis aussi sous Oracle même avec ces transactions... un UPDATE ... WHERE col='X' ne lockera que les lignes indexées candidates à la mise à jour par exemple . En revanche, Oracle ne pose pas de lock exclusif qui interdirait un SELECT et c'est heureux

Je comprends pas que tu me dises "ben c'est comme ça que marche Oracle" et que tu ne fasses pas des bons de 20 mètres quand tu dis qu'on peut faire un COMMIT dans un FOR, alors qu'on n'a pas créé de transaction au même niveau...

imagine, tu déclares deux transactions.
ensuite, tu fais un FOR qui boucle trois fois.
dans ce FOR, tu commit.
alors, au premier commit, tu fermes la seconde transaction
au second commit, tu fermes la première.
et le troisième coup ?
 
du coup quand tu sors du FOR, tu ne sais même plus dans quelle transaction tu es...
 
si toi tu trouves pas ça hérétique, alors t'es aussi pire que les gens qui ont écrit Oracle
 
(et je comprend qu'Oracle soit un des seuls SGBD professionnels à souffrir de deadlocks... )

 
Quelle conception étrange    
 
Pour moi :
1° boucle -> UPDATE qui crée la transaction + COMMIT qui ferme la transaction
2° boucle -> UPDATE qui crée une nouvelle transaction + COMMIT qui ferme la transaction
etc...
 
Et je ne comprends pas l'intérêt de savoir dans quelle transaction tu es    
 

 
je confirme... j'suis encore plus pire    

mais c'est pas à ton UPDATE de créer une transaction
 
il est déjà dans une transaction !
 
Oracle, ça me dépasse

non, il est dans une session qui ne crée une transaction que lors de la mise à jour de données

 
c'est comme Sega... c'est plus fort que toi

je pense qu'Oracle ferait bien de passer en AUTOCOMMIT, ça leur permettrait de réfléchir une bonne fois pour toute à leur support des transactions, parceque là, moi je nage en plein délire
 
et du coup, les gens qui bossent avec Oracle ne savent pas utiliser les transactions (forcément, elles tombent du ciel comme des grenouilles roses qui beuglent quand on marche dessus...)

on reprend
 
begin transaction --1
begin transaction --2
 
update truc
 
for ...
   update machin
   commit
loop
 
=> là, t'es dans quelle transaction ? la 1, la 2, ou celle qui se trouve dans la poche de la soeur du pape ?

ce que tu décris ne repondrait pas au critère d'atomicité décrit ici il me semble : http://sqlpro.developpez.com/cours [...] ents/#L4.5

les lapins qui sortent du chapeau, moi je trouve ça moyen quand on bosse sur un ERP ou un TPE...

 
Tu n'es plus dans une transaction après le COMMIT... donc tu es dans une session et non une transaction... une transaction pour moi c'est un processus dans lequel une mise à jour de données est effectuée... après un commit toutes les transactions de la session courante sont validées et donc les transactions sont closes de facto

 
Essaye de donner un exemple pratique (et simple si possible ) dans lequel ça géne STP.  
 
Moi j'en connais un seul, c'est dans le cas d'un trigger où tu peux être amené à désynchroniser du code, pour faire un SELECT sur la table qui déclenche le trigger notamment -> AUTONOMOUS_TRANSACTION et c'est réglé