Discussion :

[yoyodemars]

Bonsoir ,
Suite a mon dernier post j'ai transformé ma petite application pour fonctionner sur sql-server express 2008 r2 afin de pouvoir gérer les concurrences d’accès.

JE souhaite donc verrouiller une ligne de ma table SOCIETE en utilisant la requette suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM SOCIETE with (rowlock) WHERE CODE_SOCIETE=1

Mais visiblement cela n'a aucune influence , je peux faire deux Forms qui éditent la même ligne de ma table.

faut il que je vérifie moi même si un verrou existe ?
Si oui Comment ?

Merci.

[Bluedeep]

Bonjour

Je pense que ta question serait plus à sa place sur le forum Sql Server.

ADO.Net n'a pas grand chose à voir la dedans.

Accessoirement, lz fonctionnement de ROWLOCK dépend du niveau d'isolation de transaction qui a été spécifié (de mémoire, par exeemple, si l'isolation est de type SNAPSHOT, ROWLOCK ne fait effet que si d'autres instructions de LOCK sont indiquées - je ne sais plus lesquels, HOLDLOCK peut être ?).

cf. MSDN pour les détails.

[StringBuilder]

Bluedeep, pour une fois, t'as faux, c'est bel et bien un problème de conception C#

Pour que les LOCK soient actifs dans ton application, tu dois :
- Exécuter ta requête à l'intérieur d'une transaction
- Laisser la connexion ouverte et ne pas commit/rollback la transaction tant que tu as besoin de conserver le lock.

[Bluedeep]

Bluedeep, pour une fois, t'as faux, c'est bel et bien un problème de conception C#

Pour que les LOCK soient actifs dans ton application, tu dois :
- Exécuter ta requête à l'intérieur d'une transaction

C'est vrai. Je n'avais pas imaginé une seule seconde qu'il n'exécutait pas sa requête dans une transaction ....

[yoyodemars]

Je découvre cette notion de transaction ,car j'utilisé avant une base Access.

J'ai trouvé un cours vb.net ou il explique les transactions avec Ado.Net(en fin de pdf).

http://fikou-sama.freevar.com/cours/...ux.donnees.pdf

j'essaye de mettre en application ces infos.

Merci pour vos réponses.

[StringBuilder]

Ce qu'il faut savoir, dans les très grandes lignes (ps : Access aussi supporte les transactions) :

Une transaction a un début (en SQL c'est BEGIN TRANSACTION)
Et se termine soit :
- Par un COMMIT (on valide l'ensemble de la transaction)
- Par un ROLLBACK (on annule l'ensemble de la transaction)

Une transaction est un élément de base d'un SGBDR, dans la mesure où c'est la seule chose qui permet de garantir l'intégrité des données à tout moment. La plupart des mécanismes d'intégrité d'une base de données sont d'ailleurs basés sur des transactions implicites (c'est à dire que lorsque tu fais un "update matable where un critère", une transaction est crée, puis ligne par ligne le SGBD fait les modifs, et si tout s'est bien passé, il fait un COMMIT final, ou un ROLLBACK s'il y a eu une erreur, et à ce moment, même si tu plantes à la 1000ème ligne, aucune ligne n'aura été modifiée dans la base)

En .NET, c'est l'objet SqlTransaction qui correspond à une transaction dans la base de données.

Selon le SGBD (avec SQL Server c'est le cas) les transactions peuvent être :
- imbriquées
- concurrentes

Ainsi, tu peux faire une transaction à l'intérieur d'une autre transaction. Dans ton cas, ce sera très utile si tu as par exemple :
- Un écran "contact" que tu souhaites verrouiller lors de la modification d'un contact : création d'une transaction "majcontact", avec un rowlock.
- Un popup qui permet de saisir des informations liées au contact, mais qui peuvent être partagées (rendez-vous avec le contact par exemple). Tu crées alors une transaction imbriquée "majrdv").
=> Malgré la validation des modifications de rendez-vous, si tu fais un rollback sur la modification du contact, les modifications du rendez-vous seront perdues aussi (par exemple, l'utilisateur s'est rendu compte qu'il s'est gouré de contact)

Donc en gros, dans ton programme, dans chaque form, tu fais un begintrans, en vérifiant si tu souhaites ou non imbriquer ou mettre en concurrence les transactions. Si tu souhaites mettre en concurrence, attention, car tu peux faire des deadlock (s'il y a un lock sur le contact, et que dans le popup de mise à jour des rdv tu souhaites récupérer des données sur le contact, si c'est une transaction imbriquée, pas de problème, mais concurrente, tu vas te bloquer tout seul !). En revanche, si les transactions sont concurrentes, tu peux commit/rollback les deux séparément sans qu'elles s'impactent mutuellement (commit des rendez-vous mais rollback des modifs du contact par exemple)

Voilà, y'a pas mal d'autres choses très intéressantes à savoir sur les transactions, mais d'après l'expression de tes besoins je pense qu'on a fait le tour de celles dont tu as réellement besoin dans un premier temps.

Note toutefois que les principaux avantages d'une transaction :
- C'est de pouvoir poser un lock sur une ligne, table, etc.
- De pouvoir annuler une mise à jour même après avoir effectivement joué les requêtes de mise à jour

Les principaux inconvénients :
- Les locks justement : attention aux deadlocks ! (si un utilisateur modifie une fiche et part en pause café, personne ne peut accéder à la fiche, si un utilisateur lock une fiche contact et qu'un autre lock ses rendez-vous, il peut y avoir un deadlock si les utilisateurs souhaitent consulter/modifier les données lockées par l'autre utilisateur et surtout, l'exemple précédent, si tu gères mal les transactions concurrentes au sein de ton programme
- Les ralentissements : les transactions sont assez lourdes à gérer pour le SGBD. Il faut donc éviter de faire des transactions systématiquement, et encore mois faire trop de requêtes au sein d'une même transaction. Attention, si une transaction est imbriquée dans une autre, cela fait grossir la transaction "parente".
- Les locks à nouveau : si un utilisateur modifie des lignes et ne commit pas, même si toi tu n'as pas mis de lock explicite, des locks peuvent se produire lorsqu'une autre personne voudra consulter ou mettre à jour ces données. Exemple d'explication du pourquoi :

Tu as une table "balance" qui contient une unique ligne, avec la balance de ton compte en banque.

La balance initiale est de 100 €.
Si un utilisateur est en train de faire une dépense, il va décrémenter la balance de 100 €.
Un autre utilisateur saisi une entrée d'un chèque de 20 €.
=> Si le premier utilisateur valide sa transaction, la balance avant encaissement du chèque sera 0 €. Mais s'il rollback, elle sera de 100 €.
=> Donc le SGBD va forcément locker le second utilisateur (le programme freeze) en attendant que le premier utilisateur décide si oui ou non il valide sa transaction.

Ces freezes peuvent être très désagréables, voir source de plantages (utilisateurs qui ferment intempestivement le programme et laissent des connexions fantôme sur le serveur, timeout de connexion, etc.)

Voilà.

Mais il ne faut surtout pas en avoir peur pour autant, les transactions c'est essentiel il faut les utiliser, mais seulement lorsque c'est nécessaire

[Bluedeep]

J'ajouterais juste une chose à l'excellente description de StringBuilder.
Dans le cas de Sql Server (mais pas d'Oracle) il est possible de lire les données vérouillées par une transaction en envoyant avant le select la commande SQL :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

set transaction isolation level read uncommitted;

A utiliser avec prudence ....

[yoyodemars]

bonsoir ,
Je reviens vers vous car je n'arrive pas a utiliser ce fameux rowlock
Voila un exemple de code qui ne fonctionne pas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=devtest")
        cn.Open()
 
        Dim trs As SqlTransaction = cn.BeginTransaction
        Dim cmd As SqlCommand = cn.CreateCommand()
        cmd.Transaction = trs
 
        cmd.CommandText = "SELECT * FROM SOCIETE with (ROWLOCK) WHERE CODE_SOCIETE=1"
        Dim myReader As SqlDataReader = cmd.ExecuteReader()
 
        Do While myReader.Read()
            ListBox1.Items.Add(myReader.GetString(0))
        Loop

J'ai mis ce code dans une form que je lance 2 fois et il n'y a pas d'exception a la 2eme l'ouverture de la form.
Pourtant je n'ai pas fait le commit ni fermé la connexion.

y a t' il une erreur dans le code ou est ce moi qui est mal compris le fonctionnement ?

[StringBuilder]

Normal : ta connexion est crée dans la méthode. Son scope est alors celui de la méthode, et même si tu ne l'indique pas, elle est perdue automatiquement lorsque tu sors de la méthode.

Ce qu'il se passe dès que le programme sort de la méthode :
- Le command est détruit
- La transaction est rollbackée puis détruire
- La connexion est fermée puis détruite

Du coup le lock n'est actif que quelques millièmes de secondes

Il faut donc :
- Créer ta connexion en tant que membre de la Form
- Mettre ta transaction en tant que membre de la Form aussi, éventuellement dans une pile si tu désires imbriquer des transactions
- Ouvrir la connexion et la transaction dans une action (soit le Form_Load, soit un bonton_click)
- Fermer la transaction et la connexion dans une autre action (soit le Form_Close, soit un autre bouton_click)

[yoyodemars]

Bonjour,
J'ai un peut de mal, quand vous dites de créer la connexion en tant que membre de la Form

cela veut il dire de la créer en dehors d'une action ?

J'ai fais cela mais ça ne marche pas mieux :
Si c'est pas trop demander pourriez vous poster un exemple de code.
Merci d'avance.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Imports System.Data.SqlClient
Public Class Form1
    Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=devtest")
 
    Dim trs As SqlTransaction
    Dim cmd As SqlCommand
 
 
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        cn.Open()
        trs = cn.BeginTransaction
 
        cmd = cn.CreateCommand()
        cmd.Transaction = trs
        cmd.CommandText = "SELECT * FROM SOCIETE with (ROWLOCK) WHERE CODE=1"
        Dim myReader As SqlDataReader = cmd.ExecuteReader()
 
        Do While myReader.Read()
            ListBox1.Items.Add(myReader.GetString(0))
        Loop
        myReader.Close()
    End Sub
 
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        trs.Commit()
        cn.Close()
        Me.Close()
    End Sub
End Class

[StringBuilder]

Bonjour,

Afin de ne pas dire de bêtises (au vue de tes galères, je préfère tester avant d'affirmer) je vais tenter de faire une petite application modèle qui permet de mettre en avant le fonctionnement de ces mécanismes (que je connais très bien "sur le principe" mais que j'ai mis en place une unique fois, il y a très longtemps, et dans un contexte bien différent).

Le seul hic, c'est que sur le plan professionnel, j'ai pas trop de temps, et ça se vois un peu trop quand je glande (surtout avec mon super PC qui met 3 plombes à basculer entre Oracle SQL Developper et Microsoft SQL Server Management Studio... forcément, faire tourner Lotus Notes, SQL Server, Visual Studio, SQL Developper sur la même machine avec 1 Go de mémoire, c'est chaud...). Déjà rien que pour ouvrir une page dans Chrome il faut 5 minutes...

Et sur le plan personnel, généralement quand j'allume le PC j'ai pas le temps d'attendre la fin du post du BIOS avant de me faire rappeler à l'ordre (va préparer le biberon de notre fille !).

Bref, j'essaye d'avancer un peu... J'ai développé 2 écrans et mis en place la base... Mais pas encore eu le temps d'implémenter les mécanismes de gestion des locks. J'espère avoir le temps d'avancer en début de semaine prochaine... Je ne t'oublie pas en tout cas. Désolé de ne pas être plus présent.

PS : Par contre, mauvaise nouvelle, moi je fais du C#, il faudra donc un peu décrypter mon code. Ceci dit, j'ai cru comprendre que tu débutais... Avis personnel qui n'engage que moi : Il est encore temps de passer au C#, avant de regretter le choix de VB.NET

[yoyodemars]

Bonsoir,
Merci StringBuilder pour le temps que tu me consacre.
Ayant moi même un emploie du temps professionnel très remplis et deux petits monstres a la maison ,je sais a quel point il est dur de trouver du temps.
Comme je l'ai signalé je fais cela a titre pédagogique donc si il y doit y avoir une solution dans une semaine, un mois ce n'est pas grave.

Merci encore.

[StringBuilder]

Bonjour,

Je suis en train de "finaliser" l'application modèle qui montre comment ça marche.

Je viens de me rendre compte que ROWLOCK est insuffisant !

En effet, ROWLOCK ne lock absolument rien.
Il force juste le SGBD à utiliser le verrouillage par ligne plutôt que les autre modes de verrouillage (par clé, par page ou par table).

Il faut le combiner à XLOCK, qui permet de dire "je veux un verrou exclusif sur tout ce que je lis".

=> Ainsi, on force le SGBD à verrouiller les lignes lues par le SELECT. Si on ne met pas le XLOCK, seule une opération UPDATE ou INSERT permettrait de locker les lignes modifiées, et c'est pas ce qu'on cherche

[StringBuilder]

Bon, je vais poster mon exemple "as this".
Il n'est pas terminé, mais permet de se rendre compte du fonctionnement.

Au départ, j'avais vu "grand" (plusieurs forms, gestion de plusieurs tables) mais finalement, je ne joue que dans la table "contact", c'est bien suffisant en terme de boulot

Une piste pour améliorer considérablement l'expérience utilisateur :
- Rendre les requêtes qui risquent d'être lockées asynchrones : ainsi, on peut afficher un message qui permet à l'utilisateur d'annuler l'action, plutôt que de figer le programme.

Structure de la base de données :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
43
44
45
46
47
48
 
create database testlock;
 
use testlock;
 
create table contact
(
	contact_id int not null identity(1,1) primary key,
	contact_nom varchar(50) not null
);
 
create table teltype
(
	teltype_id int not null identity(1,1) primary key,
	teltype_nom varchar(50) not null
);
 
create table telephone
(
	telephone_id int not null identity(1,1) primary key,
	teltype_id int not null references teltype(teltype_id),
	contact_id int not null references contact(contact_id),
	telephone_numero varchar(50) not null
);
 
create table salle
(
	salle_id int not null identity(1,1) primary key,
	salle_nom varchar(50)
);
 
create table rdv
(
	rdv_id int not null identity(1,1) primary key,
	rdv_titre varchar(50) not null,
	rdv_datdeb datetime not null,
	rdv_datfin datetime not null,
	salle_id int not null references salle(salle_id),
	contact_id int not null references contact(contact_id)
);
 
create table participant
(
	participant_id int not null identity(1,1) primary key,
	contact_id int not null references contact(contact_id),
	rdv_id int not null references rdv(rdv_id),
	participant_obligatoire bit not null
);

Quelques données de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
use testlock;
 
insert into salle (salle_nom) values ('Printemps');
insert into salle (salle_nom) values ('Eté');
insert into salle (salle_nom) values ('Automne');
insert into salle (salle_nom) values ('Hivers');
 
insert into teltype (teltype_nom) values ('Mobile professionel');
insert into teltype (teltype_nom) values ('Mobile personnel');
insert into teltype (teltype_nom) values ('Domicile');
insert into teltype (teltype_nom) values ('Bureau');
insert into teltype (teltype_nom) values ('Fax');
 
insert into contact (contact_nom) values ('Sylvain');
insert into contact (contact_nom) values ('François');
 
insert into telephone (contact_id, telephone_numero, teltype_id) values (1, '0623456789', 1);
insert into telephone (contact_id, telephone_numero, teltype_id) values (1, '0654987321', 2);
insert into telephone (contact_id, telephone_numero, teltype_id) values (1, '0123456789', 3);
insert into telephone (contact_id, telephone_numero, teltype_id) values (2, '0147852369', 4);
insert into telephone (contact_id, telephone_numero, teltype_id) values (2, '0159753648', 5);

Et les passages intéressants du code :
Lorsqu'on clique sur le bouton "voir", on ne modifie pas le contact. On peut donc se baser sur les données déjà committées dans la base. On utilise alors la requête suivante, en dehors d'une transaction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select contact_nom from contact where contact_id = @contact_id

Il n'y a rien de spécial en fait. SQL Server n'est pas idiot, il sait faire la part des choses : puisqu'on ne veut rien changer, on peut tout à fait afficher les données telles qu'elles étaient avant l'application du lock.

En revanche, lorsqu'on veut modifier un contact :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select contact_nom from contact with (SERIALIZABLE NOWAIT ROWLOCK XLOCK) where contact_id = @contact_id

Ici, on veut :
- Ne lire que ce qui est déjà commité. D'où le SERIALIZABLE, qui interdit la lecture de ce qui est déjà en cours de transaction
- Planter dès qu'on rencontre un LOCK (ça évite de freezer pendant 5 minutes)
- Locker à la ligne, même si le SGBD trouve que locker toute la page ou toute la ligne serait plus approprié. C'est surtout pour éviter qu'il ne lock que la clé, puisqu'on ne veut surtout pas qu'une autre personne modifie les données de la ligne
- Forcer le lock exclusif sur les lignes lues

Tu remarqueras aussi que j'ai pas mal utilisé :
- des variables dans mes requête, plutôt que de concaténer avec les valeurs issues des textbox directement. Ceci s'appelle des requêtes paramétrées. en plus d'être bien plus performant, c'est une sécurité contre les risques d'injection SQL (tentative de hack ou plantage de ton application par des personnes mal-intentionnées)
- des blocks using : ceci sert à dire au garbage collector quand je n'ai plus besoin d'une variable qui implémente l'interface IDisposable. Ceci permet de gagner en termes de performances (utilisation mémoire surtout) car le GC sait à tout moment si une variable est vraiment encore utilisée ou non. il vaut mieux prendre l'habitude d'utiliser using, surtout pour les accès à la base, car les traitements peuvent être longs et consommateurs. Autant éviter de consevers des objets volumineux en mémoire, d'autant qu'ils peuvent être source de lock dans la base.

Voilà, j'espère que tu ne seras pas trop déçu

[yoyodemars]

Bonsoir ,
Je viens juste de finir ma journée de boulot.J'analyse ton code demain soir car la je suis claqué.
Je test cela demain soir et je te tient au courrant.
Merci encore pour tous le temps que tu ma consacré.

[yoyodemars]

Bonsoir,
je viens de tester avec xlock et sa marche.il ne me reste plus qu'a bien gérer les exceptions.

Je vais installer c# express pour tester plus en détail ton programme et plus si affinité.

Non je ne suis absolument pas déçu

Merci encore.

[yoyodemars]

Ton programme aborde exactement tous les points clés don j'ai besoin.La compréhension par rapport au c# n'est pas un gros problème finalement.
Je peux maintenant marquer le sujet résolu.