Triiger DeadLock Stop trigger

[olibara]

Bonjour

Je fais mes premiere tentative d'usage d'un trigger ett il me semble avooir reussi a creer un DeadLock

Toute les tables de la DB intervenant dans le trigger son inaccessible depuis SSMS (Querry s'execute sans fin)
Pas de problème pour les autres tables

- Est il possible de debuguer un triger ?
- Peut on savoir s'il s'est exectuté ou s'il a généré un probleme

Voici la Syntaxe de mon Trigger

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE [Clark]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tg_Total]
	ON  [Clark].[dbo].[BakMove]
AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON
 	IF EXISTS
	(
		SELECT	*
		FROM	inserted
		WHERE	TotalStatus = 9
	)
	BEGIN
		EXEC Clark.dbo.sp_DoTotal NULL,9;
		EXEC Clark.dbo.sp_ClarkMail;
	END
END

Merci de votre aide

[mikedavem]

Bonjour,

Oui vous pouvez utiliser le profiler pour votre déboggage avec les événements qui concernent l'éxecution de votre code + deadlock.

Locks : Deadlock
Locks : Deadlock chain
TSQL: StmtCompleted
SP : Completed
...

++

[SQLpro]

Postez le code de vos proc :
Clark.dbo.sp_DoTotal
Clark.dbo.sp_ClarkMail

A +

[olibara]

Envoyé par SQLpro

Postez le code de vos proc :
Clark.dbo.sp_DoTotal
Clark.dbo.sp_ClarkMail

Merci SqlPro
Je n'ai pas acces au server ce matin je les posterai cet apres-midi
Hier j'ai fini par stopper le service MS Sql pour le relancer (il prenait 100% du CPU)

J'ai donc supprimé le trigger et ajouté l'appel des deux procedures stockées depuis l'application et ca ne pose pour le moment pas de souci ?

Ce que je soupconne c'est que l'application genere en boucle une suite d'insert unitaire pour chaque record a inserer dans la table

Code :

1
2
3
4
5
6
7
8
INSERT INTO ...;
INSERT INTO ...;
INSERT INTO ...;
 
-- Plutot qu'un seul insert avec la liste des de chaque row
INSERT INTO 
(FIELDS)
(VALUES..........);

Meme si ca ne fait qu'une dizaine de row avec deux déclencheur ça a peut etre donné la migraine au trigger

[iberserk]

Un déclenchement en boucle infini de vos TRIGGERS semble plus probable...

A voir avec le code de vos SP...

[olibara]

Bonjour

Merci de votre aide
Voici le code des SP

Mais j'ai aussi contaté que le trigger je j'avais mis en place a une petite nuance de code qui peut etre de taille... Ca peut expliquer la boucle ?

J'avais écris : (sans les point virgule à la fin des deux EXEC)

Code :

1
2
3
4
BEGIN
	EXEC Clark.dbo.sp_DoTotal NULL,9
	EXEC Clark.dbo.sp_ClarkMail
END

Voici donc les deux sp :

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
USE [Clark]
GO
/****** Object:  StoredProcedure [dbo].[sp_ClarkMail]    Script Date: 03/09/2011 18:46:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ClarkMail]
AS
BEGIN
 
IF NOT EXISTS 
   (
     SELECT * FROM [TotalChauffeur]
     WHERE  MailStatus=0
    ) 
    RETURN;
 
 
EXEC msdb.dbo.sysmail_start_sp;
 
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'oxg@skynet.com;bartmissa@wareever.com;machin@recutex.com',
    @query = 'SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
    WHERE CAST(DATE AS DATE)=CAST(GETDATE() AS DATE)
    OR MailStatus=0' ,
    @subject = 'Chauffeur Gedaan !',
    @attach_query_result_as_file = 0 ;
 
   UPDATE [Clark].[dbo].[TotalChauffeur]
   SET MailStatus=1
   WHERE CAST(DATE AS DATE)=CAST(GETDATE() AS DATE)
   OR MailStatus=0;
 
 
END

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
USE [Clark]
GO
/****** Object:  StoredProcedure [dbo].[sp_DoTotal]    Script Date: 03/09/2011 18:47:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[sp_DoTotal]
   @ChauffeurID  int,
   @TotalType  int
AS
BEGIN
INSERT INTO Clark.dbo.TotalChauffeur 
(ChauffeurID,
 ChauffeurName,
 Date,
 Gewicht)
SELECT ChauffeurID
      ,ChauffeurName
      ,MAX(Date)
      ,SUM(bruto)-sum(tarra)
  FROM 
  Clark.dbo.BakMove
  WHERE ChauffeurID IN (SELECT DISTINCT COALESCE(@ChauffeurID, ChauffeurID) 
                        FROM   BakMove 
                        WHERE  TotalStatus=CASE @TotalType
                                              WHEN 0 THEN 0
                                              WHEN 9 THEN 9
                                              ELSE 0
                                           END)
  GROUP BY ChauffeurID
          ,ChauffeurName
          ,Cast(Date AS DATE)
END
BEGIN
UPDATE
 Clark.dbo.BakMove
  SET TotalStatus=1
  WHERE ChauffeurID IN (SELECT DISTINCT COALESCE(@ChauffeurID, ChauffeurID) 
                        FROM   BakMove 
                        WHERE  TotalStatus=CASE @TotalType
                                              WHEN 0 THEN 0
                                              WHEN 9 THEN 9
                                              ELSE 0
                                           END)
 
END
          RETURN;

[iberserk]

Avez vous d'autres TRIGGERS ? Sur BakMove par exemple?

Vos sous requètes avec les IN m'ont l'air un peu lourdes!

Code :

1
2
3
4
5
6
7
8
9
10
UPDATE
 Clark.dbo.BakMove
  SET TotalStatus=1
  WHERE ChauffeurID IN (SELECT DISTINCT COALESCE(@ChauffeurID, ChauffeurID) 
                        FROM   BakMove 
                        WHERE  TotalStatus=CASE @TotalType
                                              WHEN 0 THEN 0
                                              WHEN 9 THEN 9
                                              ELSE 0
                                           END)

[olibara]

Aucun autre trigger !
Un peu lourdes : Qu'est ce que ca a de lourd ?

C'est pas tres sorcier et cette table ne contient et ne contiendra que qq centaines de records !!

[elsuket]

Bonjour,

L'appel à msdb.dbo.sysmail_start_sp est inutile.
Je ne sais pas quel est l'effet de l'appel à plusieurs reprises ...

La requête pour l'envoi de mail :

Code :

1
2
3
SELECT	*
FROM	Clark.dbo.TotalChauffeur
WHERE	CAST(DATE AS date) = CAST(GETDATE() AS date)

le WHERE est cochon, puisqu'il à priori pas SARGable.
Tu as de la chance d'être sous SQL Server 2008, autrement, scan de table
Ça fonctionne assez bien donc, mais si tu écris :

Code :

WHERE DATE BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(day, 1, GETDATE()) AS date)

Ça marchera un poil mieux
Cela dit je ne crois pas que ce soit l'origine de ton problème de deadlocks.
Cela explique par contre le CPU, puisque la résolution de deadlocks coûte assez cher en CPU.

Au passage, vire les crochets, ils ne servent à rien à part alourdir le texte de la requête.
Ils servent aux gens qui ne respectent pas les règles de nommage SQL (en mettant des espaces ou des caractères de ponctuation dans les noms des objets ou des colonnes).

Tu peux changer l'UPDATE en :

Code :

1
2
3
4
UPDATE	dbo.TotalChauffeur
SET	MailStatus=1
WHERE	DATE BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(day, 1, GETDATE()) AS date)
OR	MailStatus = 0

Cela dit le OR est moche, pour la même raison que précédemment, d'autant que tu as écrit :

Code :

1
2
3
4
5
6
7
	IF NOT EXISTS 
	(
		SELECT	*
		FROM	dbo.TotalChauffeur
		WHERE	MailStatus = 0
	)
	RETURN

Je pense que tu peux enlever le OR.

Pour sp_DoTotal, le BEGIN au milieu de la procédure est inapproprié, malheureusement SQL Server le compile quand mê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
ALTER PROCEDURE [dbo].[sp_DoTotal]
   @ChauffeurID  int,
   @TotalType  int
AS
BEGIN
	INSERT INTO Clark.dbo.TotalChauffeur 
	(
		ChauffeurID
		, ChauffeurName
		, Date
		, Gewicht
	)
	SELECT		BM.ChauffeurID
			, BM.ChauffeurName
			, MAX(BM.Date)
			, SUM(BM.bruto) - SUM(BM.tarra)
	FROM		Clark.dbo.BakMove AS BM
	INNER JOIN	Clark.dbo.BakMove AS BM2
				ON BM.ChauffeurID = BM2.ChauffeurID
	WHERE		BM2.TotalStatus = CASE WHEN @TotalType = 9 THEN 9 ELSE 0 END
	AND		(
				@ChauffeurID IS NULL
				OR BM2.ChauffeurID = @ChauffeurID
			)
	GROUP BY	BM.ChauffeurID, BM.ChauffeurName, CAST(BM.Date AS DATE)
 
	UPDATE		Clark.dbo.BakMove
	SET		TotalStatus = 1
	WHERE		(
				@ChauffeurID IS NULL
				OR ChauffeurID = @ChauffeurID
			)
	AND		TotalStatus = CASE WHEN @TotalType = 9 THEN 9 ELSE 0 END
END

Mais pour moi cela n'explique toujours pas tes deadlocks.

cette table ne contient et ne contiendra que qq centaines de records !!

Ce n'est pas une raison (dans ton cas c'est même pire ).
On ne dit pas record ou enregistrement, on dit row ou ligne (Voir ma signature et ceci)

Fais-toi une trace au Profiler en choisissant uniquement l'événement Deadlock Graph :

- Sous SSMS, Outils > SQL Profiler
- Connectes-toi à l'instance à surveiller
- Coches la case "Sauvegarder dans un fichier"
- Bascules dans l'onglet "Evénements"
- Décoches toutes les cases les plus à gauche
- Coches "Afficher tous les événements"
- Coches uniquement, sous la catégorie "Locks" l'événement "Deadlock Graph"
- Cliques "Exécuter"

Dès qu'un deadlock va se produire, une ligne va s'afficher dans la trace.
Si tu cliques sur celle-ci, un graphe va apparaître dans la partie inférieure du Profiler, te donnant des indications sur l'origine du deadlock.
Tu peux poster le graphe sur le forum

@++

[olibara]

Envoyé par elsuket

L'appel à msdb.dbo.sysmail_start_sp est inutile.
Je ne sais pas quel est l'effet de l'appel à plusieurs reprises ...

Bonsoir elsuket

Je viens d'essayer de l'enlever et l'envoi de mail ne marche plus !
Il n'est pas impossible que ce soit lié au fait que je travaille avex Xpress et que dans ce cas le service mail doit etre démaré explicitement !

Car c'est depuis que j'ai ajouté cette instruction que mes envois fonctionnenent avec Xpress
Sinon il me dit "mail queued" sans plus

[olibara]

Bonsoir Elsuket

J'ai lu ton article concernant le SARGable, Si je comprends en gros ce que tu explique tu veux dire Que dans ce cas ci

Code :

WHERE	CAST(DATE AS date) = CAST(GETDATE() AS date)

On force un cast de Date a chaque ligne
Alors qu'ici

Code :

WHERE DATE BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(day, 1, GETDATE()) AS date)

Le cast se fait une fois pour la definition de l'intervalle et la date est evaluée a chaque ligne par rapport a l'intervalle défini, ce qui en theorie allege le process

Mais je ne suis pas certain que le gain soit vraiment signifiant ?

[elsuket]

Le cast se fait une fois pour la definition de l'intervalle et la date est evaluée a chaque ligne par rapport a l'intervalle défini, ce qui en theorie allege le process

J'aurai plutôt écrit :

Le cast se fait une fois pour la définition de l'intervalle et les valeurs de la colonne date sont évaluées a chaque ligne par rapport a l'intervalle défini, ce qui en théorie allège le process.

Désolé de chipoter.

Mais je ne suis pas certain que le gain soit vraiment signifiant ?

J'ai mesuré ça sur une très petite table à moi d'une base de données SQL Server 2008, et je suis passé de 31 pages à 5.
Le gain n'est donc pas énorme, mais ma table très est petite (quelques milliers de lignes).

Que te donne la requête suivante :

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
DECLARE @ts_now BIGINT
 
-- 2005 select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
-- 2008
SELECT	@ts_now = ms_ticks FROM	sys.dm_os_sys_info;      
 
SELECT	DATEADD(ms, -1 * (@ts_now - time_stamp), GETDATE()) AS event_date_time
	, SQLProcessUtilization AS SQLServer
	, 100 - SystemIdle - SQLProcessUtilization AS Other
	, SystemIdle AS Free
	, UserModeTime
	, KernelModeTime
	, PageFaults
	, WorkingSetDelta
	, MemoryUtilPct
FROM
(
	SELECT	record.value('(./Record/@id)[1]', 'int') AS record_id
		, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'tinyint') AS SystemIdle
		, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'tinyint') AS SQLProcessUtilization
		, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime
		, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime
		, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults
		, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'bigint') AS WorkingSetDelta
		, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'bigint') AS MemoryUtilPct
		, time_stamp
	FROM	(
			SELECT	timestamp AS time_stamp
				, CAST(record AS xml) AS record 
			FROM	sys.dm_os_ring_buffers 
			WHERE	ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			AND	record LIKE '%<SystemHealth>%'
		) AS x
) AS y
ORDER	BY record_id DESC
GO

@++

[aieeeuuuuu]

Bonjour

Envoyé par olibara

J'ai lu ton article concernant le SARGable,...

Peut-être que celui-ci, également écrit par Elsuket, concernant les deadlocks pourra aussi t’intéresser

[elsuket]

'M'en rappelais pas de celui-là ... je prends zéro

Merci aieeeuuuuu !

@++

[aieeeuuuuu]

Envoyé par elsuket

'M'en rappelais pas de celui-là ... je prends zéro

Merci aieeeuuuuu !

@++

Merci à toi... pour l'article ! car si moi je m'en souvenais, c'est que je l'avais trouvé fort utile !