Résolution d'un interblocage.

**MicMac42** · 01/12/2016, 14h19

Bonjour tout le monde,

C'est al première fois que je me trouve confronté à un problème d'interblocage. Après avoir lu un peu sur le sujet, j'ai réussi à identifier l'interblocage, et je cherche à le résoudre.
Malheureusement, je ne sais pas trop par ou commencer.

Apres analyse du xml, l'interblocage se produit sur un index a partir d'une requête UPDATE et une requête SELECT.
Si je comprend bien, La requête UPDATE pose un blocage sur la ligne qu'il est en train de modifier, puis ensuite veut mettre a jour l'index IX_phone_campaign_evolution_call_pcec_employee_id_pcec_start.
Alors que la Requête SELECT pose un blocage sur l'index IX_phone_campaign_evolution_call_pcec_employee_id_pcec_start, et ensuite cherche a lire à la ligne bloquée par la requête UPDATE.
Est-ce correct, ou est-ce plus compliqué que ça?

Ensuite pour résoudre ce problème que me conseillerez vous?
- supprimer, modifier l'index IX_phone_campaign_evolution_call_pcec_employee_id_pcec_start? (Il est quand même bien pratique pour la rapidité du SELECT)
- modification de la requête? (Est ce que ça a un sens de "séparer" la première partie de la requête (la définition de la TABLE temporaireOrdered_Calls)
-autre solution?

Je vous remercie d'avance de l'attention apportée à mon message, Micmac.

Voici la table concernée:

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
USE [VisionSharp]
GO
 
/****** Object:  Table [dbo].[phone_campaign_evolution_call]    Script Date: 12/01/2016 13:46:17 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[phone_campaign_evolution_call](
	[pcec_id] [int] IDENTITY(1,1) NOT NULL,
	[pcec_campaign_evolution_id] [int] NULL,
	[pcec_start] [datetime] NULL,
	[pcec_end] [datetime] NULL,
	[pcec_employee_id] [int] NULL,
	[pcec_call_status] [tinyint] NULL,
	[pcec_reached_status] [tinyint] NULL,
	[pcec_order_id] [int] NULL,
	[pcec_reminder_date] [datetime] NULL,
	[pcec_reminder_comment] [text] NULL,
	[pcec_comment] [text] NULL,
	[pcec_wav] [varchar](50) NULL,
 CONSTRAINT [PK_phone_campaign_evolution_call] PRIMARY KEY CLUSTERED 
(
	[pcec_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Cette table en plus de du Clustered Index sur la clé primaire, possède un index sur les colonnes pcec_employee_id et pcec_start

Code :

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

1
2
3
4
5
6
7
8
9
10
USE [VisionSharp]
GO
 
/****** Object:  Index [IX_phone_campaign_evolution_call_pcec_employee_id_pcec_start]    Script Date: 12/01/2016 13:49:11 ******/
CREATE NONCLUSTERED INDEX [IX_phone_campaign_evolution_call_pcec_employee_id_pcec_start] ON [dbo].[phone_campaign_evolution_call] 
(
	[pcec_employee_id] ASC,
	[pcec_start] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Voici le code xml généré par sql:

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
<event id="123" timestamp="2016-11-30T14:45:15.749Z" version="1" package="sqlserver" name="xml_deadlock_report">
<data name="xml_report">
<type package="package0" name="unicode_string"/>
<value>
	<deadlock-list> 
		<victim-list> 
		<victimProcess id="process4d3948"/> 
		<process-list> 
			<process id="process4d3948" taskpriority="0" logused="0" waitresource="KEY: 5:72057594354139136 (96002f1ae246)" waittime="2914" 
					ownerId="8468879337" transactionname="SELECT" lasttranstarted="2016-11-30T15:44:51.340" XDES="0x91afbcc0" lockMode="S" 
					schedulerid="3" kpid="679836" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="0" 
					lastbatchstarted="2016-11-30T15:44:51.340" lastbatchcompleted="2016-11-30T15:44:51.340" clientapp=".Net SqlClient Data Provider" 
					hostname="VCS-XENAPP02" hostpid="13332" loginname="Vision#" isolationlevel="read committed (2)" xactid="8468879337" 
					currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> 
			<executionStack> 
				<frame procname="" line="24" stmtstart="1278" stmtend="6934" sqlhandle="0x03000500b892320bfa18fe00c2a600000100000000000000"> 
				</frame> 
			</executionStack> 
			<inputbuf> Proc [Database Id = 5 Object Id = 187863736] prcphone_campaign_agent_hours_get
			</inputbuf> 
			</process> 
 
			<process id="process9d2d3288" taskpriority="0" logused="792" waitresource="PAGE: 5:1:815956" waittime="3936" 
					ownerId="8469042389" transactionname="UPDATE" lasttranstarted="2016-11-30T15:45:03.800" XDES="0xc5cbd970" lockMode="IX" 
					schedulerid="1" kpid="712148" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" 
					lastbatchstarted="2016-11-30T15:45:03.800" lastbatchcompleted="2016-11-30T15:45:03.800" clientapp=".Net SqlClient Data Provider" 
					hostname="VCS-TFS01" hostpid="1956" loginname="Vision#" isolationlevel="read committed (2)" xactid="8469042389" 
					currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
			<executionStack> 
				<frame procname="" line="1" stmtstart="580" sqlhandle="0x020000000bd3b70a23511235272c78a9e5b90238df5c35cd"> 
				</frame> 
				<frame procname="" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> 
				</frame> 
			</executionStack> 
			<inputbuf> (@pcec_campaign_evolution_id int,@pcec_start datetime,@pcec_end datetime,@pcec_employee_id int,@pcec_call_status tinyint,@pcec_reached_status tinyint,@pcec_order_id int,@pcec_reminder_date datetime,@pcec_reminder_comment nvarchar(4000),@pcec_comment nvarchar(4000),@pcec_wav nvarchar(4000))
				UPDATE phone_campaign_evolution_call 
				SET pcec_campaign_evolution_id = @pcec_campaign_evolution_id, pcec_start = @pcec_start, pcec_end = @pcec_end, pcec_employee_id = @pcec_employee_id, 
					pcec_call_status = @pcec_call_status, pcec_reached_status = @pcec_reached_status, pcec_order_id = @pcec_order_id, pcec_reminder_date = @pcec_reminder_date, 
					pcec_reminder_comment = @pcec_reminder_comment, pcec_comment = @pcec_comment, pcec_wav = @pcec_wav 
				WHERE pcec_id = 71574 
			</inputbuf> 
			</process> 
		</process-list> 
 
		<resource-list> 
			<keylock 
 
				hobtid="72057594354139136" 
				dbid="5" 
				objectname="PK_phone_campaign_evolution_call" 
				indexname="" 
				id="lockbabf3500" 
				mode="X" 
				associatedObjectId="72057594354139136">
 
				<owner-list> 
					<owner id="process9d2d3288" mode="X"/> 
				</owner-list> 
				<waiter-list> 
					<waiter id="process4d3948" mode="S" requestType="wait"/> 
				</waiter-list> 
			</keylock> 
			<pagelock 
				fileid="1" 
				pageid="815956" 
				dbid="5" 
				objectname="IX_phone_campaign_evolution_call_pcec_employee_id_pcec_start" 
				id="lock10da31080" 
				mode="S" 
				associatedObjectId="72057594354204672">
				<owner-list> 
					<owner id="process4d3948" mode="S"/> 
				</owner-list> 
				<waiter-list> 
					<waiter id="process9d2d3288" mode="IX" requestType="wait"/> 
				</waiter-list> 
			</pagelock> 
		</resource-list> 
	</deadlock> 
	</deadlock-list> </value>
<text/>
</data>
</event>

Et les 2 requête concernée par l'interblocage:

Code :

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

1
2
3
4
5
UPDATE phone_campaign_evolution_call 
				SET pcec_campaign_evolution_id = @pcec_campaign_evolution_id, pcec_start = @pcec_start, pcec_end = @pcec_end, pcec_employee_id = @pcec_employee_id, 
					pcec_call_status = @pcec_call_status, pcec_reached_status = @pcec_reached_status, pcec_order_id = @pcec_order_id, pcec_reminder_date = @pcec_reminder_date, 
					pcec_reminder_comment = @pcec_reminder_comment, pcec_comment = @pcec_comment, pcec_wav = @pcec_wav 
				WHERE pcec_id = 71574

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
USE [VisionSharp]
GO
/****** Object:  StoredProcedure [dbo].[prcphone_campaign_agent_hours_get]    Script Date: 30.11.2016 17:34:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[prcphone_campaign_agent_hours_get] 
	@CallcenterId int,
	@MorningStart datetime,
	@MorningEnd datetime,
	@NoonStart datetime,
	@NoonEnd datetime,
	@EveningStart datetime,
	@EveningEnd datetime,
	@ValidateString varchar(max),
	@ExistSource tinyint,
	@MaxPauseBetweenCalls int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
;WITH Ordered_Calls AS (SELECT ROW_NUMBER() OVER (PARTITION BY pcec_employee_id ORDER BY pcec_start) AS rn, * 
FROM phone_campaign_evolution_call 
LEFT JOIN employee ON pcec_employee_id = em_id
WHERE (em_callcenter_id = @CallcenterId OR @CallcenterId = 0) AND Convert(date, pcec_start) = CONVERT(date, @MorningStart)),
 
Evolution_Calls AS
 
	(SELECT 
		currentcall.pcec_employee_id AS Employee_Id, 
		DATEDIFF(second, currentcall.pcec_start, currentcall.pcec_end)/3600.0 AS Work_Time,
		currentcall.pcec_start AS work_start, currentcall.pcec_end AS work_end,
		CASE WHEN nextcall.pcec_start is not null 
			THEN DATEDIFF(second, currentcall.pcec_start, nextcall.pcec_start)/3600.0
			ELSE DATEDIFF(second, currentcall.pcec_start, currentcall.pcec_end)/3600.0 
			END AS login_time,
		CASE WHEN currentcall.pcec_start BETWEEN @MorningStart AND @MorningEnd THEN 'Morning' 
			WHEN currentcall.pcec_start BETWEEN @NoonStart AND @NoonEnd THEN 'Noon'
			WHEN currentcall.pcec_start BETWEEN @EveningStart AND @EveningEnd THEN 'Evening'
			ELSE 'none' END AS Call_Shift
	FROM Ordered_Calls currentcall
	LEFT JOIN Ordered_Calls nextcall ON currentcall.rn = nextcall.rn-1 AND currentcall.pcec_employee_id = nextcall.pcec_employee_id AND DATEDIFF(MINUTE, currentcall.pcec_start, nextcall.pcec_start) < @MaxPauseBetweenCalls),
 
Hours_Pivot AS (
SELECT Employee_Id, [Morning] AS morning_hours, [Noon] AS noon_hours, [Evening] AS evening_hours, [None] AS none_sum
FROM (SELECT Employee_Id, work_time, Call_Shift FROM Evolution_Calls) AS e
PIVOT (SUM(Work_Time) FOR Call_Shift IN ([Morning], [Noon], [Evening], [None])) AS pvt),
 
Login_Pivot AS (
SELECT Employee_Id, [Morning] AS morning_login, [Noon] AS noon_login, [Evening] AS evening_login
FROM (SELECT Employee_Id, login_time, Call_Shift FROM Evolution_Calls) AS e
PIVOT (SUM(login_time) FOR Call_Shift IN ([Morning], [Noon], [Evening])) AS pvt)
 
SELECT 
	em_id, 
	em_name + ' ' + em_fname AS em_info,
	CASE WHEN EXISTS(SELECT * FROM employee_hours WHERE eh_employee_id = em_id AND eh_date = CONVERT(date, @MorningStart) AND eh_source = @ExistSource) THEN @ValidateString ELSE '' END AS validated,
	morning_hours AS morning_sum, 
	morning_login AS morning_minmax,
	CASE WHEN morning_login <> 0 THEN morning_hours / morning_login ELSE 0 END AS morning_percent,
	noon_hours AS noon_sum, 
	noon_login AS noon_minmax,
	CASE WHEN noon_login <> 0 THEN noon_hours / noon_login ELSE 0 END AS noon_percent,
	evening_hours As evening_sum, 
	evening_login AS evening_minmax,
	CASE WHEN evening_login <> 0 THEN evening_hours / evening_login ELSE 0 END AS evening_percent,
	COALESCE(none_sum, 0) AS none_sum
FROM Hours_Pivot
LEFT JOIN Login_Pivot ON Login_Pivot.Employee_Id = Hours_Pivot.Employee_Id
LEFT JOIN employee ON em_id = Hours_Pivot.Employee_Id
END

**SQLpro** · 01/12/2016, 15h40

Dans votre cas le passage au niveau d’isolation SNAPSHOT de la requête SELECT va rendre impossible l'interblocage.

Il est dommage que ce niveau d'isolation soit si mal connu et peu utilisé !

A +

**MicMac42** · 01/12/2016, 15h53

Merci beaucoup pour cette réponse!

Je vais de suite m'intéresser aux niveaux d'isolation.

MicMac

Résolution d'un interblocage. [2008]

Développement SQL Server

Discussions similaires

Partager

Partager