PS, TRANSACTION & CURSOR

**Kagami** · 15/07/2013, 17h02

Bonjour ,
s'il vous plait est ce que quelqu'un pourra m'aider sur cette procédure stockée parce ce vraiment jusqu'à maintenant personne n'a pu l faire , soit à cause de sa taille ou bien il ne sait pas comment faire .
le problème ce qu'avec cette procédure, mon app me génère une erreur de DEADLOCK (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.) et c'est à 100% de cette PS que vient l'erreur . je veux juste enlever ce cuseur mais j'ai pas su comment l faire sans avoir d'autres erreurs :'(
la voila :

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/15/2013 15:11:57 ******/
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

La question posée c'est comment l'exécuter sans avoir à faire au cursor ou même au transaction, parce que avec ces derniers que le problème se régénère

**SQLpro** · 15/07/2013, 23h28

Il n'y a pas de magie en informatique.
L'éradication d'un curseur suppose de connaitre :
1) ce que fait le curseur
2) le modèle de données
3) la structure des tables
Et pour que l'on puisse vous aider il nous faut aussi un jeu d'essai et le résultat attendu !

Bref respecter la charte de postage : http://www.developpez.net/forums/d96...-avant-poster/

A +

**Kropernic** · 16/07/2013, 08h35

Je suis loin d'être un expert et je ne peux rien affirmer sans connaître les infos demandées par sqlpro mais, à vue de nez, ça pue la procédure écrite par un programmeur qui ne sait pas coder autrement qu'en itératif (d'où le curseur) alors qu'un SGDBR travaille de manière ensembliste...

Sur base de ce qu'il y a à faire, il faudrait p-e repenser entièrement la PS et la réécrire.

**Kagami** · 16/07/2013, 11h31

tout à fait moi aussi j 'ai rien pigé dans cette procédure stockée , surtout mélangée avec une Transaction , curseur blabla...j 'ai du mal a la réécrire sans curseur .
dans SQLpro je n'apperçoie aucune erreur

**Kagami** · 17/07/2013, 11h47

c'est enfin j'ai trouvé la solution j 'ai testé plusieurs foi ça n'a pas donné d'erreur.
pour ceux qui veulent savoir comment :

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
 
RETRY: -- Label RETRY ----N'oubliez pas cette ligne 
BEGIN TRANSACTION
BEGIN TRY
DECLARE ...............
....... --- Ecrivez vos requêtes ici 
.......
COMMIT TRANSACTION
END TRY  
BEGIN CATCH
	PRINT 'Rollback Transaction'
	ROLLBACK TRANSACTION
	IF ERROR_NUMBER() = 1205  -- IF Deadlock Error Number
	BEGIN
		WAITFOR DELAY '00:00:00.03' -- Wait for 3 ms (LE DÉLAI C'est selon la taille de votre TRANSACTION)
		GOTO RETRY  -- Go to Label RETRY
	END
END CATCH

on met le TRY CATCH à notre TRANSACTION ,dans le TRY on met apres chaque MISE A JOUR :

Code :

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

WAITFOR DELAY '00:00:03'

--et N'oubliez pas aussi cette ligne
et dans le CATCH on écrit ce qui est cité au dessus.
DITES LE MOI SI C'EST RÉSOLU

**darkelend** · 17/07/2013, 12h01

c'est moche

Ca ne résout pas du tout le problème de deadlock. Ca ne fait que reprendre en cas d'échec. Que se passera t'il si 10 utilisateurs lancent cette procédure en même temps ? Entre les multiples tentatives et les rollback, ca pourrait être pire. Et je pense à l'utilisateur qui se prend X waitfor delay de 3 secondes qui va poireauter un bon moment avant d'avoir une réponse...
La solution idéal serait de réécrire la procédure de manière ensembliste et optimisée. Plus une transaction est rapide et moins tu auras de chance d'avoir de deadlock.

**Kagami** · 17/07/2013, 15h44

t' as vu la PS comment elle est faite , et le curseur qui parcours toutes ces lignes , je ne peux pas le modifier et je ne sais pas avec quoi je vais le remplacer vu sa taille : Cry: . les utilisateurs de l'application sont 4 MAX

**Kropernic** · 17/07/2013, 15h47

Comme déjà dit, il faut analyser ce que fait ce curseur, se l'approprier et une fois qu'on a bien tout compris, on le réécrit, si possible, de manière ensembliste convenablement.

PS, TRANSACTION & CURSOR

Développement SQL Server

Discussions similaires

Partager

Partager