IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Interblocage entre 2 sessions


Sujet :

Développement SQL Server

  1. #1
    Membre régulier
    Profil pro
    Inscrit en
    Mai 2009
    Messages
    212
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Mai 2009
    Messages : 212
    Points : 71
    Points
    71
    Par défaut Interblocage entre 2 sessions
    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 : 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
     
    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

  2. #2
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juillet 2016
    Messages
    2 757
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Juillet 2016
    Messages : 2 757
    Points : 10 695
    Points
    10 695
    Billets dans le blog
    21
    Par défaut
    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.
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  3. #3
    Membre régulier
    Profil pro
    Inscrit en
    Mai 2009
    Messages
    212
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Mai 2009
    Messages : 212
    Points : 71
    Points
    71
    Par défaut
    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.

  4. #4
    Expert éminent sénior

    Avatar de François DORIN
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Juillet 2016
    Messages
    2 757
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Charente Maritime (Poitou Charente)

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Juillet 2016
    Messages : 2 757
    Points : 10 695
    Points
    10 695
    Billets dans le blog
    21
    Par défaut
    Citation Envoyé par vinch999 Voir le message
    (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.
    François DORIN
    Consultant informatique : conception, modélisation, développement (C#/.Net et SQL Server)
    Site internet | Profils Viadéo & LinkedIn
    ---------
    Page de cours : fdorin.developpez.com
    ---------
    N'oubliez pas de consulter la FAQ C# ainsi que les cours et tutoriels

  5. #5
    Membre régulier
    Profil pro
    Inscrit en
    Mai 2009
    Messages
    212
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Mai 2009
    Messages : 212
    Points : 71
    Points
    71
    Par défaut
    Ok, je voulais juste éviter de vous inonder par du code inutile

    La session A :
    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
     
    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 : 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
     
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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';

  6. #6
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Bonjour,

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

    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
     
    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.

    @++

  7. #7
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 741
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 741
    Points : 52 454
    Points
    52 454
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par vinch999 Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  8. #8
    Membre régulier
    Profil pro
    Inscrit en
    Mai 2009
    Messages
    212
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations forums :
    Inscription : Mai 2009
    Messages : 212
    Points : 71
    Points
    71
    Par défaut
    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?

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 741
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 741
    Points : 52 454
    Points
    52 454
    Billets dans le blog
    5
    Par défaut
    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 Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. Effacer une entrée cookie Session
    Par mulbek dans le forum ASP
    Réponses: 1
    Dernier message: 02/08/2007, 06h21
  2. Réponses: 5
    Dernier message: 23/03/2007, 14h22
  3. Réponses: 18
    Dernier message: 10/11/2006, 14h33
  4. Réponses: 5
    Dernier message: 24/11/2005, 12h32
  5. Réponses: 13
    Dernier message: 14/01/2004, 17h35

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo