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

  • Artículo

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

Número 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 de la base de datos. Cada conexión aparece como un identificador 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, 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 y por diseño de cualquier sistema de administración de bases de datos relacionales (RDBMS) con simultaneidad basada en bloqueos. 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 tiempo durante el cual el primer SPID bloquea el recurso es reducido. Cuando la sesión propietaria libera el bloqueo, la segunda conexión es libre de adquirir su propio bloqueo sobre el recurso y continuar con 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 cuánto tiempo se mantienen sus bloqueos y, por tanto, el efecto 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 por 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 del bloqueo, las sugerencias de bloqueo y los niveles de aislamiento de transacciones, consulte los artículos siguientes:

Cuando el bloqueo persiste hasta el punto en que ejerce un efecto perjudicial en el rendimiento del sistema, se debe a una de las siguientes razones:

  • Un SPID bloquea un conjunto de recursos durante un período de tiempo prolongado antes de liberarlos. Este tipo de bloqueo se resuelve por sí mismo con el tiempo, pero 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 por sí mismo e impide el acceso a los recursos afectados de forma indefinida.

En el primer escenario, la situación puede ser fluida, ya que diferentes SPID provocan bloqueos en diferentes recursos a lo largo del tiempo, lo que crea un objetivo móvil. Estas situaciones son difíciles de solucionar mediante SQL Server Management Studio para restringir el problema a consultas individuales. Por el contrario, la segunda situación da como resultado un estado coherente que puede ser más fácil de diagnosticar.

Aplicaciones y bloqueo

Puede haber una tendencia a centrarse en el ajuste del lado servidor y los problemas de plataforma al enfrentarse a un problema de bloqueo. Sin embargo, prestar atención solo a la base de datos puede no conducir a una resolución, así como consumir tiempo y energía mejor dirigidos a examinar la aplicación cliente y las consultas que envía. Independientemente del nivel de visibilidad que exponga la aplicación con respecto a las llamadas a la base de datos, un problema de bloqueo requiere con frecuencia tanto la inspección de las instrucciones de SQL exactas enviadas por la aplicación como el 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, el consumo de recursos, el nivel de aislamiento y la longitud de la ruta de acceso de la transacción deben evaluarse para cada consulta. Cada consulta y transacción deben ser lo más ligeras posible. Se debe ejercer una administración de conexiones correcta. Sin ella, es posible que la aplicación parezca tener un rendimiento aceptable con un número bajo de usuarios, pero puede degradarse de forma significativa a medida que el número de usuarios crece.

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.

Solucionar problemas de bloqueo

Independientemente de la situación de bloqueo que nos encontremos, la metodología para solucionar problemas de bloqueo es la misma. Estas separaciones lógicas son las que dictarán el resto de la composición de este artículo. El concepto es buscar el bloqueador de encabezado e identificar lo que está haciendo esa consulta y por qué está bloqueando. 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 para solucionar problemas:

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

  2. Busque la consulta y la transacción que provocan el bloqueo (lo que causa bloqueos durante un período prolongado)

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

  4. Solucionar el problema de bloqueo mediante el rediseño de consultas y transacciones

Ahora vamos a profundizar y analizar cómo identificar la sesión de bloqueo principal con una captura de datos adecuada.

Recopilar 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 su comparación a lo largo del tiempo. Algunos 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.

Recopilar información de DMV

Hacer referencia a DMV para solucionar problemas de bloqueo tiene el objetivo de identificar el SPID (id. de sesión) que está en el encabezado de la cadena de bloqueo y la instrucción SQL. Busque SPID víctimas que estén sufriendo bloqueos. Si otro SPID bloquea cualquier SPID, investigue el SPID que posee el recurso (el SPID que bloquea). ¿También se bloquea el SPID propietario? Puede recorrer la cadena para encontrar el bloqueador de encabezado e investigar por qué mantiene 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 sys.dm_exec_sessions de DMV devuelve más datos en un conjunto de resultados que es más fácil de consultar y filtrar. Encontrará sys.dm_exec_sessions en el núcleo de otras consultas.

  • Si ya tiene identificada una sesión determinada, 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 es más fácil de consultar y filtrar, con el session_id y el request_id. Por ejemplo, para devolver la consulta más reciente enviada por session_id 66 y request_id 0:

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, no se está bloqueando una sesión. Aunque sys.dm_exec_requests enumera solo las solicitudes que se están ejecutando actualmente, cualquier conexión (activa o no) se mostrará en sys.dm_exec_sessions. Compile esta combinación común entre sys.dm_exec_requests y sys.dm_exec_sessions en la siguiente consulta. 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 que se ejecutan de forma activa y su texto por lotes o texto de búfer de entrada de SQL 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 sesiones que bloquean otras sesiones, incluida una lista de session_ids bloqueadas por session_id.

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, proporcionada por el Soporte técnico de Microsoft, para identificar el encabezado de una cadena de bloqueo de varias sesiones, incluido el texto de consulta de las sesiones implicadas 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 solo devuelve las solicitudes activas.

Nota:

Para obtener mucha 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 qué bloqueos han colocado 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 INNER JOIN en sys.dm_os_waiting_tasks, la siguiente consulta restringe la salida de sys.dm_tran_locks solo a las solicitudes bloqueadas en ese momento, 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 a lo largo del tiempo proporcionará puntos de datos que le permitirán revisar el bloqueo durante un intervalo de tiempo especificado para identificar el bloqueo persistente o las tendencias. 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 envió. Sin embargo, una de las instrucciones anteriores puede ser la razón por la que los bloqueos todavía se mantienen. Un seguimiento le permitirá ver todos los comandos ejecutados por una sesión dentro de 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 forma predeterminada, no se generan informes de procesos bloqueados.

  • Advertencias de categoría:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • Ejecución de categorías:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Bloqueo de categoría

    • Lock_deadlock

  • Sesión de categoría

    • Existing_connection
    • Inicio de sesión
    • Cierre de sesión

Identificar y resolver escenarios comunes de bloqueo

Al examinar la información anterior, puede determinar la causa de la mayoría de los problemas de bloqueo. El resto de este artículo es una explicación sobre 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.

Analizar 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á claramente qué solicitudes están bloqueadas y cuáles están bloqueando. Mire más a fondo las sesiones que están bloqueadas y bloqueando. ¿Existe algo común o una raíz de la cadena de bloqueo? Es probable que compartan una tabla común, y una o más de las sesiones involucradas en una cadena de bloqueo está llevando a cabo 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 de suspensión indica que el SPID ha completado 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 en ese momento una consulta. En la tabla siguiente se proporcionan breves explicaciones de los distintos valores de estado.

      Estado Significado
      Información previa El SPID ejecuta una tarea en segundo plano, como la detección de interbloqueos, el escritor de registros o el punto de control.
      En suspensión El SPID no se está ejecutando actualmente. Esto suele indicar que el SPID espera un comando de la aplicación.
      En funcionamiento El SPID se está ejecutando actualmente en un programador.
      Ejecutable El SPID está en la cola que se puede ejecutar de un programador y esperando al tiempo 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 contener bloqueos adquiridos por cualquier instrucción dentro de la transacción.

    • 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 está dentro de una transacción abierta y puede contener bloqueos adquiridos por cualquier instrucción dentro de la transacción.

    • 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 last_wait_type indica el último wait_type que se encontró la solicitud. 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. El valor wait_time se puede usar para determinar si la solicitud está progresando. Cuando una consulta en la tabla sys.dm_exec_requests devuelve un valor de la columna wait_time que es menor que el valor wait_time de una consulta anterior de sys.dm_exec_requests, esto indica que el bloqueo anterior se adquirió y liberó y ahora está esperando un nuevo bloqueo (suponiendo que no sea wait_time cero). Esto se puede comprobar comparando wait_resource entre la salida de sys.dm_exec_requests, que muestra el recurso que está esperando la solicitud.

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

      Resource Formato Ejemplo Explicación
      Tabla DatabaseID:ObjectID:IndexID TABLA: 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 PÁGINA: 5:1:104 En este caso, el identificador 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 de títulos. Para identificar el object_id al que pertenece la página, use la función de administración dinámica sys.dm_db_page_info, pasando DatabaseID, FileId, PageId desde wait_resource.
      Key DatabaseID:Hobt_id (valor hash para la clave de índice) CLAVE: 5:72057594044284928 (3300a4f361aa) En este caso, el identificador de base de datos 5 es Pubs, Hobt_ID 72057594044284928 corresponde a index_id 2 para object_id 261575970 (tabla de títulos). 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 separar el hash de clave de índice en un valor de clave específico.
      Fila DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 En este caso, el identificador 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 de títulos y la ranura 3 indica la posición de la fila en la página.
      Compile DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 En este caso, el identificador 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 de títulos y la ranura 3 indica la posición de la fila en la página.

    • sys.dm_tran_active_transactions La DMV sys.dm_tran_active_transactions contiene datos sobre transacciones abiertas que se pueden unir a otras DMV para obtener una imagen completa de las transacciones en espera de confirmación o reversión. Use la consulta siguiente para devolver información sobre las transacciones abiertas, unida a otras DMV, incluida sys.dm_tran_session_transactions. Considere el estado actual de una transacción, transaction_begin_time y otros datos situacionales para evaluar si podrían ser un origen de 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 columnas restantes de sys.dm_exec_sessions y sys.dm_exec_request también pueden proporcionar información sobre la raíz 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 comunes de bloqueo

En la tabla siguiente se asignan síntomas comunes a sus causas probables.

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 "¿Lo resuelve" indica si el bloqueo se resolverá por sí mismo o si la sesión se debe eliminar mediante el comando KILL. Para obtener más información, consulte KILL (Transact-SQL).

Escenario Wait_type Open_Tran Estado ¿Resuelve? Otros síntomas
1 NOT NULL >= 0 ejecutable Sí, cuando finaliza la consulta. En sys.dm_exec_sessions, reads, cpu_time o memory_usage, las columnas aumentarán con el tiempo. La duración de la consulta será alta cuando se complete.
2 NULL >0 en suspensión No, pero SPID puede eliminarse. 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 cierre la conexión. El SPID se puede eliminar, 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 consultas o cierre las conexiones. Los SPID se pueden eliminar, pero puede 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 uno de los SPID que está bloqueando.
5 NULL >0 reversión 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 suspensión Eventualmente. Cuando Windows NT determine que la sesión ya no está activa, se romperá la conexión. El valor last_request_start_time de 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.

Solució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 de ejecución lenta, consulte Cómo solucionar problemas de consultas de ejecución lenta en SQL Server. Para más información, consulte Supervisar y ajustar el 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 no confirmada, emita la consulta siguiente:

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

después, 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 de forma explícita, una comunicación u otro error podría dejar la sesión y su transacción en un estado abierto.

Use el script anterior visto en este artículo basado en sys.dm_tran_active_transactions para identificar las transacciones no confirmadas actualmente en toda 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 de manera correcta los niveles de anidamiento de transacciones o pueden provocar un problema de bloqueo después de la cancelación de la consulta de este modo. 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 cree 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 la cancelación de la consulta, impiden que el procedimiento se ejecute más allá de la instrucción actual, por lo que incluso si el procedimiento tiene lógica para comprobar IF @@ERROR <> 0 y anular la transacción, este código de reversión no se ejecutará en tales 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 producirse un error de tiempo de ejecución, 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).

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 capturar inmediatamente todas las filas de resultados hasta su finalización. Si una aplicación no captura todas las filas de resultados, los bloqueos se pueden dejar en las tablas, y bloquear a otros usuarios. Si usa una aplicación que envía de forma transparente instrucciones SQL al servidor, 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 el problema, puede restringir las aplicaciones de comportamiento deficiente a una base de datos de informes o de soporte técnico para la toma de decisiones, independiente de la base de datos OLTP principal.

Solució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 ejecutar la paginación del lado 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.

Solució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 un efecto secundario de la desconexión de la sesión de red del cliente o cuando se selecciona una solicitud como víctima de 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.

Solució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 ejecute operaciones de escritura por lotes grandes ni de creación o mantenimiento de índices durante las horas de trabajo en sistemas OLTP. Si es posible, lleve a cabo estas operaciones durante períodos de baja actividad.

Escenario 6: Bloqueo causado por una transacció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)

después, 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 coge el valor SPID como entrada. 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.

Vea también