¡Buenas a todos de nuevo! Esta semana traemos una casuística que puede levantar dolores de cabeza pero… ¡tranquilos! Podremos trazarla y reaccionar ante ella para resolverla. Se trata de los interbloqueos y los tiempos de espera que produce.
Tiempos de espera elevados por interbloqueos
Analizando las esperas del motor, observamos lo siguiente:
| WaitType | Wait Time (s) | MAX wait time (s) | % waiting |
| LCK_M_U | 6228543.66 | 169490.88 | 34.54 |
| DTC | 2617810.54 | 7213.80 | 14.52 |
| CXPACKET | 2163635.10 | 169490.88 | 12.00 |
| CXCONSUMER | 948849.51 | 169490.89 | 5.26 |
| MSQL_DQ | 874972.64 | 7213.25 | 4.85 |
La espera que más tiempo consume es LCK_M_U.
“Este tipo de espera se produce cuando un subproceso está esperando adquirir un bloqueo de actualización en un recurso y hay al menos otro bloqueo en un modo incompatible otorgado en el recurso a un subproceso diferente.”
Este tipo de interbloqueo, es causado por una consulta INSERT, y una consulta SELECT.
Veamos cómo:
Tenemos la siguiente select:
select internal_taskid from TaskStatus where stoptime is not null and parent = @parent and starttime > @starttime and terminated is null
Y el siguiente update:
update dbo.TaskStatus set internal_taskid = @internal_taskid where taskstatusid = @taskstatusid;
Comparando planes de ejecución
Ambos planes de ejecución, utilizan el mismo índice “TaskStatus_PK” de la tabla TaskStatus. A su vez “IX_TaskStatus_terminated” (clustered index), es utilizado por la select para filtrar y por el update para actualizarlo. El problema, es que si ambas sentencias coinciden en el tiempo (o se lanzan con muy poco tiempo de diferencia), la select utiliza el índice que necesita actualizar el update. A su vez, el update necesita el índice que utiliza la select.
| select | <Lock resource_type=»KEY» index_name=»IX_TaskStatus_terminated» request_mode=»S» request_status=»GRANT» request_count=»1″ /> <Lock resource_type=»KEY» index_name=»TaskStatus_PK» request_mode=»S» request_status=»WAIT» request_count=»1″ /> |
| update | <Lock resource_type=»KEY» index_name=»IX_TaskStatus_terminated» request_mode=»X» request_status=»WAIT» request_count=»1″ /> <Lock resource_type=»KEY» index_name=»TaskStatus_PK» request_mode=»X»request_status=»GRANT« request_count=»1″ /> |
Ambas tienen un grant y una espera de recursos cruzados.
Se produce un interbloqueo de ambos recursos.
Plan de ejecución de la consulta de select.
Plan de ejecución de la consulta de update.
Ambos utilizan el mismo índice, provocando interbloqueos.
¡¡¡¡Aquí vemos claramente el problema!!!!! Tenemos que utilizar 2 índices para buscar el valor. Primero utilizamos el índice IX_TaskStatus_terminated para encontrar el valor y después lo buscamos en el índice clúster PK_TaskStatus para filtrar el resto de columnas. En este punto entramos en conflicto con la consulta update.
Ahora que sabemos el problema… ¿Cuál sería la solución?
Crearemos un índice que incluya los campos de las cláusulas del where de la select así como el campo de la selección:
CREATE UNIQUE INDEX IX_TaskStatus_terminated2 ON dbo.TaskStatus (internal_taskid) INCLUDE (stoptime, terminated, parent, starttime,);
De este modo, la consulta select irá por el índice no clusterizado. Consiguiendo así, que en vez de tener que acceder al índice clúster liberándolo así para los updates.
Si no quieres perderte estos consejos, SUSCRÍBETE A NUESTRA NEWSLETTER.