Comprender y resolver problemas de bloqueo de SQL Server

Objetivo

La intención de este artículo es proporcionar instrucciones para comprender primero qué bloqueo se encuentra en términos de SQL Server y cómo investigar su ocurrencia.

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 aparece como un identificador de sesión (SPID). Cada uno de estos SPID se suele denominar proceso, aunque no es un contexto de proceso independiente en el sentido normal. En su lugar, cada SPID consta de los recursos del servidor y las estructuras de datos necesarios 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 el punto de vista de SQL Server, no hay ninguna diferencia entre varias conexiones desde una única aplicación de cliente en un único equipo cliente y varias conexiones desde varias aplicaciones cliente o varios equipos cliente; son atómicos. Una conexión puede bloquear otra conexión, independientemente del cliente de origen.

Versión del producto original:   SQL Server
Número de KB original:   224453

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 mencionó anteriormente, 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 conflictivo en el mismo recurso. Normalmente, el intervalo de tiempo para el que el primer SPID bloquea el recurso es pequeño. Cuando la sesión propietaria libera el bloqueo, la segunda conexión es libre para adquirir su propio bloqueo en el recurso y continuar con el procesamiento. Este es el comportamiento normal y puede ocurrir varias veces durante el transcurso de un día sin afectar de forma significativa al rendimiento del sistema.

La duración y el contexto de transacción de una consulta determinan cuánto tiempo se mantienen los bloqueos y, por lo tanto, su 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 esté leyendo realmente, no durante toda la consulta. Para las instrucciones INSERT, UPDATE y DELETE, los bloqueos se mantienen mientras dure la consulta, tanto para la coherencia de los datos como para permitir la desactivación de la consulta si es necesario.

Para las consultas que se ejecutan en una transacción, la duración para la que se retienen los bloqueos se determina 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 niveles de bloqueo, sugerencias de bloqueo y aislamiento de transacciones, vea los siguientes temas en los libros en pantalla de SQL Server:

  • Bloqueo en el motor de base de datos
  • Personalización del bloqueo y el control de versiones de fila
  • Modos de bloqueo
  • Compatibilidad de bloqueo
  • Niveles de aislamiento basados en el control de versiones de fila en el motor de base de datos
  • Control de transacciones (motor de base de datos)

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

  • Un SPID tiene bloqueos en un conjunto de recursos durante un período de tiempo prolongado antes de su lanzamiento. Este tipo de bloqueo se resuelve a sí mismo con el tiempo, pero puede provocar una degradación del rendimiento.

  • Un SPID tiene bloqueos en 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 indefinidamente.

En el primer caso anterior, la situación puede tener un flujo muy fluido a medida que varios SPID provocan un bloqueo en diferentes recursos a lo largo del tiempo, lo que crea un destino en movimiento. Por este motivo, estas situaciones pueden resultar difíciles de solucionar con SQL Server Management Studio (la herramienta ir a para administrar SQL Server) y para limitar 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.

Metodología para la solución de problemas de bloqueo

Independientemente de la situación de bloqueo en la que se encuentre, la metodología para la solución de problemas de bloqueo es la misma. Estas separaciones lógicas son lo que determinará el resto de la composición de este artículo. El concepto es encontrar el bloqueador de cabezales e identificar lo que hace esa consulta y por qué está bloqueado. Una vez que se identifica la consulta problemática (es decir, qué es la retención de bloqueos durante el período prolongado), el siguiente paso es analizar y determinar por qué está ocurriendo el bloqueo. Después de comprender el motivo, podemos realizar cambios rediseñando la consulta y la transacción.

Para enumerar brevemente esto:

  1. Identificación de la sesión de bloqueo principal (bloqueador de cabezales)

  2. Buscar qué consulta y transacción está causando el bloqueo (Qué es la retención de bloqueos durante un período prolongado)

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

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

Ahora vamos a profundizar en el modo de 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 y bloqueo en SQL Server. Para recopilar estos datos, hay básicamente dos métodos. El primero consiste en realizar instantáneas de DMV dentro de SQL Server y el segundo consiste en usar trazas de XEvents/Profiler para diagnosticar lo que se estaba ejecutando.

Recopilar información de DMV

Hacer referencia a las DMV para solucionar problemas de bloqueo tiene el objetivo de identificar el SPID (identificador de sesión) al principio de la cadena de bloqueo y la instrucción SQL. Busque los SPID de la víctima que se están bloqueando. Si algún SPID está bloqueado por otro SPID, investigue el SPID propietario del recurso (el SPID de bloqueo). ¿Es que el SPID del propietario también está bloqueado o no (el bloqueador de cabezales)? En esencia, desea recorrer la cadena para encontrar el bloqueador de cabezales y, a continuación, investigar por qué está manteniendo el bloqueo. Para ello, puede usar uno de los siguientes métodos:

  • Haga clic con el botón secundario en el objeto de servidor, expanda informes, expanda  informes estándar y haga clic en  actividad – todas las transacciones de bloqueo. Este informe muestra las transacciones en el encabezado de la cadena de bloqueo. Si expande la transacción, el informe mostrará las transacciones bloqueadas por la transacción Head. Este informe también mostrará la instrucción SQL de bloqueo y la instrucción SQL bloqueada.

  • Si ya tiene una sesión determinada identificada, puede usar DBCC INPUTBUFFER(<spid>) para buscar la última instrucción que ha enviado un SPID.

  • Ejecute la siguiente consulta para encontrar las consultas que se están ejecutando activamente y su búfer de entrada. Tenga en cuenta que debe rellenarse en sys.dm_exec_requests, la consulta debe ejecutarse activamente con SQL.

    select * from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text (sql_handle)
    
  • Consulte la master.sys.sysprocesses lista y haga referencia a la columna bloqueado. Más información sobre los procesos desys.sys aquí. Los procesos (activos o no) aparecerán en la lista sys.sysprocesses .

  • Abra el monitor de actividad en SSMS y haga referencia a la columna bloqueado por . Obtenga más información sobre el monitor de actividad aquí.

  • Use el sp_who procedimiento almacenado integrado para consultar sesiones y hacer referencia a la columna BLK . Más información sobre sp_who aquí.

  • Use el sys.dm_tran_locks DMV. Más información sobre Sys.dm_tran_locks aquí.

  • ¿Desea ver si tiene una transacción abierta a largo plazo? Ejecute DBCC OPENTRAN. Tenga en cuenta que esto no es estupendo para recorrer la cadena, pero, en su lugar, puede ver si tiene una transacción de larga duración que puede estar causando una cadena de bloqueo. Más información sobre DBCC OPENTRAN aquí.

  • Sys.dm_os_waiting_tasks de referencia que se encuentra en el nivel de subproceso o tarea de SQL. Encontrará más información en Sys.dm_os_waiting_tasks aquí.

Con las DMV, la toma de instantáneas a lo largo del tiempo proporciona puntos de datos que le permitirán revisar el bloqueo en un intervalo de tiempo especificado para identificar las tendencias o los bloqueos persistentes. La herramienta Go-to for CSS para solucionar estos problemas es mediante el recopilador de datos de PSSDiag. Esta herramienta usa "Estad? as de SQL Server" en las DMV de instantáneas a las que se hace referencia más adelante. Como esta herramienta está en constante evolución, revise la última versión pública de DiagManager en github.

Recopilar información de seguimiento de SQL Server

Además de la información anterior, a menudo es necesario capturar un seguimiento de las actividades en el servidor para investigar minuciosamente 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, uno de los comandos anteriores puede ser la razón por la que todavía se mantienen los bloqueos. Un seguimiento le permitirá ver todos los comandos ejecutados por una sesión dentro de la transacción actual.

Ahora hay dos formas de capturar seguimientos en SQL Server; Seguimientos de Xevents y del analizador. SQL Server Profiler es la tecnología de rastreo original y el generador de perfiles de XEvent es la nueva tecnología de seguimiento que permite más versatilidad. En general, hay más flexibilidad con Xevents y tienen menos sobrecarga que con el analizador de SQL Server. Para obtener una solución de problemas más avanzada o capturas más largas, Xevents es mejor. Para obtener una duración de captura más corta y eventos menos intensivos, el seguimiento del analizador de SQL Server sería suficiente. Debido a que el analizador de SQL Server tiene una presencia más larga en el producto SQL Server, suele ser la herramienta de seguimiento predeterminada que se usa, pero que no siempre es la mejor.

Seguimiento del analizador de SQL Server

Haga referencia a los pasos identificados en el analizador de SQL Server .

Si decide no usar una plantilla predeterminada, tenga en cuenta que querrá seleccionar Mostrar todos los eventos y mostrar todas las columnas. Además, si está ejecutando en un entorno de producción de gran volumen, es posible que decida usar solo los eventos de la tabla 1, ya que normalmente son suficientes para solucionar la mayoría de los problemas de bloqueo. La inclusión de los eventos anexados en la tabla 2 puede facilitar la determinación rápida del origen de un problema (o estos eventos pueden ser necesarios para identificar la instrucción culpable en un procedimiento multiinstrucción) a costa de agregar una carga adicional en el sistema y aumentar el tamaño de la salida de seguimiento.

  • Tabla 1: tipos de evento

    Heading Evento
    Errores y advertencias Excepción
    Errores y advertencias Atención
    Auditoría de seguridad Inicio de sesión de auditoría
    Auditoría de seguridad Cierre de sesión de auditoría
    Sesiones Conexión existente
    Procedimientos almacenados RPC: iniciar
    TSQL SQL: BatchStarting
  • Tabla 2: tipos de evento adicionales

    Heading Evento
    Transacciones SQLTransaction
    Transacciones SQLTransaction
    Procedimientos almacenados RPC: completado
    TSQL SQL: BatchCompleted
    Procedimientos almacenados SP: StmtStarting
    Procedimientos almacenados SP: StmtCompleted

Seguimiento de XEvent

Consulte el documento en el que se explica cómo usar el generador de perfiles de XEvent. Teniendo en cuenta las mismas ventajas que se mencionan en la sección de Profiler anterior, se suele capturar:

  • Relacionados

    • Atención

    • Error_reported

    • Blocked_process_report

    • Exchange_spill

    • Execution_warning

    • Hash_warning

    • Missing_column_statistics

    • Missing_join_predicate

    • Sort_warning

  • Realización

    • Rpc_completed

    • Rpc_starting

    • Sp_cache_remove

    • Sql_batch_completed

    • Sql_batch_starting

    • Sql_statement_recompile

  • Lock

    • Lock_deadlock

    • Lock_escalation

    • Lock_timeout

  • Sesión

    • Existing_connection

    • Inicio de sesión

    • Salida

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 puede establecerse en segundos. De forma predeterminada, no se genera ningún informe de procesos bloqueados.

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 comunes de bloqueo. En este tema 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 ha realizado un seguimiento de XEvent o del generador de perfiles con los eventos descritos anteriormente.

Visualización del resultado del script de bloqueo

  • Examine el  sys.sysprocesses   resultado para determinar los cabezales de las cadenas de bloqueo

    Si no especificó el modo rápido para los scripts de bloqueo, habrá una sección titulada spids en el encabezado de las cadenas de bloqueo que enumera los SPID que bloquean otros SPID en la salida del script.

    Si especificó la opción Fast, aún puede determinar los cabezales de bloqueo mirando el  sys.sysprocesses   resultado y siguiendo la jerarquía del SPID que se indica en la columna bloqueado.

  • Examine el  sys.sysprocesses   resultado para obtener información sobre los SPID en el encabezado de la cadena de bloqueo.

    Es importante evaluar los  sys.sysprocesses   campos siguientes:

    Estado

    Esta columna muestra el estado de un SPID en particular. Normalmente, un estado inactivo indica que el SPID ha completado la ejecución y está esperando a que la aplicación envíe otra consulta o lote. Un ejecutable, en ejecución o  sos_scheduler_yield   Estado indica que el SPID está procesando actualmente una consulta. En la siguiente tabla se proporcionan explicaciones breves de los distintos valores de estado.

    Estado Significado
    Información previa El SPID está ejecutando una tarea en segundo plano, como la detección de interbloqueos.
    Hiberna El SPID no se está ejecutando actualmente. Normalmente, esto indica que el SPID está esperando un comando de la aplicación.
    En funcionamiento El SPID se está ejecutando actualmente en un programador.
    Ejecutables El SPID está en la cola ejecutable de un programador y en espera de obtener el tiempo del programador.
    Sos_scheduler_yield El SPID se estaba ejecutando, pero ha generado voluntariamente su intervalo de tiempo en el programador para permitir que otro SPID obtenga tiempo del programador.
    Suspended El SPID está esperando un evento, como un bloqueo o un bloqueo temporal.
    Inversión El SPID está en reversión de una transacción.
    Defwakeup Indica que el SPID está esperando a un recurso que está en proceso de liberarse. El waitresource campo debe indicar el recurso en cuestión.
    • Open_tran

      Este campo indica el nivel de anidamiento de la transacción del SPID. Si este valor es mayor que 0, el SPID está dentro de una transacción abierta y puede estar reteniendo los bloqueos adquiridos por cualquier instrucción dentro de la transacción.

    • Lastwaittype, waittype y waittime

      El  lastwaittype   campo es una representación de cadena del  waittype   campo, que es una columna binaria interna reservada. Si el  waittype   es 0X0000, el SPID no está actualmente en espera para nada y el  lastwaittype   valor indica el último  waittype   que tenía el SPID. Si el  waittype   no es cero, el  lastwaittype   valor indica el actual  waittype   del SPID.

      Para obtener más información  sys.dm_os_wait_stats y una breve descripción de los distintos  lastwaittype    waittype   valores y, vea los libros en pantalla de SQL Server.

      El  waittime   valor se puede usar para determinar si el SPID está realizando el progreso. Cuando una consulta en la  sys.sysprocesses   tabla devuelve un valor en la  waittime   columna que es menor que el  waittime   valor de una consulta anterior de  sys.sysprocesses , esto indica que el bloqueo anterior se ha adquirido y lanzado y que ahora está en espera de un nuevo bloqueo (siempre que sea distinto de cero waittime ). Esto puede comprobarse comparando la  waitresource   salida entre los  sys.sysprocesses   resultados.

    • Waitresource

      Este campo indica el recurso al que espera un SPID. En la tabla siguiente se enumeran los  waitresource   formatos comunes y su significado:

      Recurso Formato Ejemplo
      Table DatabaseID: ObjectID: IndexID TAB: 5:261575970:1
      En este caso, el identificador de base de datos 5 es la base de datos de ejemplo pubs y el identificador de objeto 261575970 es la tabla titles 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 identificador 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 identificador de objeto al que pertenece la página, use el comando DBCC PAGE (dbid, fileid, pageId, output_option) y mire el m_objId. Por ejemplo:
      DBCC TRACEON (3604)
      PÁGINA DBCC (5, 1, 104, 3)
      Key DatabaseID: Hobt_id (valor de 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 la index_id 2 no agrupada para el identificador de objeto 261575970 (tabla de títulos). Utilice la vista de catálogo sys. partitions para asociar la hobt_id a un identificador de índice y un identificador de objeto en particular. No es posible deshacer el hash de la clave de índice hash en un valor de clave de índice específico.
      Fila DatabaseID: FileID: PageID: Slot (fila) RID: 5:1:104:3
      En este caso, el identificador de base de datos 5 es pubs, el identificador 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 en la página.
      Compile DatabaseID: FileID: PageID: Slot (fila) RID: 5:1:104:3
      En este caso, el identificador de base de datos 5 es pubs, el identificador 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 en la página.
    • Otras columnas

      Las  sys.sysprocesses   columnas restantes 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 sólo se produce desde determinados clientes (nombre de host), en ciertas bibliotecas de red (net_library), cuando el último lote enviado por un SPID era (last_batch) y así sucesivamente.

Examinar el resultado de DBCC INPUTBUFFER

Para cualquier SPID en el encabezado de una cadena de bloqueo o con un valor distinto de cero waittype , el script de bloqueo se ejecutará DBCC INPUTBUFFER para determinar la consulta actual para ese SPID.

En muchos casos, esta es la consulta que causa los bloqueos que bloquean a otros usuarios. Sin embargo, si el SPID está dentro de una transacción, los bloqueos pueden haber sido adquiridos por una consulta ejecutada con anterioridad, no por la actual. Por lo tanto, también debe ver el resultado del generador de perfiles para el SPID, no solo el inputbuffer .

Nota

Debido a que el script de bloqueo consta de varios pasos, es posible que un SPID aparezca en la primera sección como encabezado de una cadena de bloqueo, pero en el momento en que se INPUTBUFFER ejecuta la consulta DBCC, ya no se bloquea y el INPUTBUFFER no se captura. Esto indica que el bloqueo se resuelve a sí mismo para ese SPID y puede o no ser un problema. En este punto, puede usar la versión rápida de la secuencia de comandos de bloqueo para asegurarse de que captura la antes de que se inputbuffer borre (aunque aún no existe ninguna garantía) o ver los datos del generador de perfiles a partir de ese intervalo de tiempo para determinar las consultas que se estaba ejecutando el SPID.

Visualización de los datos del generador de perfiles

Ver los datos del generador de perfiles de forma eficaz es muy útil para resolver problemas de bloqueo. Lo más importante que debe tener en cuenta es que no tiene que mirar todo lo que ha capturado; ser selectivo. El generador de perfiles proporciona funciones para ayudarle a ver los datos capturados de manera eficaz. En el cuadro de diálogo propiedades (en el menú archivo , haga clic en propiedades), el generador de perfiles permite limitar los datos que se muestran quitando columnas o eventos de datos, agrupando (ordenando) por columnas de datos y aplicando filtros. Puede buscar valores específicos en todo el seguimiento o sólo en una columna específica (en el menú edición , haga clic en Buscar). También puede guardar los datos del generador de perfiles en una tabla de SQL Server (en el menú archivo , elija Guardar como y, a continuación, haga clic en tabla) y ejecutar consultas SQL en él.

Tenga cuidado de que sólo realice el filtrado en un archivo de seguimiento previamente guardado. Si realiza estos pasos en un seguimiento activo, corre el riesgo de perder datos capturados desde que se inició el seguimiento. Guarde primero un seguimiento activo en un archivo o tabla (en el menú archivo , haga clic en Guardar como) y vuelva a abrirlo (en el menú archivo , haga clic en abrir) antes de continuar. Cuando se trabaja con un archivo de seguimiento guardado, el filtrado no quita de forma permanente los datos que se filtran, simplemente no muestra todos los datos. Puede Agregar y quitar eventos y columnas de datos según sea necesario para ayudar a centrar las búsquedas.

Qué buscar:

  • ¿Qué comandos tiene el SPID en el encabezado de una cadena de bloqueo ejecutada dentro de la transacción actual? Filtre los datos de seguimiento de un SPID concreto que esté en el encabezado de una cadena de bloqueo (en el menú Archivo, haga clic en propiedades; a continuación, en la pestaña filtros , especifique el valor de SPID ). A continuación, puede examinar los comandos que ha ejecutado antes de que bloqueara otros SPID. Si incluye los eventos de transacción, pueden identificar fácilmente Cuándo se inició una transacción. De lo contrario, puede buscar en la Text columna BEGIN SAVE COMMIT las operaciones,, u ROLLBACK TRANSACTION . Use el open_tran valor de la sysprocesses tabla para asegurarse de que detecta todos los eventos de transacción. Conocer los comandos ejecutados y el contexto de transacción le permitirá determinar por qué un SPID tiene bloqueos.

    Recuerde que puede quitar eventos y columnas de datos. En lugar de mirar los eventos de inicio y de finalización, elija uno. Si los SPID de bloqueo no son procedimientos almacenados, quite los SP:Starting SP:Completed eventos o; los eventos SQLBATCH y RPC mostrarán la llamada al procedimiento. Vea solo los eventos de SP cuando necesite ver ese nivel de detalle.

  • ¿Cuál es la duración de las consultas para los SPID en la cabeza de las cadenas de bloqueo?

    Si incluye los eventos completados anteriormente, la columna duración mostrará el tiempo de ejecución de la consulta. Esto puede ayudarle a identificar las consultas de larga duración que están causando el bloqueo. Para determinar por qué la consulta se ejecuta lentamente, vea la CPU, lea y escriba columnas, así como el evento del plan de ejecución.

Categorización de escenarios comunes de bloqueo

En la tabla siguiente se asignan los síntomas comunes a sus causas probables. El número indicado en la columna escenario corresponde al número de la sección de escenarios y resoluciones comunes de bloqueo de este artículo a continuación. Las Waittype Open_Tran columnas, y Status se refieren a la sysprocesses información. Se resuelve? columna indica si el bloqueo se resolverá por sí mismo.

Escenario Waittype Open_Tran Estado Se resuelve? Otros síntomas
1 Distinto de cero >= 0 ejecutables Sí, cuando finaliza la consulta. Las columnas Physical_IO, CPU o MEMUSAGE se incrementarán con el tiempo. La duración de la consulta será alta cuando se complete.
2 0x0000 >0 hiberna No, pero se puede sacrificar el SPID. Es posible que se vea una señal de atención en la traza del analizador para este SPID, lo que indica que se ha producido un tiempo de espera de consulta o se ha cancelado.
3 0x0000 >= 0 ejecutables No. No se resolverá hasta que el cliente recupere todas las filas o cierre la conexión. SPID puede ser eliminado, pero puede tardar hasta 30 segundos. Si open_tran = 0 y el SPID retiene los bloqueos mientras el nivel de aislamiento de transacción es predeterminado (lectura COMMMITTED), esta es una causa probable.
4 Varía >= 0 ejecutables No. No se resolverá hasta que el cliente cancele las consultas o cierre las conexiones. Los SPIDs pueden ser eliminados, pero pueden tardar hasta 30 segundos. La columna Nombre de host en sysprocesses para el SPID en el encabezado de una cadena de bloqueo será la misma que la del SPID que está bloqueando.
5 0x0000 >0 inversión Sí. Es posible que se vea una señal de atención en la traza del analizador para este SPID, lo que indica que se ha producido un tiempo de espera de consulta o que se ha cancelado, o simplemente se ha emitido una instrucción ROLLBACK.
6 0x0000 >0 hiberna Posiblemente. Cuando Windows NT determina que la sesión ya no está activa, se interrumpirá la conexión de SQL Server. El valor de last_batch sysprocesses es mucho anterior a la hora actual.

Soluciones y escenarios comunes de bloqueo

Los escenarios que se enumeran a continuación tendrán las características enumeradas en la tabla anterior. En esta sección se proporcionan detalles adicionales cuando proceda, así como rutas de resolución.

  1. Bloqueo causado por una consulta en ejecución normal con un tiempo de ejecución largo

    Solución:

    La solución para este tipo de problemas de bloqueo consiste en buscar formas de optimizar la consulta. En realidad, esta clase de problema de bloqueo puede ser simplemente un problema de rendimiento y requerir que lo siga como tal. Para obtener información sobre cómo solucionar problemas de una consulta de ejecución lenta específica, vea cómo solucionar problemas de consultas lentas en SQL Server 7,0 o en versiones posteriores.

    Para obtener más información, consulte los temas de procedimientos de supervisión y optimización del rendimiento.

    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 ayuda para la toma de decisiones.

  2. Bloqueo causado por un SPID en espera que ha perdido el seguimiento del nivel de anidamiento de la transacción

    Este tipo de bloqueo a menudo puede identificarse mediante un SPID que está inactivo o esperando un comando, pero cuyo nivel de anidamiento de transacción ( @@TRANCOUNT , open_tran desde sysprocesses ) es mayor que cero. Esto puede ocurrir si la aplicación experimenta un tiempo de espera de la consulta o si se produce una cancelación sin emitir también 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 responsable de esto, ya que SQL Server no puede dar por hecho que se debe revertir una transacción completa debido a que se cancela una única consulta. El tiempo de espera de la consulta o cancelar aparecerá como un evento de señal de atención para el SPID en la traza del analizador.

    Para demostrar esto, emita la siguiente consulta desde el analizador de consultas:

    BEGIN TRAN
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    

    Mientras se ejecuta la consulta, haga clic en el botón rojo Cancelar . Una vez cancelada la consulta, SELECT @@TRANCOUNT indica que el nivel de anidamiento de la transacción es uno. Si hubiera sido una DELETE consulta o UPDATE HOLDLOCK se hubiera usado en el SELECT , todos los bloqueos adquiridos se mantendrán todavía. Incluso con la consulta anterior, si otra consulta hubiera adquirido y mantenido bloqueos antes en la transacción, se seguiría reteniendo cuando SELECT se canceló la anterior.

    Resoluciones

    • Las aplicaciones deben administrar correctamente los niveles de anidamiento de transacciones o pueden causar un problema de bloqueo tras la cancelación de la consulta de esta manera. Esto puede realizarse de varias maneras:

      1. En el controlador de errores de la aplicación cliente, envíe un IF @@TRANCOUNT > 0 ROLLBACK TRAN error después de cualquier error, incluso si la aplicación cliente no cree que una transacción está abierta. Esto es necesario, ya que un procedimiento almacenado llamado durante el lote podría haber iniciado una transacción sin el conocimiento de la aplicación cliente. Algunas condiciones, como cancelar la consulta, impedir que se ejecute el procedimiento después 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 no se ejecutará en esos casos.

      2. Use SET XACT_ABORT ON para la conexión o en cualquier procedimiento almacenado que inicie transacciones y que no se limpie tras un error. En el caso de que se produce un error en tiempo de ejecución, esta configuración anulará cualquier transacción abierta y devolverá el control al cliente.

        Nota

        No se ejecutarán las instrucciones T-SQL que siguen a la instrucción que causó el error.

      3. Si se usa la agrupación de conexiones en una aplicación que abre la conexión y ejecuta un pequeño número de consultas antes de volver a liberar la conexión al grupo, como una aplicación basada en Web, la deshabilitación de la agrupación de conexiones temporalmente puede ayudar a aliviar el problema hasta que se modifique la aplicación cliente para controlar los errores adecuadamente. Al deshabilitar la agrupación de conexiones, la liberación de la conexión provocará un cierre de sesión físico de la conexión de SQL Server, lo que hará que el servidor revierta las transacciones abiertas.

      4. Si la agrupación de conexiones está habilitada y el servidor de destino es SQL Server 2000, la actualización del equipo cliente a MDAC 2,6 o posterior puede ser beneficiosa. Esta versión de los componentes de MDAC agrega código al controlador ODBC y al proveedor OLE DB para que la conexión se restablezca antes de su reutilización. Esta llamada para sp_reset_connection anular cualquier transacción iniciada por el servidor (las transacciones DTC iniciadas por la aplicación cliente no se ven afectadas), restablece la base de datos predeterminada, las opciones set, etc.

        Nota

        La conexión no se restablece hasta que se vuelva a usar desde el grupo de conexiones, por lo que es posible que un usuario pueda abrir una transacción y, a continuación, libere la conexión al grupo de conexión, pero no se reutilizará durante varios segundos, durante el que la transacción permanecerá abierta. Si no se reutiliza la conexión, la transacción se anulará cuando se agote el tiempo de espera de la conexión y se quitará 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.

    • En realidad, esta clase de problema de bloqueo también puede ser un problema de rendimiento y requerir que lo siga como tal. Si se puede reducir el tiempo de ejecución de la consulta, no se producirá el tiempo de espera de la consulta o la cancelación. Es importante que la aplicación pueda controlar el tiempo de espera o cancelar los escenarios en caso de que se produzcan, pero también se puede beneficiar del examen del rendimiento de la consulta.

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

    Después de enviar una consulta al servidor, todas las aplicaciones deben recuperar inmediatamente todas las filas de resultados hasta que se completen. Si una aplicación no recupera todas las filas de resultados, se pueden dejar bloqueos en las tablas, bloqueando a otros usuarios. Si está usando una aplicación que envía instrucciones SQL de forma transparente al servidor, la aplicación debe recuperar 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 apoyo a la toma de decisiones.

    Solución:

    Se debe volver a escribir la aplicación para recuperar todas las filas del resultado hasta su finalización.

  4. Bloqueo causado por un interbloqueo cliente/servidor distribuido

    A diferencia de un interbloqueo convencional, un interbloqueo distribuido no se detecta mediante el administrador de bloqueo RDBMS. Esto se debe al hecho de que solo uno de los recursos involucrados en el interbloqueo es un bloqueo de SQL Server. El otro lado del interbloqueo está en el nivel de la aplicación cliente, a través del cual SQL Server no tiene control. A continuación se muestran dos ejemplos de cómo puede ocurrir esto y las posibles formas en que la aplicación puede evitarlo.

    • Interbloqueo distribuido de cliente/servidor con un único subproceso de cliente

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

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

      En el caso anterior, un único subproceso de la aplicación cliente tiene dos conexiones abiertas. Envía de forma asincrónica una operación de SQL en dbproc1. Esto significa que no espere a que se realice la llamada para volver. A continuación, la aplicación envía otra operación SQL en dbproc2 y espera a que los resultados comiencen a procesar los datos devueltos. Cuando los datos comienzan a devolverse (lo que dbproc primero responde), se supone que es dbproc1), se procesa para finalizar todos los datos devueltos en ese dbproc. Recupera los resultados de dbproc1 hasta que SPID1 se bloquea en un bloqueo mantenido por SPID2 (porque las dos consultas se ejecutan de forma asincrónica en el servidor). En este momento, dbproc1 esperará indefinidamente más datos. SPID2 no está bloqueado en un bloqueo, pero intenta enviar datos a su cliente, dbproc2. Sin embargo, dbproc2 está correctamente bloqueado en dbproc1 en el nivel de la aplicación, ya que el único subproceso de ejecución para la aplicación está en uso por parte de dbproc1. Esto da como resultado un interbloqueo que SQL Server no puede detectar ni resolver porque solo uno de los recursos implicados es un recurso de SQL Server.

    • Interbloqueo distribuido de cliente/servidor con un subproceso por conexión

      Incluso si existe un subproceso independiente para cada conexión en el cliente, es posible que se produzca una variación de este interbloqueo distribuido, como se muestra en el siguiente ejemplo.

      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 dbproc2 y SPID2, que ejecutan una SELECT instrucción con la intención de realizar un procesamiento de fila en el momento dado y de entregar cada fila a través de un búfer a dbproc1 para una INSERT UPDATE instrucción, o DELETE en la misma tabla. Finalmente, SPID1 (la realización del INSERT , UPDATE o DELETE ) se bloquea en un bloqueo mantenido por SPID2 (realizando el SELECT ). SPID2 escribe una fila de resultado en el cliente dbproc2. A continuación, Dbproc2 intenta pasar la fila de un búfer a dbproc1, pero busca dbproc1 está ocupado (está bloqueado y espera SPID1 para finalizar el actual INSERT , que está bloqueado en SPID2). En este momento, el dbproc2 se bloquea en el nivel de la aplicación mediante la dbproc1 cuyo SPID (SPID1) se bloquea en el nivel de la base de datos mediante SPID2. Una vez más, esto da como resultado un interbloqueo que SQL Server no puede detectar ni resolver porque solo uno de los recursos implicados es un recurso de SQL Server.

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

    Resoluciones

    Hay dos soluciones confiables para usar un tiempo de espera de consulta o conexiones enlazadas.

    • Tiempo de espera de consulta

      Cuando se ha proporcionado el tiempo de espera de una consulta, si se produce el interbloqueo distribuido, se romperá cuando ocurra el tiempo de espera. Consulte la documentación de DB-Library o ODBC para obtener más información sobre el uso de un tiempo de espera de consulta.

    • Conexiones enlazadas

      Esta característica permite que un cliente que tenga varias conexiones las enlace a un único espacio de transacción, por lo que las conexiones no se bloquean entre sí. Para obtener más información, consulte el tema "usar conexiones enlazadas" en los libros en pantalla de SQL Server 7,0.

  6. Bloqueo causado por un SPID que está en un estado de oro, o reversión

    Se revertirá una consulta de modificación de datos que se haya eliminado, o que se haya cancelado fuera de una transacción definida por el usuario. Esto también puede ocurrir como un efecto secundario del reinicio del equipo cliente y su sesión de red se desconectará. Del mismo modo, se revertirá una consulta seleccionada como víctima del interbloqueo. A menudo, una consulta de modificación de datos no se puede deshacer más rápido de lo que los cambios se aplicaron inicialmente. Por ejemplo, si DELETE INSERT UPDATE se hubiera estado ejecutando una instrucción, o durante una hora, la reversión podría tardar al menos una hora en revertir. Este es el comportamiento esperado, ya que se deben revertir los cambios realizados, o bien se podría poner en peligro la integridad transaccional y física de la base de datos. Como esto debe ocurrir, SQL Server marca el SPID en un estado dorado o rollback (lo que significa que no se puede eliminar ni seleccionar como víctima del interbloqueo). A menudo, esto se puede identificar observando el resultado de sp_who , que puede indicar el comando de reversión. La columna Estado de sys.sysprocesses indicará un estado de reversión, que también aparecerá en la sp_who salida o en el monitor de actividad de SQL Server Management Studio.

    Solución:

    Debe esperar a que el SPID termine de revertir los cambios realizados.

    Si el servidor se apaga en medio de esta operación, la base de datos estará en modo de recuperación tras el reinicio y será inaccesible hasta que se hayan procesado todas las transacciones abiertas. La recuperación de inicio toma básicamente la misma cantidad de tiempo por transacción que la recuperación en tiempo de ejecución, y no se puede obtener acceso a la base de datos durante este período. Por lo tanto, forzar al servidor a bajar para corregir un SPID en un estado de reversión a menudo será una contraproducción.

    Para evitar esta situación, no realice grandes procesos por lotes INSERT , UPDATE u DELETE operaciones durante las horas ocupadas en sistemas OLTP. Si es posible, realice operaciones durante períodos de baja actividad.

  7. Bloqueo causado por una conexión huérfana

    Si la aplicación cliente captura o la estación de trabajo cliente se reinicia, es posible que la sesión de red en el servidor no se cancele inmediatamente en algunas condiciones. Desde el punto de vista del servidor, el cliente todavía parece estar presente y los bloqueos adquiridos pueden seguir retenidos. Para obtener más información, vea cómo solucionar problemas de conexiones huérfanas en SQL Server.

    Solución:

    Si la aplicación cliente se ha desconectado sin limpiar sus recursos correctamente, puede finalizar el SPID mediante el KILL comando. El KILL comando toma el valor SPID como entrada. Por ejemplo, para eliminar SPID 9, emita el comando siguiente:

    KILL 9
    

    Nota

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

Implicación de aplicaciones en problemas de bloqueo

Puede que la tendencia se Centre en los problemas de plataforma y ajuste del servidor cuando se enfrente a un problema de bloqueo. Sin embargo, esto no suele dar lugar a una solución y puede absorber la energía y el tiempo mejor al examinar la aplicación cliente y las consultas que envía. Independientemente del nivel de visibilidad que exponga la aplicación en relación con las llamadas de base de datos que se realicen, un problema de bloqueo no obstante, con frecuencia requiere la inspección de las instrucciones SQL exactas enviadas por la aplicación y el comportamiento exacto de la aplicación con respecto a la cancelación de consultas, la administración de conexiones, la recuperación de todas las filas de resultados, etc. Si la herramienta de desarrollo no permite el control explícito de la administración de conexiones, la cancelación de consultas, el tiempo de espera de consulta, la obtención de resultados, etc., es posible que no se puedan resolver los problemas de bloqueo. Este potencial debe examinarse atentamente antes de seleccionar una herramienta de desarrollo de aplicaciones para SQL Server, especialmente para los entornos OLTP críticos para la empresa.

Es fundamental que se ejerza mucha atención 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 transacción para cada consulta. Cada consulta y transacción debe ser lo más ligera posible. Se debe ejercer una buena disciplina de administración de conexiones. Si no lo hace, es posible que parezca que la aplicación tiene un rendimiento aceptable en un número reducido de usuarios, pero el rendimiento puede disminuir significativamente a medida que el número de usuarios se escala hacia arriba.

Con el diseño adecuado de la aplicación y de la consulta, SQL Server puede admitir muchos miles de usuarios simultáneos en un solo servidor, con poco bloqueo.