L’objectif de cet article est d’expliquer comment verrouiller un enregistrement sur une base de données Oracle, à l’aide d’une instruction select.
Introduction
L’objectif de cet article est d’expliquer comment verrouiller un enregistrement sur une base de données Oracle, à l’aide d’une instruction select. Cela nous permet de verrouiller un enregistrement sans avoir à le modifier.
Bien que cela puisse ne pas sembler un cas d’utilisation très courant, la vérité est que nous pouvons en avoir besoin pour des programmes plus complexes (implémentés, par exemple, en PL / SQL), où nous devons effectuer des validations complexes avant de décider de mettre à jour un certain registre, et nous devons le maintenir verrouillé pendant ces validations, donc aucune autre transaction ne peut le modifier simultanément.
Nous utiliserons SQL Developer pour interagir avec la base de données et effectuer les requêtes pour ce tutoriel.
Verrouillage du même enregistrement
Comme d’habitude, nous allons commencer par créer une simple table factice, pour contenir nos données. Vérifiez l’exemple de création ci-dessous.
create table Worker(WorkerID varchar(20),WorkerName varchar(255),WorkerJob varchar(255),CONSTRAINT PK_Worker PRIMARY KEY (WorkerID));
Après cela, nous allons insérer des enregistrements factices, nous avons donc des données sur lesquelles travailler. Nous allons insérer trois enregistrements, puis valider la transaction. Vous pouvez utiliser le bouton Valider du menu Développeur SQL ou valider explicitement la transaction avec une instruction SQL.
insert into worker values ('1','John', 'nurse');insert into worker values ('2','Grace', 'farmer');insert into worker values ('3','Smith', 'doctor');
Maintenant, nous allons ouvrir une nouvelle feuille de calcul non partagée, afin que nous puissions tester les instructions SQL dans des transactions indépendantes. Vérifiez la figure 1 sur la façon de le faire. Vous pouvez en savoir plus sur les feuilles de calcul SQL Developer unshared dans cet article précédent.
Figure 1 – Ouverture d’une nouvelle feuille de calcul non partagée.
Maintenant, la première chose que nous allons faire est d’exécuter une sélection régulière dans les deux feuilles de calcul. Nous ferons une sélection pour obtenir l’enregistrement où le WorkerID est égal à 1. Ne faites pas de commits ou de rollbacks, exécutez simplement la même requête dans les deux feuilles de calcul.
select * from worker where WorkerID = 1;
Comme prévu, il n’y a pas de problème de concurrence et nous recevrons l’enregistrement dans les deux transactions (avec le nom de travail égal à « John »).
Maintenant, nous allons revenir à la première feuille de calcul (la même où nous avons créé la table) et ajouter une clause « for update » à la fin de l’instruction select, comme indiqué ci-dessous. Encore une fois, ne faites aucun commit ou rollback.
select * from worker where WorkerID = 1 for update;
Lorsque nous faisons cela, nous verrouillons les enregistrements de l’ensemble de résultats, sans qu’il soit nécessaire de les mettre à jour. En d’autres termes, nous verrouillons explicitement les enregistrements. Bien sûr, une fois que nous les avons verrouillés, nous pouvons les mettre à jour si nous le voulons. Les verrous sur les enregistrements sont libérés lorsque nous effectuons la prochaine validation ou restauration.
Donc, pour tester si les verrous fonctionnent, nous passons maintenant à la deuxième feuille de calcul (la feuille de calcul non partagée que nous avons ouverte). Là, si nous faisons une sélection régulière, sans la phrase « pour la mise à jour », nous obtiendrons l’enregistrement sans aucun problème.
select * from worker where WorkerID = 1;
Mais alors, si nous répétons la sélection, maintenant avec la phrase « pour la mise à jour » à la fin, elle se bloquera, car l’autre transaction a l’enregistrement avec WorkerID égal à 1 verrouillé, et nous essayons de le verrouiller à nouveau.
select * from worker where WorkerID = 1 for update;
Pour le déverrouiller, revenez simplement à la première feuille de calcul et validez ou annulez la transaction. Maintenant, revenez à la feuille de calcul non partagée et l’instruction SQL aurait dû être exécutée, renvoyant l’enregistrement souhaité.
Verrouillage de différents enregistrements
Une chose importante à garder à l’esprit est que seuls les enregistrements qui font partie de l’ensemble de résultats sont verrouillés par la transaction. Ainsi, différentes transactions peuvent faire « sélectionner pour la mise à jour » dans différents enregistrements.
Pour tester cela, accédez à la première feuille de calcul et sélectionnez les enregistrements 1 et 3, avec la phrase de sélection suivante:
select * from worker where WorkerID = 1 or WorkerID = 3 for update;
Allez maintenant à la feuille de calcul non partagée et essayez de verrouiller l’enregistrement 2.
select * from worker where WorkerID = 2 for update;
Cela devrait fonctionner et renvoyer un résultat, car l’enregistrement avec WorkerID égal à 2 n’a pas été verrouillé par la première transaction. Maintenant, si dans la même feuille de calcul non partagée, nous essayons de verrouiller l’enregistrement avec un identifiant de travailleur égal à 1 ou l’enregistrement avec un identifiant de travailleur égal à 3 (ou les deux), il se bloquera jusqu’à ce que nous validions ou annulions la transaction à partir de la première feuille de calcul.
Contenu connexe
- Oracle Sélectionner pour l’état de mise à jour
