Discussion :

[hwoarang]

Bonjour,

J'ai fait un petit test avec SQL server 2008. Dans le manager, j'utilise une base avec une table MA_TABLE qui possede 2 champs : ID int identity et MON_CHAMP varchar(50).

Ensuite, j'ai ouvert une page ou j'execute :

Code :

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

1
2
3
4
BEGIN TRANSACTION
INSERT INTO MA_TABLE (MON_CHAMP) VALUES ('test')
WAITFOR DELAY '00:01:00'
COMMIT

En meme temps, dans une autre page sur la meme base, j'execute :

Code :

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

SELECT * FROM MA_TABLE

Et la, je suis etonné de voir que la seconde requete ne s'execute que lorsque la premiere se termine (c'est à dire apres 1 miniute). Le comportement que j'attendrais serait de recuperer toutes les lignes hormis celle en cours d'insertion. Est ce que c'est un probleme de configuration ou bien est ce le fonctionnement normal de SLQ server?

Merci

[SQLpro]

Vous demandez la lecture de toutes les lignes. Or l'un des lignes est dans un statut "pendant" c'est à dire ni validée, ni annulée. Aucun SGBDR ne peut donc lire la table.
Si vous aviez demander d'autres lignes (donc pas un SELECT *) vous auriez été servit, à moins que vous ne baissiez le niveau d'isolation de la transaction qui lit les données au niveau READ UNCOMMITTED.
Lisez l'article que j'ai écrit sur les transactions : http://sqlpro.developpez.com/isolation-transaction/

A +

[hwoarang]

Ca, je le comprends bien. Mais du coup, je ne vois pas du tout l'interet des transactions si les tables sont bloquées (j'ai bien compris qu'on peut acceder aux autres lignes mais à partir du moment ou on ne peut pas faire de "select *", pour moi, la table est bloquée).

Je vais poser la question autrement. Le comportement qui me paraitrait evident serait de pouvoir acceder à toutes les autres lignes (toutes celles qui sont "commited" et ne pas voir les lignes "uncommited").
Je comprends bien la problematique que ce fonctionnement suppose mais j'i du mal à croire, compte tenu du nombre de versions de SQL Server qu'on en soit la. Est ce qu'il y a une configuration qui permettrait d'avoir le comportement que je viens de décrire?

En tout cas, merci pour la réponse

[SQLpro]

Effectivement vous n'avez rien compris aux transactions...

Imaginez que la ligne que vous insérez ou celle que vous modifiez soit la seule ligne de crédit d'un lot de ligne comptable. Alors, lorsque vous allez calculer votre balance comptable, vous serez déficitaire...

Si vous n'avez pas besoin de toutes les lignes de votre tables, pourquoi faire un SELECT * ?

De plus en développement le select * est d'une haute stupidité... Il ne devrait jamais être utiliser.

A +

[hwoarang]

Effectivement vous n'avez rien compris aux transactions...

J'ai surtout du mal à voir leur interet...

Imaginez que la ligne que vous insérez ou celle que vous modifiez soit la seule ligne de crédit d'un lot de ligne comptable. Alors, lorsque vous allez calculer votre balance comptable, vous serez déficitaire...

D'accord mais le resultat sera le meme que si j'avais executé ma requete 10 secondes plus tot avant l'update. Donc ca me choquerait pas que les données qui sont modifiées pendant une transaction ne soient pas visible (je dirais meme que c'est le contraire qui me choque).

Si vous n'avez pas besoin de toutes les lignes de votre tables, pourquoi faire un SELECT * ?

De plus en développement le select * est d'une haute stupidité... Il ne devrait jamais être utiliser.

Je ne trouve pas particulierement stupide de vouloir avoir la liste des factures correspondant à un client (ce qui revient à faire un select * from ... where client = ..., et qui bloquerait si une transaction touche la facture d'un client) ou bien la liste des clients...

Mais bon, pour en revenir à ma question de départ, il semble qu'il ne soit pas possible d'avoir le comportement auquel je m'attendais. Merci pour l'info.

[SQLpro]

Les SGBDR fonctionne d'une manière logique. Si vous leur demandez une travail à la con, ne soyez pas étonné d’obtenir n'importe quoi comme résultat.
Vous voudriez qu'il fonctionnent de la même manière que votre intuition !
il serait peut être temps de vous former et de tenter de comprendre les concepts des transactions....

Quelques lectures pour ce faire :
"a quoi servent les transactions " : http://sqlpro.developpez.com/cours/sqlaz/techniques/#L1
"Transactions et niveau d'isoaltion" : http://sqlpro.developpez.com/isolation-transaction/
"es transactions imbriquées" : http://sqlpro.developpez.com/cours/s...ns-imbriquees/

A +

[hwoarang]

Les SGBDR fonctionne d'une manière logique. Si vous leur demandez une travail à la con, ne soyez pas étonné d’obtenir n'importe quoi comme résultat.

Je leur demande pas grand chose, j'essaye juste de comprendre l'interet des transactions (que je ne voyais pas).

Vous voudriez qu'il fonctionnent de la même manière que votre intuition !

Non, j'imagine comment ca peut fonctionner, je fais des hypotheses et essaye de les verifier. Ca me semble pas completement idiot comme approche car en ce qui me concerne, je n'ai pas la science infuse.

il serait peut être temps de vous former et de tenter de comprendre les concepts des transactions....

Merci pour ces tutos qui illustrent bien l'interet des transactions. Il semble que le concept auquel je pensais n'existe pas sous sql server. Pas grave, je ferais sans.

[SQLpro]

Le "concept" que vous évoquez n'est ni plus ni moins que la façon dont on lit des fichiers ligne à ligne... Bref, retour au Cobol !!!

Un SGBDR ne gère pas des fichier mais des relations (objets mathématique porteur de données, c'est à dire des tables).

Il n'y a aucun point commun entre un gestionnaire de fichier et un SGBDR !

A +

[hwoarang]

J'aurais peut etre du commencer par le début : ce que j'aimerais faire.
En fait, j'ai 3 tables qui ressemblent à : FOURNISSEUR, PRODUIT et FOURNISSEURS_POSSIBLES. Dans PRODUIT et FOURNISSEUR, il y a une clé unique identity. FOURNISSEURS_POSSIBLES contient une clé pour chaque table.

Dans mon interface, je voudrais pouvoir créer un PRODUIT et y associer les FOURNISSEUR possibles. Le probleme, c'est que je voudrais pouvoir annuler.

Mon idée de départ était d'utiliser une transaction, créer un PRODUIT, insérer ce qu'il faut dans FOURNISSEURS_POSSIBLES et, si l'utilisateur clic sur Valider, je fais un COMMIT. S'il clic sur annuler, je fais un ROLLBACK. J'ai bien compris que ce n'est pas la fonction des transactions et c'est pourquoi je voudrais savoir s'il existe un moyen de faire cela sans bloquer les PRODUIT (je ne voudrais pas qu'un autre utilisateur qui consulte les PRODUIT soit bloqué jusqu'a ce que notre utilisateur numero 1 valide ou annule).

Pour l'instant, je gere ce genre de situation par code. Mais je suis tombé sur un article qui disait qu'oracle permettait assez simplement de faire ce genre de chose donc je me demandais si c'est aussi possible avec sql server ?

Merci

[SQLpro]

Oracle et SQL Server fonctionne de la même façon d'un point de vu transactionnel. Si par exemple quelqu'un consulte une donnée qu'une autre session veut supprimer, alors il sera retardé le temps de la lecture de la table par SQL (quelques fraction de ms) mais il ne sera pas bloqué par l'affichage des données, sauf si vous avez codé dans votre application un curseur for UPDATE...

Pour votre solution, le plus simple est d'implémenter un vrai modèle de données avec l'intégrité référentielle en mode cascade ce qui fait que si l'utilisateur décide à n'importe quel moment de supprimer un produit, cela supprimera aussi les lignes filles de la table d'association.

A +

[hwoarang]

Les SGBDR fonctionne d'une manière logique. Si vous leur demandez une travail à la con, ne soyez pas étonné d’obtenir n'importe quoi comme résultat.
Vous voudriez qu'il fonctionnent de la même manière que votre intuition !
il serait peut être temps de vous former et de tenter de comprendre les concepts des transactions....

Je déterre ce fil car j'ai trouvé la réponse et malgré l'agressivité des réponses que j'ai eus, si certains font la recherche, il seront contents de savoir que SQL Server dispose de la fonctionnalité que j'ai décrit depuis la version 2005 d'apres ce que j'ai lu (j'ai testé sur la version 2008 et ca marche). Pour l'activer, il faut faire : ALTER DATABASE .... SET READ_COMMITTED_SNAPSHOT ON.
Faut croire que c'est pas un travail si con que ca.

[iberserk]

Cela n’empêche pas que vous êtes bien toujours au sein d'une transaction...

Cette "option" ne fait qu'activer un mode ou les données lues sont des copies...
Vous jouez donc simplement sur le type/niveau de blocage.

cya

[SQLpro]

... Pour l'activer, il faut faire : ALTER DATABASE .... SET READ_COMMITTED_SNAPSHOT ON.
Faut croire que c'est pas un travail si con que ca.

Ce mode ne vous empêchera pas d'être bloqué par le fait que', si entre temps, les données ont évolués hors snapshot, vous aboutirez à une erreur rendant impossible la mise à jour !

A +

[hmira]

En plus de la remarque fondée et juste de SQLPro, il est par ailleurs fortement déconseillé d’utiliser l’option de base de données READ_COMMITTED_SNAPSHOT pour les applications déjà existantes sans refaire des tests de qualification complets de toutes les applications !

En effet, l’option base de données READ_COMMITTED_SNAPSHOT n’est pas une solution miracle, sinon ça se saurait ! Elle est même déconseillée pour les applications héritées !

Selon la façon dont les applications ont été développées, l’option base de données READ_COMMITTED_SNAPSHOT peut aboutir à des résultats complètement erronées !!!

Ci-dessous un exemple concret

Supposons que votre application a été développée selon l’exemple du bloc ci-dessous et qui fonctionne comme attendu.

Création et emplissage de la table Stock

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE Stock 
  (IdProduit  INT, 
   Qte   SMALLINT, 
   PRIMARY KEY (IdProduit) ); 
INSERT INTO Stock 
     (IdProduit, Qte ) 
VALUES 
  (1, 5),   
  (2, 10);

Pour simuler les accès concurrents aux données, lancez le bloc ci-dessus dans 2 sessions séparées (Soient Session_1 et Session_2)

Code SQL :

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
BEGIN     
	BEGIN TRANSACTION
	DECLARE @IdProduit INT, 
			@QteCommandee smallint, 
			@QteEnStock   smallint; 
 
	SET @IdProduit = 1;     -- Produit commandé 
	SET @QteCommandee = 4;  -- Qantité commandée
 
	SET  @QteEnStock = (SELECT Qte FROM Stock WHERE IdProduit = 1); 
 
	IF @QteEnStock - @QteCommandee >= 0 
	BEGIN
		UPDATE Stock 
		   SET Qte = Qte - @QteCommandee 
		WHERE @IdProduit = 1 ; 
		-- Laisser le temps à d'autres session de tenter de lire la même ligne du stock ! 
		WAITFOR DELAY '00:00:10'
		SELECT GETDATE() as Stamp, 'Commande effectuée'; 
	END
	ELSE
	BEGIN   
		SELECT GETDATE(), 'Quantité en stock insuffisante pour honorer la commande !'
    END 
 
	COMMIT TRANSACTION
	SELECT GETDATE() as Stamp, * FROM Stock WHERE IdProduit = 1 
END; 
GO

Résulat :
Session 1

Code SQL :

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

1
2
3
2013-04-21 20:12:48.700	Commande effectuée
Stamp   IdProduit   Qte
2013-04-21 20:12:48.703  1   1

Session 2

Code SQL :

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

1
2
3
2013-04-21 20:12:48.703	Quantité en stock insuffisante pour honorer la commande !
Stamp   IdProduit  Qte
2013-04-21 20:12:48.703   1  1

Maintenant modifiez l’option de base de données puis rejouez le scénario :
Remarque : Vous devez déconnecter l’ensemble des utilisateurs pour pouvoir modifier l’option de base de données :

Code SQL :

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

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

Réinitialisez le contenu de la table Stock

Code SQL :

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

1
2
3
4
5
6
DELETE FROM Stock; 
INSERT INTO Stock 
 (IdProduit, Qte ) 
VALUES 
 (1, 5), 
 (2, 10);

Rejouez le scénario et lancez le bloc ci-haut dans 2 sessions séparées (Soient Session_1 et Session_2)
Résultat :
Session 1

Code SQL :

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

1
2
3
2013-04-21 20:30:56.543	Commande effectuée
Stamp	IdProduit	Qte
2013-04-21 20:30:56.547	1	1

Session 2

Code SQL :

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

1
2
3
2013-04-21 20:31:06.547	Commande effectuée
Stamp   IdProduit	  Qte
2013-04-21 20:31:06.550    1   -3      !!!! RESULTAT INCOHERENT !!!

Vous constatez que le résultat est complément faux pour ne pas dire catastrophique !!!
Pour corriger ce problème il aurait fallu modifier également l’application pour utiliser, dans cet exemple en particulier, le niveau d’isolation REPEATABLE READ
Comme ceci :

Code SQL :

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
BEGIN 
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
	BEGIN TRANSACTION
	DECLARE @IdProduit INT, 
			@QteCommandee smallint, 
			@QteEnStock   smallint; 
 
	SET @IdProduit = 1;     -- Produit commandé 
	SET @QteCommandee = 4;  -- Qantité commandée
 
	SET  @QteEnStock = (SELECT Qte FROM Stock WHERE IdProduit = 1); 
 
	IF @QteEnStock - @QteCommandee >= 0 
	BEGIN
		UPDATE Stock 
		   SET Qte = Qte - @QteCommandee 
		WHERE @IdProduit = 1 ; 
		-- Laisser le temps à d'autres session de tenter de lire la même ligne du stock ! 
		WAITFOR DELAY '00:00:10'
		SELECT GETDATE() as Stamp, 'Commande effectuée'; 
	END
	ELSE
	BEGIN   
		SELECT GETDATE(), 'Quantité en stock insuffisante pour honorer la commande !'
    END 
 
	COMMIT TRANSACTION
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
	SELECT GETDATE() as Stamp, * FROM Stock WHERE IdProduit = 1 
END; 
GO

CONCLUSION

READ_COMMITTED_SNAPSHOT n'est pas une solution miracle. Elle est même déconsillée pour les applications héritées (Vous savez, ces applications qui donnent parfois même des irritations !)

A+

[hwoarang]

Désolé de ne pas avoir répondu plus tot, je n'avais pas vu les réponses.

Cela n’empêche pas que vous êtes bien toujours au sein d'une transaction...

Cette "option" ne fait qu'activer un mode ou les données lues sont des copies...
Vous jouez donc simplement sur le type/niveau de blocage.

J'ai décrit un comportement dans mon message #3. Et utiliser READ_COMMITTED_SNAPSHOT donne bien le comportement souhaité.

Quand aux autres remarques, merci de faire remarquer ces risques potentiels en particulier hmira pour l'exemple. Je pense qu'il n'y a pas de solution miracle qui marche pour tous les cas et les problemes qui peuvent se produire à cause de READ_COMMITTED_SNAPSHOT me semblent assez intuitifs. Bref, comme souvent, c'est l'utilisation qui permet de savoir quel est le mode le mieux adapté. Et dans mon cas, ce mode aurait été util. Mais bon, maintenant, mon appli est developpée donc j'attendrais de faire une batterie de tests conséquente avant de changer de mode...