avril
2012
Un article de zinzineti
Lorsque deux processus (instructions sql) tentent simultanément de modifier (INSERT,UPDATE,DELETE) les mêmes données, le SGBD empêche les traitements simultanés de ces processus afin de protéger les données et l’intégrité de la base. SQL Server crée un verrou pour chaque processus et les gère selon un agorithme FIFO (First In First Out). Dans ce cas SQL Server impose un traitement en série dans instructions, ce qui entraine donc des temps d’attente, donc des blocages. Mais lorsque ce blocage dure longtemps, les applications qui pointent vers la base de données deviennent inutilisables et les utilisateurs ne sont pas contents …
Dans ce billet, nous mettre en évidence quelques situations provoquant des blocages, des interblocages et des pistes pour anticiper/gérer aux mieux ces situations.
Dans les exemples qui vont suivre nous allons utiliser deux sessions via SQL Server Management Studio (ssms)
Pour passer d’une session à une autre j’ai préféré la présentation ci-dessous pour plus de confort visuel.
Une fois les deux fenêtre ouvertes, clique droit sur l’onglet
Choisir l’affichage verticale des sessions
=> Mise en évidence d’un blocage
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T
CREATE TABLE T (id INT,col CHAR(1))
-- insertion des données dans la table
INSERT INTO T (id,col) VALUES (1,'a')
INSERT INTO T (id,col) VALUES (2,'b')
Supposons que deux utilisateurs veulent exécuter presque au même moment, la même transaction.
–> Session 1 (utilisateur 1) : démarrer la transaction suivante
————–
UPDATE T
SET col = 'c'
WHERE id = 1
/*
Résultat : UPDATE effectué dans la session 1
*/
–> Session 2 (utilisateur 2) : démarrer la transaction suivante
————–
UPDATE T
SET col = 'd'
WHERE id = 1
/*
Résultat : UPDATE mis en attente dans la session 2, le processus est bloqué !
*/
–> Comment identifier les processus bloqués sur une instance ?
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
SELECT spid,open_tran,blocked
FROM sys.sysprocesses WHERE blocked <> 0
L’ID des sessions (session_id ou spid) responsables du blocage sont donc identifiés .
–> Comment mettre fin à ce blocage ?
Il suffit de tuer le processus responsable du blocage à l’aide la commande suivante :
Exemple
--------
KILL 54
Cette commande annule toutes les transactions liées à ce processus. Cette opération est tracé dans les fichiers de
d’erreurs SQL Server (ERRLOG) et Event Viewver Application (\WINDOWS\system32\config\AppEvent.Evt)
=> Interblocage des transactions SQL : deadlock – verrou mortel
Le phénomène d’interblocage (deadlock) se présente lorsque plusieurs processus (transactions sql) se bloquent de façon à ce qu’aucun de ces processus ne peut se débloquer quelque soit la durée d’attente.
Ceci apparaît lors de la modification des données(INSERT, UPDATE, DELETE). Dans ce cas le gestionnaire de verrou SQL Server détecte qu’il y a un deadlock et va donc tuer un des processus, annuler la totalité des
transactions sql du processus liquidé et renvoyé le code d’erreur 1205 à la session concernée.
Sur quels critères SQL Server choisit la processus à tuer ? Pour deux transactions ayant la même priorité, les règles de sélection du processus à liquider ne sont pas clairement détaillées par Microsoft .
Rappelons que l’option SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH } permet de spécifier la priorité d’une transaction en cas de deadlock.
Ouvrons une petite parenthèse : Sous ORACLE le code d’erreur des deadlocks est ORA-00060 (détection d’interblocage pendant l’attente d’une ressource) et fermons cette parenthèse
–> Mise en évidence du phénonème de verrou mortel (deadlock)
– # Cas une table : objet non distinct
IF OBJECT_ID('T') IS NOT NULL DROP TABLE T
CREATE TABLE T (id INT,col CHAR(1))
-- insertion des données dans la table
INSERT INTO T (id,col) VALUES (1,'a')
INSERT INTO T (id,col) VALUES (2,'b')
Dans la session 1, démarrer la transaction suivante :
UPDATE T
SET col = 'c'
WHERE id = 2
/*
Résultat : UPDATE effectué dans la session 1
*/
— Dans la session 2, démarrer la transaction suivante :
UPDATE T
SET col = 'e'
WHERE id = 1
/*
Résultat : UPDATE en attente dans la session 2
*/
— Dans la session 1, exécuter la requête suivante :
SET col = 'f'
WHERE id = 2
/*
Résultat : un message d’erreur apparaît dans la session 1.
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
dans la session 2, la mis à jour est réalisée
— Vérification de la modification dans la session 2
select * from T where id = 1
id col
——-
1 e
*/
Comment résoudre ce problème dans ce cas ?
Pour contouner ce deadlock, il suffit de créer sur cette table un index du genre :
CREATE INDEX IX ON T (id) INCLUDE (col)
Et le tour est joué !
– # Cas de deux tables distinctes : objets distincts
IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
CREATE TABLE T1 (id1 INT,col1 CHAR(1))
CREATE TABLE T2 (id2 INT,col2 CHAR(1))
-- insertion des données dans la table
INSERT INTO T1 (id1,col1) VALUES (1,'a')
INSERT INTO T2 (id2,col2) VALUES (2,'b')
Dans la session 1, démarrer la transaction suivante :
UPDATE T1
SET col1 = 'c'
WHERE id1 = 1
/*
Résultat : UPDATE effectué dans la session 1
*/
Dans la session 2, démarrer la transaction suivante :
UPDATE T2
SET col2 = 'd'
WHERE id2 = 2
/*
Résultat : UPDATE effectué dans la session 2
*/
Dans la session 1, exécuter la requête suivante :
SET col2 = 'e'
WHERE id2 = 2
/*
Résultat : UPDATE en attente dans la session 2
*/
Dans la session 2, exécuter la requête suivante :
SET col1 = 'f'
WHERE id1 = 1
/*
Résultat :
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
*/
Dans ce cas, la création des index sur les tables T1 et T2 ne permet pas de solutionner le problème. Une piste consiste à catcher l’erreur 1205 (TRY/CATCH) puis tenter de rejouer la transaction. D’autres solutions existent, mais avant de les présenter examinons d’abord comment identifier les instructions responsables du deadlock sur une instance
=> Comment identifier les instructions responsable du deadlock ?
– # Avec XEvents
Sous SQL Server 2008 et + on peut utiliser XEvents pour collecter les informations relatives aux deadlocks.
La requête ci-dessous renvoie un fichier XML montrant les requêtes responsables des deadlocks. On peut faire des filtres horodatages pour cibler les deadlocks ayant lieu à un moment donnée.
SELECT @xml = CAST(st.target_data as xml)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s on s.address = st.event_session_address
INNER JOIN sys.server_event_sessions es on s.name = es.name
WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
-- and convert(varchar(8),s.create_time,112) = '20120422' -- cibler une date particulière
SELECT @xml.query('/RingBufferTarget/event [@name="xml_deadlock_report"]')
– # Avec la trace 1222
Activer Trace Flag 1222 option -1 :
{1222} : pour collecter toutes les informations relatives aux deadlocks.
{-1} : appliquer la collecte des traces deadlock à toutes les sessions et à tous les utilisateurs
-- voir le status des traces
DBCC TRACESTATUS(1222,-1)
-----------------------------------------------------
1222 ----- 1 ----- 1 ----- 0
L’activation de la trace 1222 option -1 permet de cibler le problème et d’identifier les instructions responsables de l’interblocage. Voici ce qu’on observe dans le fichier ERRORLOG après activation des traces et reproduction du premier scénario (cas d’un même objet) deadlock
————————————————————————————————–
— Fichier de log emplacement : \Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
————————————————————————————————–
2012-04-22 19:43:47.15 spid56 DBCC TRACEON 1222, server process ID (SPID) 56. This is an informational message only; no user action is required.
2012-04-22 19:44:11.92 spid53 Starting up database ‘AdventureWorks2008R2′.
2012-04-22 19:44:36.56 spid19s deadlock-list
2012-04-22 19:44:36.56 spid19s deadlock victim=processd0bc78
2012-04-22 19:44:36.56 spid19s process-list
2012-04-22 19:44:36.56 spid19s process id=processd0bc78 taskpriority=0 logused=208 waitresource=RID: 6:1:15160:0 waittime=1429 ownerId=170698 transactionname=user_transaction lasttranstarted=2012-04-19T19:44:21.183 XDES=0x4338ac08 lockMode=U schedulerid=1 kpid=5488 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-04-19T19:44:35.137 lastbatchcompleted=2012-04-19T19:44:21.190 lastattention=2012-04-19T16:41:08.620 clientapp=Microsoft SQL Server Management Studio – Query hostname=XX-XX hostpid=4064 loginname=xx isolationlevel=read committed (2) xactid=170698 currentdb=6 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2012-04-22 19:44:36.56 spid19s executionStack
2012-04-22 19:44:36.56 spid19s frame procname=adhoc line=1 stmtstart=58 sqlhandle=0x020000003737ba3a21719d4dd9cf31b6b7211a6fb6e2e5d1
2012-04-22 19:44:36.56 spid19s UPDATE [T] set [col] = @1 WHERE [id]=@2
2012-04-22 19:44:36.56 spid19s frame procname=adhoc line=1 sqlhandle=0x02000000fcf4a3305a4444f97c3ec147da8f12aa2b69fcd2
2012-04-22 19:44:36.56 spid19s UPDATE T
2012-04-22 19:44:36.56 spid19s SET col = ‘f’
2012-04-22 19:44:36.56 spid19s WHERE id = 2
2012-04-22 19:44:36.56 spid19s inputbuf
………………………………………..
………………………………………..
………………………………………..
Dans le fichier de trace ERRLOG on identifie clairement :
* les requêtes responsables du deadlock,
* les objets concernés (nom du serveur,nom de la base,nom des tables,…)
– # Autres méthodes d’identification des deadlocks :
Mise en place des traces via SQL Server Profiler ou ajout de l’option ‘-T1222′ lors du redémarrage de l’instance SQL SERVER
=> Quelques pistes pour anticiper/gérer les deadlocks
–# Ecrire des requêtes courtes et précises sur des tables bien indexées.
–# Respecter le même ordre d’exécution des requêtes dans les transactions (voir exemple plus bas)
–# Catcher (TRY/CATCH) l’erreur 1205 puis ré-exécuter la transaction (voir exemple plus bas)
–# Utiliser si possible l’option SET DEADLOCK_PRIORITY pour générer les priorités entre les transactions en cas de deadlock
–> Exemples : Gestion des erreurs 1205 (TRY/CATCH)
– Exemple 1 : TRY/CATCH simple
———————
/* Mettre ici les opérations à effectuer */
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205 RAISERROR('deadlock erreur !', 16, 1);
ELSE RAISERROR('Pas de deadlock mais une autre erreur est survenue ! ', 16, 1);
END CATCH;
– Exemple 2 : TRY/CATCH avec Re-exécution de la requête
———————
SET @RetriesCounter = 3;
WHILE @RetriesCounter > 0
BEGIN
BEGIN TRY
/* Mettre ici les opérations à effectuer */
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205
BEGIN
SET @RetriesCounter = @RetriesCounter - 1;
IF @RetriesCounter = 0 RAISERROR('Impossible de terminer la transaction !', 16, 1);
END
ELSE
RAISERROR('Pas de deadlock mais autre erreur ! ', 16, 1);
END CATCH
END;
–> Exemple : Respecter le même ordre d’exécution des transactions
IF OBJECT_ID('T2') IS NOT NULL DROP TABLE T2
CREATE TABLE T1 (id1 INT,col1 CHAR(1))
CREATE TABLE T2 (id2 INT,col2 CHAR(1))
-- insertion des données dans la table
INSERT INTO T1 (id1,col1) VALUES (1,'a')
INSERT INTO T2 (id2,col2) VALUES (2,'b')
exécuter dans la session 1
UPDATE t1 SET col1 = 'x'
WAITFOR DELAY '00:00:05'
UPDATE t2 SET col2 = 'y'
ROLLBACK TRAN
exécuter dans la session 2
UPDATE t2 SET col2 = 'y'
WAITFOR DELAY '00:00:05'
UPDATE t1 SET col1 = 'x'
ROLLBACK TRAN
/* Résultat :
(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 6
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
*/
–> Solution : Exécution des requêtes dans le même ordre
exécuter dans la session 1
UPDATE t1 SET col1 = 'x'
WAITFOR DELAY '00:00:05'
UPDATE t2 SET col2 = 'y'
ROLLBACK TRAN
exécuter dans la session 2
UPDATE t1 SET col1 = 'x'
WAITFOR DELAY '00:00:05'
UPDATE t2 SET col2 = 'y'
ROLLBACK TRAN
/* Résultat : Opération effectuée avec succès */
Il y a encore des choses à dire sur les deadlocks. Mais l’idée c’est d’avoir ici une petite synthèse
du phénomène et d’essayer de présenter quelques pistes pour gérer au mieux les blocages et interblocages
——————————————-
Etienne ZINZINDOHOUE
——————————————-
bonjour , je voudrais savoir s’il vous plait comment on peut résoudre ce problème sur sql 2000 (serveur MSDE)