Bonjour.

Je vous présente "SP_MouvementProduitV6"

Une centaine de PC à travers notre usine utilisent en permanence cette procedure stockée. Ce qui fait qu'elle est lancée sur le serveur environ trois fois par secondes.

Nous avons de plus en plus souvent des processus bloqués qui bloquent tous les autres en cascade. Phénomène normal... sauf lorsque le blocage ne se libère plus, ce qui nous mène régulièrement à un freeze complet de notre base de production (plusieurs dizaines de tables et relations).

Voyez-vous dans cette SP des choses qui vous parraissent suspectes ou dangeureuses pouvant éventuellement mener à des interblocage ?

Je suis désespéré, depuis plusieurs mois, nous n'avons aucune piste sérieuse...

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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
CREATE PROCEDURE SP_MouvementProduitV6 
	(@NumSerie varchar(15)='', 
	@PrIdK DECIMAL(9,0), 
	@Letat varchar(2)='', 
	@maDate varchar(20)='', 
	@Badge varchar(10)='', 
	@Observation varchar(40)='', 
	@EtpId DECIMAL(9,0)=0, 
	@EtpIdSuiv DECIMAL(9,0)=0, 
	@PcId DECIMAL(9,0)=0, 
	@MettreAJourNs bit=1,  -- Par défaut, on mettra à jour la table des numéros de série.
	@BonOuValide varchar(2)='1', -- 1 = Bon ou Mauvais... Statuts par défaut à LFB. A SBL, c'était VALIDE ou NON VALIDE.
	@IdMvt DECIMAL(9,0) OUTPUT) 
 
AS 
 
	SET NOCOUNT ON 
	DECLARE 
		@PrId decimal(9,0), 
		@etat bit, 
		@NumSeriePresent DECIMAL (2,0), 
		@IdNumSerie DECIMAL(9,0) ,
		@laDate DATETIME ,
		@rowcount DECIMAL(9,0),
		@etatNumSerie DECIMAL(9,0)
 
	IF ((@BonOuValide <> '1' ) AND (@BonOuValide <> '2' ))
	begin
		Raiserror('Erreur dans "SP_MouvementProduitV6". Le choix entre Bon ou Valide n''est pas correct',16,1) 
		return 
	end
 
	SET @PrId=0 
	SET @etat = NULL 
	IF @Letat = '1' 
	begin
		if @BonOuValide = 1 
		begin
			set @etatNumSerie=1 -- Etat bon selon LFB (BON)
		end
		else
		begin
			set @etatNumSerie=10 -- Etat bon selon SBL (VALIDE)
		end
		SET @etat = 1 
	end
	else
	begin
		if @BonOuValide = 1 
		begin
			set @etatNumSerie=2 -- Etat mauvais selon LFB (MAUVAIS)
		end
		else
		begin
			set @etatNumSerie=24 -- Etat mauvais selon SBL (NON VALIDE)
		end
		SET @etat = 0 
	end
	SET @IdMvt = -1 ;
 
--	SET @laDate = COALESCE(@laDate, CURRENT_TIMESTAMP) --  => pas besoin de tests
	if len(ltrim(rtrim(@madate)))<=0 
		set @ladate = getdate()
	else
		SET @laDate = CONVERT(DATETIME,@maDate,120);
 
	-- On nous a fourni directement le PrIdK mais il faut tout de même vérifier qu'il est ok.
	SELECT @rowcount=COUNT(*) 
	FROM   PRODUIT 
	WHERE  PR_ID_K=@PrIdK
	AND (PR_ST_ID_FK=1  OR PR_ST_ID_FK=10)
	if (@@error<>0)
	BEGIN
		Raiserror('Erreur dans "SP_MouvementProduitV6". Le Produit PrIdK %d (et statut BON ou VALIDE) n''est pas présent dans la base',16,1,@PrIdK) 
		return 
	END
	SET @PrId = @PrIdK ;
 
 
	BEGIN TRANSACTION SP_MouvementProduitV6 
 
 
	-- Vérification de l'existance du dit produit dans la table des numéros de série
	SELECT @NumSeriePresent=COUNT(*) FROM NUM_SERIE
	WHERE 
	NS_NUM_SERIE_K = @NumSerie
	AND NS_PR_ID_FK_K = @PrId
--	AND (NS_ST_ID_FK = 1 OR NS_ST_ID_FK = 10) -- Bon ou Valide -- Il ne faut pas faire de test sur le statut pour savoir si on doit recreer le ns car il peut exister avec un autre statut...
	if ((@@ROWCOUNT = 0) OR (@@error<>0))
	begin
		Raiserror('Erreur dans "SP_MouvementProduitV6". Impossible de savoir si le produit PrIdK=%d et Ns=%s statut BON ou VALIDE est dans la table des ns car il a provoqué une erreur.\n@@rowcount=%d\n@@error=%d',16,1,@PrId,@NumSerie,@@rowcount,@@error) 
		GOTO TRAITE_ERREUR 
	end;
	if ( @NumSeriePresent < 1 )
	begin
		-- Le numéro de série n'a pas été trouvé dans la table NUM_SERIE
		-- On souhaite creer automatiquement les numéros de série non existants.
		INSERT INTO NUM_SERIE 
		(NS_NUM_SERIE_K, 
		NS_PR_ID_FK_K, 
		NS_ETP_ID_PREC_FK, 
		NS_ETP_ID_SUIV_FK, 
		NS_ST_ID_FK, 
		NS_QTE) 
		VALUES ( 
		@NumSerie, 
		@PrId, 
		@EtpId,
		@EtpIdSuiv,
		@etatNumSerie,  
		0) -- la quantité 
		if ((@@ROWCOUNT = 0) OR (@@error<>0))
		begin
			Raiserror('Erreur dans "SP_MouvementProduitV6". Impossible de creer le produit PrIdK=%d et Ns=%s dans la table des numéros de série.\n@@rowcount=%d\n@@error=%d',16,1,@PrId,@NumSerie,@@rowcount,@@error) 
			GOTO TRAITE_ERREUR 
		end;
	end
	else
	begin
		-- Le numéro de série a été trouvé dans la table NUM_SERIE
		if(@MettreAJourNs = 1)
		begin
			-- print 'On va mettre à jour'
			-- print 'L''état du numéro de série sera : ' + convert(char(20),@etatNumSerie)
			-- print 'UPDATE NUM_SERIE SET NS_ETP_ID_SUIV_FK='+convert(char(20),@EtpIdSuiv)+', NS_ETP_ID_PREC_FK='+convert(char(20),@EtpId)+', NS_ST_ID_FK='+convert(char(20),@etatNumSerie)+' WHERE NS_NUM_SERIE_K='+@NumSerie +' AND NS_PR_ID_FK_K='+convert(char(20),@PrId) 
			-- Il faut mettre à jour les étapes du numéro de série.
			UPDATE NUM_SERIE SET NS_ETP_ID_SUIV_FK=@EtpIdSuiv, NS_ETP_ID_PREC_FK=@EtpId, NS_ST_ID_FK=@etatNumSerie WHERE NS_NUM_SERIE_K=@NumSerie AND NS_PR_ID_FK_K=@PrId 
			IF ((@@ERROR <> 0) OR (@@ROWCOUNT = 0)) 
			BEGIN 
				Raiserror('Erreur dans "SP_MouvementProduitV6", la mise à jour du numéro de série %s du produit %d pour la gestion des etapes n''a pas pu se réaliser.\n@@rowcount=%d\n@@error=%d',10,1,@NumSerie,@PrId,@@rowcount,@@error) 
				GOTO TRAITE_ERREUR 
			END
		end
		else
		begin
			print 'On ne va pas mettre à jour.'
		end
	end;
	-- Ici, le numéro de série est forcément présent dans la base.
	-- Il n'est pas forcément à la bonne étape, mais c'est selon ce qu'à demandé l'appelant donc nous n'avons rien à redire. Maintenant, il faut faire le mouvement
	-- raison même pour laquelle cette procédure a été crée.
 
	/* Insertion du mouvement dans la table MVPT_PRODUIT*/ 
	INSERT INTO MVT_PRODUIT (MVPT_NS_NUM_SERIE_FK,MVPT_ETAT,MVPT_DATE,MVPT_BADGE,MVPT_OBSERVATION,MVPT_NS_PR_ID_FK,MVPT_ETP_ID_FK,MVPT_PC_ID_FK) 
	VALUES ( 
	convert(char(20),@NumSerie), 
	@etat, 
	@laDate, 
	@Badge, 
	@Observation, 
	@PrId, 
	@EtpId, 
	@PcId) 
	IF ((@@ERROR <> 0) OR (@@ROWCOUNT = 0)) 
	BEGIN 
		Raiserror('Erreur dans "SP_MouvementProduitV6", Le mouvement du produit de PrIdK %d et Ns=%s n''a pas pu se réaliser.\n@@rowcount=%d\n@@error=%d',10,1,@PrId,@NumSerie,@@rowcount,@@error) 
		GOTO TRAITE_ERREUR 
	END 
	else 
		SET @IdMvt=@@identity 
 
 
 
	COMMIT TRANSACTION SP_MouvementProduitV6 
	RETURN 
 
	TRAITE_ERREUR: 
	ROLLBACK TRANSACTION SP_MouvementProduitV6
GO
edit : Je suspecte cette procédure stockée uniquement parce que c'est celle-ci qui est appelée le plus souvent... le problème viens peut-être d'ailleurs ?...