Descripción y resolución de problemas de bloqueo en SQL Server

Se aplica a: SQL Server (todas las versiones compatibles), Azure SQL Instancia administrada

Número de KB original: 224453

Objetivo

En el artículo se describe el bloqueo en SQL Server y se muestra cómo solucionar problemas y resolver el bloqueo.

En este artículo, el término "conexión" hace referencia a una única sesión iniciada en la base de datos. Cada conexión se muestra como un id. de sesión (SPID) o session_id en muchas DMV. Cada uno de estos SPID se conoce a menudo como un proceso, aunque no es un contexto de proceso independiente en el sentido habitual. En su lugar, cada SPID consta de los recursos de servidor y las estructuras de datos necesarias para atender las solicitudes de una única conexión de un cliente determinado. Una sola aplicación cliente puede tener una o varias conexiones. Desde la perspectiva de SQL Server, no hay ninguna diferencia entre varias conexiones desde una sola aplicación cliente en un único equipo cliente y varias conexiones desde varias aplicaciones cliente o varios equipos cliente; son atómicos. Una conexión puede bloquear otra conexión, independientemente del cliente de origen.

Qué es el bloqueo

El bloqueo es una característica inevitable e inherente de cualquier sistema de administración de bases de datos relacionales (RDBMS) con simultaneidad basada en bloqueo. Como se mencionó anteriormente, en SQL Server el bloqueo se produce cuando una sesión mantiene bloqueado un recurso específico y un segundo SPID intenta adquirir un tipo de bloqueo en conflicto en el mismo recurso. Normalmente, el período de tiempo durante el que el primer SPID bloquea el recurso es pequeño. Cuando la sesión propietaria libera el bloqueo, la segunda conexión puede bloquear el recurso y continuar el procesamiento. El bloqueo tal como se describe aquí es un comportamiento normal y puede ocurrir muchas veces durante el transcurso de un día sin ningún efecto notable en el rendimiento del sistema.

La duración y el contexto de transacción de una consulta determinan el tiempo que durarán sus bloqueos y, por lo tanto, el impacto en otras consultas. Si la consulta no se ejecuta dentro de una transacción (y no se usan sugerencias de bloqueo), los bloqueos de las instrucciones SELECT solo se conservarán en un recurso en el momento en que realmente se está leyendo, no durante la consulta. En el caso de las instrucciones INSERT, UPDATE y DELETE, los bloqueos se mantienen durante la consulta, tanto para la coherencia de los datos como para permitir que la consulta se revierta si es necesario.

En el caso de las consultas ejecutadas dentro de una transacción, la duración de los bloqueos se determina según el tipo de consulta, el nivel de aislamiento de la transacción y en función de si se usan sugerencias de bloqueo en la consulta. Para obtener una descripción de los bloqueos, las sugerencias de bloqueo y los niveles de aislamiento de transacción, consulte los siguientes artículos:

• Bloqueo del motor de base de datos
• Personalizar las versiones de fila y bloqueo
• Modos de bloqueo
• Compatibilidad de bloqueos
• Niveles de aislamiento basado en versiones de fila del motor de base de datos
• Transacciones

Cuando los bloqueos aumentan hasta el extremo de que hay un efecto perjudicial en el rendimiento del sistema, se debe a uno de los siguientes motivos:

• Un SPID contiene bloqueos en un conjunto de recursos durante un período de tiempo prolongado antes de liberarlos. Este tipo de bloqueo se resuelve con el tiempo, aunque puede provocar una degradación del rendimiento.

• Un SPID contiene bloqueos en un conjunto de recursos y nunca los libera. Este tipo de bloqueo no se resuelve automáticamente e impide indefinidamente el acceso a los recursos afectados.

En el primer escenario, la situación puede resultar muy fluida, ya que los diferentes SPID generan el bloqueo de diferentes recursos a lo largo del tiempo, lo que crea un destino móvil. Estas situaciones son difíciles de solucionar con SQL Server Management Studio para delimitar el problema a las consultas individuales. En cambio, la segunda situación da lugar a un estado coherente que puede ser más fácil de diagnosticar.

Aplicaciones y bloqueo

Puede haber una tendencia a centrarse en los problemas de la plataforma y la optimización del servidor al enfrentarse a un problema de bloqueo. No obstante, puede no encontrar la solución si solo se presta atención a la base de datos y esta puede acaparar el tiempo y la energía que sería mejor enfocar en examinar la aplicación cliente y las consultas que envía. Independientemente del nivel de visibilidad que ofrece la aplicación en relación con las llamadas realizadas a la base de datos, un problema de bloqueo con frecuencia requiere de la inspección de las instrucciones SQL exactas que envía aplicación, así como del comportamiento exacto de la aplicación con respecto a la cancelación de consultas, la administración de conexiones, la captura de todas las filas de resultados, etc. Si la herramienta de desarrollo no permite el control explícito sobre la administración de conexiones, la cancelación de consultas, el tiempo de espera de la consulta, la captura de resultados, etc., es posible que los problemas de bloqueo no se puedan resolver. Este potencial debe examinarse detenidamente antes de seleccionar una herramienta de desarrollo de aplicaciones para SQL Server, especialmente para entornos OLTP sensibles al rendimiento.

Preste atención al rendimiento de la base de datos durante la fase de diseño y construcción de la base de datos y la aplicación. En concreto, se debe evaluar el consumo de recursos, el nivel de aislamiento y la longitud de la ruta de acceso de la transacción para cada consulta. Cada consulta y transacción deben ser lo más ligeras posible. Se debe ejercer una buena disciplina de administración de conexiones, ya que sin ella, es posible que la aplicación parezca tener un rendimiento aceptable con un número reducido de usuarios, pero el rendimiento podría degradarse significativamente a medida que el número de usuarios escale.

Con el diseño adecuado de aplicaciones y consultas, SQL Server es capaz de admitir muchos miles de usuarios simultáneos en un solo servidor, con poco bloqueo.

Solución de problemas de bloqueo

Independientemente de la situación de bloqueo en la que se encuentre, la metodología para solucionar los problemas de bloqueo es la misma. Estas separaciones lógicas son lo que determinará el resto de la estructura de este artículo. El concepto consiste en encontrar el bloqueador de encabezado e identificar lo que está haciendo la consulta y por qué está bloqueada. Una vez identificada la consulta problemática (es decir, lo que contiene bloqueos durante el período prolongado), el siguiente paso es analizar y determinar por qué se produce el bloqueo. Después de entender por qué, podemos realizar cambios rediseñando la consulta y la transacción.

Pasos de solución de problemas:

1. Identificar la sesión de bloqueo principal (bloqueador de encabezado)

2. Buscar la consulta y la transacción que están causando el bloqueo (lo que mantiene los bloqueos durante un período prolongado)

3. Analizar/comprender por qué se produce el bloqueo prolongado

4. Resolver el problema de bloqueo al rediseñar la consulta y la transacción

Ahora expliquemos detalladamente cómo localizar la sesión de bloqueo principal con una captura de datos adecuada.

Recopilación de información de bloqueo

Para contrarrestar la dificultad de solucionar problemas de bloqueo, un administrador de bases de datos puede usar scripts SQL que supervisen constantemente el estado de bloqueo y bloqueo en SQL Server. Para recopilar estos datos, hay dos métodos gratuitos.

El primero consiste en consultar objetos de administración dinámica (DMO) y almacenar los resultados para las comparaciones a lo largo del tiempo. Algunos de los objetos a los que se hace referencia en este artículo son vistas de administración dinámica (DMV) y otros son funciones de administración dinámica (DMF).

El segundo es usar eventos extendidos (XEvents) o seguimientos de SQL Profiler para capturar lo que se está ejecutando. Dado que SQL Trace y SQL Server Profiler están en desuso, esta guía de solución de problemas se centrará en XEvents.

Recopilación de información a partir de DMV

Se hace referencia a DMV para solucionar los problemas de bloqueo con el objetivo de identificar el SPID (id. de sesión) en el encabezado de la cadena de bloqueo y la instrucción SQL. Busque los SPID objetivo que se van a bloquear. Si algún SPID está bloqueando por otro SPID, investigue el SPID que es propietario del recurso (el SPID que realiza el bloqueo). ¿El SPID propietario también está bloqueado? Puede recorrer la cadena para buscar el bloqueador de encabezado y, a continuación, investigar por qué está manteniendo el bloqueo.

Para ello, puede usar uno de los métodos siguientes:

• En SQL Server Management Studio (SSMS) Explorador de objetos, haga clic con el botón derecho en el objeto de servidor de nivel superior, expanda Informes, expanda Informes estándar y, a continuación, seleccione Actividad: todas las transacciones de bloqueo. En este informe se muestran las transacciones actuales al principio de una cadena de bloqueo. Si expande la transacción, el informe mostrará las transacciones bloqueadas por la transacción principal. Este informe también mostrará la instrucción SQL de bloqueo y la instrucción SQL bloqueada.

• Abra el Monitor de actividad en SSMS y consulte la columna Bloqueado por. Obtenga más información sobre el Monitor de actividad aquí.

Los métodos basados en consultas más detallados también están disponibles mediante DMV:

• Los sp_who comandos y sp_who2 son comandos anteriores para mostrar todas las sesiones actuales. La DMV sys.dm_exec_sessions devuelve más datos en un conjunto de resultados que resultan más fáciles de consultar y filtrar. Encontrará sys.dm_exec_sessions en el centro de otras consultas.

• Si ya tiene una sesión específica identificada, puede usar DBCC INPUTBUFFER(<session_id>) para buscar la última instrucción enviada por una sesión. Se pueden devolver resultados similares con la función de administración dinámica (DMF) sys.dm_exec_input_buffer en un conjunto de resultados que son más fáciles de consultar y filtrar, si se proporcionan los elementos session_id y request_id. Por ejemplo, para devolver la consulta más reciente enviada por ession_id 66 y request_id 0, haga lo siguiente:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

• Consulte y sys.dm_exec_requests haga referencia a la blocking_session_id columna . Cuando blocking_session_id = 0, una sesión no está bloqueada. Mientras sys.dm_exec_requests solo muestra las solicitudes que se están ejecutando actualmente, todas las conexiones (activas o no) se mostrarán en sys.dm_exec_sessions. Use como base esta combinación común entre sys.dm_exec_requests y sys.dm_exec_sessions en la consulta siguiente. Tenga en cuenta que la consulta debe ejecutarse sys.dm_exec_requestsactivamente con SQL Server.

• Ejecute esta consulta de ejemplo para buscar las consultas que están ejecutándose activamente y su texto de proceso por lotes de SQL o texto de búfer de entrada actual mediante las DMV sys.dm_exec_sql_text o sys.dm_exec_input_buffer. Si los datos devueltos por la text columna de sys.dm_exec_sql_text son NULL, la consulta no se está ejecutando actualmente. En ese caso, la event_info columna de contendrá la última cadena de sys.dm_exec_input_buffer comandos que se pasa al motor de SQL. Esta consulta también se puede usar para identificar las sesiones que bloquean otras sesiones, incluida una lista de identificadores de sesión bloqueados por identificador de sesión.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

• Ejecute esta consulta de ejemplo más elaborada, que proporciona el servicio de soporte técnico de Microsoft, para identificar el encabezado de una cadena de bloqueo de varias sesiones, como el texto de consulta de las sesiones que intervienen en una cadena de bloqueo.

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

• Para detectar transacciones de larga duración o no confirmadas, use otro conjunto de DMV para ver las transacciones abiertas actuales, incluidos sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections y sys.dm_exec_sql_text. Hay varias DMV asociadas a las transacciones de seguimiento, consulte más DMV sobre transacciones aquí.

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

• Referencia sys.dm_os_waiting_tasks que se encuentra en la capa de subprocesos o tareas de SQL Server. Esto devuelve información sobre lo que SQL wait_type la solicitud está experimentando actualmente. Al igual que sys.dm_exec_requests, sys.dm_os_waiting_tasks devuelve solo las solicitudes activas.

• Use la DMV sys.dm_tran_locks para obtener información más detallada sobre los bloqueos que han realizado las consultas. Esta DMV puede devolver grandes cantidades de datos en una instancia de SQL Server de producción y es útil para diagnosticar qué bloqueos se mantienen actualmente.

Debido a la instrucción INNER JOIN de sys.dm_os_waiting_tasks, la siguiente consulta restringe la salida de sys.dm_tran_locks solo a las solicitudes bloqueadas actualmente, su estado de espera y sus bloqueos:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

Con las DMV, el almacenamiento de los resultados de la consulta con el tiempo proporcionará puntos de datos que le permitirán revisar el bloqueo durante un intervalo de tiempo especificado para identificar las tendencias o bloqueos persistentes. La herramienta de uso de CSS para solucionar estos problemas es usar el recopilador de datos PSSDiag. Esta herramienta usa las "Estadísticas de rendimiento de SQL Server" para recopilar conjuntos de resultados de DMV a los que se hace referencia anteriormente, con el tiempo. A medida que esta herramienta evoluciona constantemente, revise la versión pública más reciente de DiagManager en GitHub.

Recopilación de información de eventos extendidos

Además de la información anterior, a menudo es necesario capturar un seguimiento de las actividades en el servidor para investigar exhaustivamente un problema de bloqueo en SQL Server. Por ejemplo, si una sesión ejecuta varias instrucciones dentro de una transacción, solo se representará la última instrucción que se haya enviado. Sin embargo, una de las instrucciones anteriores puede ser el motivo por el que los bloqueos continúan. Un seguimiento le permitirá ver todos los comandos ejecutados por una sesión durante la transacción actual.

Hay dos maneras de capturar seguimientos en SQL Server; Eventos extendidos (XEvents) y seguimientos del generador de perfiles. Sin embargo, los seguimientos de SQL que usan SQL Server Profiler están en desuso. XEvents es la plataforma de seguimiento más reciente y superior que permite una mayor versatilidad y menos impacto en el sistema observado, y su interfaz se integra en SSMS.

Hay sesiones de eventos extendidos preparadas previamente para iniciarse en SSMS, que aparecen en Explorador de objetos en el menú de XEvent Profiler. Para obtener más información, vea XEvent Profiler. También puede crear sus propias sesiones de eventos extendidos personalizados en SSMS, consulte Asistente para nueva sesión de eventos extendidos. Para solucionar problemas de bloqueo, normalmente capturaremos:

• Errores de categoría:
  • Atención
  • Blocked_process_report**
  • Error_reported (administrador del canal)
  • Exchange_spill
  • Execution_warning

**Para configurar el umbral y la frecuencia con la que se generan informes de procesos bloqueados, use el comando sp_configure para configurar la opción de umbral de proceso bloqueado, que se puede establecer en segundos. De manera predeterminada, se producen informes de procesos no bloqueados.

• Advertencias de categoría:

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Ejecución de categoría:

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Bloqueo de categoría

  • Lock_deadlock

• Sesión de categoría

  • Existing_connection
  • Iniciar sesión
  • Logout

Identificación y resolución de escenarios de bloqueo comunes

Al examinar la información anterior, puede determinar la causa de la mayoría de los problemas de bloqueo. En el resto de este artículo se explica cómo usar esta información para identificar y resolver algunos escenarios de bloqueo comunes. En esta explicación se supone que ha usado los scripts de bloqueo (a los que se hace referencia anteriormente) para capturar información sobre los SPID de bloqueo y que ha capturado la actividad de la aplicación mediante una sesión XEvent.

Análisis de datos de bloqueo

• Examine la salida de las DMV sys.dm_exec_requests y sys.dm_exec_sessions para determinar los encabezados de las cadenas de bloqueo, mediante blocking_these y session_id. Esto identificará con mayor claridad qué solicitudes están bloqueadas y cuáles están realizando el bloqueo. Examine más exhaustivamente las sesiones que están bloqueadas y que realizan el bloqueo. ¿Hay una raíz o elemento común para la cadena de bloqueo? Probablemente comparten una tabla común y una o varias de las sesiones implicadas en una cadena de bloqueo están realizando una operación de escritura.

• Examine la salida de las DMV sys.dm_exec_requests y sys.dm_exec_sessions para obtener información sobre los SPID en el encabezado de la cadena de bloqueo. Busque las columnas siguientes:

  • sys.dm_exec_requests.status

    Esta columna muestra el estado de una solicitud determinada. Normalmente, un estado "En espera" indica que el SPID ha finalizado la ejecución y está esperando a que la aplicación envíe otra consulta o lote. Un estado "Ejecutable" o "En ejecución" indica que el SPID está procesando una consulta actualmente. En la tabla siguiente se proporcionan explicaciones breves sobre los distintos valores de estado.

    Status	Significado
    Información previa	El SPID está ejecutando una tarea en segundo plano, como una detección de interbloqueos, un escritor de registros o un punto de control.
    En espera	El SPID no se está ejecutando actualmente. Normalmente, esto indica que el SPID está esperando un comando de la aplicación.
    En ejecución	El SPID está ejecutándose actualmente en un programador.
    Ejecutable	El SPID se encuentra en la cola de ejecutables de un programador y está en espera para obtener una hora del programador.
    Suspended	El SPID está esperando un recurso, como un bloqueo o un bloqueo temporal.

  • sys.dm_exec_sessions.open_transaction_count

    Esta columna indica el número de transacciones abiertas en esta sesión. Si este valor es mayor que 0, el SPID está dentro de una transacción abierta y puede estar manteniendo los bloqueos adquiridos por cualquier instrucción de la transacción. La transacción abierta podría haber sido creada por una instrucción activa actualmente o por una solicitud de instrucción que se ha ejecutado en el pasado y que ya no está activa.

  • sys.dm_exec_requests.open_transaction_count

    Del mismo modo, esta columna indica el número de transacciones abiertas en esta solicitud. Si este valor es mayor que 0, el SPID se encuentra dentro de una transacción abierta y puede contener bloqueos adquiridos por cualquier instrucción activa dentro de la transacción. A diferencia sys.dm_exec_sessions.open_transaction_countde , si no hay una solicitud activa, esta columna mostrará 0.

  • sys.dm_exec_requests.wait_type, wait_time y last_wait_type

    Si sys.dm_exec_requests.wait_type es NULL, la solicitud no está esperando nada, y el valor de last_wait_type indica el último elemento wait_type que la solicitud ha encontrado. Para obtener más información sobre sys.dm_os_wait_stats y una descripción de los tipos de espera más comunes, consulte sys.dm_os_wait_stats. Se puede usar el valor de wait_time para determinar si la solicitud está avanzando. Cuando una consulta con relación a la tabla sys.dm_exec_requests devuelve un valor en la columna wait_time que es inferior al valor wait_time de una consulta anterior de sys.dm_exec_requests, indica que el bloqueo anterior se ha adquirido y liberado y está ahora en espera de un nuevo bloqueo (suponiendo que wait_time sea distinto de cero). Esta situación se puede comprobar comparando el elemento wait_resource entre la salida de sys.dm_exec_requests, que muestra el recurso al que espera la solicitud.

  • sys.dm_exec_requests.wait_resource

    Esta columna indica el recurso en el que una solicitud bloqueada está esperando. En la siguiente tabla se enumeran los formatos comunes de wait_resource y su significado:

    Resource	Formato	Ejemplo	Explicación
    Tabla	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	En este caso, el identificador de base de datos 5 es la base de datos de ejemplo pubs y object_id 261575970 es la tabla titles y 1 es el índice agrupado.
    Página	DatabaseID:FileID:PageID	PAGE: 5:1:104	En este caso, el id. de base de datos 5 es pubs, el id. de archivo 1 es el archivo de datos principal y la página 104 es una página que pertenece a la tabla titles. Para identificar el elemento object_id al que pertenece la página, use la función de administración dinámica sys.dm_db_page_info y pase los valores de DatabaseID, FileId, PageId de wait_resource.
    Clave	DatabaseID:Hobt_id (valor hash de la clave de índice)	KEY: 5:72057594044284928 (3300a4f361aa)	En este caso, el id. de base de datos 5 es Pubs y Hobt_ID 72057594044284928 le corresponde a index_id 2 para object_id 261575970 (tabla titles). Use la vista de sys.partitions catálogo para asociar a hobt_id un determinado index_id y object_id. No hay ninguna manera de deshacer el hash de la clave de índice correspondiente a un valor de clave concreto.
    Row	DatabaseID:FileID:PageID:Slot(fila)	RID: 5:1:104:3	En este caso, el id. de base de datos 5 es pubs, el id. de archivo 1 es el archivo de datos principal, la página 104 es una página que pertenece a la tabla titles y la ranura 3 indica la posición de la fila dentro de la página.
    Compile	DatabaseID:FileID:PageID:Slot(fila)	RID: 5:1:104:3	En este caso, el id. de base de datos 5 es pubs, el id. de archivo 1 es el archivo de datos principal, la página 104 es una página que pertenece a la tabla titles y la ranura 3 indica la posición de la fila dentro de la página.

  • sys.dm_tran_active_transactions La DMV sys.dm_tran_active_transactions contiene datos sobre transacciones abiertas que se pueden combinar con otras DMV para obtener un panorama completo de las transacciones que esperan una confirmación o reversión. Use la siguiente consulta para devolver información sobre transacciones abiertas combinadas con otras DMV, como sys.dm_tran_session_transactions. Considere el estado actual de una transacción, el elemento transaction_begin_time y otros datos de la situación para evaluar si puede ser el origen de un bloqueo.

    SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                    WHEN 2 THEN 'Read-only transaction'
                                                    WHEN 3 THEN 'System transaction'
                                                    WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
                WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                WHEN 1 THEN 'The transaction has been initialized but has not started.'
                WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
                WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                WHEN 6 THEN 'The transaction has been committed.'
                WHEN 7 THEN 'The transaction is being rolled back.'
                WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
    FROM sys.dm_tran_active_transactions tat 
    INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
    INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
    

  • Otras columnas

    Las demás columnas de sys.dm_exec_sessions y sys.dm_exec_request también pueden ofrecer información sobre la causa de un problema. Su utilidad varía en función de las circunstancias del problema. Por ejemplo, puede determinar si el problema solo se produce desde determinados clientes (hostname), en determinadas bibliotecas de red (client_interface_name), cuando el último lote enviado por un SPID estaba last_request_start_time en sys.dm_exec_sessions, cuánto tiempo se había ejecutado una solicitud con start_time en sys.dm_exec_requests, etc.

Escenarios de bloqueo comunes

En la tabla siguiente se asignan los síntomas comunes con sus posibles causas.

Las columnas wait_type, open_transaction_count y status hacen referencia a la información que devuelve sys.dm_exec_request, mientras que sys.dm_exec_sessionspuede devolver otras columnas. La columna "¿Se resuelve?" indica si el bloqueo se resolverá por sí mismo o si se debe terminar la sesión mediante el comando KILL. Para más información, consulte KILL (Transact-SQL).

Escenarios de bloqueo detallados

Escenario 1: Bloqueo causado por una consulta que se ejecuta normalmente con un tiempo de ejecución largo

En este escenario, una consulta que ejecuta activamente ha adquirido bloqueos y los bloqueos no se liberan (se ve afectado por el nivel de aislamiento de transacción). Por lo tanto, otras sesiones esperarán en los bloqueos hasta que se liberen.

Resolución:

La solución a este problema de bloqueo es buscar formas de optimizar la consulta. Esta clase de problema de bloqueo puede ser un problema de rendimiento y requiere que lo trate como tal. Para obtener información sobre cómo solucionar problemas de una consulta específica que se ejecuta con lentitud, consulte Solución de problemas en consultas que se ejecutan con lentitud en SQL Server. Para obtener más información, vea Supervisión y optimización del rendimiento.

Los informes integrados a SSMS de la Almacén de consultas (introducidos en SQL Server 2016) también son una herramienta muy recomendada y valiosa para identificar las consultas más costosas y los planes de ejecución poco óptimos.

Si tiene una consulta de ejecución prolongada que bloquea a otros usuarios y no se puede optimizar, considere la posibilidad de moverla de un entorno OLTP a un sistema de informes dedicado. También puede usar grupos de disponibilidad AlwaysOn para sincronizar una réplica de solo lectura de la base de datos.

Escenario 2: Bloqueo causado por un SPID en suspensión que tiene una transacción no confirmada

Este tipo de bloqueo a menudo se puede identificar mediante un SPID que está durmiendo o esperando un comando con un nivel de anidamiento de transacciones (@@TRANCOUNT, open_transaction_count de sys.dm_exec_requests) mayor que cero. Esta situación puede producirse si la aplicación experimenta un tiempo de espera de consulta o emite una cancelación sin emitir el número necesario de instrucciones ROLLBACK o COMMIT. Cuando un SPID recibe un tiempo de espera de consulta o una cancelación, finalizará la consulta actual y el lote, pero no revertirá ni confirmará automáticamente la transacción. La aplicación es responsable de esto, ya que SQL Server no puede suponer que se debe revertir toda una transacción debido a que se cancela una sola consulta. El tiempo de espera o la cancelación de la consulta aparecerán como un evento de señal ATTENTION para el SPID en la sesión de eventos extendidos.

Para mostrar una transacción explícita pendiente de confirmación, emita la siguiente consulta:

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

A continuación, ejecute esta consulta en la misma ventana:

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

La salida de la segunda consulta indica que el recuento de transacciones es uno. Todos los bloqueos adquiridos en la transacción se mantienen hasta que la transacción se confirmó o reviertó. Si las aplicaciones abren y confirman transacciones explícitamente, un error de comunicación u otro tipo podría dejar la sesión y su transacción en estado abierto.

Use el script descrito anteriormente en este artículo basado en sys.dm_tran_active_transactions para identificar las transacciones actualmente no confirmadas en la instancia.

Soluciones:

• Esta clase de problema de bloqueo también puede ser un problema de rendimiento y requiere que lo trate como tal. Si se puede reducir el tiempo de ejecución de la consulta, es posible que no se produzca el tiempo de espera de la consulta o la cancelación. Es importante que la aplicación pueda controlar los escenarios de tiempo de espera o cancelación en caso de que surjan, pero también puede beneficiarse del examen del rendimiento de la consulta.

• Las aplicaciones deben administrar correctamente los niveles de anidamiento de las transacciones o podrían provocar un problema de bloqueo después de la cancelación de la consulta con este método. Tenga en cuenta lo siguiente.

  • En el controlador de errores de la aplicación cliente, ejecute IF @@TRANCOUNT > 0 ROLLBACK TRAN después de cualquier error, incluso si la aplicación cliente no considera que una transacción esté abierta. Es necesario comprobar las transacciones abiertas porque un procedimiento almacenado llamado durante el lote podría haber iniciado una transacción sin el conocimiento de la aplicación cliente. Ciertas condiciones, como cancelar la consulta, impiden que el procedimiento se ejecute después de la instrucción actual, de modo que incluso si el procedimiento tiene lógica para comprobar si IF @@ERROR <> 0 y anular la transacción, este código de reversión no se ejecutará en estos casos.

  • Si la agrupación de conexiones se usa en una aplicación que abre la conexión y ejecuta algunas consultas antes de volver a liberar la conexión al grupo, como una aplicación basada en web, deshabilitar temporalmente la agrupación de conexiones puede ayudar a aliviar el problema hasta que la aplicación cliente se modifique para controlar los errores correctamente. Al deshabilitar la agrupación de conexiones, liberar la conexión provocará una desconexión física de la conexión de SQL Server, lo que provocará que el servidor revierta las transacciones abiertas.

  • Se usa SET XACT_ABORT ON para la conexión o en los procedimientos almacenados que inician transacciones y no se limpian después de un error. En caso de que se produzca un error en tiempo de ejecución, este valor anulará cualquier transacción abierta y devolverá el control al cliente. Para obtener más información, consulte SET XACT_ABORT (Transact-SQL).

Escenario 3: Bloqueo causado por un SPID cuya aplicación cliente correspondiente no capturó todas las filas de resultados hasta su finalización

Después de enviar una consulta al servidor, todas las aplicaciones deben recuperar inmediatamente todas las filas de resultados hasta su finalización. Si una aplicación no recupera todas las filas de resultados, pueden quedar bloqueos en las tablas que impidan el acceso a otros usuarios. Si usa una aplicación que envía de manera transparente instrucciones SQL al servidor, la aplicación debe capturar todas las filas de resultados. Si no lo hace (y si no se puede configurar para ello), es posible que no pueda resolver el problema de bloqueo. Para evitar este problema, puede restringir las aplicaciones con un comportamiento deficiente a una base de datos de informes o de ayuda para la toma de decisiones, independiente de la base de datos OLTP.

Resolución:

se debe volver a escribir la aplicación para que recupere todas las filas de resultados hasta su finalización. Esto no descarta el uso de OFFSET y FETCH en la cláusula ORDER BY de una consulta para realizar la paginación del servidor.

Escenario 4: Bloqueo causado por un interbloqueo de cliente o servidor distribuido

A diferencia de un interbloqueo convencional, no se puede detectar un interbloqueo distribuido mediante el administrador de bloqueos RDBMS. Esto se debe a que solo uno de los recursos implicados en el interbloqueo es un bloqueo de SQL Server. El otro lado del interbloqueo está en el nivel de aplicación cliente, sobre el que SQL Server no tiene control. En las dos secciones siguientes se muestran ejemplos de cómo puede ocurrir esto y las posibles formas en que la aplicación puede evitarlo.

Ejemplo A: Interbloqueo distribuido de cliente/servidor con un único subproceso de cliente

Si el cliente tiene varias conexiones abiertas y un único subproceso de ejecución, puede producirse el siguiente interbloqueo distribuido. Tenga en cuenta que el término dbproc que se usa aquí hace referencia a la estructura de conexión de cliente.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

En el caso anterior, un único subproceso de aplicación cliente tiene dos conexiones abiertas. Envía de forma asincrónica una operación SQL en dbproc1. Esto significa que no espera a que se devuelva la llamada antes de continuar. A continuación, la aplicación envía otra operación SQL en dbproc2 y espera los resultados para empezar a procesar los datos devueltos. Cuando los datos comienzan a volver (siempre que dbproc responda primero: suponga que se trata de dbproc1), procesa para completar todos los datos devueltos en ese dbproc. Captura los resultados de dbproc1 hasta que SPID1 se bloquea en un bloqueo mantenido por SPID2 (porque las dos consultas se ejecutan de forma asincrónica en el servidor). En este momento, dbproc1 esperará indefinidamente para obtener más datos. SPID2 no está bloqueado en un bloqueo, pero intenta enviar datos a su cliente, dbproc2. Sin embargo, dbproc2 se bloquea eficazmente en dbproc1 en la capa de aplicación, ya que el único subproceso de ejecución de la aplicación está en uso por dbproc1. Esto da como resultado un interbloqueo que SQL Server no puede detectar o resolver porque solo uno de los recursos implicados es un recurso de SQL Server.

Ejemplo B: Interbloqueo distribuido de cliente/servidor con un subproceso por conexión

Incluso si existe un subproceso independiente para cada conexión en el cliente, puede producirse una variación de este interbloqueo distribuido, como se muestra a continuación.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Este caso es similar al ejemplo A, salvo que dbproc2 y SPID2 ejecutan una SELECT instrucción con la intención de realizar el procesamiento de fila a tiempo y entregar cada fila a través de un búfer a dbproc1 para una INSERTinstrucción , UPDATEo DELETE en la misma tabla. Finalmente, SPID1 (realizando , INSERTUPDATEo DELETE) se bloquea en un bloqueo mantenido por SPID2 (realizando el SELECT). SPID2 escribe una fila de resultados en el dbproc2 de cliente. Dbproc2 intenta pasar la fila de un búfer a dbproc1, pero busca que dbproc1 esté ocupado (está bloqueado esperando a SPID1 para finalizar el actual INSERT, que está bloqueado en SPID2). En este momento, dbproc2 se bloquea en la capa de aplicación mediante dbproc1 cuyo SPID (SPID1) está bloqueado en el nivel de base de datos por SPID2. De nuevo, esto da como resultado un interbloqueo que SQL Server no puede detectar o resolver porque solo uno de los recursos implicados es un recurso de SQL Server.

Ambos ejemplos A y B son problemas fundamentales que los desarrolladores de aplicaciones deben tener en cuenta. Deben codificar las aplicaciones para controlar estos casos de forma adecuada.

Resolución:

Cuando se ha proporcionado un tiempo de espera de consulta, si se produce el interbloqueo distribuido, se romperá cuando se produzca un tiempo de espera. Consulte la documentación del proveedor de conexiones para obtener más información sobre el uso de un tiempo de espera de consulta.

Escenario 5: Bloqueo causado por una sesión en un estado de reversión

Se revertirá una consulta de modificación de datos que se elimina o se cancela fuera de una transacción definida por el usuario. Esto también puede producirse como efecto secundario de la desconexión de una sesión de red del cliente, o cuando se selecciona una solicitud como elemento afectado del interbloqueo. Esto se puede identificar a menudo observando la salida de sys.dm_exec_requests, que puede indicar rollback commandy la percent_complete columna puede mostrar el progreso.

Se revertirá una consulta de modificación de datos que se elimina o se cancela fuera de una transacción definida por el usuario. Esto también puede producirse como un efecto secundario del reinicio del equipo cliente y su desconexión de sesión de red. Del mismo modo, se revertirá una consulta seleccionada como víctima de interbloqueo. Una consulta de modificación de datos a menudo no se puede revertir más rápido que los cambios se aplicaron inicialmente. Por ejemplo, si una DELETEinstrucción , INSERTo UPDATE se había estado ejecutando durante una hora, podría tardar al menos una hora en revertirse. Este es el comportamiento esperado, ya que los cambios realizados deben revertirse, o la integridad transaccional e física de la base de datos estaría en peligro. Dado que esto debe ocurrir, SQL Server marca el SPID en un estado dorado o de reversión (lo que significa que no se puede matar ni seleccionar como víctima de interbloqueo). Esto se puede identificar a menudo mediante la observación de la salida de sp_who, que puede indicar el comando ROLLBACK. La status columna de sys.dm_exec_sessions indicará un estado ROLLBACK.

Resolución:

Debe esperar a que la sesión termine de revertir los cambios realizados.

Si la instancia se cierra en medio de esta operación, la base de datos estará en modo de recuperación al reiniciarse y será inaccesible hasta que se procesen todas las transacciones abiertas. La recuperación de inicio tarda esencialmente la misma cantidad de tiempo por transacción que la recuperación en tiempo de ejecución y la base de datos es inaccesible durante este período. Por lo tanto, forzar al servidor a corregir un SPID en un estado de reversión a menudo será revés. En SQL Server 2019 con la recuperación acelerada de bases de datos habilitada, esto no debería producirse.

Para evitar esta situación, no realice operaciones de escritura por lotes de gran tamaño, operaciones de creación de índices u operaciones de mantenimiento durante las horas punto de los sistemas OLTP. Si es posible, realice estas operaciones durante los períodos de baja actividad.

Escenario 6: Bloqueo causado por una transacción huérfana

Se trata de un escenario de problema común y se superpone parcialmente con el escenario 2. Si la aplicación cliente se detiene, se reinicia la estación de trabajo de cliente o se produce un error de anulación por lotes, todo esto puede dejar abierta una transacción. Esta situación puede producirse si la aplicación no revierte la transacción en los CATCH bloques o FINALLY de la aplicación o si no controla esta situación.

En este escenario, mientras se cancela la ejecución de un lote de SQL, la aplicación deja abierta la transacción SQL. Desde la perspectiva de la instancia de SQL Server, el cliente sigue apareciendo y se conservan los bloqueos adquiridos.

Para demostrar una transacción huérfana, ejecute la consulta siguiente, que simula un error de anulación por lotes insertando datos en una tabla inexistente:

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

A continuación, ejecute esta consulta en la misma ventana:

SELECT @@TRANCOUNT;

La salida de la segunda consulta indica que el recuento de transacciones es uno. Todos los bloqueos adquiridos en la transacción se mantienen hasta que la transacción se confirma o se revierte. Dado que la consulta ya anula el lote, la aplicación que lo ejecuta puede seguir ejecutando otras consultas en la misma sesión sin limpiar la transacción que todavía está abierta. El bloqueo se mantendrá hasta que se mate la sesión o se reinicie la instancia de SQL Server.

Soluciones:

• La mejor manera de evitar esta condición es mejorar el control de excepciones o errores de la aplicación, especialmente para las finalizaciones inesperadas. Asegúrese de usar un Try-Catch-Finally bloque en el código de la aplicación y revertir la transacción en caso de una excepción.
• Considere la posibilidad de usar SET XACT_ABORT ON para la sesión o en cualquier procedimiento almacenado que inicie transacciones y no se limpie después de un error. En caso de un error en tiempo de ejecución que anula el lote, esta configuración revertirá automáticamente las transacciones abiertas y devolverá el control al cliente. Para obtener más información, consulte SET XACT_ABORT (Transact-SQL).
• Para resolver una conexión huérfana de una aplicación cliente que se ha desconectado sin limpiar correctamente sus recursos, puede finalizar el SPID mediante el KILL comando . Para obtener referencia, consulte KILL (Transact-SQL).

El comando KILL toma como entrada el valor del SPID. Por ejemplo, para matar SPID 9, ejecute el siguiente comando:

KILL 99

Consulte también

• Supervisión del rendimiento mediante el Almacén de consultas
• Resolución de problemas de bloqueo causados por la extensión de bloqueo en SQL Server
• Guía de versiones de fila y bloqueo de transacciones
• SET TRANSACTION ISOLATION LEVEL
• Inicio rápido: Eventos extendidos en SQL Server