Gestion des verrous

Bonjour,
Dans le cadre de mon projet, je dois garantir que les données ne soient modifiées que par un seul utilisateur à la fois. Plus précisément, il s'agit d'empêcher l'accès à une ligne si un utilisateur la déjà sélectionnée.
Je pense donc utiliser le mécanisme des verrous:
- De quelle manière puis-je m'y prendre
- Suis-je sur la bonne voie?
- Est-ce que seule la base de données doit gérer cela?

Merci par avance pour vos réponses.

PS : Des liens vers des tutoriels seraient le top

Cordialement,
Daniel

Bonjour,

Vous pouvez laisser faire SQL Server, il sait très bien faire cela.

Vous pouvez aussi ajouter une colonne à votre table et la mettre à jour à chaque fois qu'un utilisateur demande à la modifier, et ne remonter aux autres utilisateurs que les lignes pour lesquelles cette colonne n'est pas en cours de mise à jour ...

@++ ;)

Citation:

---

Envoyé par elsuket

Bonjour,

Vous pouvez laisser faire SQL Server, il sait très bien faire cela.

Vous pouvez aussi ajouter une colonne à votre table et la mettre à jour à chaque fois qu'un utilisateur demande à la modifier, et ne remonter aux autres utilisateurs que les lignes pour lesquelles cette colonne n'est pas en cours de mise à jour ...

@++ ;)

---

Peux-tu être plus précis sur la méthode à employer?

Merci par avance

Je vais tenter :)

Supposons que vous faites partie d'une équipe de tests informatique.
Chacun des membres de votre équipe peut réaliser un seul test à la fois.

Au départ, vous avez donc l'architecture suivante :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SET NOCOUNT ON GO   CREATE TABLE TEST ( IDTest INT IDENTITY CONSTRAINT PK_TEST PRIMARY KEY, nomTest VARCHAR(16) ) GO   INSERT INTO dbo.TEST VALUES ('INTEGRATION') INSERT INTO dbo.TEST VALUES ('UNITAIRE') INSERT INTO dbo.TEST VALUES ('FONCTIONNEL') INSERT INTO dbo.TEST VALUES ('RECETTE') INSERT INTO dbo.TEST VALUES ('NON-REGRESSION') GO

---

Mais comme votre équipe est très efficace, vous enchaînez les tests et il arrive parfois que vous fassiez parfois le même test, donc un travail en double.
Pour pallier à ce problème, vous décidez donc d'ajouter une identification au logiciel de tests, qui vous permettra de n'affecter un test qu'à un seul membre de votre équipe :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE TESTEUR ( IDTesteur INT IDENTITY CONSTRAINT PK_TESTEUR PRIMARY KEY, prenomTesteur VARCHAR(20) NOT NULL, nomTesteur VARCHAR(20) NOT NULL, CONSTRAINT UQ_TESTEUR UNIQUE (prenomTesteur, nomTesteur) ) GO   INSERT INTO dbo.TESTEUR VALUES ('El', 'SUKET') INSERT INTO dbo.TESTEUR VALUES ('Dana', 'X') INSERT INTO dbo.TESTEUR VALUES ('Barbara', 'ZEDPRAI') INSERT INTO dbo.TESTEUR VALUES ('Tatiana', 'TATIENAPAS') INSERT INTO dbo.TESTEUR VALUES ('Camille', 'HONNETE') GO

---

Puis vous modifiez la table TEST :

Code:

---

1 2	ALTER TABLE TEST ADD IDTesteur INT NULL CONSTRAINT FK_TEST_IDTesteur FOREIGN KEY (IDTesteur) REFERENCES dbo.TEST

---

Ainsi donc vous pouvez savoir qui effectue quel test, mais on ne garantit toujours pas qu'un utilisateur n'effectue qu'un seul test à la fois.
On ne peut pas ajouter une contrainte d'unicité, puisqu'il est possible que deux tests ne soient pas en cours, donc qu'ils n'aient pas d'utilisateur affecté (dans ce cas deux lignes auraient le marqueur NULL :aie:)

Nous créons donc le trigger suivant :

Code:

---

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

CREATE TRIGGER TR_AU_TEST ON TEST AFTER UPDATE AS BEGIN IF UPDATE(IDTesteur) BEGIN DECLARE @listeTesteurs VARCHAR(50)   -- Recherche si le testeur courant a déjà un test en cours -- et concaténation de leurs identifiants SELECT @listeTesteurs = ISNULL(@listeTesteurs, '') + CAST(I.IDTesteur AS VARCHAR) + ',' FROM dbo.TEST T JOIN INSERTED I ON T.IDTesteur = I.IDTesteur GROUP BY I.IDTesteur HAVING COUNT(*) > 1   IF @listeTesteurs IS NOT NULL -- si le testeur courant a déjà un test en cours BEGIN RAISERROR('Les utilisateurs %s ont déjà un test en cours !', 16, 1, @listeTesteurs) ROLLBACK END END END GO

---

Ensuite, vous créez la procédure stockée qui se chargera de marquer les tests :

Code:

---

1 2 3 4 5 6 7 8 9

CREATE PROCEDURE usp_Teste @_IDTest INT, @_IDTesteur INT AS BEGIN UPDATE dbo.TEST SET IDTesteur = @_IDTesteur WHERE IDTest = @_IDTest END

---

Testons, sachant qu'actuellement la colonne IDTesteur de la table TEST a le marqueur NULL pour toutes ses lignes :

Code:

---

EXEC dbo.usp_Teste 1, 1

---

Citation:

---

IDTest nomTest IDTesteur
----------- ---------------- -----------
1 INTEGRATION 1
2 UNITAIRE NULL
3 FONCTIONNEL NULL
4 RECETTE NULL
5 NON-REGRESSION NULL

---

Prenons le test n°2 et affectons-le au même utilisateur :

Code:

---

EXEC dbo.usp_Teste 2, 1

---

Nous obtenons le message d'erreur suivant :

Citation:

---

Msg*50000, Niveau*16, État*1, Procédure*TR_AU_TEST, Ligne*20
Les utilisateurs 1, ont déjà un test en cours !
Msg*3609, Niveau*16, État*1, Procédure*usp_Teste, Ligne*6
La transaction s'est terminée dans le déclencheur. Le lot a été abandonné.

---

C'est le comportement que nous voulions :)
N'hésitez pas à poster s'il y a quelque chose d'obscur.

@++ ;)

Merci pour cette magnifique astuce néanmoins, il va me falloir gérer l'accès à cette table via les sessions. Imaginons si un utilisateur est sur une ligne et qu'il se déconnecte, il me faut libérer la ligne pour éviter un blocage intempestif.

Encore merci, je vais tester cela aujourd'hui

C'est exactement pour cela que par principe on ne gère jamais de verrouillage pessimiste dans un SGBDR C/S, sauf si vous voulez des performances lamentables du fait des blocages qui seront induits !

A +

Citation:

---

Envoyé par SQLpro

C'est exactement pour cela que par principe on ne gère jamais de verrouillage pessimiste dans un SGBDR C/S, sauf si vous voulez des performances lamentables du fait des blocages qui seront induits !

A +

---

Veux-tu dire que la méthode décrite ci-dessus n'est pas valide parce qu'il me semble pas qu'il utilise des verrous mais plutôt un mécanisme liant la gestion des sessions utilisateurs et la mise à jour d'un champ

Un verrou c'est un concept. Ce qu'il tente de faire est du verrouillage pessimiste manuellement, technique abandonnées de puis les années 70 à causes des performances catastrophique que cela induit.

A +

Attention,

J'ai bien écrit :

Citation:

---

Vous pouvez laisser faire SQL Server, il sait très bien faire cela.

---

@++ ;)

Désolé d'être insistant mais alors comment faire autrement?

De plus amples renseignements me seraient d'un grand aide

La probabilité que deux personnes modifient la même ligne en même temps est très faible. plus la base grossie plus cette probabilité tends vers zéro.

Aussi est-il absolument pas nécessaire de pré verrouiller (verrouillage pessimiste).

De plus en faisant cela vous ne réglez pas le problème fonctionnel de fond : imaginez que la secrétaire "monte" la fiche du client 133, ce qui verrouille donc les lignes de tables relatif à ce client. Puis survient Marcel, le délégué CGT... il vient lui parler d'une rumeur de plan de licenciement... On va à la cafète... Bref les lignes sont verrouillées intempestivement pendant 2 heures? Pendant ce temps là le PDG qui veut rentrer de nouvelles informations sur ce client ne peut rien faire... Il enrage et va trouver le c**** CENSURÉ *** de développeur qui à mis ce verrous de m**** CENSURÉ *** et le licencie sur le champ...

C'est en gros ce qui risque de vous arrivez si vous tenez absolument à faire du verrouillage pessimiste !

A +

Merci pour ces excellentes remarques, j'en avais entraperçu la difficulté mais j'essayai de trouver une solution puisqu'en faîtes ce besoin est demandé par le responsable informatique client=> Je vais devoir étayer avec tes exemples.

Merci par avance.

Citation:

---

ce besoin est demandé par le responsable informatique client

---

Virez le pour incompétence notoire ! ;-)

A +

Si je suis le fil de la discussion, je pose cette question :
Par défaut, comment sql server 2005 gére les verrous par défaut.
Mon cas ressmble un peu au problème du topic, car je dois réaliser une application de gestion d'une clinique, donc je ne sais pas si je dois laisser sql server 2005 gérer par defaut, ou bien je dois gérer manuellement les verrous sur les transactions.
Cas de figure : La récéptionniste veut modifier le dossier du patient qu'un médecin est entrain de consulter au même moment....

Bonjour,

Citation:

---

Cas de figure : La récéptionniste veut modifier le dossier du patient qu'un médecin est entrain de consulter au même moment....

---

Le médecin lit le dossier, il a donc extrait les données à un moment précis.

La transaction qui permet au réceptionniste de modifier le dossier d'un patient ne doit pas durer une éternité, sinon cela signifie que l'application est mal conçue, car elle attend que l'utilisateur ait validé sa modification pour la valider en base de données.

Certes, lorsque le médecin consulte le dossier du patient, certaines données sont obsolètes dès lors que le réceptionniste a validé ces données.
Mais je doute que le réceptionniste puisse modifier les mêmes données que le médecin ;), ou là encore il y a un problème de conception de l'application.

@++ ;)

Bonjour,
Pour reprendre l'exemple cité :

Citation:

---

Envoyé par SQLpro

La probabilité que deux personnes modifient la même ligne en même temps est très faible. plus la base grossie plus cette probabilité tends vers zéro.

Aussi est-il absolument pas nécessaire de pré verrouiller (verrouillage pessimiste).

De plus en faisant cela vous ne réglez pas le problème fonctionnel de fond : imaginez que la secrétaire "monte" la fiche du client 133, ce qui verrouille donc les lignes de tables relatif à ce client. Puis survient Marcel, le délégué CGT... il vient lui parler d'une rumeur de plan de licenciement... On va à la cafète... Bref les lignes sont verrouillées intempestivement pendant 2 heures? Pendant ce temps là le PDG qui veut rentrer de nouvelles informations sur ce client ne peut rien faire... Il enrage et va trouver le c**** CENSURÉ *** de développeur qui à mis ce verrous de m**** CENSURÉ *** et le licencie sur le champ...

C'est en gros ce qui risque de vous arrivez si vous tenez absolument à faire du verrouillage pessimiste !

A +

---

Si on reste dans votre logique ca veut dire que dans le cas ou j'ai une fiche du client 133 nommé "TOTO", ouvert par deux utilisateur .
Si j'autorise les deux utilisateurs a entré en modification du client alors c'est le dernier qui va sauvegarder qui aura raison. Du coup, si le premier qui sauvegarde renomme le client en "DUPONT" et le second en "DUDULE", le nom finale sera "DUDULE".

De ce fait qu'elle est la meilleur solution ?


J'ai posté mon problème qui est quasiement le même http://www.developpez.net/forums/d70...ql-nhibernate/

++

Comme à pu l'indiquer SQLPro, il faut réfléchir sur les impacts de la mise en place d'un tel système : Mais les risques sont extrêmement limités