En voulant me documenter sur le fonctionnement d’InnoDB, j’ai été assez surpris de constater le peu d’information claire et de qualité disponible sur le net à ce sujet.
Je me suis donc procuré une version du « High Performance MySQL » d’O'Reilly et voici un petit résumé de ce que j’ai appris.

Granularité des verrous
Pour conserver un véritable système multi-utilisateur, il faut pouvoir verrouiller uniquement les ressources désirées et non des ensembles trop généraux. Le revers de la médaille est que la performance de l’application en sera nécessairement ralenti: plus la gestion des verrous sera précise et complexe, plus le système y passera du temps. Il est donc primordial de trouver le bon compromis entre concurrence et performance.
Les verrous peuvent ainsi être posés à plusieurs échelles:

  • à l’échelle des tables (possible avec le moteur MyISAM).
  • à l’échelle des pages: le verrou s’applique à une ou plusieurs portions de la table, appelées pages, de tailles identiques et configurables (possible avec le moteur Berkeley DB).
  • à l’échelle des lignes (possible avec InnoDB).

Bien sûr, plus la granularité est fine, plus la gestion de la concurrence est complexe, et plus le risque de voir apparaître des situations d’inter-blocage est grand. Heureusement, InnoDB inclue un système de détection et de résolution des deadlocks en levant des exceptions pour les requêtes concernées.

Les types de verrous
Il existe deux types de verrous:

  • les verrous partagés (shared locks): on autorise les autres threads / processus à accéder aux données en lecture mais pas en écriture. On peut déposer des verrous partagés sur des lignes avec InnoDB à l’aide de la requête SELECT … LOCK IN SHARE MODE.
  • les verrous exclusifs (exclusive locks): on interdit toute lecture ou écriture sur les données marquées par ces verrous. Les verrous exclusifs peuvent être placés avec InnoDB à l’aide de la requête SELECT … FOR UPDATE.

L’utilisation du verrouillage de lignes n’a de sens qu’à l’intérieur d’une transaction. C’est logique: en dehors d’une transaction, le verrouillage sera automatiquement relâché une fois l’exécution de la requête terminée.
Autre remarque importante: une sélection ne prendra pas en compte les verrous déjà posés par une autre transaction si elle n’essaye pas elle même de poser un verrou. Autrement dit une simple requête de type SELECT sans demande de verrouillage partagé ou exclusif ne verra pas les verrous posés par d’autres requêtes dans d’autres transaction. Il faudra donc toujours veiller à verrouiller au minimum à l’aide d’un verrou partagé les données que vous lisez si vous voulez conserver la gestion de la concurrence.

Une dernière remarque, tout aussi importante: si vous exécutez

SELECT * FROM tableX WHERE tableX.champ > 50 FOR UPDATE

ou bien

SELECT * FROM tableX WHERE tableX.champ > 50 LOCK IN SHARE MODE

Si aucun index n’a été défini pour la colonne champ, alors le moteur va devoir lire toutes les lignes de la table pour déterminer lesquelles doivent être sélectionnées. Se faisant il va déposer un verrou sur chacune d’entre elles, et au final c’est toute la table qui sera verrouillée. Pour verrouiller uniquement les lignes désirées, il faudra donc créer un index sur la colonne champ (merci à dbnews pour la précision).

Les transactions
Avec InnoDB, les transactions respectent les 4 règles édictées par le modèle ACID:

    Atomicité: une transaction s’effectue entièrement ou pas du tout.
    Consistance: l’intégrité de la base de données doit être garantie.
    Isolation: les action d’une transaction non validée doivent être invisibles aux autres transactions.
    Durabilité: un crash du système ne doit entamer en rien l’intégrité de la base de données.

Mais le concept d’isolation est plus difficile à mettre en oeuvre qu’il n’y parait. Et de fait il existe quatre degrés d’isolation:

  • Read uncommitted ou dirty read: la règle d’isolation n’est pas respectée (à éviter).
  • Read committed: satisfait la règle de l’isolation mais peut poser problème.
    Exemple:
    Début de la transaction
    Requête 1: sélection de la clé primaire d'une ligne de la table A.
    Requête 2: insertion d'une ligne dans table B référençant la clé précédemment sélectionnée.
    Validation
    

    L’ennui c’est que l’insertion échouera si, entre la première et la seconde requête, une autre transaction supprime la ligne sélectionnée dans A.
    Plus généralement, le mode read committed pose le problème des lectures non répétables, c’est à dire que deux sélections identiques ayant cours dans la même transactions ne retourneront pas forcément le même résultat.

  • Repeatable read: ici le problème des lectures non-répétables est en parti résolu, mais il reste un dernier soucis: les lectures fantômes.
    Exemple:
    Début de la transaction
    Requête 1: select * from A
    // traitement
    Requête 2: select * from A
    Validation
    

    Si une autre requête insère des données dans la table A juste entre l’exécution des requêtes 1 et 2, les deux lectures n’auront pas les mêmes résultats.
    Toutefois avec InnoDB le problème n’est que théorique: il apparaissait tellement souvent que les concepteurs du moteur ont fait en sorte de le régler.

  • Serializable: le plus haut niveau d’isolation en ordonnant les transactions de manière à ce que les lectures fantômes n’apparaissent pas, mais dégrade considérablement les performances.

Avec InnoDB, les transactions ne font intervenir aucun verrou d’aucune sorte. En fait l’isolation est possible grâce à un système de contrôle de concurrence multi-version (Multi-Version Concurrency Control, MVCC). Pour faire simple: un identifiant de création et de suppression est associé à chaque ligne. Ces identifiants jouent le rôle de marqueur temporel, ils permettent donc de travailler sur des instantanés de la base de données et ainsi d’isoler, plus ou moins bien selon le mode utilisé, les transactions les unes des autres.