Interblocage entre 2 sessions

Version imprimable

Bonjour j'ai un soucis d'interblocage entre 2 sessions que je nommerai A et B.

La session A est lockée par la session B.
Et La session B est lockée par la session A.

D'après ce lien : https://msdn.microsoft.com/fr-be/library/ms186736.aspx
L'interblocage devrait être géré par l'instance , ce qui n'est pas le cas.

Ma session A fait via une procédure stockée: ALTER DATABASE [ma_db] REMOVE FILE ' + @old_F_name;
Ma session B fait en résumé un listing des fichiers DB avec leurs infos, via cette query :

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
 
INSERT INTO [info].[ma_table]
SELECT 
		TAB_B.Drive_B as Drive,
		TAB_B.Drive_total_GB,
		TAB_B.Drive_available_GB,
		TAB_B.Drive_DiskUsage_percent,
		CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.group_name END AS group_name, 
		CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.file_name END AS file_name, 
		TAB_C.file_size_GB,
		TAB_C.file_space_used_GB,
		TAB_C.file_free_space_GB,
		TAB_C.[file_%_used],
		TAB_C.file_path
	FROM
	(
	SELECT ISNULL(groupname,'TLOG') group_name, name as file_name, file_size/1024 as file_size_GB, space_used/1024 as file_space_used_GB, free_space/1024 as file_free_space_GB,CAST(ROUND((space_used/(free_space+space_used))*100,2) as float) as 'file_%_used', filename as file_path, LEFT(filename,1) as Drive,SUBSTRING(filename,1,CHARINDEX('\',filename,(charindex('\',filename)+1))) as begin_file_path
	FROM 
	(
		SELECT
		DB_NAME() as database_name
		,sysfilegroups.groupid
		,sysfilegroups.groupname
		,fileid
		,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size
		,convert(decimal(12,2),round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,2)) as space_used
		,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,2)) as free_space
		,sysfiles.name
		,sysfiles.filename
		FROM sys.sysfiles WITH(NOLOCK)
		LEFT OUTER JOIN sys.sysfilegroups WITH(NOLOCK) ON sysfiles.groupid = sysfilegroups.groupid
	)TB
	)TAB_C FULL JOIN
	(
		SELECT *,ROUND(100-((CAST(Drive_available_GB as float) /CAST(Drive_total_GB as float))*100),2) as 'Drive_DiskUsage_percent'
		FROM
		(
		SELECT DISTINCT
			SUBSTRING(volume_mount_point, 1, 1) AS Drive_B
			,SUBSTRING(physical_name,1,CHARINDEX('\',physical_name,(charindex('\',physical_name)+1))) as begin_file_path
			,total_bytes/1024/1024/1024 AS Drive_total_GB
			,available_bytes/1024/1024/1024 AS Drive_available_GB
		FROM
			sys.master_files AS f WITH(NOLOCK)
		CROSS APPLY
			sys.dm_os_volume_stats(f.database_id, f.file_id)
		)TAB
	)TAB_B
	ON TAB_C.Drive =TAB_B.Drive_B AND TAB_C.begin_file_path = TAB_B.begin_file_path

Si les 2 sessions arrivent en même temps. ça se bloque et ne se libère JAMAIS.
Serait-il possible de contourner ce problème?

Merci d'avance pour l'aide ,

Vinc

15/11/2016, 14h32
François DORIN

Bonjour,

Pour qu'il y ait un interblocage, il doit y avoir d'autres ressources en jeu. Il faut que la session A essaie d'accéder à un objet verrouillé par la session B et que la session B essaie d'accéder à un objet verrouillé par la session A.

Il nous faut donc le détail exact de ce que fait la session A et la session B.
15/11/2016, 14h54
vinch999

Salut,
C'est bien ce que j'ai décrit dans mon post.

La session A fait un REMOVE FILE sur la db (je ne pense pas que le reste du code de la sp soit utile car je vois bien que ça lock sur cette opération). En même temps s'exécute un SELECT (j'ai mis le code dans mon post) sur les tables système (dont sysfiles,...) , je ne sais pas exactement quelle partie provoque le lock. (peut-être ceci ? : sys.dm_os_volume_stats(f.database_id, f.file_id) )
J'ai rajouté des NOLOCK au niveau des lectures et ne suis pas certain que ça résoudra le problème.
15/11/2016, 14h58
François DORIN

Citation:

Envoyé par vinch999

(je ne pense pas que le reste du code de la sp soit utile car je vois bien que ça lock sur cette opération).

Dans le cas d'un interblocage, c'est une erreur de penser que le code fautif est le code où ça bloque. Il fait parti du problème, mais n'en représente qu'une partie.

Donc, je réitère ma demande : "Il nous faut donc le détail exact de ce que fait la session A et la session B.".

Et quand je dis le détail exact, ce n'est pas "en gros ça fait ça", mais le code exécuté sur chacune des deux sessions.

Ok, je voulais juste éviter de vous inonder par du code inutile ;)

La session A :

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
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
 
ALTER PROCEDURE [toto].[myData_partitioning] AS
BEGIN
	/*Déclaration des variables*/
	DECLARE @ma_date date;
	DECLARE @old_date date;
	DECLARE @new_date date;
	DECLARE @old_date_int int;
	DECLARE @new_date_int int;
	DECLARE @old_mois varchar(255);
	DECLARE @new_mois varchar(255);
	DECLARE @old_FG_name varchar(255);
	DECLARE @new_FG_name varchar(255);
	DECLARE @old_F_name varchar(255);
	DECLARE @new_F_name varchar(255);
	DECLARE @sql_remove_old_file varchar(MAX);
	DECLARE @sql_remove_old_filegroup varchar(MAX);
	DECLARE @sql_add_new_file varchar(MAX);
	DECLARE @sql_add_new_filegroup varchar(MAX);
	DECLARE @sql_alter_partition_scheme varchar(MAX);
	/*SET des variables*/
		SET @ma_date = CAST(GETDATE() as date);
		SET @old_date = DATEADD(MONTH,-2,@ma_date);
		SET @new_date = DATEADD(MONTH,1,@ma_date);
		SET @old_date_int =	CAST(CAST(YEAR(@old_date) as varchar(255))+CASE WHEN MONTH(@old_date) <10 THEN '0'+CAST(MONTH(@old_date) as varchar(255)) ELSE CAST(MONTH(@old_date) as varchar(255)) END+'01' as int)
		SET @new_date_int =	CAST(CAST(YEAR(@new_date) as varchar(255))+CASE WHEN MONTH(@new_date) <10 THEN '0'+CAST(MONTH(@new_date) as varchar(255)) ELSE CAST(MONTH(@new_date) as varchar(255)) END+'01' as int)
 
		SET @old_mois = 
		CASE MONTH(@old_date)
			WHEN 1 THEN 'JANVIER'
			WHEN 2 THEN 'FEVRIER'
			WHEN 3 THEN 'MARS'
			WHEN 4 THEN 'AVRIL'
			WHEN 5 THEN 'MAI'
			WHEN 6 THEN 'JUIN'
			WHEN 7 THEN 'JUILLET'
			WHEN 8 THEN 'AOUT'
			WHEN 9 THEN 'SEPTEMBRE'
			WHEN 10 THEN 'OCTOBRE'
			WHEN 11 THEN 'NOVEMBRE'
			WHEN 12 THEN 'DECEMBRE'
			ELSE 'UNKNOWN'
		END;
		SET @new_mois = 
		CASE MONTH(@new_date)
			WHEN 1 THEN 'JANVIER'
			WHEN 2 THEN 'FEVRIER'
			WHEN 3 THEN 'MARS'
			WHEN 4 THEN 'AVRIL'
			WHEN 5 THEN 'MAI'
			WHEN 6 THEN 'JUIN'
			WHEN 7 THEN 'JUILLET'
			WHEN 8 THEN 'AOUT'
			WHEN 9 THEN 'SEPTEMBRE'
			WHEN 10 THEN 'OCTOBRE'
			WHEN 11 THEN 'NOVEMBRE'
			WHEN 12 THEN 'DECEMBRE'
			ELSE 'UNKNOWN'
		END;
 
	SET @old_FG_name = 'FG_DMA_DATA_AVG_'+@old_mois;
	SET @new_FG_name = 'FG_DMA_DATA_AVG_'+@new_mois;
	SET @old_F_name = 'F_DMA_DATA_AVG_'+@old_mois;
	SET @new_F_name = 'F_DMA_DATA_AVG_'+@new_mois;
 
	INSERT INTO LOG.toto_partitioning(message) VALUES ('OLD:   '+CAST(@old_date_int as varchar(255))+'   -   Mois   -   '+@old_mois+'   -   FileGroup   -   '+@old_FG_name+'   -   File   -   '+@old_F_name);
	INSERT INTO LOG.toto_partitioning(message) VALUES ('NEW:   '+CAST(@new_date_int as varchar(255))+'   -   Mois   -   '+@new_mois+'   -   FileGroup   -   '+@new_FG_name+'   -   File   -   '+@new_F_name);
 
	IF NOT EXISTS(SELECT * FROM sys.filegroups WHERE name =@new_FG_name)
	BEGIN
		INSERT INTO LOG.toto_partitioning(message) VALUES ('Filegroup '+@new_FG_name+ ' does not exists. Begin creation.');
		/*Suppression des anciennes data*/
		INSERT INTO LOG.toto_partitioning(message) VALUES ('CREATE TABLE toto.myData_old_data.');
			CREATE TABLE toto.myData_old_data
			(
				[iId] [bigint] IDENTITY(1,1) NOT NULL,
				[iDmaId] [int] NOT NULL,
				[iEId] [int] NOT NULL,
				[iPid] [int] NOT NULL,
				[chValueAvg] [varchar](500) NULL,
				[chValueMax] [varchar](500) NULL,
				[chValueMin] [varchar](500) NULL,
				[dtFirst] [datetime] NOT NULL,
				[iStatus] [int] NOT NULL,
				[chIndex] [nvarchar](500) NULL,
				[chElement] [nvarchar](500) NULL,
				[chParameter] [nvarchar](500) NULL,
				[DateKey] [int] NOT NULL,
				Constraint PK_myData_old_data Primary Key Clustered
				(
					DateKey
				  , iId
				)
			) ON FG_DMA_DATA_AVG_OLD_DATA;
			INSERT INTO LOG.toto_partitioning(message) VALUES ('ALTER TABLE toto.myData SWITCH PARTITION 1 TO toto.myData_old_data');
			ALTER TABLE toto.myData SWITCH PARTITION 1 TO toto.myData_old_data;
			INSERT INTO LOG.toto_partitioning(message) VALUES ('DROP TABLE toto.myData_old_data');
			DROP TABLE toto.myData_old_data;
		/*MERGE avant d'enlever la partition du mois-2*/
			INSERT INTO LOG.toto_partitioning(message) VALUES ('ALTER PARTITION FUNCTION Partition_Function_toto_myData() MERGE RANGE ('+CAST(@old_date_int as varchar(255))+')');
			ALTER PARTITION FUNCTION Partition_Function_toto_myData() MERGE RANGE (@old_date_int);
		/*REBUILD clustered index - Permet de déplacer les data de mois-2 vers F_DMA_DATA_AVG_OLD_DATA*/
			--ALTER INDEX [PK_toto_myData] ON [toto].[myData] REBUILD;
			--SELECT partition_number FROM dbo.FileGroupDetail WHERE partition_filegroup = 
			INSERT INTO LOG.toto_partitioning(message) VALUES ('ALTER INDEX [PK_toto_myData] ON [toto].[myData] REBUILD PARTITION = 1 WITH (SORT_IN_TEMPDB = ON)');
			ALTER INDEX [PK_toto_myData] ON [toto].[myData] REBUILD PARTITION = 1 WITH (SORT_IN_TEMPDB = ON);
		/*Suppression de File et FileGroup de mois-2*/
			SET @sql_remove_old_file = 'ALTER DATABASE [ma_db] REMOVE FILE ' + @old_F_name;
			INSERT INTO LOG.toto_partitioning(message) VALUES (@sql_remove_old_file);
			EXEC (@sql_remove_old_file);
			SET @sql_remove_old_filegroup = 'ALTER DATABASE [ma_db] REMOVE FILEGROUP ' + @old_FG_name;
			INSERT INTO LOG.toto_partitioning(message) VALUES (@sql_remove_old_filegroup);
			EXEC (@sql_remove_old_filegroup);
		/*Creation de File et FileGroup de mois+2*/
			SET @sql_add_new_filegroup = 'ALTER DATABASE [ma_db] ADD FILEGROUP ' + @new_FG_name;
			INSERT INTO LOG.toto_partitioning(message) VALUES (@sql_add_new_filegroup);
			EXEC (@sql_add_new_filegroup);
			SET @sql_add_new_file ='ALTER DATABASE [ma_db] ADD FILE ( name =' + @new_F_name+',filename =''H:\SQL_DATA7\SQL_DATA\'+@new_F_name+'.ndf'',size=128MB, filegrowth=256MB) TO FILEGROUP ' + @new_FG_name;	
			INSERT INTO LOG.toto_partitioning(message) VALUES (@sql_add_new_file);
			EXEC (@sql_add_new_file);
		/* on dit à SQL que le nouveau FG de la nouvelle partition sera : */
			SET @sql_alter_partition_scheme = 'ALTER PARTITION SCHEME Partition_Scheme_toto_myData NEXT USED '+ @new_FG_name;
			INSERT INTO LOG.toto_partitioning(message) VALUES (@sql_alter_partition_scheme);
			EXEC (@sql_alter_partition_scheme);
		/* On Split pour rajouter le mois+2 */
			INSERT INTO LOG.toto_partitioning(message) VALUES ('ALTER PARTITION FUNCTION Partition_Function_toto_myData() SPLIT RANGE ('+CAST(@new_date_int as varchar(255))+')');
			ALTER PARTITION FUNCTION Partition_Function_toto_myData() SPLIT RANGE (@new_date_int);
			-- ==>Les données qui étaient dans le FG_juin mais qui sont ultérieures sont déplacées dans le dernier FG
			INSERT INTO LOG.toto_partitioning(message) VALUES ('ALTER INDEX [PK_toto_myData] ON [toto].[myData] REBUILD PARTITION = 4 WITH (SORT_IN_TEMPDB = ON)');
			ALTER INDEX [PK_toto_myData] ON [toto].[myData] REBUILD PARTITION = 4 WITH (SORT_IN_TEMPDB = ON);
	END
END

En gros, cette stored proc me permet de partitionner une table en 4 parties:
Les vieilles données, le mois -1 , le mois en cours, le futur.

Cette session A est déclenchée par un job Talend.

La session B appelle cette vue via un job SQL server qui s'exécute toutes les 2 minutes (géré par l'agent sql server):

La vue:

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
 
CREATE VIEW [info].[get_files_space] AS
	SELECT 
		TAB_B.Drive_B as Drive,
		TAB_B.Drive_total_GB,
		TAB_B.Drive_available_GB,
		TAB_B.Drive_DiskUsage_percent,
		CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.group_name END AS group_name, 
		CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.file_name END AS file_name, 
		TAB_C.file_size_GB,
		TAB_C.file_space_used_GB,
		TAB_C.file_free_space_GB,
		TAB_C.[file_%_used],
		TAB_C.file_path
	FROM
	(
	SELECT ISNULL(groupname,'TLOG') group_name, name as file_name, file_size/1024 as file_size_GB, space_used/1024 as file_space_used_GB, free_space/1024 as file_free_space_GB,CAST(ROUND((space_used/(free_space+space_used))*100,2) as float) as 'file_%_used', filename as file_path, LEFT(filename,1) as Drive,SUBSTRING(filename,1,CHARINDEX('\',filename,(charindex('\',filename)+1))) as begin_file_path
	FROM 
	(
		SELECT
		DB_NAME() as database_name
		,sysfilegroups.groupid
		,sysfilegroups.groupname
		,fileid
		,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size
		,convert(decimal(12,2),round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,2)) as space_used
		,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,2)) as free_space
		,sysfiles.name
		,sysfiles.filename
		FROM sys.sysfiles WITH(NOLOCK)
		LEFT OUTER JOIN sys.sysfilegroups WITH(NOLOCK) ON sysfiles.groupid = sysfilegroups.groupid
	)TB
	WHERE database_name ='ma_db'
	)TAB_C FULL JOIN
	(
		SELECT *,ROUND(100-((CAST(Drive_available_GB as float) /CAST(Drive_total_GB as float))*100),2) as 'Drive_DiskUsage_percent'
		FROM
		(
		SELECT DISTINCT
			SUBSTRING(volume_mount_point, 1, 1) AS Drive_B
			,SUBSTRING(physical_name,1,CHARINDEX('\',physical_name,(charindex('\',physical_name)+1))) as begin_file_path
			,total_bytes/1024/1024/1024 AS Drive_total_GB
			,available_bytes/1024/1024/1024 AS Drive_available_GB
		FROM
			sys.master_files AS f WITH(NOLOCK)
		CROSS APPLY
			sys.dm_os_volume_stats(f.database_id, f.file_id)
			WHERE (type_desc = 'ROWS' OR SUBSTRING(physical_name,1,CHARINDEX('\',physical_name,(charindex('\',physical_name)+1))) = 'E:\SQL_LOGS\')
			AND SUBSTRING(physical_name,1,CHARINDEX('\',physical_name,(charindex('\',physical_name)+1))) != 'D:\SQL_ROOT\'
		)TAB
	)TAB_B
	ON TAB_C.Drive =TAB_B.Drive_B AND TAB_C.begin_file_path = TAB_B.begin_file_path

Le code du job

Code:

1
2
3
4
5
6
7
8
9
10
11
 
INSERT INTO [info].[monitoring_transaction_log]
	SELECT 
		CAST(REPLACE(CAST(GETDATE() as date),'-','') as int) as DateKey,
		GETDATE() as moment,
		Drive_total_GB,
		file_size_GB as file_size_GB,
		file_space_used_GB as file_used_GB,
		CAST((CAST(file_space_used_GB as float)/CAST(Drive_total_GB as float))*100 as decimal(10,2)) as file_percent_used
	FROM [info].[get_files_space] WITH(NOLOCK)
	WHERE GROUP_NAME = 'TLOG';

Bonjour,

Enlevons les NOLOCK et remettons nous-en aux DMVs les plus récentes :

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
 
SELECT		TAB_B.Drive_B as Drive
		, TAB_B.Drive_total_GB
		, TAB_B.Drive_available_GB
		, TAB_B.Drive_DiskUsage_percent
		, CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.group_name END AS group_name
		, CASE WHEN TAB_B.Drive_B = 'F' THEN 'TEMP_DB' ELSE TAB_C.file_name END AS file_name
		, TAB_C.file_size_GB
		, TAB_C.file_space_used_GB
		, TAB_C.file_free_space_GB
		, TAB_C.[file_%_used]
		, TAB_C.file_path
FROM		(
			SELECT	ISNULL(groupname,'TLOG') group_name
				, name as file_name
				, file_size / 1024 as file_size_GB
				, space_used / 1024 as file_space_used_GB
				, free_space / 1024 as file_free_space_GB
				, CAST(ROUND((space_used/(free_space + space_used))*100,2) AS float) AS [file_%_used]
				, filename AS file_path
				, LEFT(filename,1) AS Drive
				, SUBSTRING(filename, 1, CHARINDEX('\', filename, (CHARINDEX('\', filename) + 1))) AS begin_file_path
			FROM	(
					SELECT		DB_NAME() AS database_name
							, fg.data_space_id AS groupid
							, fg.name AS groupname
							, df.file_id
							, df.size / 128.0 AS file_size
							, CONVERT(decimal(12,2), ROUND(FILEPROPERTY(df.name, 'SpaceUsed') / 128.0, 2)) AS space_used
							, CONVERT(decimal(12,2), ROUND((df.size - FILEPROPERTY(df.name,'SpaceUsed')) / 128.0, 2)) AS free_space
							, df.name AS name
							, df.physical_name AS filename
					FROM		sys.database_files AS df
					LEFT JOIN	sys.filegroups AS fg
								ON fg.data_space_id = df.data_space_id
				) AS TB
		) AS TAB_C
FULL JOIN	(
			SELECT	*
				, ROUND(100 - ((CAST(Drive_available_GB AS float) / CAST(Drive_total_GB AS float))*100), 2) AS Drive_DiskUsage_percent
			FROM	(
					SELECT		DISTINCT SUBSTRING(vs.volume_mount_point, 1, 1) AS Drive_B
							, SUBSTRING(f.physical_name, 1, CHARINDEX('\', f.physical_name, (CHARINDEX('\', f.physical_name) + 1))) AS begin_file_path
							, vs.total_bytes / 1024 / 1024 / 1024 AS Drive_total_GB
							, vs.available_bytes / 1024 / 1024 / 1024 AS Drive_available_GB
					FROM		sys.master_files AS f
					CROSS APPLY	sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
				) AS TAB
		) AS TAB_B
			ON TAB_C.Drive = TAB_B.Drive_B COLLATE database_default
			AND TAB_C.begin_file_path = TAB_B.begin_file_path COLLATE database_default

Vous pouvez en plus démarrer SQL Profiler ou une session d'événements étendus pour capturer les caractéristiques des situations de blocage et les deadlocks.

@++ ;)

16/11/2016, 09h13
SQLpro

Citation:

Envoyé par vinch999

Bonjour j'ai un soucis d'interblocage entre 2 sessions que je nommerai A et B.

La session A est lockée par la session B.
Et La session B est lockée par la session A.

Ma session A fait via une procédure stockée: ALTER DATABASE [ma_db] REMOVE FILE ' + @old_F_name;
Ma session B fait en résumé un listing des fichiers DB avec leurs infos, via cette query...

Il faut comprendre que ceci est parfaitement normal. La détection des interblocages, donc un KILL automatique entrainant un ROLLBACK ne peut intervenir que s'il y a journalisation. Le problème est que la suppression d'un fichier n'est, par nature, pas une opération journalisable puisqu'elle agit au niveau du système donc en dehors du scope de SQL Server.
Il en est de même de l'utilisation de la fonction table sys.dm_os_volume_stats qui va récupérer des informations du système.

Il n'y a aucune solution magique à ce problème, si ce n'est qu'un DBA doit faire attention à ce qu'il fait lorsqu'il agit au niveau système (suppression de fichier, lectures des sonnées système) et non plus au niveau data.

Au passage l'utilisation des NOLOCK est stupide, car elle ne garantie pas que la requête ne posera pas de verrous, mais elle effectue des lectures sales...

A +
16/11/2016, 13h00
vinch999

merci pour les réponses je vais regarder à tout ça.
Concernant le REMOVE FILE, il doit se faire de manière automatique, pas à la main par le DBA. Il est utilisé pour gérer la période de rétention des données sur une table partitionnée.

Je n'avais pas mis de NOLOCK à l'origine. J'espérais juste que cela puisse empêcher le blocage :)
J'en profite pour vous poser une question sur les NOLOCK.
J'ai pris l'habitude de les utiliser car ai déjà eu de nombreux problème de lock dû à des utilisateurs .
Par exemple , un user fait un SELECT sur une table.
Pendant ce temps là, un process automatique doit faire une opération( DELETE, un UPDATE,...) sur cette table. Il est locké par ce SELECT. Comment éliminer ce problème sans utiliser un NOLOCK sur le SELECT?
16/11/2016, 13h46
SQLpro
L'utilisation systématique de NOLOCK est une véritable aberration hélas courantes chez les développeur qui sont persuadés que ceci
- permet de lire les données sans poser de verrous. Il n'en est rien. Le NOLOCK provoque des lectures sales qui sont de deux types :
- absence de prise en compte des lignes actuellement verrouillées par les autres sessions (donc moins de lignes !)
- prise en compte plusieurs fois d'une même ligne, lorsqu'une autre session la met à jour pendant le balayage en mode NOLOCK (donc doublons)
Mis à part le cas particulier ou l'on veut des données grossières (par exemple nombre moyen d'enfant par français présenté sur un graphique camembert), des traitements se basant sur le NOLOCK ont toutes les chances de présenter des résultats faux !

Citation:

Envoyé par vinch999

Par exemple , un user fait un SELECT sur une table.
Pendant ce temps là, un process automatique doit faire une opération( DELETE, un UPDATE,...) sur cette table. Il est locké par ce SELECT. Comment éliminer ce problème sans utiliser un NOLOCK sur le SELECT?

Il suffit d'utiliser le niveau d'isolation SNAPSHOT qui autorise le verrouillage optimiste.
- Soit globalement en lieu et place des lectures pessimistes avec ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT
- Soit spécifiquement, transaction par transaction en combinant ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION et en commençant chaque transaction par le niveau d'isolation SNAPSHOT (SET TRANSACTION ISOLATION LEVEL SNAPSHOT).
A +