Installation et configuration d'InnoDB

Ce tutoriel va vous permettre d'installer, de configurer et d'utiliser les tables InnoDB dans MySql.

Pour réagir à ce tutoriel, un espace de dialogue vous est proposé sur le forum : Commentez Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Dans plusieurs débats il a été reproché à MySql, de ne pas être un véritable serveur de base de données. Par défaut MySql, il est vrai ne supporte ni les transactions ni les contraintes sur les clés étrangères. Tout ce qui fait qu'un serveur puisse éviter la corruption des données lors de sessions multiples simultanées.


C'est le format de table, Myisam, utilisé par défaut dans MySQL, qui est en cause. Ce type de table, même s'il permet de très bonnes performances, ne supporte ni les transactions ni les contraintes sur les clés étrangères ni les verrous au niveau utilisateur…


Cette limitation peut être facilement contournée dans certains cas. Mais des environnements plus critiques demandent une exigence accrue de sécurité pour les données.


Introduit avec la version 3.23.5 de MySQL, InnoDB répond à cette exigence en offrant le support des transactions, des clés étrangères et les verrous dans les enregistrements. Les versions futures annoncent le support des requêtes imbriquées et des procédures stockées.

Ce tutoriel va vous permettre d'installer, de configurer et d'utiliser les tables InnoDB dans MySQL.

II. Installation et configuration d'InnoDB

II-A. Installation

InnoDB est présent avec les versions binaires et sources Unix et binaires Windows depuis la version 3.23.6 ainsi que dans les versions standard, Pro et max de MySQL.

L'installation de MySQL avec le support InnoDB ne pose pas de problème particulier à partir des binaires, ceux-ci ayant été compilés pour fournir le moteur de transaction InnoDB.

Il faut cependant savoir que Mysqld-Opt n'inclut pas InnoDB, préférez Mysqld-max.

L'installation à partir des sources nécessite d'ajouter un préfixe dans le script de configuration :

 
Sélectionnez
1.
./configure --with-innodb

II-B. Configuration

Contrairement aux tables de types MyIsam, InnoDB utilise un ou plusieurs fichiers pour stocker les données et les index de toutes les tables et non un fichier par table. C'est ce que l'on nomme un tablespace, la structure des tables reste stockée dans un fichier.frm. InnoDB utilise aussi des fichiers journaux pour conserver les traces des transactions en cours.

L'utilisation d'InnoDB suppose de pouvoir configurer les emplacements de ces fichiers.

MySQL utilise un fichier de configuration pour modifier le comportement du serveur. Ce fichier est présent sur votre serveur au niveau du disque de démarrage sous Windows (i.e. c:/) ou dans /etc./ sous Unix. Il peut aussi se situer dans le répertoire data de MySQL ou peut être précisé dans les options de démarrage de mysqld.

MySQL AB donne quelques exemples de fichiers configuration :

my-medium.cnf, my-large.cnf, my-huge.cnf, my-small.cnf

Exemple de fichier de configuration MySQL AB
Sélectionnez
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.
38.
39.
40.
41.
42.
# The MySQL server
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable       = key_buffer=256M
set-variable       = max_allowed_packet=1M
set-variable       = table_cache=256
set-variable       = sort_buffer=1M
set-variable       = record_buffer=1M
set-variable       = myisam_sort_buffer_size=64M
set-variable       = thread_cache=8

# Try number of CPU's*2 for thread_concurrency

set-variable       = thread_concurrency=8
log-bin
server-id       = 1

# Uncomment the following rows if you move the MySQL distribution to another
# location
#basedir = d:/mysql/
#datadir = d:/mysql/data/

# Uncomment the following if you are using BDB tables
#set-variable       = bdb_cache_size=64M
#set-variable       = bdb_max_lock=100000
# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:1000M
#innodb_data_home_dir = c:\ibdata
#innodb_log_group_home_dir = c:\iblogs
#innodb_log_arch_dir = c:\iblogs
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

Le fichier de configuration My.cnf est divisé en plusieurs sections destinées à modifier le comportement de tel ou tel élément des outils de MySQL. Les sections sont identifiées par des entêtes entre crochets.

Seule la section [mysqld] permet de configurer le serveur.

InnoDB fonctionnant avec des « tablespace(s) », il faut indiquer où ce(s) fichier(s) doit(vent) se trouver.

L'instruction « innodb_data_file_path » spécifie le nom et la taille du/des fichier(s) « tablespace » d'InnoDB. C'est l'option minimum pour l'utilisation d'InnoDB.

 
Sélectionnez
1.
innodb_data_file_path = ibdata1:400M

Le format est donc nom_du_fichier1:taille;nom_du_fichier2:taille;…

La taille des fichiers peut être limitée par le système exploitation (2 go sur certaines versions de Linux, 4 go sous Windows NT/2000).

Par défaut le(s) fichier(s) sera(ont) créé(s) dans le répertoire de données de MySQL. Pour les créer dans un répertoire spécifique, il faut ajouter l'option «innodb_data_home_dir».

 
Sélectionnez
1.
innodb_data_home_dir = /usr/inodb/data/

L'utilisation d'une chaîne vide dans cette option permet l'emploi de chemin absolu dans l'option “innodb_data_file_path”, permettant ainsi la création de « tablespaces » sur plusieurs partitions.

 
Sélectionnez
1.
2.
innodb_data_home_dir =
innodb_data_file_path = /usr/inodb/data/ibdata1:400M;/users/inodb/ibdata2:400M

Il faut cependant s'assurer que Mysqld peut avoir les droits en lecture et en écriture sur le répertoire en question. MySQL ne pouvant le créer, il est impératif de s'assurer que le(s) répertoire(s) existe(nt) et qu'il(s) soit(ent) accessible(s) avant de relancer MySqld.

La nature transactionnelle d'InnoDB oblige MySQL à gérer un certain nombre de fichiers de logs pour conserver les différentes versions des tables en cours d'utilisation.

Par défaut les fichiers logs sont situés dans le répertoire de données de MySQL. Il est possible d'utiliser un autre emplacement en utilisant l'option « innodb_log_group_home_dir »

 
Sélectionnez
1.
innodb_log_group_home_dir = /usr/innodb/logs/

Les fichiers journaux sont gérés en pool circulaire. Le nombre de pools de fichiers logs peut être paramétré par « innodb_mirrored_log_groups ». Cependant MySQL AB recommande de n'utiliser qu'un seul groupe de fichiers de logs.

Les journaux sont utilisés lors d'une validation d'une transaction, lorsque l'administrateur utilise flush log ou lorsque la mémoire dédiée aux transactions est pleine.

Le nombre de fichiers de log par groupe doit aussi être spécifié. C'est l'option « innodb_log_files_in_group » qui nous le permet. MySQL AB recommande de n'utiliser que trois fichiers de logs par pool, mais certaines utilisations, ne nécessitant que peu d'utilisation du moteur transactionnel, permettent d'en utiliser que deux.

La taille de chaque fichier journal peut être spécifiée par « innodb_log_file_size ». Ce paramètre doit être choisi en fonction de l'utilisation du serveur, du nombre de transactions et de la taille de la mémoire tampon que vous désirez allouer. Tout comme les fichiers de données, la taille est limitée aux règles du système d'exploitation. La taille doit être comprise entre 1Mo et 15 % de la taille de la mémoire tampon du pool.

Cette dernière valeur peut être fixée « innodb_buffer_pool_size ». Cette mémoire tampon est utilisée par MySQL pour stocker les données. Ainsi une mémoire tampon élevée permet d'éviter des accès au disque dur trop nombreux. Il faut éviter cependant que la taille de cette mémoire tampon dépasse les 80 % de la RAM du serveur. Ces valeurs doivent être données en regard du nombre d'enregistrements dans les tables que vous utilisez ainsi que du nombre de transactions.

Cette valeur est aussi associée à « innodb_additional_mem_pool_size » qui fixe la mémoire dédiée aux informations du dictionnaire de données et aux structures internes de données. La valeur par défaut est de 2 Mo, elle doit être fixée en fonction du nombre de tables gérées.

La mémoire tampon des logs permet, elle aussi, d'influer sur les performances de votre serveur. Chaque manipulation est enregistrée dans cette mémoire tampon. Elle est vidée vers le disque lorsque la limite est atteinte. Si vous voulez limiter ces accès au disque lors de transactions importantes, vous pouvez donner une valeur élevée pour faire en sorte que les transactions restent en mémoire jusqu'à leur validation.

Autre moyen d'agir sur les performances, «innodb_flush_log_at_trx_commit». Lorsque cette valeur est fixée à 1, les transactions abouties sont enregistrées dans les fichiers journaux. Fixées à 0, les transactions ne sont écrites sur le disque que lors du vidage de la mémoire tampon et non à chaque validation. Cela accroît les performances du système, mais peut compromettre les dernières transactions en cas de crash. La valeur 2 procède de même, elle permet de garder en mémoire les transactions abouties.

L'archivage des fichiers journaux se fait en fixant la valeur « innodb_log_archive » à 1.

Cependant cette fonction est relativement peu utile. Les fonctions de restauration étant assurées grâce « innodb_log_arch_dir ».

 
Sélectionnez
1.
innodb_log_arch_dir  = /usr/innodb/logs/

La valeur doit obligatoirement être le même que pour « innodb_log_group_home_dir », du moins jusqu'à la version 4.0.6 de MySQL.

D'autres paramètres peuvent être utiles lors de la configuration de votre serveur.

« innodb_file_io_threads » donne le nombre limite d'accès disque simultanés. La valeur optimale pour Unix est 4. Pour Windows, suivant la vitesse des disques, ce nombre peut être augmenté jusqu'à 6 - 7.

« innodb_lock_wait_timeout » : si InnoDB détecte les transactions bloquées et effectue un rollback, il se peut que certaines transactions utilisant LOCK TABLES puissent ne pas être détectées. « innodb_lock_wait_timeout » permet de fixer un timeout en secondes limitant les transactions bloquées. Si vous expérimentez un nombre important de DEAD LOCK, il est utile de baisser cette valeur de 30 s, valeur par défaut, à 10 secondes.

« innodb_flush_method » fixe la méthode de gestion des log pour Unix et Unix seulement. Par défaut mySQL utilise fsync. La valeur O_DSYNC permet d'utiliser O_SYNC. Cette valeur dépend de la config de votre serveur Unix.

« innodb_thread_concurrency » gère les ressources en processeur de la machine. InnoDB utilise ce paramètre pour garder le nombre de threads sur InnoDB. Suivant le nombre de processeurs du serveur, vous pouvez augmenter cette valeur au-dessus de 8, valeur par défaut. En cas de baisse des performances, vous pouvez baisser ce nombre.

« innodb_fast_shutdown » : par défaut Innodb ne purge pas les mémoires tampons lors d'un arrêt normal du serveur. Pour effectuer une purge, utilisez la valeur 0. Cependant l'opération de purge peut prendre plusieurs minutes suivant l'utilisation du serveur.

Après avoir modifié le fichier de configuration, vous devez redémarrer MySQL pour que les modifications soient prises en compte. Il est conseillé de lancer le dameon MySQL avec la console, pour vérifier la présence d'un ou plusieurs messages d'erreur.

C'est à ce moment que MySQL crée les fichiers nécessaires au fonctionnement d'InnoDB et tente d'initialiser le moteur transactionnel.

Exemple de création de fichiers données et journaux dans le journal de mysql
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
InnoDB: The first specified data file c:\innodb\ibdata2 did not exist:
InnoDB: a new database to be created!
030428 21:34:23  InnoDB: Setting file c:\innodb\ibdata2 size to 20 MB
InnoDB: Database physically writes the file full: wait...
030428 21:34:27  InnoDB: Log file C:\innodb\var\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file C:\innodb\var\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030428 21:34:28  InnoDB: Log file C:\innodb\var\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file C:\innodb\var\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
030428 21:34:29  InnoDB: Log file C:\innodb\var\ib_logfile2 did not exist: new to be created
InnoDB: Setting log file C:\innodb\var\ib_logfile2 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
mysqld-max: ready for connections.
Version: '4.0.12-max'  socket: ''  port: 3306
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
030428 21:34:41  InnoDB: Started

Deux process sont lancés dans mysql, mysqld le moteur traditionnel et InnoDB le moteur de transaction.

Si une erreur survient, vous devez vérifier les points suivants :

  • les répertoires des données et des logs n'ont pas été créés ;
  • l'utilisateur exécutant MySqld ne dispose pas des droits nécessaires pour créer/lire les fichiers et les répertoires en question ;
  • les répertoires d'archives et les répertoires de logs ne sont pas les mêmes ;
  • votre disque dur est plein ;
  • Le nom d'un répertoire de données ou de log est le même qu'un des noms des fichiers (log ou data).

Pour réparer une erreur, il convient d'arrêter le deamon/service et supprimer les fichiers nouvellement créés, corriger le problème et relancer MySqld.

II-C. Maintenance

La maintenance des tables InnoDB est différente de celle des tables MYISAM.

III. Ibmonitor et innodb statut

Le moteur transactionnel renvoie toutes les informations d'état utiles au management d'un serveur.

Par défaut ces données sont renvoyées par la sortie standard de mysqld. Pour les voir il est possible d'exécuter MySQL en tant qu'application console ou d'utiliser le client MySQL et la commande :

 
Sélectionnez
1.
Show innodb status.

Les informations sont séparées en plusieurs sections (SEMAPHORES, TRANSACTIONS, FILE I/O, INSERT BUFFER AND ADAPTIVE HASH INDEX, LOG, BUFFER POOL AND MEMORY, ROW OPERATIONS).

SEMAPHORES liste les threads en attente. Un nombre important indique que la valeur donnée à « innodb_thread_concurrency » est trop forte.

TRANSACTIONS liste les transactions en cours et les lock tables. Il est important de vérifier ici les deadlock pour vérifier les goulets d'étranglement dans votre configuration.

FILE I/O liste les demandes d'écritures et leur état. Sous Windows si le nombre est élevé vous pouvez augmenter la valeur du paramètre « innodb_file_io_threads ».

INSERT BUFFER AND ADAPTIVE HASH INDEX liste les paramètres des index.

LOG liste les paramètres des Logs.

BUFFER POOL AND MEMORY donne les statistiques sur les pages lues et écrites permettant de vérifier si la taille de la mémoire tampon est suffisante ou non.

ROW OPERATIONS Statistiques sur les requêtes.

IV. Sauvegarde et restauration

Les sauvegardes des tables InnoDB ne peuvent s'envisager comme la sauvegarde de tables MyIsam. En effet un simple DUMP des tables ne permet que d'extraire les données. Les transactions en cours ne sont donc pas conservées. Cette méthode ne doit être utilisée que dans les cas de corruption totale des fichiers de données.

MySql n'inclut pas d'outils de backup pour les tables InnoDB. Pour effectuer un backup des tables et des transactions, il existe deux méthodes : la copie de fichiers ou la réplication de tables.

La copie des fichiers s'effectue après l'arrêt du serveur, vous devez alors copier les fichiers de données et les fichiers de logs dans un endroit sûr.

Les fichiers à copier sont :

  • les fichiers de données ;
  • les fichiers journaux ;
  • les fichiers.FRM correspondant à la structure des tables ;
  • les fichiers de configuration de MySQL.

La réplication utilise un serveur secondaire où les données sont copiées. Le support des tables InnoDB permet une copie propre des données et journaux.

La restauration dépend de la méthode utilisée. Il faut cependant savoir que MySQL vérifie les logs à chaque redémarrage du serveur. Les transactions non abouties seront donc totalement annulées.

Après avoir récupéré les fichiers venant soit d'une simple copie soit d'une réplication, remplacez et redémarrez le serveur si possible à partir de la ligne de commande, pour vérifier que les opérations se déroulent bien.

Si l'opération échoue pour cause de fichiers corrompus, tentez de trouver un jeu de fichiers non corrompus. SI cela échoue, vous pouvez toujours tenter de recréer les tables et d'insérer les enregistrements que vous avez conservés.

V. Réplication

La création d'un serveur de réplication s'effectue par la copie des tablespaces, des fichiers de logs sur le serveur esclave, des fichiers.frm et l'édition de son fichier de configuration.

La réplication avec les tables InnoDB inclut la lecture des logs. Si une transaction a échoué, celle-ci ne sera pas répliquée.

La gestion des tables n'est pas la même qu'avec MyIsam. Il n'est pas possible d'utiliser « optmize table » pour vérifier et gérer les tables InnoDB. Il est préférable d'effectuer alors un dump de la table et de supprimer celle-ci avant de la recréer et de réimporter les données.

V-A. L'utilisation

L'utilisation d'InnoDB diffère aussi de l'utilisation classique de MySQL. Les tables et les index de type InnoDB utilisent des méthodes différentes d'accès. Chaque table possède un « clustered index ». Les données sont sur la même page que cet index.

Les index sont de type B-Trees, la taille d'une page d'index est par défaut de 16 K. InnoDB peut aussi automatiquement construire un index de hash (hash index).

Avec InnoDB, toute l'activité se produit dans un contexte transactionnel. À savoir que même si par défaut MySQL valide automatiquement les transactions, celles-ci restent des transactions et MySQL effectue en réalité un commit à chaque fin de requête.

Pour éviter que MySQL utilise ce comportement, on peut commencer le script par BEGIN et le finir par COMMIT ou ROLLBACK. Une autre méthode consiste à fixer la variable autocommit à 0 à chaque début de session. Il est possible de fixer cette valeur dans le fichier de config du client MySQL.

 
Sélectionnez
1.
SET AUTOCOMMIT = 0;

La création de table d'InnoDB se fait en ajoutant type = INNODB à la fin de l'instruction de création de la table :

 
Sélectionnez
1.
2.
3.
CREATE TABLE nom_table
(. . .)
TYPE = INNODB;

Il est aussi possible de modifier une table existante d'un autre type vers InnoDB :

 
Sélectionnez
1.
ALTER TABLE nom table type = innodb.

Si une erreur survient lors de la modification d'une table, vérifiez si celle-ci ne contient pas d'index full text. Il convient aussi non pas d'utiliser ALTER TABLE, mais de supprimer la table en prenant soin d'avoir effectué un DUMP de la table, puis d'importer les données dans la table en utilisant set autocommit = 0 avant l'insertion et un commit à la fin.

Il est à noter que la compression de tables MYISAM n'est pas supportée sous InnoDB.

Le support InnoDB permet l'utilisation des transactions, des contraintes sur les clés étrangères et de gérer des verrous au niveau des enregistrements et des tables.

Pour illustrer notre propos, nous allons créer deux tables que nous utiliserons en exemple.

Cet exemple est une gestion, simplifiée à l'extrême, de salles de classe. Les instituteurs (table users) sont liés à une et une seule salle.
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
set auto_commit = 1;
create database innodbtest;
CREATE TABLE users (
  id int(11) NOT NULL auto_increment,
  nom varchar(25) default NULL,
  prenom varchar(25) default NULL,
  age int(11) default NULL,
  PRIMARY KEY  (id)
) TYPE=InnoDB;

CREATE TABLE salles (
  idsalle int(11) NOT NULL auto_increment,
  num char(3) default NULL,
  refuser int(11) default NULL,
  PRIMARY KEY  (idsalle),
  index user_ref (refuser),
  foreign key theuser (refuser) references users(id) on delete set null
) TYPE=InnoDB;

grant select, insert, update, delete on innodbtest.* to user1@localhost;
grant select, insert, update, delete on innodbtest.* to user2@localhost;
flush privileges;
flush tables;

V-B. Les transactions

L'un des buts d'InnoDB est de pouvoir, à tout moment, revenir à l'état antérieur d'une table après une transaction. En d'autres termes, seules les transactions validées sont considérées comme durables et leurs données enregistrées dans les tables.

Par défaut et jusqu'à la version 4.0.4 le niveau d'isolation des transactions et REPEATABLE READ. Les versions supérieures permettent de changer ce niveau d'isolation vers READ UNCOMMITED, READ COMMITED, SERIALISABLE.

 
Sélectionnez
1.
2.
SET [SESSION | GLOBAL]
TRANSACTION ISOLATION LEVEL

Niveau d'isolation

REPEATABLE READ lit et bloque les index en laissant la possibilité d'effectuer des inserts.

READ UNCOMMITED lit les enregistrements sans possibilité de regarder à la dernière version.

READ COMMITED : une instruction peut seulement voir les lignes validées avant qu'elle ne débute.

SERIALISABLE : la transaction en cours peut seulement voir les lignes validées avant qu'une première requête ou une instruction de modification de données soit exécutée dans la transaction.

User1
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Set autocommit = 0 ;
Update users set age=25 where id = 1;
commit
delete from salles where idsale = 1;
select * from salles where idsale = 1;
Empty set
Rollback;
select * from salles where idsale = 1;
idsalle   num    refuser
1         AC3    Null
User2
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Set autocommit = 0 ;
Select age from users where id = 1;
Age
23
Commit
Select age from users where id = 1;
Age
25
select * from salles where idsale = 1;
idsalle   num    refuser
1         AC3    Null

VI. Contraintes sur les clés étrangères

Les contraintes sur les clés étrangères sont gérées par InnoDB. Pour ce faire InnoDB utilise les index. Il est donc obligatoire que ces contraintes soient gérées à partir des index des deux tables liées, la clé primaire de la table principale et un index multiple ou unique pour la table secondaire. Les deux colonnes doivent être du même type.

 
Sélectionnez
1.
2.
3.
4.
[CONSTRAINT SYMBOL]  FOREIGN KEY nom_contrainte (colonne, …)
REFERENCE nom_table_principale (colonne, …)
[ON DELETE (CASCADE | SET NULL | NO ACTION | RESTRIC)] (version > 3.23.50)
[ON UPDATE (CASCADE | SET NULL | NO ACTION | RESTRIC)] ( version > 4.0.8)
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE salles (
  idsalle int(11) NOT NULL auto_increment,
  num char(3) default NULL,
  refuser int(11) default NULL,
  PRIMARY KEY  (idsalle),
  index user_ref (refuser),
  foreign key theuser (refuser) references users(id) on delete set null
) TYPE=InnoDB;

Par défaut, cette contrainte empêche la création d'enregistrements dans la table secondaire sans qu'aucune référence ne soit possible.

ON DELETE CASCADE permet la suppression d'enregistrements de la table secondaire.

ON DELETE SET NULL permet de donner une valeur nulle à la col référencée.

Si lors de la création d'une contrainte, vous avez une erreur 150, cela signifie que celle-là est mal formée. Vous devez vérifier que les colonnes sont bien indexées.

De plus, il faut savoir que l'altération d'une table ou la création d'un index sur les versions inférieures à 3.23.50 supprime toutes les contraintes.

La suppression d'une table engendre la suppression des contraintes qui lui sont associées. Enfin certaines versions de l'utilitaire MySqlDump ne recréent pas les contraintes sur les clés étrangères dans le script de création de base de données.

La vérification des contraintes se fait grâce à show create table nom de la table (< 3.23.50).

 
Sélectionnez
1.
2.
3.
Set autocommit = 0 ;
Update salles set refuser = 1 where idsalle = 1 ;
Commit;

VI-A. Les verrous

InnoDB permet la gestion des verrous au niveau des enregistrements et non plus au niveau des tables.

Ces verrous sont associés à une requête de sélection. Il en existe de deux sortes.

 
Sélectionnez
1.
SELECTLOCK IN SHARE MODE

Permet d'éviter aux autres utilisateurs de modifier ou d'effacer les enregistrements sélectionnés. Cela permet d'avoir la dernière version des enregistrements.

 
Sélectionnez
1.
SELECTFOR UPDATE

Même chose que pour LOCK IN SHARE MODE, sauf que les autres utilisateurs ne peuvent lire les enregistrements verrouillés.

Cela peut être utile lorsque l'on doit modifier des enregistrements à partir de données dans ces mêmes enregistrements.

La validation de la transaction implicite ou explicite permet de déverrouiller les tables et les enregistrements.

Si vous projetez d'utiliser régulièrement les verrous sur les enregistrements, il est judicieux de donner une valeur en secondes à innodb_lock_wait_timeout pour éviter des effets de verrous permanents. Vous pouvez d'ailleurs vérifier vos tables et l'état des derniers verrous bloqués avec innodb monitor.

User1
Sélectionnez
1.
2.
3.
4.
5.
6.
Set autocommit = 0 ;
Select * from salles where idsalle = 4 FOR UPDATE;
idsalle   num    refuser
4         BC5    Null
update salles set refuser = 2 where idsalle = 4;
commit;
User2
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
Set autocommit = 0 ;
Select * from salles where idsalle = 4 FOR UPDATE;
idsalle   num    refuser
4         BC5    Null
commit;
Select * from salles where idsalle = 4 FOR UPDATE;
idsalle   num    refuser
4         BC5    2

VII. Conclusion

InnoDB est une grande avancée dans MySQL. Il permet de s'affranchir de plusieurs limites physiques (la gestion de tables supérieures à 4 GO) ou conceptuelles (les transactions et les clés étrangères).

InnoDB ne remplace pas les tables MyIsam. Son utilisation doit être envisagée dans des cas de besoins particuliers :

  • environnement multiutilisateur ;
  • taille de table supérieure à la limite du système d'exploitation ;
  • gestion des clés étrangères ;

Dans d'autres cas, InnoDB peut ralentir votre système sans y apporter une richesse fonctionnelle importante.

VIII. Remerciements

Nous tenons à remercier Claude Leloup pour sa relecture orthographique et Malick SECK pour la mise au gabarit.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+