Compartir a través de

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

  • Artículo

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

Número de KB original: 224453

Objetivo

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

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ómicas. Una conexión puede bloquear otra conexión, independientemente del cliente de origen.

Nota:

Este artículo se centra en las instancias de SQL Server, incluidas las instancias administradas de Azure SQL. Para obtener información específica sobre la solución de problemas de bloqueo en Azure SQL Database, consulte Descripción y resolución de problemas de bloqueo de Azure SQL Database.

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 ha mencionado antes, en SQL Server, el bloqueo se produce cuando una sesión mantiene un bloqueo en 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, como se describe aquí, es un comportamiento normal y puede producirse muchas veces a lo largo 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 se lea de verdad, 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 consultas ejecutadas dentro de una transacción, la duración de los bloqueos viene determinada por el tipo de consulta, el nivel de aislamiento de la transacción y 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:

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 bloquea 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 bloquea 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 consultas, la captura de resultados, etc., es posible que los problemas de bloqueo no se puedan resolver. Este potencial debe examinarse con atención antes de seleccionar una herramienta de desarrollo de aplicaciones para SQL Server, sobre todo 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 un diseño adecuado de aplicaciones y consultas, SQL Server es capaz de admitir muchos miles de usuarios simultáneos en un único 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, la que causa bloqueos durante un período prolongado), el siguiente paso es analizar y determinar por qué se produce el bloqueo. Después de comprender por qué, podemos ejecutar 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 supervisan constantemente el estado de bloqueo en SQL Server. Para recopilar estos datos, hay dos métodos complementarios.

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).

La segunda consiste en 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 siguientes métodos:

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

  • 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 comandos sp_who 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 sys.dm_exec_requests y haga referencia a la columna blocking_session_id. 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, para que sys.dm_exec_requests la devuelva, la consulta debe ejecutarse activamente 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 columna text de sys.dm_exec_sql_text son NULL, la consulta no se está ejecutando actualmente. En ese caso, la columna event_info de sys.dm_exec_input_buffer contendrá la última cadena de comando pasada 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;

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];
  • Haga referencia a sys.dm_os_waiting_tasks que se encuentra en la capa de subprocesos o tareas de SQL Server. Esto devuelve información sobre qué wait_type de SQL está experimentando actualmente la solicitud. Al igual que sys.dm_exec_requests, sys.dm_os_waiting_tasks devuelve solo las solicitudes activas.

Nota:

Para más información sobre los tipos de espera, incluidas las estadísticas de espera agregadas a lo largo del tiempo, consulte la DMV sys.dm_db_wait_stats.

  • 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 producción SQL Server 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 referencia para que el CSS solucione estos problemas es el recopilador de datos PSSDiag. Esta herramienta usa las "estadísticas de rendimiento de SQL Server" para recopilar conjuntos de resultados de las DMV mencionadas anteriormente, a lo largo del tiempo. A medida que esta herramienta evoluciona constantemente, revise la versión pública más reciente de DiagManager en GitHub.

Recopilar 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 a fondo 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. Los XEvents son la plataforma de seguimiento más reciente y superior que permite más versatilidad y menos impacto en el sistema observado, y su interfaz está integrada en SSMS.

Hay sesiones de eventos extendidos preparadas previamente para iniciarse en SSMS, que se muestran en el Explorador de objetos en el menú del Generador de perfiles de XEvent. Para obtener más información, consulte Generador de perfiles de XEvent. También puede crear sus propias sesiones de eventos extendidos personalizadas en SSMS, consulte Asistente para nueva sesión de eventos extendidos. Para solucionar problemas de bloqueo, suele capturarse lo siguiente:

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

**Para configurar el umbral y la frecuencia con la que se generan los 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

      De forma similar, 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, object_id 261575970 es la tabla de títulos y 1 es el índice agrupado.
      Page 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 catálogo sys.partitions para asociar hobt_id a 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 se produce solo 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 estado ejecutando una solicitud mediante 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 devuelta por sys.dm_exec_request, sys.dm_exec_sessions puede 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).

Escenario Wait_type Open_Tran Status ¿Se resuelve? Otros síntomas
1 NOT NULL >= 0 ejecutable Sí, cuando finaliza la consulta. En sys.dm_exec_sessions, las columnas reads, cpu_time o memory_usage aumentarán con el tiempo. La duración de la consulta será elevada cuando se complete.
2 NULL >0 en espera No, pero se puede terminar el SPID. Se puede ver una señal de atención en la sesión de eventos extendidos para este SPID, lo que indica que se ha producido un tiempo de espera de consulta o una cancelación.
3 NULL >= 0 ejecutable No. No se resolverá hasta que el cliente recupere todas las filas o se cierre la conexión. Se puede terminar el SPID, pero puede tardar hasta 30 segundos. Si open_transaction_count = 0 y el SPID contiene bloqueos mientras el nivel de aislamiento de transacción es el predeterminado (READ COMMITTED), es una causa probable.
4 Varía >= 0 ejecutable No. No se resolverá hasta que el cliente cancele las consultas o cierre las conexiones. Se pueden terminar los SPID, pero pueden tardar hasta 30 segundos. La columna hostname de sys.dm_exec_sessions para el SPID del encabezado de una cadena de bloqueo será la misma que la del SPID que está realizando el bloqueo.
5 NULL >0 revertir Sí. Se puede ver una señal de atención en la sesión de eventos extendidos para este SPID, lo que indica que se ha producido un tiempo de espera de consulta o una cancelación o, simplemente, que se ha emitido una instrucción de reversión.
6 NULL >0 en espera Con el tiempo. Cuando Windows NT determine que la sesión ya no está activa, se romperá la conexión. El valor de last_request_start_time en sys.dm_exec_sessions es muy anterior a la hora actual.

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 se ejecuta activamente ha adquirido bloqueos que no se liberan (se ve afectada 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 tipo de problema de bloqueo es buscar formas de optimizar la consulta. Esta clase de problema de bloqueo puede ser de rendimiento y requerir 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 en SSMS del 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 larga duración 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 Always On para sincronizar una réplica de solo lectura de la base de datos.

Nota:

El bloqueo durante la ejecución de la consulta puede deberse a la escalación de la consulta, un escenario en el que los bloqueos de fila o página se escalan a bloqueos de tabla. Microsoft SQL Server determina de forma dinámica cuándo se debe llevar a cabo la escalación de bloqueos. La manera más sencilla y segura de evitar la escalación de bloqueos es mantener las transacciones cortas y reducir la superficie de bloqueo de consultas costosas para que no se superen los umbrales de escalación de bloqueo. Para obtener más información sobre la detección y la prevención de una escalación de bloqueos excesiva, consulte Resolver el problema de bloqueo causado por la escalación de bloqueos.

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á en suspensión o esperando un comando, con un nivel de anidamiento de transacciones (@@TRANCOUNT, open_transaction_count desde sys.dm_exec_requests) mayor que cero. Esta situación puede ocurrir 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 y el lote actuales, pero no revertirá ni confirmará automáticamente la transacción. La aplicación es la responsable de esto, ya que SQL Server no puede suponer que se debe revertir toda una transacción debido a la cancelación de 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 conservan hasta que esta se confirma o se revierte. 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:

  • Además, esta clase de problema de bloqueo también puede ser de rendimiento y requerir 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 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 convenirle examinar el 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 si hay transacciones abiertas, ya que 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 se usa la agrupación de conexiones en una aplicación que abre la conexión y ejecuta algunas consultas antes de 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 se modifique la aplicación cliente 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 revoque las transacciones abiertas.

    • Use SET XACT_ABORT ON para la conexión, o en cualquier procedimiento almacenado que inicie transacciones y no se limpie 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).

Nota:

La conexión no se restablece hasta que se reutiliza desde el grupo de conexiones. Por tanto, es posible que un usuario pueda abrir una transacción y, después, liberar la conexión al grupo de conexiones, pero puede que no se reutilice durante varios segundos, durante los cuales la transacción permanecerá abierta. Si no se reutiliza la conexión, la transacción se anulará cuando se agote el tiempo de espera y se quite del grupo de conexiones. Por lo tanto, es óptimo que la aplicación cliente anule las transacciones en su controlador de errores o use SET XACT_ABORT ON para evitar este posible retraso.

Precaución

Después de SET XACT_ABORT ON, no se ejecutarán las instrucciones T-SQL tras una instrucción que provoca un error. Esto podría afectar al flujo previsto del código existente.

Escenario 3: Bloqueo causado por un SPID cuya aplicación cliente correspondiente no capturó todas las filas de resultados hasta la 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:

La aplicación debe volver a escribirse para capturar todas las filas del resultado 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, un interbloqueo distribuido no se puede detectar 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 ningún control. Las dos secciones siguientes 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 o servidor con un único subproceso de cliente

Si el cliente tiene varias conexiones abiertas y un único subproceso de ejecución, puede ocurrir el siguiente interbloqueo distribuido. Tenga en cuenta que el término dbproc usado 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 mostrado antes, un único subproceso de aplicación cliente tiene dos conexiones abiertas. Envía de forma asincrónica una operación de SQL en dbproc1. Esto significa que no espera a que la llamada vuelva antes de continuar. Entonces, la aplicación envía otra operación de SQL en dbproc2 y espera a los resultados para empezar a procesar los datos devueltos. Cuando los datos comienzan a volver (cualquier dbproc responde por primera vez; suponga que se trata de dbproc1), procesa la finalización de 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á de forma indefinida más datos. SPID2 no está en un bloqueo, pero intenta enviar datos a su cliente, dbproc2. Sin embargo, dbproc2 se bloquea de forma efectiva en dbproc1 en la capa de aplicación, ya que dbproc1 usa el único subproceso de ejecución de la aplicación. Esto da lugar a un interbloqueo que SQL Server no puede detectar ni resolver porque solo uno de los recursos implicados es un recurso de SQL Server.

Ejemplo B: Interbloqueo distribuido de cliente o 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 aquí.

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, excepto que dbproc2 y SPID2 ejecutan una instrucción SELECT con la intención de llevar a cabo el procesamiento de fila en fila a la vez y entregar cada fila a través de un búfer a dbproc1 para una instrucción INSERT, UPDATE o DELETE en la misma tabla. Finalmente, SPID1 (que ejecuta INSERT, UPDATE o DELETE) se bloquea en un bloqueo mantenido por SPID2 (efectuando SELECT). SPID2 escribe una fila de resultado en el cliente dbproc2. Después, dbproc2 intenta pasar la fila en un búfer a dbproc1, pero encuentra que dbproc1 está ocupado (se bloquea esperando que SPID1 finalice el actual INSERT, que está bloqueado en SPID2). En este momento, dbproc2 está bloqueado en la capa de aplicación por dbproc1, cuyo SPID (SPID1) está bloqueado en el nivel de base de datos por SPID2. De nuevo, esto da lugar a un interbloqueo que SQL Server no puede detectar ni 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 aplicaciones para controlar estos casos correctamente.

Resolución:

Cuando se haya proporcionado un tiempo de espera de consulta, si se produce el interbloqueo distribuido, se romperá cuando se produzca el 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 elimine o cancele 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. A menudo, esto se puede identificar observando la salida de sys.dm_exec_requests, que puede indicar el ROLLBACK command, y la columna percent_complete puede mostrar el progreso.

Se revertirá una consulta de modificación de datos que se elimine o cancele 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 del interbloqueo. Una consulta de modificación de datos a menudo no puede revertirse más rápido de lo que se aplicaron los cambios inicialmente. Por ejemplo, si una instrucción DELETE, INSERT o UPDATE se hubiera estado ejecutando durante una hora, podría tardar al menos una hora en revertirse. Este es el comportamiento esperado, ya que los cambios ejecutados deben revertirse o la integridad transaccional y física de la base de datos se vería comprometida. 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 eliminar ni seleccionar como una víctima de interbloqueo). Esto se puede identificar a menudo observando la salida de sp_who, que puede indicar el comando ROLLBACK. La columna status de sys.dm_exec_sessions indicará un estado ROLLBACK.

Nota:

Las reversiones prolongadas son poco frecuentes cuando la característica Recuperación acelerada de la base de datos está habilitada. Esta característica se añadió en SQL Server 2019.

Resolución:

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

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

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 conexión huérfana

Este es un problema común y se superpone, en parte, con el Escenario 2. Si la aplicación cliente se detiene, se reinicia la estación de trabajo cliente o se produce un error de anulación por lotes, puede quedar abierta una transacción. Esta situación puede producirse si la aplicación no revierte la transacción en los bloques de la aplicación CATCH o FINALLY, o si no controla esta situación de otro modo.

En este escenario, mientras que se ha cancelado 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 todavía parece estar presente y se conservan los bloqueos adquiridos.

Para mostrar 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 conservan hasta que esta se confirma o se revierte. Dado que la consulta ya ha anulado el lote, la aplicación que la 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 elimine 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 errores/excepciones de la aplicación, sobre todo para las finalizaciones inesperadas. Asegúrese de usar un bloqueo Try-Catch-Finally en el código de la aplicación y revertir la transacción en caso de excepción.
  • Piense en 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 producirse un error de tiempo de ejecución que aborte el lote, esta configuración anulará 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 comando KILL. Como referencia, vea KILL (Transact-SQL).

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

KILL 99

Nota:

El comando KILL puede tardar hasta 30 segundos en completarse, debido al intervalo entre las comprobaciones del comando KILL.

Consulte también