Dynamics AX : Diagnostiquer les problèmes de verrouillage (locks) - PART 1

Dans le passé, j’ai écrit un billet au sujet des blocs dans votre base de données SQL, aujourd'hui je voulais approfondir le sujet.

Comme nous l'avons vu, il est possible de savoir les sessions SQL bloquées. De plus, nous sommes capables d'associer la session SQL (SPID) avec la session AX lorsque connectioncontext est activé sur le serveur AOS

Aujourd’hui, je veux savoir pourquoi la session est bloquée.

Afin de faire la démonstration, je vais volontairement créer un blocage dans la base de données. Dans ma première requête, je vais mettre à jour le champ ENABLE pour le ID mathieu. Toutefois, je ne vais pas commettre la transaction immédiatement, ceci aura pour effet de verrouiller la ligne pour une durée de 30min.

(SPID 78)

BEGIN TRANSACTION
UPDATE USERINFO SET ENABLE=1 WHERE ID = 'mathieu.'
    WAITFOR DELAY '00:30:00' 
ROLLBACK TRANSACTION

Dans la seconde requête, je vais tenter de faire une modification à la même ligne:

(SPID 80)

UPDATE USERINFO SET ENABLE=0
WHERE ID = 'mathieu.'

En utilisant cette requête, je suis capable de voir que mes deux requêtes sont en cours d’exécution :

Queries Running.sql

SELECT
cast(context_info as varchar(128)) AS context_info,
req.session_id AS sql_spid,
req.command,
sqltext.text,
req.status,
req.cpu_time,
req.reads,
req.writes,
req.logical_reads,
req.last_wait_type,
req.wait_time,
req.total_elapsed_time,
req.open_transaction_count,
d.name AS db_name
FROM sys.databases d, sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE d.database_id = req.database_id 


En utilisant cette requête, je peux voir que le SPID 78 bloque le SPID 80. Mais je ne sais pas pourquoi.

Blocks.sql

USE [master]
GO

SELECT cast(s.context_info as varchar(128)) AS ax_spid,
s.host_name,
w.session_id AS sql_spid,
w.wait_duration_ms,
r.total_elapsed_time,
t.text,
w.wait_type,
w.blocking_session_id AS sql_head_blocker,
s.cpu_time,
s.reads,
s.writes,
s.logical_reads,
r.open_transaction_count,
DB_NAME(s.database_id) AS db_name
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1 
--AND s.program_name like '%Dynamics%'
ORDER BY sql_spid
GO

SELECT cast(s.context_info as varchar(128)) AS ax_spid,
p1.spid AS sql_spid,
p1.hostname,
p1.cmd,
text,
p1.cpu AS cpu_time,
p1.physical_io AS physical_io,
p1.memusage,
p1.open_tran
FROM sysprocesses p1
INNER JOIN sys.dm_exec_connections c
ON p1.spid = c.session_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)
WHERE EXISTS (SELECT p2.spid FROM sysprocesses p2 WHERE p2.blocked > 0 AND p2.blocked = p1.spid)
   AND NOT EXISTS (SELECT p3.spid FROM sysprocesses p3 WHERE p3.blocked > 0 AND p3.spid = p1.spid)
   --AND s.program_name like '%Dynamics%'
GO

Dans ce cas-ci, nous pouvons voir que le SPID 78 est bloqué à cause de la commande WAITFOR. Nous pouvons aussi voir que le SPID 80 attend afin d'acquérir un lock de type Update (LCK_M_U) et celui-ci attend après le SPID 78.


J'aimerais avoir plus d’information à ce sujet et pour ceci nous pouvons vérifier les locks dans la base de données:  

Locks.sql

USE [databasename]
GO

SELECT 
  cast(context_info as varchar(128)) AS context_info,
  dm_tran_locks.request_session_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'OBJECT'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
LEFT JOIN sys.dm_exec_sessions ON sys.dm_tran_locks.request_session_id = sys.dm_exec_sessions.session_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY ObjectName ASC, resource_associated_entity_id

Je peux voir que la session 80 attend pour obtenir un lock de type U sur la ressource (362b1400cd67). La raison est parce que la session 78 a déjà un lock de type X sur la même ressource. Un lock de type X est incompatible avec les autres lock, pour cette raison, la session 80 doit attendre. Vous pouvez lire plus au sujet des compatibilités entre lock ici: Lock Compatibility


Je vois que le type de ressource est KEY. Ceci veut dire que le lock est sur une ligne. Vous pouvez lire plus au sujet des différents types de ressource  ici: sys.dm_tran_locks (Transact-SQL). Afin de connaître la ligne, vous pouvez exécuter cette requête. 

KEY Lock.sql

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, 
%%lockres%% AS LockResource, *
FROM USERINFO
WHERE %%lockres%% ='(362b1400cd67)' 

Et voilà, maintenant je sais que la session 80 attend après la session 78 afin de mettre a jour la ligne suivante :



Previous
« Prev Post