DEADLOCK & PROCEDURE STOCKEE

**Kagami** · 09/07/2013, 12h12

bonjour ,
j ai eu un message d'erreur :
La transaction (ID de processus 52) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime. Réexécutez la transaction.
je pensais connaitre la procédure stockée où se produit ce problème, mais je ne sais pas comment le détecter , il y a 2 tables qui veulent faire la même action ou quelque chose du genre de deadlock .. je ne sais pas exactement de quoi s'agit de phénomène....
Voici la procédure :

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
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
USE [Pharmatica]
GO
/****** Object:  StoredProcedure [dbo].[FinDel]    Script Date: 07/09/2013 09:24:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FinDel]
@iIdVente int,
@NewNumVenteErrorChek int=0
AS
declare @iIdArticle int,
@dPrix decimal(16,2),
@iStock int,
@iQteReserve int,
@iQteServie int,
@sDate varchar(50),
@iUnite int,
@iUniteGratuiteVente int,
@iIdTransVente int,
@iMois int,
@iAnnee int,
@iNbreVente int,
@iQteVendu int,
@iSomme int,
@iConsommation int,
@iVenteMois int,
@fPrixVente decimal(16,2),
@iMAJQteStock int,
@iStockTotal int,
@iMAJUniteGratuite int,
@iUniteGratuitePdt int,
@QteReserve int, @StockTotal int
begin transaction
	set @sDate = convert(varchar(50),getdate(),103)
	declare encours cursor for 
		SELECT id_trans_vente,transactions_lingne_ventes.id_article,prix_unit_ttc_article,stock_article_stock,qte_reserve_article_stock,qte_servie_ligne,unite_gratuite_article_stock
		from transactions_lingne_ventes,article_stock
		where id_vente=@iIdVente
		and transactions_lingne_ventes.id_article=article_stock.id_article
		and transactions_lingne_ventes.prix_unit_ttc_article=article_stock.prix_vente_article_stock
	OPEN Encours
		FETCH NEXT FROM Encours
		INTO @iIdTransVente,@iIdArticle,@dPrix,@iStock,@iQteReserve,@iQteServie,@iUnite
		WHILE @@FETCH_STATUS = 0
		BEGIN
		if(@iQteServie>0)
		begin		
			if(@iUnite>=@iQteServie)
			begin
				set @iUniteGratuiteVente = @iQteServie
			end
			else
			begin
				set @iUniteGratuiteVente = @iUnite
			end
			set @iUnite = @iUnite - @iUniteGratuiteVente
			update transactions_lingne_ventes set unite_gratuite_ligne=@iUniteGratuiteVente where id_trans_vente=@iIdTransVente
			set @NewNumVenteErrorChek = @@ERROR
			update article_stock set unite_gratuite_article_stock = @iUnite where id_article=@iIdArticle and prix_vente_article_stock like @dPrix
			set @NewNumVenteErrorChek = @@ERROR
		end
		else
		begin
			if(@iUnite>0)
			begin
				set @iUniteGratuiteVente = @iQteServie
			end
			else
			begin
				set @iUniteGratuiteVente = 0
			end
			--set @iUnite = @iUnite - @iUniteGratuiteVente
			update transactions_lingne_ventes set unite_gratuite_ligne=@iUniteGratuiteVente where id_trans_vente=@iIdTransVente
			set @NewNumVenteErrorChek = @@ERROR
		end
 
 
 
 
 
------------------------------------------------------------------------------------------------------------------------------------------
		--------------------MAJ historique article et nbre vente article
		--exec MAJArticle @iIdArticle,@iQteServie,1,@sDate
 
	set @iNbreVente = (select distinct NBR_SORTIE_ARTICLE from ARTICLE where ID_ARTICLE=@iIdArticle)
	if @iNbreVente= null
		begin
			set @iNbreVente = 0
		end
	set @iNbreVente = @iNbreVente	+ 1
 
	set @iQteVendu = (select distinct NBR_VENTE_ARTICLE from ARTICLE where ID_ARTICLE=@iIdArticle)
	if @iQteVendu=null
		begin
			set @iQteVendu = 0
		end
	set @iQteVendu = @iQteVendu + @iQteServie
 
	update ARTICLE
	set NBR_VENTE_ARTICLE = @iQteVendu,
	NBR_SORTIE_ARTICLE= @iNbreVente,
	DATE_DERN_SORTIE_ARTICLE=@sDate
	where ID_ARTICLE=@iIdArticle
	set @NewNumVenteErrorChek = @@ERROR
--
set @iMois = month(getdate())
set @iAnnee = year(getdate())
-----------
--test si le pdt existe ds la table historique pr le mois et l'annee courante
 set @iSomme =(select count(*) from HISTORIQUE_ARTICLE where ID_ARTICLE=@iIdArticle and annee_historique_article=@iAnnee and mois_historique_article=@iMois)
 if(@iSomme>0)
--si il existe on fait la MAJ
	begin
		update historique_article
		set NBR_VENTE_MOIS_ARTICLE = (select NBR_VENTE_MOIS_ARTICLE from HISTORIQUE_ARTICLE where ID_ARTICLE=@iIdArticle and annee_historique_article=@iAnnee and mois_historique_article=@iMois) + @iQteServie,
		NBR_SORTIE_MOIS_ARTICLE=  (select NBR_SORTIE_MOIS_ARTICLE from HISTORIQUE_ARTICLE where ID_ARTICLE=@iIdArticle and annee_historique_article=@iAnnee and mois_historique_article=@iMois) + 1
		where ID_ARTICLE=@iIdArticle and annee_historique_article=@iAnnee and mois_historique_article=@iMois
		set @NewNumVenteErrorChek = @@ERROR
 
		---MAJ VENTE_MOIS_ARTICLE et CONS_MOIS_ARTICLE de la table article
		set @iVenteMois = 0
		set @iConsommation = 0
		set @iVenteMois = (select VENTE_MOIS_ARTICLE from article where id_article=@iIdArticle)
		if(@iVenteMois is null)
 
		begin
			set @iVenteMois = 0
		end
		set @iConsommation = (select CONS_MOIS_ARTICLE from article where id_article=@iIdArticle)
 
		if(@iConsommation is null)
		begin
			set @iConsommation = 0
		end
		set @iConsommation = @iConsommation + @iQteServie
		set @iVenteMois = @iVenteMois + 1
-------------------------------------------------------------------------
		update article set CONS_MOIS_ARTICLE=@iConsommation,VENTE_MOIS_ARTICLE=@iVenteMois where ID_ARTICLE=@iIdArticle
		set @NewNumVenteErrorChek = @@ERROR
-------------------------------------------------------------------------
	end 
else
	begin
		insert into historique_article(id_article,annee_historique_article,mois_historique_article,NBR_VENTE_MOIS_ARTICLE,NBR_ACHAT_MOIS_ARTICLE,NBR_SORTIE_MOIS_ARTICLE,NBR_ENTREE_MOIS_ARTICLE) values(@iIdArticle,@iAnnee,@iMois,@iQteServie,0,1,0)
		set @NewNumVenteErrorChek = @@ERROR
-------------------------------------------------------------------------
update article set CONS_MOIS_ARTICLE=@iQteServie,
		VENTE_MOIS_ARTICLE=1,
		NBR_SORTIE_ARTICLE=@iQteServie,
		NBR_VENTE_ARTICLE=1
		 where ID_ARTICLE=@iIdArticle
 
		set @NewNumVenteErrorChek = @@ERROR
-------------------------------------------------------------------------
	end
 
----------------
		set @NewNumVenteErrorChek = @@ERROR
		set @iStock		= @iStock - @iQteServie
		set @iQteReserve	= @iQteReserve - @iQteServie
 
------------------------------------------------------------------------------------------------------------------------------------------
		---------------------MAJ Stock
		--exec MAJQteReserveArticle @iIdArticle,@iQteReserve,@dPrix,1,@iStock
set @iMAJQteStock=1
set @iStockTotal=0
set @iMAJUniteGratuite=0
 
/*MAJ de la Qte Reserve ds ARTICLE_STOCK et ARTICLE*/
UPDATE 
	ARTICLE_STOCK
SET
	QTE_RESERVE_ARTICLE_STOCK= @iQteReserve
 
WHERE 
	ID_ARTICLE=@iIdArticle
	and
	PRIX_VENTE_ARTICLE_STOCK like @dPrix
set @NewNumVenteErrorChek = @@ERROR
 
UPDATE 
	ARTICLE
SET
	QTE_RESERVE_ARTICLE= (SELECT sum(QTE_RESERVE_ARTICLE_stock) FROM ARTICLE_stock where ID_ARTICLE=@iIdArticle)
 
WHERE 
	ID_ARTICLE=@iIdArticle
set @NewNumVenteErrorChek = @@ERROR	
 
/*MAJ STOCK ARTICLE STOCK*/
if(@iMAJQteStock>0)
begin
	---insertion ds la table mouvement article
	declare @iStock1 int,
	@iQte int
		set @iStock1 = (SELECT top 1 STOCK_ARTICLE_STOCK FROM ARTICLE_STOCK where ID_ARTICLE=@iIdArticle and PRIX_VENTE_ARTICLE_STOCK like @dPrix)
		set @iQte     = @iStock - @iStock1
		if(@iQte!=0)
		begin
			insert into mouvement_article(date_mouvement,stock_ancien,nature_mouvement,qte_modifie,prix_vente_article,id_article) values(GETUTCDATE(),@iStock1,'VENTE',@iQte,@dPrix,@iIdArticle)
			set @NewNumVenteErrorChek = @@ERROR
		end
 
	/*MAJ de stock  de l'article */
		update
			ARTICLE_STOCK
		set
			STOCK_ARTICLE_STOCK=@iStock,
			date_vente_article_stock = convert(varchar(50),getdate(),103)
		where
			ID_ARTICLE=@iIdArticle
			and
			PRIX_VENTE_ARTICLE_STOCK like @dPrix
		set @NewNumVenteErrorChek = @@ERROR
		if(exists(select * from article_stock where ID_ARTICLE=@iIdArticle and PRIX_VENTE_ARTICLE_STOCK like @dPrix and suppression like 'oui'))
		begin
			if(@iStock!=0)
			begin
				update 	ARTICLE_STOCK set suppression='NON' where ID_ARTICLE=@iIdArticle and PRIX_VENTE_ARTICLE_STOCK like @dPrix
				set @NewNumVenteErrorChek = @@ERROR
			end
		end
		update
			ARTICLE
		set
			STOCK_TOTAL_ARTICLE=(SELECT sum(STOCK_ARTICLE_STOCK) FROM ARTICLE_STOCK where ID_ARTICLE=@iIdArticle)
		where
			ID_ARTICLE=@iIdArticle
		set @NewNumVenteErrorChek = @@ERROR
		if(@iMAJUniteGratuite!=0)
		begin
			set @iUniteGratuitePdt = (select unite_gratuite_article_stock from article_stock  where ID_ARTICLE=@iIdArticle and PRIX_VENTE_ARTICLE_STOCK like @dPrix)
			if(@iUniteGratuitePdt is null)
			begin
				set @iUniteGratuitePdt = 0
			end
			set @iUniteGratuitePdt = @iUniteGratuitePdt+@iUnite
			update article_stock set unite_gratuite_article_stock=@iUniteGratuitePdt  where ID_ARTICLE=@iIdArticle and PRIX_VENTE_ARTICLE_STOCK like @dPrix
			set @NewNumVenteErrorChek = @@ERROR
		end
 
end
------------------------------------------------------------------------------------------------------------------------------------------
set @NewNumVenteErrorChek = @@ERROR
-----------------------------------
FETCH NEXT FROM Encours 
INTO @iIdTransVente,@iIdArticle,@dPrix,@iStock,@iQteReserve,@iQteServie,@iUnite
END
CLOSE Encours
DEALLOCATE Encours
--commit transaction
if @NewNumVenteErrorChek = 0 
	BEGIN
		COMMIT TRANSACTION
	END
else
	BEGIN
		ROLLBACK TRANSACTION
	END

si par hasard vous sauriez où est exactement se produit ce message d'erreur.
Merci

**SQLpro** · 09/07/2013, 13h27

1) à part perdre beaucoup de temps votre curseur ne sert à rien et peut être remplacé par des requêtes directes qui sont optimisables...

2) si vous n'êtes pas assez compétent pour transformer cet immonde curseur en quelques UPDATE, alors ouvrez le en minimisant les ressources (LOCAL, STATIC, FORWARD_ONLY, READ_ONLY)

A +

**Kagami** · 09/07/2013, 13h46

en fait ce n'est pas moi qui ai fait ce script , j'ai juste eu l'application déjà codée mais avec des erreurs de ce genre , parce que moi aussi j'arrive pas à bien comprendre ce script . et je n'ai pas compris ce que vous vouliez dire par "en minimisant les ressources (locales et statiques, FORWARD_ONLY, READ_ONLY)"
Merci

**darkelend** · 09/07/2013, 14h17

Il y a toutes les informations dans la documentation.

En choisissant les bons paramètres, tu pourras peut etre réduire le cout de ton curseur et donc réduire le nombre de deadlock. Mais je ne peux pas t'aider sur les curseurs. Les seules fois ou je dois travailler avec les curseurs, c'est pour les supprimer et les remplacer par des traitements ensemblistes

**Kagami** · 10/07/2013, 12h40

est ce que ce script peut être la cause d'un DEADLOCK ou juste qu'il est mal organisé et qui n'a aucun rapport avec tout ça ???????
MERCI DE ME RÉPONDRE

**SQLpro** · 10/07/2013, 14h12

Oui, cette proc a toute les chances de générer un DEADLOCK du fait des curseurs et de la mise à jour

A +

**Kagami** · 10/07/2013, 14h24

merci beaucoup

DEADLOCK & PROCEDURE STOCKEE

Développement SQL Server

Discussions similaires

Partager

Partager