Discussion :

[Drag]

Je sais que la question peut sembler bête... mais j'ai beau rechercher dans plusieurs tutoriels, je n'arrive pas à être sûr du comportement d'Oracle.

Voici un petit bout de code pour appuyer la question.
(Note: Le code est volontairement très simplifié pour souligner l'essentiel. Dans ce cas par exemple, c'est clair qu'on pourrait directement faire l'incrémentation lors de l'update.)

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
CREATE OR REPLACE FUNCTION TRANSACTION_OR_NOT (param1 IN VARCHAR2)
RETURN NUMBER IS
  idLast NUMBER(12);
  idNew NUMBER(12);
 
  BEGIN
 
    SELECT ID_LAST into idLast
    FROM ID_TEST;
 
    idNew := idLast + 1;
 
    UPDATE ID_TEST
    SET ID_LAST = idNew;
 
    RETURN idNew;
  END;

Dans ce cas-ci, est-ce que la partie comprise entre le begin et le end correspond bien à une transaction "isolée"?

De ce que j'ai compris, avec Oracle, la transaction est automatiquement initialisée... et j'ai l'impression que le "commmit" se fait automatiquement à la fin de la procédure si il n'y a pas eu de rollback (mais je n'en suis pas certain).
Si plusieurs composants font appel à cette procédure en même temps, cela peut-il engendrer des problèmes? A mon sens, si une procédure/fonction est bien une transaction par défaut serializable, il est impossible qu'un second appel à cette procédure fasse le "select" alors qu'un premier appel à cette procédure a fait le "+1", mais n'a pas encore eu le temps de faire le "update". Est-ce que je me trompe?


J'imagine que je pourrais rajouter un "set transaction isolation level serializable;" après le BEGIN, ainsi qu'un "COMMIT;"' avant le RETURN, mais j'ai l'impression que ça ne changerait rien au comportement...
D'autant plus que sur le tutoriel que l'on trouve sur developpez.com, il est bien écrit en gras rouge "D'une façon générale, les procédures ne devraient pas exécuter d'instruction de fin de transaction (COMMIT, ROLLBACK, Ordre DDL). La décision d'enregistrer ou annuler la transaction en cours relève du programme appelant "... (D'ailleurs, lorsqu'il n'y a pas vraiment de programme appelant faisant explicitement un "commit, je me demande qui s'en charge... mais c'est une autre histoire.)


Merci à ceux qui pourront me répondre... même si c'est une question de débutant, ça me semble important de connaitre ce fonctionnement avant de pouvoir utiliser efficacement les procédures en PLSQL.

[McM]

La fonction est utilisée dans la même transaction que la procédure appelante.

Si tu veux être sur que cette fonction ne puisse pas être appelée 2 fois en même temps afin qu'un second SELECT passe avant le premier UPDATE :
Utilises un curseur verrouillant l'enreg

Code :

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

1
2
3
CURSOR c IS
 SELECT ID_LAST INTO idLast
    FROM ID_TEST FOR UPDATE;

Comme ça tu es sur que le second attend le premier.

Ensuite tu peux passer la function en autonome afin de commiter à la fin de la fonction et de libérer l'enregistrement très vite

Code :

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

PRAGMA AUTONOMOUS_TRANSACTION;

[Drag]

Merci beaucoup pour la réponse!

J'avoue qu'il y a encore des points qui m'échappent, je me permets donc de poser des questions.

1) Si une fonction est une transaction par défaut sérializable, pourquoi faudrait-il verouiller cet enregistrement? N'y a-t-il pas des sytème interne à Oracle qui vont s'en charger?

2) Comment fonctionne le curseur? Le fait de déclarer un curseur locke automatiquement un enregistrement dans une transaction donnée? Est-ce qu'utiliser cet effet est une pratique courante, ou une pratique détournée? Parce que concrètement, je n'ai pas besoin de ce curseur...

3) Ensuite, vis-à-vis du autonomous_transaction, j'en avais rapidement fait allusison dans le premier message avant d'éditer... mais plus je lis des explications dessus, plus j'ai l'impression qu'il faut éviter de l'utiliser si possible. (A part pour faire du log d'erreurs.) Peut-être que c'est un excès de prudence, et que ce serait dommage de s'en passer?

[McM]

Réponse 1 :
Non, parce que la lecture n'est pas bloquante.
Donc si T1 lance ta fonction, récupère idLast=1 mets à jour idLast=2, mais ne commite pas.
T2 lance ta fonction : récupère idLast=1 (pas celui mis à jour car pas de commit de la part de T1)
T2 est bloqué sur l'update car ligne lockée par T1
T1 commite
T2 continue sa transaction : c'est dire dire update avec idLast=2

Réponse 2
Donc le curseur FOR UPDATE (peut être qu'un select into for update fonctionne je ne sais plus) permet d'être sur que personne ne va updater entre le select et l'update.
Pratique courante et utilisée par Forms quand tu modifies un champ, oracle balance un select for update pour locker l'enregistrement (et permet d'avoir l'erreur "Could not reserve record" sans attendre que la transaction bloquante libère le verrou.

Réponse 3 : Le Autonomous transaction, en effeet, il ne faut l'utiliser que dans des cas précis.
Dans ton cas, ça dépend de l'utilisation de cette fonction. Si jamais tu as un gros traitement (en durée) qui commite à la fin, et que tu récupère cet ID au début, mais que tu ne veux pas bloquer les autres appels à cette fonction (par d'autres traitements) alors c'est mieux.
Sinon, si jamais en cas de rollback, tu ne veux pas que le +1 soit fait, alors il n'en faut pas.

[Drag]

Et bien merci pour ces explications, ça devient clair maintenant, bonne journée pour moi!

Je n'avais pas pensé que le "Select" ne bloquerait pas d'autres lectures. Surement trop naïf, je pensais que si une partie d'une transaction était liée à un état dans la DB, c'est la transaction tout entière qui pouvait être bloquée. (Si ça fonctionnait comme ça, il y aurait souvent des ressources bloquées inutilement, on est bien d'accord.)

Je viens de tester des accès concurrents avec "SELECT... FOR UPDATE", et apparemment ça fonctionne bien, même si je dois encore vérifier. L'enregistrement est bien locké, et le second "SELECT" attend le "commit" suivant le premier select. Dans ce cas-ci, je préfère cette façon de faire, histoire de ne pas déclarer des curseurs inutilement.


Je sens qu'on arrive au bout, mais je me permets une dernière question...
Dois-je placer moi-même un "COMMIT;" pour préciser quand je veux déverrouiller l'enregistrement du "SELECT... FOR UPDATE"? Et si oui, où? A la fin de la fonction?

Lorsque je fais des essais en plaçant le code dans une fonction, j'ai l'impression qu'à la fin de la fonction, le commit se fait automatiquement. Dans les autres sessions, les données sont à jour et l'enregistrement n'est plus verrouillé...
Est-ce bien le cas? Est-ce que je suis assuré qu'à la sortie de ma fonction PL/SQL, un commit est effectué? Ou alors, existe-t-il un méchanisme "caché" qui me donne l'illusion de ce commit automatique? (Par exemple, que cela se produit pour les fonctions de plus haut niveau... On aurait donc avec "DECLARE BEGIN MA_FONCTION(); END;" un commit à la sortie de la fonction, mais pas à la sortie des potentielles sous-fonctions?) Vu qu'il est conseillé de ne pas mettre de commit dans les fonctions, je ne sais pas trop où le mettre, ni si il faut le mettre.

[McM]

Pour le commit, certains programmes ont un paramètre auto-commit, qui lorsque tu fermes la session, commite tout seul.

Le commit, tu le mets où il faut qu'il soit. Comme je l'ai dit dans le post d'avant, tout dépend de ce que tu dois faire, donc soit la procédure le fait (mais ça commite tout ce que la session a fait avant) soit c'est la procédure appelante qui le gère.

[Drag]

Dans ce cas-ci, cette fonction est "maitresse", si j'ose dire. On peut considérer qu'elle débute la session. Si il n'y a pas eu d'exception avant le "RETURN", la modification doit être effectuée -ou en tout cas, elle peut l'être. Et surtout, l'enregistrement verrouillé doit être libéré le plus rapidement possible pour permettre à d'autres de faire une demande. Je pense donc que je vais mettre le "COMMIT;" directement dans la fonction.


Merci encore pour toutes ces réponses (rapides)!

[pachot]

Bonjour,

Juste quelques précisions:

Si une fonction est une transaction par défaut sérializable, pourquoi faudrait-il verouiller cet enregistrement? N'y a-t-il pas des sytème interne à Oracle qui vont s'en charger?

Dans ton exemple (où ID_TEST n'a qu'une seule ligne) tu peux isoler ta transction de 2 manières
- serializable: tu ne verra pas les modifications concurrentes, même commitées, si elles ont été commitées après le début de ta transaction. donc, tu n'est pas bloqué, mais tu ne vois pas les données les plus fraîches.
- select for update: tu verra les modifications comitées les plus récentes, mais pour cela tu attendra qu'elles soient finies.

Plus d'infos ici si tu veux.

Dans ce cas-ci, cette fonction est "maitresse", si j'ose dire. On peut considérer qu'elle débute la session.

Alors c'est bon. C'est souvent au plus haut niveau qu'on gère la transaction: transaction utilisateur pour de l'interactif, ou 'main' d'un batch.
Mais si tu appelles cette fonction "maitresse" à partir de sqlplus, par exemple, alors tu peux plutôt gérer les transactions dans le script sqlplus.


autonomous transaction: c'est lorsque tu veux faire quelque chose de non transactionnel, qui reste même si la transaction principalle est rollbackée. Donc pour logger des erreurs en base, c'est une très bonne idée. Rarement pour autre chose.

Cordialement,
Franck.

[Drag]

Merci pour ce lien clair et facile à lire.
C'est clair que dans mon cas, le serializable n'est pas ce dont j'ai besoin. Chaque select / update doit prendre en compte toutes les potentielles modifications précédentes. Le Select for update est vraiment ce qui me semble le plus approprié, d'autant plus que la transaction est relativement courte (peu d'attente).

Comment cela fonctionne en fait... par défaut, une transaction est bien READ COMITTED avec Oracle? Ainsi, un SELECT FOR UPDATE force les autres transactions à attendre? Si une autre transaction est déclarée serializable, elle ne serait pas bloquée par un SELECT FOR UPDATE d'une autre transaction, et elle utiliserait la DB dans l'état où elle se trouvait en début de transaction? [EDIT]Je crois pouvoir répondre à ma question. (Avec les explications que l'on trouve ici: http://www.fil.univ-lille1.fr/~durif...onisation.html... ainsi que dans le lien fournit par pachot )
Si une transaction est serializable et qu'il y a changement dans les données auxquelles elle s'intéresse, cela va entrainer une erreur -08177 (sériabilité non garantie).[/EDIT]

Quant à l'autonomous transaction, je le garde pour une prochaine fois, on ne sait jamais.

[pachot]

Merci pour ce lien clair et facile à lire.

Merci, c'est un article de Tom Kyte, mais c'est moi qui l'ai traduit en français

Pour être sûr que j'ai bien compris, si une autre transaction est déclarée serializable, elle ne serait pas bloquée par un SELECT FOR UPDATE d'une autre transaction, et elle utiliserait la DB dans l'état où elle se trouvait en début de transaction?

Oui c'est exact.
Le select (pas for update) va lire les données telles qu'elles étaient au début de la transaction, sans avoir besoin de poser aucun verrou.

Par contre si la transaction serializable fait un SELECT FOR UPDATE, là elle aura l'erreur car elle ne peut pas à la fois lire une image passée et verrouiller l'image courante si les données on changé, ce serait incohérent.