Un deadlock est un conflit multi-utilisateurs produit et géré par la base de données quand deux utilisateurs concurrents ont entamé une transaction qui nécessite de verrouiller des ressources en cours d'utilisation par ces deux utilisateurs.
- La transaction de l'utilisateur 1 a verrouillé la ressource A et attend que la ressource B se libère pour la verrouiller.
- La transaction de l'utilisateur 2 a verrouillé la ressource B et attend que la ressource A se libère pour la verrouiller.
Les ressources A et B peuvent être deux tables différentes, mais cela peut aussi correspondre à des lignes ou des pages différentes dans la même table. SQL Server dispose d'un système de détection de telles situations et, afin d'éviter d'attendre indéfiniment, il met fin à une des deux transactions. Comme l'ensemble de la transaction est annulée, cela n'a aucun impact sur la cohérence des données. L'utilisateur "victime" doit simplement recommencer la transaction (par exemple, en demandant à nouveau une sauvegarde).
Ce mécanisme est donc sain et normal dans un environnement multi-utilisateurs.
Il se peut toutefois que des patterns de programmation SQL favorisent ce type de situations. Par exemple, si un code met à jour la table A et ensuite la table B, alors qu'à un autre endroit du programme, les mises à jour sont effectuées dans l'ordre inverse. Lorsque ces situations se répètent et nuisent au confort des utilisateurs, il est nécessaire de comprendre la cause du deadlock et, probablement, d'y remédier en modifiant le code SQL.
Ce script SQL permet d'obtenir des informations sur les deadlocks qui ont été rencontrés dans la base de données :
DECLARE @xelfilepath NVARCHAR(260)
SELECT @xelfilepath = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @xelfilepath = @xelfilepath + N'system_health_*.xel'
DROP TABLE IF EXISTS #TempTable
SELECT CONVERT(XML, event_data) AS EventData
INTO #TempTable FROM sys.fn_xe_file_target_read_file(@xelfilepath, NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
SELECT EventData.value('(event/@timestamp)[1]', 'datetime2(7)') AS UtcTime,
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,
EventData.value('(event/@timestamp)[1]', 'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LocalTime,
EventData.query('event/data/value/deadlock') AS XmlDeadlockReport
FROM #TempTable
ORDER BY UtcTime DESC;
Source : How to resolve deadlocks in SQL Server sur SQLShack