Discussion :

[PatricePatrice]

Bonjour,

j'ai fait une SP pour mettre à jour des champs d'une table. L'application montant en charge, j'ai parfois cette sp qui est exécutée à 1 seconde d'intervalle à partir d'un autre PC mais sous le même login SQL.

j'ai le message suivant

Error : System.Exception: System.Data.SqlClient.SqlException (0x80131904): La transaction d'isolement d'instantané a été abandonnée en raison d'un conflit de mise à jour. Vous ne pouvez pas utiliser l'isolement d'instantané pour accéder à la table 'dbo.Journee' directement ou indirectement dans la base de données '' afin de mettre à jour, de supprimer ou d'insérer la ligne modifiée ou supprimée par une autre transaction.

je suppose que c'est parce que la première est en cours de mise à jour et qu'il ne veut pas laisser la seconde s'exécuter pour ne pas avoir de mauvais chiffre.

existe-t-il un moyen de dire à SQL d'attendre que la première est finie avant de faire la seconde plutôt que planter ? comme un FIFO. ou alors une autre approche.
ma SP met à jour des infos grâce à une requête imbriquées. Peut être que c'est préférable de faire la requête de comptage , puis l'update ?

Merci
morceau de contenu de la SP.

Code :

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

1
2
3
4
5
6
7
8
9
update Journee set UVGlobalJournee = ( SELECT
													CASE WHEN max(E.UVGlobale) IS NULL THEN 0 
													ELSE max(E.UVGlobale) 
													END 
                                               FROM Examen E inner join 
												    IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
                                               WHERE Journee.IdJournee = IT.IDJOURNEE
											  ) 
        WHERE Test = '1'  and Journee.IdPlanning = @IdPlanning

[SQLpro]

Vous utilisez un verrouillage optimiste (READ COMITTED SNAPSHOT) par défaut au niveau de la base... les transactions sont donc lancées concurremment, chacune travaillant sur sa copie (SNAPSHOT). Si la copie de l'une est mise à jour avant la copie de l'autre il en résulte une anomalie de perte de mise à jour. C'est le message qui vous est adressé.

Repassez à une verrouillage pessimiste rendra vos transactions sérielles...

A +

[PatricePatrice]

Merci beacoup pour votre réponse. par contre je ne sais pas comment faire ce que vous m'indiquez.

La conf de la base sur SQL Server 2014 standard est comme suit
- Autoriser l'isolement d'instantanée = true
- Durabilité retardée = false
- Est un instantanée Read cOmmited = false

J'ai essayé tous les modes et a chaque fois j'ai une erreur dans mon appli. Ne peut on pas dire au systeme d'attendre que la premiere transaction soit temriné pour exécuter la suivante ?
Je suis completement perdu sur cette notion que je ne maitrise pas. Mon souhait serait que le système bufferise dans une file d'attente et qu'il exécute des qu'il le peut.
J'ai essayé read commited, read uncommited et il semble que ce soit pire.... Peut etre que ma base est mal configurée

Merci pour vos conseils

ma SP

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
ALTER PROCEDURE [dbo].[MajDispoJournees] @DateJourneeDeb nchar(6),
@DateJourneeFin nchar(6),@MajPlanning int,@IdJournee int,@IdPlanning int,@IdEnt int
 
AS
BEGIN
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
--Verif si on doit mettre a jour toutes la session ou que la journée
IF (@MajPlanning = 1)
	BEGIN
--Mise a jour des UV global des journées Test pour la session spécifiée
		update Journee set UVGlobalJournee = ( SELECT
													CASE WHEN max(E.UVGlobale) IS NULL THEN 0 
													ELSE max(E.UVGlobale) 
													END 
                                               FROM Examen E inner join 
												    IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
                                               WHERE Journee.IdJournee = IT.IDJOURNEE
											  ) 
        WHERE Test = '1'  and Journee.IdPlanning = @IdPlanning
 
--Mise a jour des UV unitaires des journées Test pour la session spécifiée
		update Journee set UVUnitaireJournee = ( SELECT
													CASE WHEN SUM(E.UVUnitaire) IS NULL THEN 0 
													ELSE SUM(E.UVUnitaire) 
													END 
												 FROM Examen E inner join IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
												WHERE Journee.IdJournee = IT.IDJOURNEE) 
        WHERE Test = '1'  and Journee.IdPlanning =@IdPlanning
 
--Mise a jour des dipos formateur pour le non A définir on en prend toutes journées du formateur dont le format est yyMMdd
		update Journee set UVDispoFormateur = ( select 6 - sum(UVUnitaireJournee) from Journee J where CONVERT(varchar, Journee.DateDebut, 12) = CONVERT(varchar, J.DateDebut, 12) and Journee.Test = '1' and Journee.IdFormateur = J.IdFormateur)   WHERE Test = '1' AND IdEntreprise=@IdEnt and IdFormateur <> -1  AND CONVERT(varchar, DateDebut, 12) >= @DateJourneeDeb  AND CONVERT(varchar, DateDebut, 12) <= @DateJourneeFin
 
--Mise a jour des dipos formateur pour le A définir on en prend que la journée
		update Journee set UVDispoFormateur = ( select 6 - sum(UVUnitaireJournee) from Journee J where Journee.IdJournee = J.IdJournee)    WHERE Test = '1'  AND IdEntreprise=@IdEnt and IdFormateur = -1 AND CONVERT(varchar, DateDebut, 12) >= @DateJourneeDeb  AND CONVERT(varchar, DateDebut, 12) <= @DateJourneeFin
 
 
 
	END
ELSE
	BEGIN
--Mise a jour des UV global des journées Test pour la journée spécifiée
		update Journee set UVGlobalJournee = ( select 
                                                   case
                                                   WHEN max(E.UVGlobale) IS NULL THEN 0 
                                                   ELSE 
                                                   max(E.UVGlobale) 
                                                   END 
                                                   from Examen E inner join IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
                                                   WHERE Journee.IdJournee = IT.IDJOURNEE) 
                                                  WHERE Test = '1'  and Journee.IdJournee = @IdJournee
--Mise a jour des UV unitaires des journées Test pour la journée spécifiée
		update Journee set UVUnitaireJournee = ( select 
                                            case  
                                            WHEN SUM(E.UVUnitaire) IS NULL THEN 0 
                                            ELSE 
                                            SUM(E.UVUnitaire) 
                                            END 
                                            from Examen E inner join IT_Journee_Examen IT on E.IdExamen = IT.IDEXAMEN 
                                            WHERE Journee.IdJournee = IT.IDJOURNEE) 
                                            WHERE Test = '1'  and Journee.IdJournee =@IdJournee
--Mise a jour des dipos formateur pour le non A définir on en prend toutes journées du formateur dont le format est yyMMdd
		update Journee set UVDispoFormateur = ( select 6 - sum(UVUnitaireJournee) from Journee J where Journee.DateDebut = J.DateDebut and Journee.Test = '1' and Journee.IdFormateur = J.IdFormateur)   WHERE Test = '1'  AND IdEntreprise=@IdEnt and IdFormateur <> -1 AND CONVERT(varchar, DateDebut, 12) = @DateJourneeDeb
--Mise a jour des dipos formateur pour le A définir on en prend que la journée
		update Journee set UVDispoFormateur = ( select 6 - sum(UVUnitaireJournee) from Journee J where Journee.DateDebut = J.DateDebut and Journee.Test = '1' and Journee.IdJournee = J.IdJournee)    WHERE Test = '1'  AND IdEntreprise=@IdEnt and IdFormateur = -1 AND CONVERT(varchar, DateDebut, 12) = @DateJourneeDeb
	END
COMMIT TRANSACTION
END

[SQLpro]

Pour sérialiser les transactions il faut :
1) placer la base en mode de verrouillage pessimiste
2) entamer une transaction explicite au niveau d'isolation SERIALIZABLE

ATTENTION : placer la base en mode de verrouillage pessimiste aura des conséquences sur TOUTES les autres transactions en cours (sachant que n'importe quelle commande manipulant les données ou modifiant la structure de la base est une transaction...).

À mon sens ce que vous tentez de faire est une ineptie... En effet les SGBD Relationnel sont là pour faire des transactions avec une forte concurrence. Si vous voulez être seul à exécuter des traitement, il fallait plutôt utiliser de l'Excel !

Essayez de nous dire quel est le sens de votre traitement sur le plan sémantique, autrement dit expliquez nous ce que fait votre code et termes fonctionnels. Je soupçonne une incompréhension des mécanismes de concurrence et probablement une mauvaise structure de votre base de données.

A +

[PatricePatrice]

Bonjour,
je ne suis pas le seul à utiliser la base. Il y a aujourdhui environ 20 personnes qui l'utilise toute la journée et un vingtaine ponctuellement.

cette SP permet de définir les disponibilités d'une personne sur une journée en unité UV. Sachant qu'il peu en faire 6 par jour.
A chaque modification du un nombre d'UV faite sur une journée (table de liaison entre journée et stagiaire) cette sp recalculer les disponibiltiés soit de la journée en allant récupérer le nombre d'UV pour cette personne sachant qu'il peut etre sur plusieurs sessions.

L'application connectée dessus est développée en C#. Il y a une connexion commune vers la base pour tous les utilisateurs. Je ne sais pas si en faire une par utilisateur serait mieux.

je pensais que le mode SNAPSHOT était adapté en me disant que le systeme vérouille les aute requetes tant que celle en court n'est pas terminée.
Or j'ai de plus en plus de message d'erreur comme celui mis dans mon premier message ou comme : La transaction a été bloquée sur les ressources verrous par un autre processus et a été choisie comme victime......
C'est du à la montée en puissance de l'application.

Aujourdhui je n'arrive pas à reproduire le cas qui est aléatoire. Donc très complexe de modifié le code côté applicatif.

Bref cela comme a etre très pénalisant et je ne sais aps trop par ou chercher...

Merci

[SQLpro]

...
cette SP permet de définir les disponibilités d'une personne sur une journée en unité UV. Sachant qu'il peu en faire 6 par jour.
A chaque modification du un nombre d'UV faite sur une journée (table de liaison entre journée et stagiaire) cette sp recalculer les disponibiltiés soit de la journée en allant récupérer le nombre d'UV pour cette personne sachant qu'il peut etre sur plusieurs sessions....

Apparemment, si je comprends bien, votre calcul est ensuite stocké dans une autre table...

Si tel est bien le cas, vous constituez une redondance par calcul... Ce qui prose deux problèmes !
1) dans un SGBD Relationnel il ne doit jamais y avoir de redondance
2) pour stocker cette redondance vous devez utiliser une transaction explicite qui bloque les accès concurrent susceptible de générer des verrous mortels

Solution... Revoyez votre code. En aucun cas vous ne devriez redonder ce calcul, mais simplement l'afficher en faisant lecture des informations.
Ceci peut sans doute être fait en une seule requête et par conséquent être traduit en vue SQL...

A +