L’isolement des transactions dans Firebird

Introduction

La norme SQL est un document (très cher...) qui décrit le langage SQL sans proposer de solutions quant à l’implémentation de ce langage dans un serveur de bases de données. Il y a cependant un concept quand on parle de serveur de bases de données qui ne peut pas être décrit précisément sans se référer à son implémentation, il s’agit de celui des transactions. Les transactions sont le mécanisme qui permet d’assurer que les données de la base resteront dans un état stable, quoi qu’il arrive. On mesure l’efficacité d’un système transactionnel en termes d’ACIDité. ACID est un acronyme mnémotechnique anglais qui signifie Atomicity, Consistency, Isolation and Durability. On peut le traduire tel quel en Français : Atomicité, Cohérence, Isolement et Durabilité. Nous verrons ce qui ce cache derrière ces termes plus loin dans ce document.

Remerciements

Je tenais avant toute chose à remercier Ann W. Harrisson qui partage sans compter ses connaissances concernant Firebird. Ensuite je veux remercier Philippe Makowski pour le travail considérable qu’il fait pour mettre Firebird à la portée des développeurs francophones. Puis tous ceux qui m’ont aidé, critiqués et remis sur le droit chemin pendant l’écriture de cet article, je pense à Faust, Beru et Merlin. Et enfin tous les membres des forums et des listes de diffusion qui partagent leurs connaissances et leur savoir.

Acronymes utiles

• ACID : Atomic, Consistency, Isolation, Durability
• MGA : Multi Generational Architecture
• MVCC : Multi-Version Concurrency Control
• OIT : Oldest Interresting Transaction
• OAT : Oldest Active Transaction
• OST : Oldest Snapshot Transaction
• DBMS : DataBase Managment System
• SGBD : Système de Gestion de Base de Données.

A ces deux derniers acronymes on ajoute souvent le "R" de Relationnel [1] (RDBMS ou SGBDR) car la plupart des serveurs de bases de données modernes se fondent sur le concept d’entité relation pour décrire les objets qu’ils servent. Une "table" (au sens SQL) est une entité et les relations qui existent entre les entités permettent d’assurer la cohérence du système grâce à des règles de gestion simples que vous pouvez retrouver dans tout bon cours sur les SGBD comme celui de Frédéric Brouard.

A propos de la traduction

La notion que je vous propose d’expliquer dans cet article s’écrit en anglais : "Transaction Isolation Level".

Google et moi sommes d’accord pour traduire cette expression : "Niveau d’isolement des transactions". On trouve parfois dans la littérature (généralement des traductions de l’anglais) l’expression "Niveau d’Isolation des Transactions".

D’après le dictionnaire :

ISOLEMENT

subst. masc. Action d’isoler, de s’isoler ; fait d’être isolé ; état de celui, de ce qui est isolé.

ISOLATION

subst. fém. A. Action, fait d’isoler.

Bien que les deux sens soient très proches, je préfère celui d’isolement parce qu’il indique plus clairement que ce n’est pas la transaction qui s’isole toute seule ou qui a la capacité d’être isolée. C’est bien le moteur du SGBD qui isole chaque transaction des autres transactions qui sont actives simultanément. Une transaction au sens de l’implémentation n’est rien de plus qu’un numéro qui identifie la génération de données sur laquelle le serveur est en train de travailler.

Ce point terminologique posé, nous pouvons attaquer le problème par la description du concept de transaction. Nous verrons ce que la norme en dit et enfin ce que Firebird nous propose.

Les Transactions en SQL

La transaction est un mécanisme fourni par le moteur du serveur de base de données pour assurer la cohérence des données quelles que soient les conditions d’utilisation de la base de données. Une transaction permet d’isoler les requêtes exécutées par un client [2] de celles exécutées, au même moment par d’autres clients. On caractérise un système transactionnel par sa capacité à répondre aux 4 points suivants : Atomicité, Cohérence, Isolement et Durabilité :

Atomicité

Toutes les requêtes exécutées dans le cadre de cette transaction sont validées en même temps ou aucune ne l’est. L’ensemble des opérations réalisées sont vues de l’extérieur comme une seule et même opération.

Cohérence

Une transaction quand elle est validée génère un nouvel état stable du système, ou si un problème survient, le système retourne dans l’état qui était considéré comme stable avant qu’elle n’ait commencé.

Isolement

Une transaction, tant qu’elle n’a pas été validée reste isolée des autres transactions. Les autres transactions n’ont pas accès aux modifications effectuées par cette transaction.

Durabilité

Les données validées sont enregistrées telles quelles, même si un problème survient ou que le système redémarre. Les données sont disponibles dans un état correct.

Vous l’aurez compris, on peut se souvenir de ces 4 points grâce à l’acronyme ACID.

Dans Firebird "tout est transaction", il n’est pas possible d’exécuter une requête hors du contexte d’une transaction. Vous ne le savez probablement pas si vous utilisez des suites de composants d’accès à Firebird comme le BDE et les objets TTable qui se chargent pour vous de gérer les transactions nécessaires à l’exécution des requêtes sous-jacentes au fonctionnement d’une TTable. Les autres, ceux qui utilisent des composants spécifiques à Firebird (et Interbase) comme UIB, IBX, FIB ou IBO savent qu’ils doivent systématiquement utiliser une transaction pour toute requête qu’ils font exécuter par leur SGBD préféré sous peine de voir les performances se dégrader considérablement.

Nous devons maintenant comprendre pourquoi il est important de maîtriser l’utilisation des transactions. Cela peut permettre, dans bien des cas, d’améliorer les performances d’une application multi-thread ou multi-clients puisque de la manière dont vont interagir nos transactions, dépendront les résultats que nous obtiendrons.

La norme SQL [3] décrit 3 phénomènes qui peuvent survenir pendant l’exécution concurrentes de transactions. Malheureusement cette norme a été écrite avec l’idée d’une implémentation basée sur des verrous et les phénomènes décrits sont incomplets et inadaptés à l’implémentation Multi Générationnelle choisie pour Firebird.

Dans le cas d’un système de gestion des accès concurrents par verrous, quand une transaction démarre, le SGBD bloque l’accès [4] aux ressources utilisées par cette transaction. Il faut alors attendre que la transaction ait validé ou annulé son travail pour que d’autres transactions puissent accéder à ces ressources. Comme souvent les ressources dont aura besoin la transaction ne sont pas connues d’avance (imaginez le cas d’une insertion par exemple ; difficile de verrouiller un enregistrement qui n’existe pas encore), ces SGBD doivent résoudre le problème du "verrouillage prédictif" en verrouillant les chemins d’accès à ces données par exemple les pages d’index, les pages de fin de tables...

Firebird procède différemment : Les verrous ne sont posés sur les ressources que s’ils sont demandés explicitement par l’utilisateur (voyez le détail des options de configuration des transactions), par défaut Firebird implémente un Système de Contrôle des accès Concurrents Multi Générationnel. Chaque modification d’un enregistrement modifie le numéro de version de l’enregistrement, plusieurs versions d’un enregistrement peuvent coexister dans plusieurs transactions différentes. Un peu comme on peut faire de la gestion de versions de fichiers avec CVS ou SVN.

Le document que j’ai choisi comme référence pour l’écriture de cet article est "Une critique des niveaux d’isolement de la norme SQL ANSI" (voir références dans la Webographie à la fin de l’article), où ses auteurs, non contents de simplement critiquer la norme SQL actuelle proposent d’autres phénomènes et d’autres niveaux d’isolement plus adaptés au cas d’architectures multi générationnelles, nous verrons que les solutions proposées par Firebird en matière d’isolement des transactions est très proche de ce qui est décrit dans cet article.

Le niveau d’isolement d’une transaction est celui qui exclu un des phénomènes ci-dessous. La norme SQL propose 4 niveaux d’isolement READ UNCOMMITED, READ COMMITED, REPEATABLE READ et SERIALIZABLE, deux sont ajoutés par la critique : CURSOR STABILITY et SNAPSHOT.

Phénomène 0 "Ecriture sale"

Une transaction T1 modifie un enregistrement. Une autre transaction T2 modifie aussi cet enregistrement avant que T1 n’ait exécuté un COMMIT ou un ROLLBACK. Si T1 ou T2 exécute un ROLLBACK, on ne sait pas quelle devrait être la valeur de l’enregistrement.

Phénomène 1 "Lecture sale"

Une transaction T1 modifie un enregistrement. Une transaction T2 lit le même enregistrement avant que T1 n’exécute un COMMIT ou un ROLLBACK. Si T1 effectue un ROLLBACK, T2 aura lu un enregistrement qui n’a jamais été validée et qui, par conséquent, peut être considérée comme n’ayant jamais existé.

Phénomène 2 "Lecture non répétable"

Une transaction T1 lit un enregistrement. Une transaction T2 modifie ou supprime cet enregistrement et effectue un COMMIT. Si T1 essaye de relire l’enregistrement, il reçoit l’enregistrement modifié ou découvre que l’enregistrement a été supprimé.

Phénomène 3 " Fantômes "

Une transaction T1 lit un ensemble d’enregistrements N qui satisfont la condition <condition de recherche>. Une transaction T2 exécute des opérations SQL qui vont générer un ou plusieurs enregistrements qui satisfont la même <condition de recherche> utilisée par T1. Si T1 répète la lecture initiale avec la même <condition de recherche>, elle obtient une collection d’enregistrements différente.

Phénomène 4 " Modifications perdues "

Une transaction T1 lit un enregistrement. Une Transaction T2 modifie le même enregistrement que T1 a lu (d’après, par exemple, une lecture qu’elle aurait fait avant). T1 modifie l’enregistrement et exécute un COMMIT. A ce moment, même si T2 exécute un COMMIT, la mise à jour effectuée par T2 est perdue.

Le phénomène 4 peut survenir dans un cas plus précis lorsqu’on utilise des curseurs pour parcourir un ensemble de données :

Phénomène 4C " Modifications d’un curseur perdues "

Une transaction T1 lit un enregistrement en utilisant un curseur. Une transaction T2 modifie le même enregistrement que T1 a lu. Le curseur dans T1 modifie alors l’enregistrement et exécute un COMMIT. La modification opérée par T2 sur l’enregistrement est perdue.

Pour les anomalies 5A et 5B, on suppose que C() est une contrainte de la base de données qui lie deux enregistrements x et y. Ces anomalies décrivent des violations de cette contrainte :

Anomalie 5A " Lecture biaisée "

On suppose que la transaction T1 lise x, ensuite une deuxième transaction T2 met à jour x et y et exécute un COMMIT. Si maintenant T1 lit y, elle verra les données dans un état inconsistant qui provoquera un état inconsistant en sortie.

Anomalie 5B " Ecriture biaisée "

On suppose que la transaction T1 lise x et y, ensuite une transaction T2 lit x, le modifie et exécute un COMMIT. Puis T1 modifie y. S’il y avait une contrainte entre x et y, elle risque d’avoir été violée.

On notera que les anomalies 5A et 5B ne peuvent se produire si l’on est à un niveau d’isolement qui interdit P2. En fait A5A et A5B servent à décrire des niveaux d’isolement inférieurs ou incompatibles avec REPEATABLE READ.

Voici le tableau final des niveaux d’isolement correspondants aux anomalies qu’ils autorisent.

Niveau	P0	P1	P4C	P4	P2	P3	A5A	A5B
READ UNCOMMITED	Impossible	Possible	Possible	Possible	Possible	Possible	Possible	Possible
READ COMMITED	Impossible	Impossible	Possible	Possible	Possible	Possible	Possible	Possible
CURSOR STABILITY	Impossible	Impossible	Impossible	Parfois Possible	Parfois Possible	Possible	Possible	Parfois Possible
REPEATABLE READ	Impossible	Impossible	Impossible	Impossible	Impossible	Possible	Impossible	Impossible
SNAPSHOT	Impossible	Impossible	Impossible	Impossible	Impossible	Parfois Possible	Impossible	Possible
SERIALIZABLE	Impossible	Impossible	Impossible	Impossible	Impossible	Impossible	Impossible	Impossible

Le niveau d’isolement SNAPSHOT est très important dans un SGBD comme Firebird. Il est à la base du mécanisme qui permet à GBAK de faire des sauvegardes d’une base en cours d’utilisation. Nous verrons plus loin comment il fonctionne.

L’utilisation des transactions avec Firebird

Le niveau d’isolement READ UNCOMMITED n’existe même pas dans Firebird. Il est impossible de lire les données qui sont en train d’être écrites par une autre transaction. Le danger de ce genre de pratique est tel que l’on peut remercier les auteurs de Firebird de nous éviter cette tentation.

Le niveau d’isolement READ COMMITED est utile dans Firebird lorsqu’on souhaite utiliser des transactions longues en lecture seule (de préférence) comme expliqué dans l’article de Craiz Stunz Utilisation pratique des transactions longues il est parfois nécessaire de devoir conserver une transaction ouverte pendant longtemps [5] afin de rafraîchir périodiquement le contenu d’un contrôle connecté à la base de données (dans l’article de Craig Stunz il s’agit d’une application de Monitoring qui permet d’observer le comportement d’une base de données dans le cadre d’une utilisation réélle.)

Dans Firebird et jusqu’à ce que des développements aient été entrepris dans ce sens, il est tout à fait déconseillé d’utiliser des transactions longues en lecture/écriture. Le problème est que l’OST (voir glossaire) reste collé à la transaction en lecture/écriture la plus ancienne (OAT) ce qui oblige le serveur à réaliser des opérations parfois très complexes de parcours des anciennes versions d’un enregistrement pour résoudre les conflits d’accès concurrents. C’est expliqué dans la traduction par Bérenger Enselme d’un article paru chez IBPhoenix "Définition de l’OIT" (dans la Webographie). De la même manière il est absolument déconseillé d’utiliser des transactions longues au niveau SNAPSHOT.

Si vous ne respectez pas ces conseils, votre application deviendra lente et le mécanisme de nettoyage des transactions fermées (le fameux SWEEP) sera coûteux (en temps et en ressources) et absolument inefficace à cause de ces vieilles transactions ouvertes en lecture/écriture.

Le niveau d’isolement CURSOR STABILITY est un renforcement du niveau d’isolement READ COMMITED dans lequel ont évite les problèmes de pertes de modifications décrites dans les phénomènes P4 et P4C. Notez que l’implémentation de READ COMMITED dans Firebird n’inclut pas le renforcement de sécurité prévu par CURSOR STABILITY !

Le niveau d’isolement REPEATABLE READ n’existe pas dans Firebird au sens de la norme SQL. Il est meilleur encore que celui décrit par la norme puisque nous sommes assurés que le phénomène P3 (Enregistrements Fantômes) ne pourra pas arriver. On l’appelle d’ailleurs le niveau SNAPSHOT. Il s’agit du niveau d’isolement par défaut des transactions de Firebird. Ce niveau d’isolement assure que si une transaction exécute deux fois la même requête de lecture elle obtiendra deux fois les mêmes enregistrements, quoi qu’aient pu faire d’autres transactions.

Le niveau d’isolement SNAPSHOT est intéressant à décrire : Lorsqu’on commence une transaction SNAPSHOT on conserve l’heure du démarrage DebT1 et toutes les lectures qui seront exécutées depuis cette transaction se réfèreront aux états stables des enregistrements antérieurs à DebT1. Cette transaction n’est donc jamais bloquée en lecture et ne peut jamais recevoir de données inconsistantes. Au moment où T1 veut exécuter un COMMIT elle reçoit une heure de COMMIT ComT1 supérieure à toutes les autres DebT et ComT d’autres transactions qui auraient pu démarrer après elle. Le COMMIT de T1 ne sera validé que si aucune autre transaction validée entre DebT1 et ComT1 n’a écrit de données que T1 aurait pu aussi écrire. Cette fonctionnalité est appelée "Le premier qui valide gagne" (First-committer-wins) et permet d’éviter les anomalies P4 et P4C. Au moment où T1 est validée les modifications qu’elle a apportées aux données ne seront visibles qu’aux transactions qui auront démarré après ComT1.

Le niveau d’isolement SERIALIZABLE est celui qui offre théoriquement le plus haut niveau de sécurité. Je vais vous citer le passage de la norme SQL qui décrit le niveau SERIALIZABLE :

• L’exécution de transactions concurrentes au niveau d’isolement SERIALIZABLE a la garantie d’être sérialisable [6] .
• Une exécution sérialisable est définie comme étant l’exécution d’opérations de transactions concurrentes qui produirait le même effet que l’exécution en série de ces mêmes transactions.
• L’exécution en série est celle dans laquelle chaque transaction s’exécute complètement avant que la suivante ne démarre.

Vous avez compris que le niveau d’isolement SERIALIZABLE permet de simuler... la non concurrence d’accès au SGBD. Effectivement à ce niveau là aucun risque de conflit.

Dans Firebird le niveau SERIALIZABLE n’existe pas en tant que tel. Il faut le simuler en plaçant des verrous explicites sur TOUTES les tables de la base de données.

La norme SQL affirme que SERIALIZABLE est le niveau d’isolement par défaut des transactions. C’est le cas dans SQL Server de Microsoft mais pas dans Firebird.

Pour résumer :

Niveau d’Isolement	Support dans Firebird
READ UNCOMMITED	N’existe pas
READ COMMITED	Existe
REPEATABLE READ	Existe
CURSOR STABILITY	Simulable
SNAPSHOT	Existe
SNAPSHOT TABLE STABILITY	Existe
SERIALIZABLE	Simulable

Les options de configuration des transactions dans Firebird

C’est au développeur de choisir le niveau d’isolement de ses transactions, mais Firebird ne propose pas de modes "tout faits", il faut combiner des options de configuration pour obtenir le résultat souhaité.

Voici le détail des options de configuration de Firebird, plus bas je résumerai dans un tableau les combinaisons les plus courantes de ces options de configuration.

Option	Description
isc_tpb_version1	Obsolète - Les numéros de version permettent de changer la sémantique des objets d’interface (les API)
isc_tpb_version3	C’est le numéro de version actuellement utilisé pour Firebird et Interbase
isc_tpb_consistency	Modèle de transaction basé sur le verrouillage des tables utilisées. Les verrous utilisés sont alors de type protected et permettent d’obtenir un résultat conforme au niveau d’isolement SERIALIZABLE sans verrous mortels s’ils sont utilisés conjointement avec isc_tpb_lock_read et isc_tpb_lock_write.
isc_tpb_concurrency	Transactions hautement concurrentes avec un niveau de consistance acceptable. Il faut utiliser ce paramètre pour tirer parti de l’architecture multi générationnelle de Firebird. C’est le niveau d’isolement SNAPSHOT par défaut.
isc_tpb_shared	Accès concurrent partagé à une table spécifiée entre toutes les transactions. A utiliser avec isc_tpb_lock_read et isc_tpb_lock_write pour définir les options de verrouillage des tables. Il s’agit du mode de verrouillage des tables utilisé par défaut avec le paramètre isc_tpb_concurrency. Il n’y a pas d’intérêt à utiliser ce mode de verrouillage des tables puisqu’il ne prémunit pas contre les verrous mortels.
isc_tpb_protected	Accès concurrent restreint à une table spécifiée. A utiliser avec les isc_tpb_lock_read et isc_tpb_lock_write pour établir les options de verrouillage des tables. Il s’agit du mode de verrouillage des tables utilisé par défaut avec le paramètre isc_tpb_consistency. Ce mode permet d’assurer un fonctionnement sans verrous mortels à condition d’accepter que les transactions démarrent avec un certain délai.
isc_tpb_exclusive	C’est un mode "psychologique" car en réalité le mode de verrouillage exclusif est transformé en mode de verrouillage protégé par Firebird. Ceux qui pensent avoir besoin d’un accès exclusif à une tale oublient qu’avec l’architecture multi-générationnelle les modifications réalisées par unr transaction sont invisible par les autres. Dans le pire des cas, le mode protected avec un verrou explicite sur une table permet d’interdire aux autres transactions de réaliser des modifications sur un enregistrement.
isc_tpb_wait	En mode wait, une transaction qui est en conflit de modification avec une autre transaction attend que l’autre transaction ait terminé son travail avant de lever une exception (si l’autre COMMIT) ou bien de continuer son travail (si l’autre ROLLBACK). Ce mode de fonctionnement permet de résoudre le problème du "live lockl" qui peut survenir lorsque deux transactions modifient le(s) même(s) enregistrement(s) dans un ordre différent. Si toutes les transactions quittent le conflit lorsqu’il a été découvert, elles vont réessayer toutes ensemble et on part alors dans le "live lock". Si une au moins attend que l’autre ait terminé sont travail, alors une au moins réussira à sortir du conflit et pourra faire autre chose.
isc_tpb_nowait	En mode no_wait, deux transaction qui sont en conflit de modification (UPDATE ou DELETE) échoueront en levant immédiatement une exception.
isc_tpb_read	Mode d’accès en lecture seule qui n’autorise une transaction qu’à faire des opérations de type SELECT.
isc_tpb_write	Mode d’accès en lecture écriture qui autorise une transaction à faire toutes les opérations SELECT, INSERT, UPDATE et DELETE
isc_tpb_lock_read	Mode lecture seule pour une table spécifiée. A utiliser en complément de isc_tpb_shared, isc_tpb_protected ou isc_tpb_exclusive pour définir les options de verrouillage. Ce mode permet d’indiquer que la transaction, même si elle a les droits de lecture/écriture n’a l’intention d’utiliser certaines tables qu’en lecture seule.
isc_tpb_lock_write	Mode lecture écriture pour une table spécifiée. A utiliser en complément de isc_tpb_shared, isc_tpb_protected ou isc_tpb_exclusive pour définir les options de verrouillage. Indique que la transaction a l’intention d’utiliser certaines tables en lecture et en écriture.
isc_tpb_verb_time	Non implémenté. Ce paramètre est censé concerner le moment où les contraintes d’intégrité référentielle sont testées, dans Firebird ces contraintes sont vérifiées immédiatement, c’est à dire dès que l’ordre SQL d’ajout, de modification ou de suppression a été exécuté.
isc_tpb_commit_time	Non implémenté. Voir ci-dessus. Dans ce mode les contraintes seraient vérifiées au moment du commit.
isc_tpb_ignore_limbo	Permet à une transaction d’accèder à des données qui peuvent être dans "les limbes". Ce cas arrive lorsqu’on utilise les transactions multi-bases de données. Lorsqu’une telle transaction démarre, elle est marquée PREPARED dans les pages d’inventaire respectives des transactions des bases de données référencéees, les modifications qui sont apportées à ce moment ne sont pas utilisables tant que la transaction n’a pas été validée sur toutes les bases de données elles sont dites "dans les limbes". C’est le fonctionnement du "two-way commit".
isc_tpb_read_committed	Transactions hautement concurrentes qui peuvent lire les changements validés par d’autres transactions. Ce paramètre permet de tirer partir de l’Architecture Multi Générationnelle de Firebird.
isc_tpb_autocommit	Une transaction qui démarre avec ce paramètre exécute un CommitRetaining à chaque fois qu’elle change un enregistrement, poste un évènement (post_event) ou qu’elle appelle une procédure stockée qui réalise l’une de ces opérations. Attention le paramètre autocommit ne permet pas d’exécuter un CommitRetaing automatiquement après un SELECT. On ne peut donc pas l’utiliser comme un mode "AutoCommit" global qui fermerait tout seul une transaction dans une application cliente. Il faudra, dans tous les cas, exécuter un commit ou un rollback à la fin de la transaction.
isc_tpb_rec_version	Avec ce paramètre une transaction peut dans tous les cas lire la dernière version stable d’un enregistrement, même s’il est en cours de modification par une autre transaction.
isc_tpb_no_rec_version	Avec ce paramètre une transaction doit attendre (isc_tpb_wait) - ou lever immédiatement une exception (isc_tpb_no_wait) - que toutes les autres transactions qui sont en train de modifier un enregistrement aient été validées (ou annulées) avant de pouvoir lire la dernière version de cet enregistrement.
isc_tpb_restart_requests	Cherche toutes les requêtes exécutées dans le cadre de le même connexion qui étaient en cours en cours dans d’autres transactions, les déroule, et les redémarre dans le cadre de la nouvelle transaction (... ?!?...)
isc_tpb_no_auto_undo	Par défaut le système conserver un log d’annulation automatique pour permettre de transformer un rollback en commit (quand c’est possible) en annulant les modifications réalisées pour restaurer les anciennes versions des enregistrements. Dans le cas de très grandes insertions comme peut le faire gbak quand il restaure une base de données, le log d’annulation automatique est une surcharge inutile pour le serveur
isc_tpb_lock_timeout	Firebird 2. C’est le paramètre à utiliser pour que les transactions en mode isc_tpb_wait n’attendent pas éternellement.

Compatibilité des modes de verrouillage des tables : Selon le niveau d’isolement choisi, Firebird utilise deux modes de verrouillage des tables implicites : isc_tpb_shared ou isc_tpb_protected. On a vu dans le tableau ci-dessus que le mode isc_tpb_exclusive était traduit par Firebird en isc_tpb_protected. Dans les modes isc_tpb_read_commited et isc_tpb_concurrency, c’est isc_tpb_shared qui est utilisé par défaut. En mode isc_tpb_consistency c’est isc_tpb_protected qui est utilisé par défaut. Selon que l’on accède à la table en lecture ou en écriture c’est un verrou en lecture ou en lecture/écriture qui sera posé. Le tableau suivant indique les compatibilités entre les modes de verrouillage :

.	sharead read	shared write	protected read	protected write
shared read	Compatibles	Compatibles	Compatibles	Compatibles
shared write	Compatibles	Compatibles	Incompatibles	Incompatibles
protected read	Compatibles	Incompatibles	Compatibles	Incompatibles
protected write	Compatibles	Incompatibles	Incompatibles	Incompatibles

Quand deux modes sont incompatibles il se produit un conflit de verrouillage qui est résolu en tenant compte des paramètres isc_tpb_wait, no_wait, rec_version et no_rec_version des transactions.

Reprenons maintenant le tableau des niveaux d’isolement de la norme SQL (en incluant les modes ajoutés par la critique de la norme) et voyons comment il est possible de les programmer avec Firebird :

Niveau d’Isolement	Paramètres des Transactions
READ UNCOMMITED	N’existe pas
READ COMMITED	isc_tpb_read_commited + isc_tpb_rec_version + isc_tpb_wait
REPEATABLE READ	N’existe pas au sens de la norme
CURSOR STABILITY	isc_tpb_read_commited + CommitRetaining
SNAPSHOT	isc_tpb_concurrency
SNAPSHOT TABLE STABILITY	isc_tpb_consistency
SERIALIZABLE	isc_tpb_consistency + isc_tpb_wait + isc_tpb_lock_read (lock explicite des tables auxquelles on accède uniquement en lecture) + isc_tpb_lock_write (lock explicite des tables auxquelles on accède en lecture et écriture)

Lorsque isc_tpb_rec_version, no_rec_version, wait ou no_wait ne sont pas explicitement spécifiés c’est que l’on peut choisir la combinaison qu’on veut en fonction des résultats que l’on souhaite obtenir.

Et à la fin : COMMIT ou ROLLBACK ?

Littéralement Commit permet de "commettre" physiquement les modifications dans la base de données (donc de les valider) et Rollback de "revenir en arrière" (donc de les annuler).

Il est donc légitime de penser que lorsqu’on a terminé de travailler avec une transaction il faut la fermer proprement en indiquant au serveur le traitement à réaliser en fonction de ce qu’on a fait : si on a fait que lire des données, pas de raison de valider quoi que ce soit, et un rollback permet de s’assurer en "revenant en arrière" qu’aucune modification n’a été apportée directement ou indirectement à la base de données. Dans le cas d’une modification que l’on veut annuler il faut utiliser Rollback absolument et dans tous les autres cas Commit.

S’arrêter à se raisonnement c’est montrer qu’on ne connait pas le fonctionnement interne de son serveur de base de données.

La première chose c’est qu’il y a un bien meilleur moyen de s’assurer qu’une transaction ne modifiera pas de données : le paramètre isc_tpb_read. Avec ce mode, la transaction sera marquée Read-Only (lecture seule) et il sera impossible de modifier quoi que ce soit, directement ou indirectement.

Petit apparté : qu’est-ce qu’une modification "indirecte" de la base de données ?

Considérez cette procédure stockée:

create procedure PS_LIT_DES_TRUCS_IMPORTANTS (ID INTEGER)
returns (TRUC_IMPORTANT INTEGER)
as
begin
 insert into LOG (LOG_USER, LOG_ACTION)
 values (CURRENT_USER,'Lecture de trucs importants !');
 for select TRUCS_ID from T_TRUCS_IMPORTANTS
     into :TRUC_IMPORTANT do
   suspend;
end

Cette procédure stockée ne fait pas que renvoyer des données : elle écrit dans une table LOG "à l’insu de l’utilisateur".

Si l’appel à cette procédure stockée était embarqué dans une transaction en lecture seule, l’appel se solderait par une exception.

Ensuite il faut savoir qu’avec chaque transaction Firebird conserve un log d’annulation automatique. Il s’agit d’un mécanisme qui permet d’annuler les modifications opérées par une transaction sans avoir à mettre en branle toute le mécanique du rollback. En fait si vous modifiez moins de 100 000 enregistrements dans une transaction et que vous l’annulez avec un rollback, firebird va utiliser le log d’annulation automatique pour restaurer les anciennes versions des enregistrements modifiés et transformer votre rollback en ... commit !

Il est possible de désactiver le log d’annulation automatique en utilisant le paramètre non documenté isc_tpb_no_auto_undo, c’est ce que fait gbak pour ne pas surcharger le serveur pendant la restauration d’une base de données. Dans ce cas tous les rollback sont gérés par le biais de l’architecture multi-générationnelle et sont plus lourds à gérer pour le serveur.

Il faut aussi savoir qu’une transaction quand elle se termine (par un commit ou par un rollback) est marquée dans cet état dans les pages d’inventaire des transactions. Plus il y a de pages marquées RolledBack, plus le serveur aura un travail complexe à réaliser pour trouver la dernière version intéressante d’un enregistrement. C’est pour ça qu’il existe un mécanisme de SWEEP (que l’on peut forcer avec gfix ou qui est démarré automatiquement par le serveur toutes les 20000 transactions) qui nettoie les versions des enregistrements modifiés par des transactions annulées et les transforme en transactions COMMITED.

Donc il faut préférer Commit à Rollback quand c’est possible. Je conseille de n’utiliser le Rollback que quand il est absolument nécessaire d’annuler une transaction complète, sur demande de l’utilisateur ou bien à la suite d’une exception logicielle.

Quand une exception survient après une modification (INSERT, UPDATE, DELETE) comme une violation d’une contrainte d’intégrité référentielle (clé primaire, clé étrangère, contrainte unicité...), la modification qui concerne l’enregistrement qui pose un problème est automatiquement annulée. Mais la transaction reste dans l’état et il est possible de continuer quand même pour valider les modifications déjà réalisées ou bien d’annuler le tout avec un ROLLBACK.

Attention : Les composants UIB intègrent un mécanisme qui permet de prendre automatiquement une décision lorsqu’une erreur, quelle qu’elle soit, survient. Par défaut cette décision est un ROLLBACK global de la transaction. Ce n’est pas forcément ce dont vous aurez besoin et il est possible de modifier ce comportement en changeant la valeur de la propriété OnError de TJvUIBStatement.

Conclusion

Nous avons vu comment fonctionnement les transactions de Firebird. J’aimerais attirer votre attention sur l’importance de ce sujet et sur la grossière erreur technique que l’on fait en laissant une librairie de composants décider toute seule de la politique de gestion des transactions dans une application. Même s’il est très difficile d’obtenir des données erronées avec Firebird grâce à l’achitecture multi générationnelle qui isole parfaitement les transactions, il est risqué de ne pas se préoccuper consciencieusement de la manière dont elles sont utilisées. Dans le pire des cas, lorsque toute l’application n’utilise qu’une seule transaction globale, les utilisateurs observent des ralentissements parfois considérables qui vont jusqu’à la remise en cause du choix du serveur de base de données.

Apprennez à utiliser Firebird et les librairies de composants qui permettent de simplifier (peut-être un peu trop parfois) son utilisation. Raisonnez en termes d’ACIDité pour décider des responsabilités que vous donnerez à vos transactions. Et n’hésitez pas à demander l’avis de développeurs expérimentés ;-)

Webographie

• Standards SQL http://www.wiscorp.com/SQLStandards.html
• Norme SQL99 http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/
• Critique des Niveaux d’Isolement des Transactions dans la norme SQL ANSI http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf
• Définition de l’acronyme ACID http://whatis.techtarget.com/definition/0,289893,sid9_gci213756,00.html
• Utilisation pratique des transactions longues http://blogs.teamb.com/craigstuntz/articles/PracticalUseOfLongRunningTransactions.aspx
• Comprendre la durée de vie d’une transaction http://blogs.teamb.com/craigstuntz/articles/UnderstandingTransactionLifetimes.aspx

Liens

• Firebird http://www.firebirdsql.org
• IBPhoenix http://www.ibphoenix.com
• UIB http://www.progdigy.com

• [1] Notez qu’il existe d’autres systèmes de gestion de base de données basés sur d’autres concepts (les SGBD "Objet", "Orientés Aspect", "Hiérarchiques", "Réseau"...
• [2] au sens général, qu’il s’agisse d’un utilisateur physique ou d’un thread qui exécuterait des requêtes en parallèle de celles exécutées par l’utilisateur lui même
• [3] Norme SQL99 ISO/IEC 9075-2, section 4.32
• [4] avec des sections critiques, des mutex, des sémaphores...
• [5] La définition du terme longtemps est sujette à controverses et dépend du contexte de l’application à laquelle il s’applique. En général on accepte que longtemps signifie de plusieurs minutes à plusieurs semaines.
• [6] Ils ont le sens de la formule, on a du mal à le nier...