Fonctionnement SQL Server et transactions

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:

---

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:

---

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

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 +

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

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 +

Citation:

---

Envoyé par SQLpro

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

---

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

Citation:

---

Envoyé par SQLpro

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).

Citation:

---

Envoyé par SQLpro

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.

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 +

Citation:

---

Envoyé par 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.

---

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

Citation:

---

Envoyé par SQLpro

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.

Citation:

---

Envoyé par SQLpro

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.

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 +

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

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 +

Citation:

---

Envoyé par 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....

---

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.

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

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

Citation:

---

Envoyé par hwoarang

... 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 +

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:

---

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:

---

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:

---

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:

---

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:

---

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

---

Réinitialisez le contenu de la table Stock

Code:

---

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:

---

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:

---

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:

---

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+

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

Citation:

---

Envoyé par iberserk

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

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...