Blocage insertion sql

Version imprimable

24/09/2014, 12h47
joujousagem2006

Blocage insertion sql

Bonjour a tous

une autre fois j'ai eu un phénomène de blocage sur mon serveur SGBD ce dernier est dédier pour la production ainsi pour le reporting

les requêtes qui sont dédier pour le reporting se sont les plus coûteuse car il utilise beaucoup des fonction d'agrégation (order by, distinct,count.....)

aussi beaucoup de jointure dans les procédures sans utilisation des opération de type commit,roolback,begin....

je sais bien que le verrou est un phénomène normal pour le moteur SGBD mais je veux qu'elle ne serai pas un blocage pour cela ma collègue ma conseiller de

modifier le paramétrage

Code:

@@LOCK_TIMEOUT

vers 3000ms la valeur actuelle est donner par la requête

Code:

SELECT @@LOCK_TIMEOUT

est -1

est ce que cette modification peuvent minimiser le phénomène du blocage ????
24/09/2014, 14h55
StringBuilder

Ça devrait éventuellement peut-être résoudre partiellement le problème = au lieu d'attendre indéfiniment, le SGBD ne va conserver des locks que 3 secondes.

Seul souci, c'est qu'une fois les locks expirés, les requêtes de mise à jour vont être shootées, et donc cela va avoir des répercussion sur le fonctionnement des outils.

En revanche, pour du reporting, il est peut-être possible d'utiliser des hints dans les requêtes pour lire une UNCOMMITED. Ca devrait alléger les blocages en permettant de lire des données qui n'ont pas encore été committées.
24/09/2014, 15h26
joujousagem2006

Citation:

Envoyé par StringBuilder

Ça devrait éventuellement peut-être résoudre partiellement le problème = au lieu d'attendre indéfiniment, le SGBD ne va conserver des locks que 3 secondes.

Seul souci, c'est qu'une fois les locks expirés, les requêtes de mise à jour vont être shootées, et donc cela va avoir des répercussion sur le fonctionnement des outils.

Donc comment je peux résoudre mon problème sans perturber est ce que la valeur 3000 est élevé ???

Citation:

Envoyé par StringBuilder

En revanche, pour du reporting, il est peut-être possible d'utiliser des hints dans les requêtes pour lire une UNCOMMITED. Ca devrait alléger les blocages en permettant de lire des données qui n'ont pas encore été committées.

plus de détaille stp ??? j'ai pas compris cette point

autre point est ce que l'augmentation du RAM peuvent améliorer les choses???
24/09/2014, 15h35
mikedavem

le niveau d'solation de transaction read uncommited est en effet une solution possible ou l'utilisation des hint de requête NOLOCK. Cependant si les résultats doivent être consistants ce n'est pas forcément la meilleure option. On peut également utiliser le niveau d'isolation de transaction read committed snapshot par exemple.

L'activité de Reporting malgré l'utilisation de ces mécanismes pour éviter les blocages risque peut être d'avoir un impact sur les ressources du serveur. Ceci est à prendre en considération et dans le cas où l'impact serait trop grand, il existe ici aussi la possibilité de déporter l'activité de Reporting sur un autre serveur (à voir les besoins en Reporting en temps réel ou non dans ce cas et les technologies associées qui permettent de répondre au besoin ...)

Bref en fonction du contexte la solution n'est pas forcément si simple que cela.

++

Citation:

Envoyé par mikedavem

le niveau d'solation de transaction read uncommited est en effet une solution possible ou l'utilisation des hint de requête NOLOCK. Cependant si les résultats doivent être consistants ce n'est pas forcément la meilleure option. On peut également utiliser le niveau d'isolation de transaction read committed snapshot par exemple.

L'activité de Reporting malgré l'utilisation de ces mécanismes pour éviter les blocages risque peut être d'avoir un impact sur les ressources du serveur. Ceci est à prendre en considération et dans le cas où l'impact serait trop grand, il existe ici aussi la possibilité de déporter l'activité de Reporting sur un autre serveur (à voir les besoins en Reporting en temps réel ou non dans ce cas et les technologies associées qui permettent de répondre au besoin ...)

Bref en fonction du contexte la solution n'est pas forcément si simple que cela.

++

ci joint la procédure utilisé pour la partie reporting et qui pause beaucoup du problème

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
CREATE         PROCEDURE [dbo].[spSGM_GetDonneesCapabilite]
 
@dateDebut	datetime,
@dateFin 	datetime,
@Transaction	varchar(30),
@LpNum		varchar(2),
@TypeProduit    varchar(20)=NULL
 
AS
 
IF (@TypeProduit IS NULL)
	SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
	FROM Mvttest
	INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id 
	INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
	INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
	LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
	WHERE MvtTest.mt_datedeb >= @datedebut
	and mvttest.mt_datedeb <=@dateFin
	and MvtTest.mt_test=@transaction
	and vSGM_produit.lp_num=@LpNum
	and sp_id in('PROD','PROTO')
	order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
ELSE
	IF (@TypeProduit <> 'Standard')
		SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
		FROM Mvttest
		INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id 
		INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
		INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
		LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
		WHERE MvtTest.mt_datedeb >= @datedebut
		and mvttest.mt_datedeb <=@dateFin
		and MvtTest.mt_test=@transaction
		and vSGM_produit.lp_num=@LpNum
		and sp_id in('PROD','PROTO')
		AND mp_cod like '%' + @Typeproduit + '%'
		order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
	ELSE
	BEGIN
		SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
		FROM Mvttest
		INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id 
		INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
		INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
		LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
		WHERE MvtTest.mt_datedeb >= @datedebut
		and mvttest.mt_datedeb <=@dateFin
		and MvtTest.mt_test=@transaction
		and vSGM_produit.lp_num=@LpNum
		and sp_id in('PROD','PROTO')
		AND mp_cod IN('ADSL', 'ADSL2+')
		order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
	END

si j'ajoute l'option SET isolation level REPEATABLE READ + instruction begin transaction + instruction commit pour que le procédure deviens

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
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
 
SET isolation level  REPEATABLE READ
Begin transaction;
CREATE         PROCEDURE [dbo].[spSGM_GetDonneesCapabilite]
 
@dateDebut	datetime,
@dateFin 	datetime,
@Transaction	varchar(30),
@LpNum		varchar(2),
@TypeProduit    varchar(20)=NULL
 
AS
 
IF (@TypeProduit IS NULL)
	SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
	FROM Mvttest
	INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id 
	INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
	INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
	LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
	WHERE MvtTest.mt_datedeb >= @datedebut
	and mvttest.mt_datedeb <=@dateFin
	and MvtTest.mt_test=@transaction
	and vSGM_produit.lp_num=@LpNum
	and sp_id in('PROD','PROTO')
	order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
ELSE
	IF (@TypeProduit <> 'Standard')
		SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
		FROM Mvttest
		INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id 
		INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
		INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
		LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
		WHERE MvtTest.mt_datedeb >= @datedebut
		and mvttest.mt_datedeb <=@dateFin
		and MvtTest.mt_test=@transaction
		and vSGM_produit.lp_num=@LpNum
		and sp_id in('PROD','PROTO')
		AND mp_cod like '%' + @Typeproduit + '%'
		order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
	ELSE
	BEGIN
		SELECT distinct mvttest.pt_num, vSGM_produit.pt_lib, mt_testeur, vSGM_Postes.pr_lib, me_libelle
		FROM Mvttest
		INNER JOIN Detailmesure ON DetailMesure.mt_id = mvttest.mt_id 
		INNER JOIN Mesure on detailmesure.me_id = mesure.me_id
		INNER JOIN vSGM_produit ON MvtTest.pt_num = vSGM_produit.pt_num
		LEFT JOIN vSGM_Postes on Mvttest.mt_testeur COLLATE SQL_Latin1_General_CP1_CI_AS = vSGM_Postes.pr_IDreseau
		WHERE MvtTest.mt_datedeb >= @datedebut
		and mvttest.mt_datedeb <=@dateFin
		and MvtTest.mt_test=@transaction
		and vSGM_produit.lp_num=@LpNum
		and sp_id in('PROD','PROTO')
		AND mp_cod IN('ADSL', 'ADSL2+')
		order by MvtTest.pt_num, MvtTest.mt_testeur, me_libelle
	END
commit transaction;

Est ce que le problème se corrige ??