Discussion :

[Zzapi]

Bonjour, je cherche à comprendre comment réaliser une application delphi/sqlserver multi-utilisateur ou les utilisateurs peuvent voir les modifications effectués par les autres utilisateurs ?!

Pour cela je me suis connecté avec les composants ADO à la base Northwind fournit en exemple dans SQLServer (voir le code ci dessous).

Problémes :
Quand je lance 2 instances sur mon poste disons Inst1 et Inst2, si je modif un enregistrement dans le DBGrid1 de Inst1 alors DBGrid1 de Inst2 est aussi modifié (à condition de l'aider un peu en cliquant dessus, c'est pas top) mais si je modif DBGrid1 de Inst2 alors Inst1 quand à lui n'est pas modifié ?

Manifestement je ne suis pas au point une âme charitable peut-elle m'aider à comprendre mes erreurs, 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
30
31
 
procedure TForm1.FormCreate(Sender: TObject);
begin
 
  // connexion à la base de donnée "Northwind" fournit en exemple dans SqlServer
  with ADOConnection do
  begin
    ConnectionString := 'Driver={SQL Server}; ...';
    CursorLocation := clUseServer;
    IsolationLevel := ilUnspecified;
    LoginPrompt := False;
    Connected := True;
  end;
 
 // selection de la table "Region"
  with ADOTable do
  begin
    Connection := ADOConnection;
    CursorLocation := clUseServer;
    CursorType := ctKeyset;
    ReadOnly := False;
    TableDirect := True;
    TableName := 'dbo.Region';
    Active := True;
  end;
 
  // affichage de la table dans le DBgrid
  DataSource.DataSet := ADOTable;
  DBGrid1.DataSource := DataSource;     
 
end;

[cmen76]

C'est un piège classique.
Il ne faut pas croire que le serveur va "broadcaster" (envoyer une notification à tous ses clients connectés) pour avertir d'une modification d'un utilisateur vers tous les autres, susceptibles d'exploiter les mêmes données.
Le CursorLocation clUseServer d'ADO peut lui aussi induire en erreur : Il ne faut pas en déduire qu'un curseur unique va être partagé entre les différents clients ; en réalité, chaque client va se créer un curseur stocké sur le serveur, représentant une image à un moment T des données souhaitées.
La modification par le client 1 d'une donnée affichée par le client 2 n'aura donc de façon "live" aucun impact sur l'affichage du client 2. En revanche, dès que le client 2 va vouloir modifier à son tour la même donnée, il obtiendra une erreur lui indiquant que les données ont changé entre temps (et donc qu'il doit redemander les données à jour).
Par expérience, 2 possibilités pour gérer l'accès concurrent :
* le mode optimiste : le dernier qui validera ses données a raison, quitte à perdre la modification effectuée entre temps
* le mode pessimiste : la première modification verrouille la donnée, qui devient indisponible pour tous les autres.

Se rapprocher de la documentation d'SQL Server pour ce qui concerne le "niveau d'isolation des transactions" et la gestion des verrous (locks), sachant que Microsoft préconise des transactions courtes dans le temps pour limiter la consommation de ressources par les verrouillages.

[Andry]

En plus de ce que cmen76 à dit, j'ai mis un truc en place pour dispatcher les mis à jour au niveau de chaque client : chat inter-applicatif qui comprend :
- un serveur qui va recevoir les messages d'un client et les dispatcher aux autres qui sont en ecoute.
- les clients qui à chaque actions envoie le message vers le serveur pour en avertir les autres.
Si la liaison vers le serveur est couper, je bascule en mode manuel ou via un timer pour le refresh.

Voilà.

[Zzapi]

Merci "cmen76" pour ton explication très instructive. Je perçois un peu mieux les choses, j'ai donc essayé de réalisé un verrou pessimiste avec un curseur serveur sur ma fameuse table Région (bdd nothwind dans sqlserver). En particulier je n'ai pas pu affecter la propriété adoTable.IndexName avec la clé primaire de la table avec une erreur du genre le fournisseur ne supporte pas cette propriété. Je déposerai le source très prochainement pour étayer mais propos.

Pour "Andry" stp une petite précision. Ta solution à l'air sympa mais je veux m'assurer de bien comprendre, il s'agit de la mise en place d'un serveur Udp, tes clients en + des leurs cnx à la base de données, notifies à chaque requête (delete, update ...) le serveur udp qui se charge de dispatcher l'infos de rafraîchissements des données aux autres clients connectés. Mais dans ce cas tous les clients exécutent un Refresh en même tps ? Si tu as a bcp de clients connectés et une bdd volumineuse y pas de pb ? Quels sont les avantages et inconvénient de ta méthode ?

[Rayek]

Merci "cmen76" pour ton explication très instructive. Je perçois un peu mieux les choses, j'ai donc essayé de réalisé un verrou pessimiste avec un curseur serveur sur ma fameuse table Région (bdd nothwind dans sqlserver). En particulier je n'ai pas pu affecter la propriété adoTable.IndexName avec la clé primaire de la table avec une erreur du genre le fournisseur ne supporte pas cette propriété. Je déposerai le source très prochainement pour étayer mais propos.

Déjà travailler avec des TAdoTable est une très mauvaise idée, il vaut mieux travailler avec des requetes.
Sinon, il y a aussi les transactions (fortement conseillé surtout qu'elles fonctionnent très bien avec SQL server) qui permettent de gèrer les problèmes concurentiels.

Petite explication : http://sql.developpez.com/sqlaz/techniques/

[Andry]

stp une petite précision. Ta solution à l'air sympa mais je veux m'assurer de bien comprendre, il s'agit de la mise en place d'un serveur Udp, tes clients en + des leurs cnx à la base de données, notifies à chaque requête (delete, update ...) le serveur udp qui se charge de dispatcher l'infos de rafraîchissements des données aux autres clients connectés.

Oui, tu as tout compris.

Mais dans ce cas tous les clients exécutent un Refresh en même tps ? Si tu as a bcp de clients connectés et une bdd volumineuse y pas de pb ? Quels sont les avantages et inconvénient de ta méthode ?

Bah en faites, ça dépends, seules les clients concernés seront mis à jour. Si par exemple, un message informe les clients qu'une update de la table XXX est effectué, seule les clients qui est en relation avec la table XXX est affecté, les autres ignorent le message.

Voila

[Zzapi]

Merci "Malatar" mais pourquoi il faut mieux pas utiliser TAdoTable ?
Tu me conseilles donc d'utiliser TAdoQuery pour les requêtes qui retourne un résultats et TAdoCommand
pour le reste (je vois pas a quoi sert TAdoCommand d'ailleurs car on peut tout faire TAdoQuery) ?

Bon admettons que je veuille supprimer une ligne dans ma table Région mais il se pourrait que cette ligne
soit déjà supprimée du fait d'un autre utilisateur, est ce que je peux le savoir avant de lancer la reqête de suppression ?

Sinon comment faire pour ce type de code :
- Connection avec un curseur serveur car multi utilisateur.
- adoconnection.IsolationLevelil := ilRepeatableRead; (pour delete avec sqlserver).
- LockType : ? je sais pas quoi mettre

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
    ADOConnection1.BeginTrans
    try
        AdoQuery.SQL.Add('Delete from region where IDRegion = 1');
        AdoQuery.Active:=True;
        ADOConnection1.Commit;
    except
      on e:exception do
      begin
        ADOConnection1.RollBack;
        AdoTable.Refresh;            // table region
      end;
    end;

Dans ce cas la détection de la suppression de la ligne par un autre utilisateur se produit à posteriori, est ce une bonne façon de faire ?

[cmen76]

En l'occurrence,

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
ADOConnection1.BeginTrans
    try
        AdoQuery.SQL.Text := 'Delete from region where IDRegion = 1';
        AdoQuery.ExecSQL;
        ADOConnection1.Commit;
    except
      on e:exception do
      begin
        ADOConnection1.RollBack;
        AdoTable.Refresh;            // table region
      end;
    end;

le bloc except...end ne sera quasiment jamais appelé (sauf déconnexion), car le delete ne lèvera pas d'exception (0 ligne affectée, et c'est tout)

[Zzapi]

est ce que c mieux comme ça ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
ADOConnection1.BeginTrans
    try
        AdoQuery.SQL.Text := 'Delete from region where IDRegion = 1';
        if ADOQuery1.ExecSQL=0 then
           Showmessage('déja supprimé, les données vont être màj...') 
        else   
          ADOConnection1.Commit;
        AdoTable.Refresh;            // table region 
    except
      on e:exception do Showmessage('application déconnectée de la base ...')
    end;

[cmen76]

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
    ADOConnection1.BeginTrans
    try
        AdoQuery.SQL.Text := 'Delete from region where IDRegion = 1';
        if ADOQuery1.ExecSQL=0 then
        begin
           ADOConnection1.Rollback;
           Showmessage('déja supprimé, les données vont être màj...');
        end
        else   
          ADOConnection1.Commit;
        AdoTable.Refresh;            // table region 
    except
      on e:exception do Showmessage('application déconnectée de la base ...')
    end;

Pas d'objection votre honneur

[Zzapi]

merci

Et avec une requête Update ou Insert le code du thread #7 est il valide ?

[cmen76]

Update pourrait échouer si 1 enregistrement du lot concerné par la clause where est verrouillé par un autre utilisateur, c'est-à-dire que l'autre utilisateur est au sein d'une transaction, a modifié les données mais n'a pas encore commité.

Update comme Insert pourraient échouer par violation de contrainte par exemple (clé primaire déjà utilisée, ou contrainte de clé étrangère en échec, etc).

Actuellement le message utilisateur indiquerait un problème de connexion...

[Zzapi]

Ok ce code pour du update et insert c'est mieux ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
    ADOConnection1.BeginTrans
    try
        // ou avec une req insert 
        AdoQuery.SQL.Add('UPDATE region SET description = 'ma_desc' WHERE IDRegion = 1');        
        AdoQuery.Active:=True;
        ADOConnection1.Commit;
    except
      on e:exception do ADOConnection1.RollBack;
    end;
   AdoTable.Refresh;   // dans tous les cas

[cmen76]

Pour insert, update, delete on écrit

Code :

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

adoquery.execsql

Pour select

Code :

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

adoquery.open

qui est l'équivalent de

Code :

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

adoquery.active := true;

Par ailleurs, Malatar te déconseillait l'usage de TADOTable, à juste titre puisqu'il est préférable de ne sélectionner que les lignes utiles à l'affichage, et non toutes celles contenues dans la table... surtout si ce dataset est lié à une dbGrid... Généralement on préférera utiliser un TADOQuery contenant un select... where...

Pour le reste, rien à dire.

[Zzapi]

super clair !

Sinon concernant le multi utilisateur j'ai vu qu'il fallait tj utiliser un curseur coté serveur, est ce toujours vrai ?

J'ai également vu qu'avec sqlserveur il fallait changer "adoconnection.IsolationLevel" en fonction de la requête à exécuter, en pratique est ce comme ça que l'on fonctionne ?
ex :
UPDATE avec mise à jour de clef primaire , INSERT : SERIALIZABLE
UPDATE sur valeurs courantes, DELETE filtrés sur CLEFS : REPEATABLE READ
Etc …

Pour le verrou il faut choisir entre optimiste et pessimiste.

Enfin pour les transactions il est conseillé de placer les transactions sur le serveur, je suppose qu'ils parlent de transaction volumineuse dans de procédure stockées, nos petites transactions précédentes (UPADATE, INSERT ..) ne sont pas concernées ?

[cmen76]

Bienvenue dans les méandres du paramétrage ADO.

Pour ce qui est de CursorLocation et LockType :
Côté serveur, le curseur est maintenu par SQL Server (clUseServer, ltOptimistic ou ltReadOnly)
Côté client, il est local au poste client, et maintenu par ADO ; cela peut être utile pour un mode déconnecté : on rapatrie un recordset, on le manipule localement puis on passera plus tard un lot de requêtes correspondant aux modifications différées (clUseClient, ltBatchOptimistic ou ltReadOnly).

NB 1 : ces différents paramètres génèrent parfois des combinaisons non supportées, sans qu'ADO lève une exception (il se contente de modifier le paramètre "gênant").
NB 2 : On pourrait croire que ces paramètres sont propres à chaque dataset, mais il n'en est rien ! C'est l'objet ADOConnection qui centralise, et cet objet n'apprécie pas que l'on change de mode en cours de route !
Idem pour le niveau d'isolation des transactions.

A bon entendeur...

[Zzapi]

En effet çà devient moins trivial, mais tes explications sont toujours aussi claires Mci !

[cmen76]

Si j'osais juste un conseil, ce serait de bien séparer les requêtes qui permettront des mises à jour en base, de celles de consultation simple, et d'éventuellement lier ces différentes catégories de requêtes à des TADOConnection distinctes, pouvant accepter un paramétrage différent.

Par ailleurs, la tendance depuis quelques années (notamment à cause de l'architecture multi-tiers) est de résoudre les conflits d'accès aux données par l'ignorance (le dernier à parler a raison), ou au mieux par des verrous applicatifs bien placés (untel a réservé le dossier n° xxx et donc bloque fonctionnellement toutes les données qui s'y rattachent) et donc finalement d'abandonner la gestion fine des verrous par le SGBD qui devient très rapidement une grosse galère (surtout avec MSSQL , mais ça n'engage que moi).

[Zzapi]

Ces conseils sont fort bien venus !

Pour ce qui concerne la séparation des requêtes : TADOConnection distinctes, pouvant accepter un paramétrage différent on se retrouve pas vite avec le double ou le triple de connexion simultanées au serveur, ça pose pas de problème ?

Si j'ai bien compris pour les verrous la tendance est donc a l'optimisme .

Pour les verrous applicatifs je suis pas sur d'avoir bien compris ? Il s'agit par exemple pour un client voulant ouvrir un dossier de lire dans la table des réservations pour voir si le dossier n'est pas marqué comme réservé par un autre client ?
Dans ce cas le client possède le code pour la gestion de ce cas, du genre;

Code :

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

1
2
3
4
5
Sélectionner dans la table des réservations le n° du dossier
if DossierReserver then
  Showmessage('le dossier est verrouillé')
else
  ... // autres requêtes, on poursuit le travail

[cmen76]

Pour ce qui concerne la séparation des requêtes : TADOConnection distinctes, pouvant accepter un paramétrage différent on se retrouve pas vite avec le double ou le triple de connexion simultanées au serveur, ça pose pas de problème ?

Il s'agit de bien dimensionner le serveur en fonction du nb d'utilisateurs simultanés ; En réalité, ADO joue allègrement déjà avec les connexions base en se créant des processus base pour les fonctions d'agrégat notamment (sum, avg, count). MS SQL supporte des centaines de connexions simultanées, voire plus.

Si j'ai bien compris pour les verrous la tendance est donc a l'optimisme .

Affirmatif.

Pour les verrous applicatifs je suis pas sur d'avoir bien compris ?

On peut créer une information en base pour indiquer si la donnée "racine" fonctionnelle est réservée pour modification. Cela peut être un identifiant de session, ou un nom d'utilisateur, associé à la donnée clé. Le verrou est dit applicatif quand il est géré par programme. Dans ce cas, avant de pouvoir accéder à la donnée, le programme vérifie que la donnée est accessible, et si tel est le cas la verrouille pour l'utilisateur. Le mécanisme est donc tout à la charge du programme. L'inconvénient est que si le programme s'interrompt brutalement, le verrouillage peut rester en base, il faut alors imaginer un système de déblocage...