Suivant le type d’instruction exécutée, il y a deux cas de délimitation :
Dans les deux cas, la transaction commence en même temps que l’exécution de la première instruction SQL. PostgreSQL ne connaît que le second cas pour DDL et DCL.
| Transaction Oracle | Transaction Postgres |
En Oracle, toute exécution fait partie d’une transaction.
Une transaction commence avec la première instruction DML
ou set transaction qui suit :
|
En PostgreSQL une transaction commence avec
l’instruction start transaction ... (ou begin)
et se termine comme en Oracle. Si on n’utilise pas l’instruction start transaction ... (ou begin) alors, par défaut, chaque instruction DML est exécutée comme une transaction complète, on parle alors de fonctionnement en auto commit (un peu comme le mode par défaut de JDBC). |
Cette instruction, la première de la transaction (appelons la T), règle
le niveau d’isolation de T par rapport au reste du monde.
Plus précisément, un niveau d’isolation indique dans quelle
mesure T verra les modifications validées par
d’autres transactions. En revanche le niveau d’isolation
ne permet pas de restreindre la visibilité des modifications
qui seront faites par T.
En quelque sorte l’isolation n’est pas symétrique :
elle permet de dire ce qu’on veut qu’une transaction puisse
voir du monde extérieur mais elle ne permet pas d’empêcher les autres
de voir les modifications qu’elle effectue,
par exemple une autre transaction en non isolation
(read uncommitted de SQL2) verra toutes les
modifications même celles qui ne sont pas validées !
Ce niveau de non isolation n’est disponible ni en Oracle ni en
PostgreSQL même si PostgreSQL le reconnaît syntaxiquement,
voir le tableau un peu plus loin.
set transaction <option> ;
<option> ::= read only | isolation level <niveau-d-isolation>
| read write | use rollback segment <rollback_segment>
<niveau-d-isolation> ::= serializable | read committed
Cette déclaration (optionnelle) doit être la première
instruction de la transaction.
La valeur d’isolation
par défaut est positionnable dans une variable qui est
initialisée à read committed.
Le mot read committed est d’ordre technique et signifie qu’une
instruction peut voir toute modification validée avant qu’elle ne démarre.
PostgreSQL utilise l’instruction start transaction ....
| options | |
|
serializable
|
(SQL92, Oracle, PostgreSQL) transaction sérialisable
(isolation par défaut en SQL92).
Une transaction sérialisable voit la base telle qu’elle était
validée quand elle a commencé, autrement elle ne voit aucunes des
modifications validées par d’autres transactions après son démarrage.
Bien entendu elle voit ses propres modifications.
2 erreurs possibles : non sérialisabilité, interblocage
Ces deux erreurs son dues à pas de chance et ne devraient donc pas être interprétées comme des bogues, mais plutôt comme des circonstances empêchant l’aboutissement de la transaction. Si la transaction sérialisable T tente de modifier un nuplet modifié par une autre transaction validée après le début de T, l’instruction DML correspondante échoue : ORA-08177: Can’t serialize access for this transaction. Attention : cette erreur a lieu aussi si la transaction validée a simplement effectué un select for update, même si elle n’a pas modifié les lignes ainsi verrouillées. |
|
read committed |
(SQL92) (isolation par défaut en Oracle et en PostgreSQL) :
chaque instruction DML de la transaction read committed voit
ce qui est validé au moment où l’instruction
a commencé à s’exécuter ou bien quand ele est débloquée.
1 erreur possible : interblocage
Si cette instruction DML tente de verrouiller un nuplet
déjà verrouillé par une autre transaction, elle est bloquée
jusqu’au déverrouillage ; quand elle se débloque elle réévalue
complètement la sélection des nuplets.
Cette erreur est due à pas de chance et ne devrait
donc pas être interprétée comme un bogue, mais plutôt comme
une circonstance empêchant l’aboutissement de la transaction.
|
|
read uncommitted |
(SQL92) (absent de Oracle) :
à ce niveau, la transaction voit toutes les modifications,
même celles non validées, elle n’est donc aucunement isolée.
Présent en PostgreSQL8 mais la documentation dit clairement
que c’est implanté par du read committed :
When you select the level Read Uncommitted you
really get Read Committed !
|
|
read only
|
(Oracle) transaction-level read consistency.
La transaction ne voit que les changements commis avant
son début (commandes autorisées : select, manipulation
de curseurs, lock table,
set role, alter session, alter system,
commit et rollback) L’intérêt de read only par rapport à serializable est certainement de faire des économies sur les ressources allouées à la transaction puisqu’on sait qu’elle ne pourra pas modifier la base de données. |
Q. 239 Oracle permet-il à une transaction d’observer des modifications non validées ?
En Oracle ou Postgres, tant qu’elle est vivante, une transaction est la seule
à voir les modifications qu’elle a effectuées.
Oracle ne dispose que du verrouillage exclusif (X) des nuplets, il ne dispose pas de
verrouillage partagé (S)1. C’est grâce à la gestion des multiples versions des nuplets
qu’Oracle peut se passer de ces verrous S tout en garantissant
que les lectures ne sont jamais bloquées ni bloquantes par/pour
les écritures faites par d’autres transactions.
Ce niveau d’isolation par défaut peut être redéfini pour la session en cours avec la commande alter session.
alter session set isolation_level = {serializable | read committed}
savepoint <nom-de-point-de-sauvegarde> ] ; commit [ work ] ; rollback [ work ] [ to [ savepoint ] <nom-de-point-de-sauvegarde> ] ;
Pose un point de reprise intermédiaire dans la transaction courante, ce qui permettra de faire un rollback partiel de la transaction mais sans terminer cette transaction, on peut ensuite retenter le traitement annulé sans devoir créer une nouvelle transaction.
Termine la transaction et tous les changements effectués par la transaction deviennent permanents. Les éventuels points de reprise intermédiaires posés depuis le début de la transaction sont oubliés et tous les verrous posés par la transaction sont relâchés.
Forme rollback work, qui utilise le segment de rollback,
annule le travail fait depuis le début de la transaction,
relâche tous les verrous et oublie tous les points de
reprise. La transaction est terminée.
Forme rollback work to savepoint xxx; annule le travail fait depuis le point de reprise mentionné (qui appartient bien sûr à la transaction courante). Tous les points de reprise posés après le point de reprise mentionné sont oubliés. Le point de reprise mentionné est conservé, les verrous obtenus depuis le point de sauvegarde sont relâchés mais :
La transaction n’est évidemment pas terminée, voici un exemple :
update Etudiant set note = 14 where nom = 'Durand' ; savepoint Durand_note ; update Etudiant set note = 18 where nom = 'Dupont' ; savepoint Dupont_note ; -- oups! ce n'est pas Dupont mais Dupire qui a 18 : rollback to savepoint Durand_note ; update Etudiant set note = 18 where nom = 'Dupire' ; commit ;
Un rollback to savepoint ne termine pas la transaction.
Une transaction doit échouer (rollback) si une erreur du SGBD s’est produite pendant son exécution. Ce qu’il faut faire en réponse à cet échec varie en fonction de la nature de l’erreur :
DBMS_LOCK.Sleep) puis de relancer automatiquement la modification
dans une nouvelle transaction2.Cet exemple ne reprend pas tous les points du squelette de la section 17.5 page ?? :
create table Compte ( id Number (5) primary key, solde Number (5) constraint Solde_Positif check (solde >= 0) ) ;
La procédure Virer doit laisser inchangée la somme des soldes
quitte à ne rien faire si ce n’est pas possible :
create procedure Virer(D in Compte.id%type,C in Compte.id%type,S in PositiveN) is
Interblocage exception ;
pragma Exception_Init (Interblocage, -00060) ; -- deadlock (interblocage)
Solde_Negatif exception ;
pragma Exception_Init (Solde_Negatif, -02290) ; -- Solde_Positif violé (check)
begin
set transaction isolation level read committed ;
update Compte set solde = solde - S where id = D ;
if SQL%rowcount = 0 then
rollback ; raise_application_error (-20111, 'Compte à débiter inexistant') ;
end if ;
update Compte set solde = solde + S where id = C ;
if SQL%rowcount = 0 then
rollback ; raise_application_error (-20111, 'Compte à créditer inexistant') ;
end if ;
commit ;
exception
when Interblocage then
rollback ; raise_application_error (-20111, 'Interblocage') ;
when Solde_Negatif then
rollback ; raise ;
end Virer ;
Q. 240 Montrer que deux exécutions simultanées de Virer peuvent s’interbloquer.
Q. 241 Réécrire la procédure Virer pour qu’elle reprenne le traitement en cas d’interblocage.
Ici l’isolation read committed est adaptée :
Q. 242 Montrer que l’isolation serializable pourrait provoquer inutilement des erreurs de sérialisabilité.
Règle : les transactions qui modifient la base ne doivent
pas être trop longues, par exemple, au lieu de faire
une seule transaction qui effectue N virements,
il vaut probablement mieux en faire N qui effectuent chacune
un virement.
Q. 243
Trouver la bêtise dans le code suivant, puis la corriger.
create procedure Betise (D in Compte.id%type) is
Interblocage exception ;
pragma Exception_Init (Interblocage, -00060) ; -- deadlock
Serialisabilite_Non_Garantie exception ;
pragma Exception_Init (Serialisabilite_Non_Garantie, -08177) ;
begin
set transaction isolation level serializable ;
loop
begin
update Compte set solde = solde * 1.1 where id = D ;
commit ;
exit ;
exception
when Interblocage or Serialisabilite_Non_Garantie then
-- attendre un peu que les choses se calment :
DBMS_Lock.Sleep (3.14) ; -- en secondes
when others then
rollback ; raise ;
end ;
end loop ;
end Betise ;
On a parfois envie qu’une transaction dite mère puisse provoquer l’exécution d’une autre transaction dite fille. Certains SGBD permettent de faire cela mais avec des sémantiques très différentes.
En Oracle cette sémantique est très simple : les transactions mère et fille sont complètement indépendantes, c’est pourquoi on parle de transaction autonome pour une transaction fille.
La seule relation entre mère et fille est que la fille est exécutée complètement avant que sa mère ne reprenne son exécution : en fait la fille peut-être considérée comme l’exécution d’un sous-programme devant se terminer par un commit ou un rollback.
Pour disposer de transactions autonomes, il suffit
d’utiliser le pragma AUTONOMOUS_TRANSACTION dans la partie
déclarative de la procédure ou du bloc anonyme réalisant cette transaction.
Par exemple, un trigger d’audit doit inscrire des informations dans une table de façon persistante, même si l’instruction DML qui a déclenché le trigger échoue :
create table Memoire (auteur Varchar2 (20), message Varchar2 (50)) ; create table Salaire ( id Number (5) primary key, sal Number (7, 2) constraint Sal_Pos check (sal >= 0)) ;
create procedure Auditer (U in Memoire.auteur%type, M in Memoire.message%type) is
begin
insert into Memoire values (U, M) ;
end Auditer ;
create trigger Auditeur
before update on Salaire
for each row
declare
pragma AUTONOMOUS_TRANSACTION ;
begin
Auditer (user, 'modif salaire ' ||
'old =(' || to_char(:old.id) || ',' || to_char(:old.sal) || '),' ||
'new =(' || to_char(:new.id) || ',' || to_char(:new.sal) || ')') ;
commit ; -- interdit dans un trigger non autonome
end ;
insert into Salaire values (1, 7000) ; insert into Salaire values (2, 5000) ; update Salaire set id = id + 1, sal = sal + 50 ; select * from Memoire ; AUTEUR MESSAGE ---------------------------- DURIF modif salaire old = (1, 7000), new = (2, 7050) DURIF modif salaire old = (2, 5000), new = (3, 5050) select * from Salaire ; ID SAL ---------------------------- 2 7050 3 5050 update Salaire set id = id + 1, sal = sal - 6000 ; ORA-02290: violation de contraintes (DURIF.SAL_POS) de vérification select * from Memoire ; AUTEUR MESSAGE ---------------------------- DURIF modif salaire old = (1, 7000), new = (2, 7050) DURIF modif salaire old = (2, 5000), new = (3, 5050) DURIF modif salaire old = (2, 7050), new = (3, 1050) DURIF modif salaire old = (3, 5050), new = (4, -950) select * from Salaire ; ID SAL ---------------------------- 2 7050 3 5050
Malgré l’erreur pendant le second update, toutes les
inscriptions faites par le trigger sont là !
Le pragma peut aussi être mis dans la procédure, mais cela la spécialise et
n’est probablement pas une bonne idée.
Q. 244 Que se passe-t-il si une transaction autonome se bloque sur une des lignes verrouillées par sa transaction mère ?