Vérouiller une table en mode exclusif pour effectuer des opérations spéciales
Hier j'ai eu l'occasion d'utiliser le verrouillage de table pour un problème précis en production. Je devais insérer dans une table X et mettre à jour une table Y en lien avec la table X. Je devais également désactiver un trigger d'insertion et un trigger de mise à jour sur cette table X. J'ai fait ceci dans un bloc pl/sql anonyme.
À la première idée, l'analyste et moi nous nous sommes dit que l'opération durerait une seconde et qu'il faudrait manquer de chance pour qu'une autre personne inscrive un enregistrement dans la table X avec les triggers de désactivés. Toutefois, un doute demeurait. Il y avait une chance que cela se produise si petite soit elle. C'est alors que mon collègue DBA m'a parlé d'une instruction appelé "LOCK TABLE" que Oracle nous offre pour une situation du genre. Il y a plusieurs options pour verrouiller une table. Nous avons choisi le mode exclusif (exclusive mode). Dans ce mode, personne ne peut insérer (insert), mettre à jour (update) ou effacer (delete) des enregistrements de cette table. Seule la lecture (select) est permise. Ce mode est le plus restrictif.
Ainsi, la commande que nous avons utilisé à l'intérieur du bloc pl/sql est la suivante:
lock table schema.table_X in exclusive mode;
Lorsque notre traitement est fini et que nous faisons un commit ou un rollback (dans le cas d'une exception), le verrou sur la table se libère automatiquement. De plus, par défaut, l'instruction de verrou attend que la table soit libre pour en prendre possession. Si une autre transaction est en cours, il va attendre pour l'acquérir. De l'autre côté, pendant que la table est verrouillée par mon bloc pl/sql, les autres utilisateurs sont en mode wait sur cette table. Rien n'est perdu. Un principe similaire aux mutex et sémaphores.
Pour plus d'information:
Documentation officielle de Oracle sur la commande
Détail de la commande
À la première idée, l'analyste et moi nous nous sommes dit que l'opération durerait une seconde et qu'il faudrait manquer de chance pour qu'une autre personne inscrive un enregistrement dans la table X avec les triggers de désactivés. Toutefois, un doute demeurait. Il y avait une chance que cela se produise si petite soit elle. C'est alors que mon collègue DBA m'a parlé d'une instruction appelé "LOCK TABLE" que Oracle nous offre pour une situation du genre. Il y a plusieurs options pour verrouiller une table. Nous avons choisi le mode exclusif (exclusive mode). Dans ce mode, personne ne peut insérer (insert), mettre à jour (update) ou effacer (delete) des enregistrements de cette table. Seule la lecture (select) est permise. Ce mode est le plus restrictif.
Ainsi, la commande que nous avons utilisé à l'intérieur du bloc pl/sql est la suivante:
lock table schema.table_X in exclusive mode;
Lorsque notre traitement est fini et que nous faisons un commit ou un rollback (dans le cas d'une exception), le verrou sur la table se libère automatiquement. De plus, par défaut, l'instruction de verrou attend que la table soit libre pour en prendre possession. Si une autre transaction est en cours, il va attendre pour l'acquérir. De l'autre côté, pendant que la table est verrouillée par mon bloc pl/sql, les autres utilisateurs sont en mode wait sur cette table. Rien n'est perdu. Un principe similaire aux mutex et sémaphores.
Pour plus d'information:
Documentation officielle de Oracle sur la commande
Détail de la commande
Commentaires