INF: Descripción y resolver problemas de bloqueo de SQL Server

Se aplica a: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Standard Edition

Resumen


En este artículo, el término "conexión" se refiere a una sola sesión ha iniciado la sesión de la base de datos. Cada conexión aparece como un identificador de sesión (SPID). Cada uno de estos SPID se denomina menudo a un proceso, aunque no es un contexto de proceso independiente en el sentido habitual. En su lugar, cada SPID consiste en los recursos de servidor y estructuras de datos necesarias para atender las solicitudes de una única conexión desde un cliente dado. Una aplicación de cliente solo puede tener una o varias conexiones. Desde la perspectiva de SQL Server, no hay ninguna diferencia entre conexiones múltiples desde una aplicación de cliente único en un único equipo cliente y conexiones múltiples desde varias aplicaciones de cliente o varios equipos cliente. Una conexión puede bloquear otra conexión, independientemente de si procede de la misma aplicación o de aplicaciones diferentes en dos equipos cliente diferentes.

Más información


El bloqueo es una característica inevitable de cualquier sistema de administración de bases de datos relacionales (RDBMS) con concurrencia basada en bloqueo En SQL Server, bloqueo se produce cuando un SPID mantiene un bloqueo en un recurso específico y un segundo SPID intenta adquirir un tipo de bloqueo en conflicto sobre el mismo recurso. Normalmente, el intervalo de tiempo para que el SPID primero bloquea el recurso es muy pequeño. Cuando libera el bloqueo, la segunda conexión es libre de adquirir su propio bloqueo en el recurso y continuar el proceso. Este comportamiento es normal y puede ocurrir muchas veces durante el curso de un día con ningún efecto apreciable en el rendimiento del sistema.

El contexto de transacción y de duración de una consulta determinar cuánto tiempo se mantienen sus bloqueos y, por tanto, su impacto en otras consultas. Si la consulta no se ejecuta dentro de una transacción (y no se usan sugerencias de bloqueos), los bloqueos de las instrucciones SELECT sólo se realizarán en un recurso en el momento en que realmente se lean, no durante la vida de consulta. Para las instrucciones INSERT, UPDATE y DELETE, los bloqueos se mantienen durante la duración de la consulta, tanto para la consistencia de los datos y para permitir que la consulta se desharán si es necesario.

Para las consultas ejecutadas dentro de una transacción, la duración para el cual se mantienen los bloqueos están determinados por el tipo de consulta, el nivel de aislamiento de la transacción y si las sugerencias de bloqueo son o no usadas en la consulta. Para obtener una descripción de bloqueo, sugerencias de bloqueo y niveles de aislamiento, vea los siguientes temas en libros en pantalla de SQL Server:
  • Bloqueo en el motor de base de datos
  • Personalizar el bloqueo y versiones de fila
  • Modos de bloqueo
  • Compatibilidad de bloqueos
  • Niveles de aislamiento basado en versiones de fila en el motor de base de datos
  • Controlar transacciones (motor de base de datos)
Cuando el bloqueo y el bloqueo de aumento hasta el punto donde existe un efecto perjudicial sobre el rendimiento del sistema, suele ser debido a una de las siguientes razones:
  • Un SPID mantiene bloqueos en un conjunto de recursos durante un período prolongado de tiempo antes de soltarlos. Este tipo de bloqueo se resuelve sólo con el tiempo, pero puede provocar una degradación del rendimiento.
  • Un SPID mantiene bloqueos en un conjunto de recursos y no libera nunca. Este tipo de bloqueo no se resuelve a sí mismo e impide el acceso a los recursos afectados indefinidamente.
En el primer caso, el problema de bloqueo se resuelve sólo con el tiempo como el SPID libera los bloqueos. Sin embargo, la situación puede ser muy fluida como causa de SPID diferente en distintos recursos de bloqueo con el tiempo, la creación de un destino en movimiento. Por este motivo, estas situaciones pueden ser difíciles solucionar problemas mediante el Administrador corporativo de SQL Server o las consultas SQL individuales. La segunda situación se produce en un estado coherente que puede ser más fácil de diagnosticar.

Recopilación de información de bloqueo

Para contrarrestar la dificultad de solución de problemas de bloqueo, un administrador de base de datos puede utilizar secuencias de comandos SQL que supervisen constantemente el estado de bloqueo y el bloqueo en SQL Server. Estas secuencias de comandos pueden proporcionar instantáneas de instancias específicas con el tiempo, llevando a una visión general del problema. Para obtener una descripción de cómo supervisar el bloqueo de secuencias de comandos SQL, consulte los artículos siguientes en Microsoft Knowledge Base:
271509 cómo supervisar el bloqueo en SQL Server 2005 y SQL Server 2000

Las secuencias de comandos en este artículo se realizará las tareas siguientes. Siempre que sea posible, se proporciona el método para obtener esta información desde SQL Server Management Studio.
  1. Identificar el SPID (identificador de sesión) al principio de la cadena de bloqueo y la instrucción SQL.
    Además de utilizar las secuencias de comandos en el artículo de Knowledge Base mencionado anteriormente, puede identificar la cabeza de la cadena de bloqueo mediante el uso de características que se proporcionan a través de SQL Server Management Studio. Para ello, utilice uno de los métodos siguientes:
    • Haga clic en el objeto de servidor, expanda informes, expanda Informes estándar y, a continuación, haga clic en actividad: todas las transacciones de bloqueo. Este informe muestra las transacciones a la cabeza de la cadena de bloqueo. Si expande la transacción, el informe mostrará las transacciones que están bloqueadas por la transacción de cabeza. Este informe también muestra el "bloqueo SQL Statement" y "bloqueado SQL."
    • Utilice DBCC INPUTBUFFER(<spid>) para buscar la última instrucción enviada por un SPID.
  2. Encontrar el nivel de anidamiento de transacción y el estado de proceso de lo SPID de bloqueo.
    El nivel de anidamiento de transacción de un SPID está disponible en la variable global @@TRANCOUNT. Sin embargo, se puede determinar desde fuera el SPID consultando la tabla sysprocesses como sigue:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    El valor devuelto es el valor de @@TRANCOUNT para el SPID. Muestra el nivel de anidamiento de transacción para el SPID de bloqueo, que a su vez puede explicar por qué mantiene bloqueos. Por ejemplo, si el valor es mayor que cero, el SPID está en medio de una transacción (en cuyo caso se espera que conserva ciertos bloqueos que ha adquirido, según el nivel de aislamiento de transacción).

    También puede comprobar para ver si cualquier transacción abierta a largo plazo existe en la base de datos con DBCC OPENTRAN
    database_name.

Recopilar información de seguimiento de SQL Server Profiler

Además de la información anterior, a menudo es necesario capturar una traza del analizador de las actividades en el servidor para investigar un problema de bloqueo en SQL Server. Si un SPID ejecuta varias instrucciones dentro de una transacción, sólo la última declaraciónque envió se mostrarán en el informe, el búfer de entrada o la salida de monitor de actividad. Sin embargo, uno de los comandos anteriores puede ser la razón bloqueos que aún se mantienen. Una traza del Analizador le permitirá ver todos los comandos ejecutados por un SPID en la transacción actual. Los pasos siguientes le ayudan a configurar SQL Server Profiler para capturar una traza.
  1. Abra SQL Server Profiler.
  2. En el menú archivo, elija nuevo y, a continuación, haga clic en seguimiento.
  3. En la ficha General, especifique un nombre de traza y un nombre de archivo para capturar los datos.

    Importante: El archivo de seguimiento debe escribirse en un disco rápido local o compartido. Evitar el seguimiento a una unidad de disco o de red lenta. Asegúrese también de que servidor procesa se seleccionan datos de seguimiento.
  4. En la ficha Selección de eventos , haga clic en Mostrar todos los eventos y las casillas de verificación Mostrar todas las columnas .
  5. En la ficha Selección de eventos , agregue los tipos de eventos que se enumeran en la tabla 1 para su seguimiento.

    Además, puede incluir los tipos de sucesos adicionales que se enumeran en la tabla 2 para obtener más información. Si está ejecutando en un entorno de producción de alto volumen, puede decidir utilizar sólo los eventos en la tabla 1, como son normalmente suficientes para solucionar los problemas de bloqueo más. Incluyendo los eventos adicionales en la tabla 2 puede que sea más fácil determinar rápidamente el origen de un problema o estos eventos pueden ser necesarios para identificar la instrucción culpable en un procedimiento de múltiples instrucciones. Sin embargo, incluidos los eventos en la tabla 2 también agregará a la carga en el sistema y aumentar el tamaño de salida de la traza.
Tabla 1: Tipos de eventos
TítuloEvento
Errores y advertenciasExcepción
Errores y advertenciasAtención
Auditoría de seguridadAudit Login
Auditoría de seguridadAudit Logout
SesionesConexión existente
Procedimientos almacenadosRPC:Starting
TSQLSQL:BatchStarting

Tabla 2: Tipos de sucesos adicionales
TítuloEvento
TransaccionesDTCTransaction
TransaccionesSQLTransaction
Procedimientos almacenadosRPC:Completed
TSQLSQL:BatchCompleted
Procedimientos almacenadosSP:StmtStarting
Procedimientos almacenadosSP:StmtCompleted

Para obtener más información acerca de cómo utilizar al SQL Server Profiler, consulte Libros en pantalla de SQL Server.

Identificar y resolver escenarios comunes de bloqueo

Examinando la información anterior, puede determinar la causa de la mayoría de problemas de bloquea. El resto de este artículo es una explicación de cómo utilizar esta información para identificar y resolver algunos escenarios comunes de bloqueo. Este análisis se supone que ha utilizado los scripts de bloqueo en el artículo 271509 (mencionados anteriormente) para capturar información sobre los SPID de bloqueo y ha realizado un seguimiento del analizador con los eventos descritos anteriormente.

Visualizar la salida de secuencia de comandos de bloqueo

Examine la salida sys.sysprocesses para determinar las cabeceras de las cadenas de bloqueo
Si no especifica un modo rápido para las secuencias de comandos de bloqueo, habrá una sección titulada "SPID en la cabeza de las cadenas de bloqueo" que enumera los SPID que están bloqueando otros SPID en la salida de la secuencia de comandos.
SPIDs at the head of blocking chains
Si especifica la opción fast, todavía puede determinar los cabezales de bloqueo mirando la salida sys.sysprocesses y siguiendo la jerarquía de lo SPID que se indica en la columna bloqueada.
Examine la salida sys.sysprocesses para obtener información sobre los SPID al principio de la cadena de bloqueo.
Es importante evaluar los siguientes campos sys.sysprocesses :

Estado

Esta columna muestra el estado de un SPID determinado. Normalmente, un estado de suspensión indica que el SPID ha finalizado su ejecución y está esperando la aplicación enviar otra consulta o proceso por lotes. El estado puede ejecutar, Ejecutaro sos_scheduler_yield indica que el SPID está procesando una consulta. La tabla siguiente proporciona explicaciones breves acerca de los distintos valores de estado.
EstadoSignificado
FondoEl SPID está ejecutando una tarea en segundo plano, como la detección de interbloqueos.
Modo de suspensiónEl SPID no está ejecutando actualmente. Esto normalmente indica que el SPID está esperando un comando de la aplicación.
EjecutaEl SPID se está ejecutando en un programador.
Puede ejecutarEl SPID está en la cola ejecutable de un programador y esperar para obtener la hora de programador.
Sos_scheduler_yieldSe estaba ejecutando el SPID, pero ha cedido voluntariamente su segmento de tiempo en el programador para permitir que otro SPID adquirir la hora de programador.
SuspendidoEl SPID está esperando a un evento, como un bloqueo o un pestillo.
ReversiónEl SPID es en la reversión de una transacción.
DefwakeupIndica que el SPID está esperando un recurso que está en proceso de ser liberado. El campo waitresource debe indicar el recurso en cuestión.

Open_tran

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

Waittime, lastwaittype y waittype

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

Para una breve descripción de los diferentes lastwaittype y waittype valores, consulte el artículo siguiente en Microsoft Knowledge base:
822101 descripción de las columnas waittype y lastwaittype en la tabla master.dbo.sysprocesses en SQL Server 2000 y SQL Server 2005

Para obtener más información acerca de sys.dm_os_wait_stats, consulte los libros en pantalla de SQL Server.

El valor waittime puede utilizarse para determinar si el SPID está realizando un progreso. Cuando una consulta contra la tabla sys.sysprocesses devuelve un valor en la columna de waittime es menor que el valor waittime de una consulta anterior de sys.sysprocesses, esto indica que se adquirió el bloqueo previo y publicado y está esperando una cerradura nueva (suponiendo que waittime distinto de cero). Esto puede comprobarse comparando el waitresource entre sys.sysprocesses salida.

Waitresource

Este campo indica el recurso que está esperando un SPID. La tabla siguiente enumeran los formatos waitresource comunes y su significado:
RecursoFormatoEjemplo
TablaDatabaseID:ObjectID:IndexIDFICHA: 5:261575970:1
En este caso, 5 ID de la base de datos es la base de datos de ejemplo pubs y objeto 261575970 ID es la tabla titles y 1 es el índice agrupado.
PáginaDatabaseID:FileID:PageIDPÁGINA: 5:1:104
En este caso, la base de datos ID 5 es pubs, ID 1 es el archivo de datos principal y 104 es una página que pertenece a la tabla titles .

Para identificar el identificador de objeto al que pertenece la página, utilice el comando DBCC PAGE (dbid, fileid, pageid, output_option) y mirar el m_objId. Por ejemplo:
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
ClaveDatabaseID:Hobt_id (valor de Hash de clave de índice)CLAVE: 5:72057594044284928 (3300a4f361aa)

En este caso, la base de datos ID 5 es Pubs, Hobt_ID 72057594044284928 corresponde a no agrupado IdÍndice 2 para el id de objeto 261575970 (tablatítulos ). Utilice la vista de catálogo sys.partitions para asociar el hobt_id a un identificador de índice determinado y el identificador de objeto. No hay ninguna manera de unhash el hash de clave de índice para un valor de clave de índice específico.
FilaDatabaseID:FileID:PageID:Slot(row)RID: 5:1:104:3

En este caso, la base de datos ID 5 es pubs, ID 1 es el archivo de datos principal, 104 es una página que pertenece a la tabla titles y ranura 3 indica la posición de la fila en la página.
CompilarDatabaseID:ObjectID [[compilación]]FICHA: 5:834102012 [[compilación]] no es un bloqueo de tabla, sino más bien una compilación bloquea en un procedimiento almacenado. Identificador de la base de datos 5 es pubs, objeto ID 834102012 es usp_myprocedure del procedimiento almacenado. Consulte 263889 de artículo de Knowledge Base para obtener más información acerca del bloqueo causado por bloqueos de compilación.
Otras columnas

Las restantes columnas sys.sysprocesses pueden proporcionar la penetración en la raíz de un problema así. Su utilidad varía dependiendo de las circunstancias del problema. Por ejemplo, puede determinar si el problema ocurre sólo desde determinados clientes (hostname), en determinadas bibliotecas de red (net_library), cuando el último lote enviado por un SPID era (last_batch) y así sucesivamente.
Examine la salida de DBCC INPUTBUFFER.
Para cualquier SPID al principio de una cadena de bloqueo o con un waittype distinto de cero, la secuencia de comandos de bloqueo ejecute DBCC INPUTBUFFER para determinar la consulta actual para ese SPID.

En muchos casos, ésta es la consulta que está causando los bloqueos que están bloqueando otros usuarios a realizarse. Sin embargo, si el SPID está dentro de una transacción, el que se han adquirido bloqueos por una consulta ejecutada con anterioridad, no lo actual. Por lo tanto, también debería ver el resultado de Profiler para el SPID, no sólo el inputbuffer.

Nota: Dado que la secuencia de comandos de bloqueo consta de varios pasos, es posible que un SPID puede aparecer en la primera sección como la cabeza de una cadena de bloqueo, pero por el momento en que se ejecuta la consulta de DBCC INPUTBUFFER, ya no se bloquea y no se captura el INPUTBUFFER. Esto indica que el bloqueo está resolviendo propia para dicho SPID y puede o no puede ser un problema. En este punto, puede utilizar la versión rápida de la secuencia de comandos de bloqueo para intentar asegurar que captura el inputbuffer antes de que borra (aunque todavía no hay ninguna garantía) o ver los datos del analizador de ese periodo de tiempo para determinar qué consultas se estaba ejecutando el SPID.

Ver los datos del generador de perfiles

Ver datos del generador de perfiles eficaz es extremadamente valiosa en la resolución de problemas de bloqueo. Lo más importante darse cuenta de que no tiene que mirar de todo lo se captura; sea selectivo. El analizador proporciona capacidades de forma eficaz permite ver los datos capturados. En el cuadro de diálogo Propiedades (en el menú archivo , haga clic en Propiedades), Analizador le permite limitar los datos mostrados por quitar columnas de datos o eventos, agrupar (ordenar) por las columnas de datos y la aplicación de filtros. Puede buscar el seguimiento completo o sólo una columna específica para valores específicos (en el menú Edición , haga clic en Buscar). También puede guardar los datos del generador de perfiles a una tabla de SQL Server (en el menú archivo , elija Guardar como y, a continuación, haga clic en tabla) y ejecutar consultas SQL contra él.

Tenga cuidado de que realizar el filtrado sólo en un archivo de traza guardada anteriormente. Si realiza estos pasos en una traza activa, se arriesga a perder los datos que se ha capturado desde que se inició la traza. Guardar una traza activa en un archivo o tabla primero (en el menú archivo , haga clic en Guardar como) y, a continuación, vuelva a abrirlo (en el menú archivo , haga clic en Abrir) antes de continuar. Cuando se trabaja en un archivo de traza guardada, el filtrado no elimina permanentemente 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 centrar las búsquedas.

Qué buscar:
  • ¿Qué comandos tiene el SPID al principio de una cadena de bloqueo ejecutada dentro de la transacción actual?
    Filtrar los datos de seguimiento para un SPID determinado que está a la cabeza de una cadena de bloqueo (en el menú archivo , haga clic en Propiedades, a continuación, en la pestaña filtros , especifique el valor SPID). A continuación, puede examinar los comandos que se ha ejecutado antes de la hora en que fue bloqueando otros SPID. Si incluye los eventos de transacción, puede identificar fácilmente cuando se inicia una transacción. De lo contrario, puede buscar la columna de texto BEGIN, guardar, confirmar o deshacer las operaciones de transacción. Utilice el valor open_tran de la tabla sysprocesses para asegurarse de que captura todos los eventos de transacción. Conocer los comandos ejecutados y el contexto de transacción le permitirá determinar por qué un SPID está reteniendo bloqueos.

    Recuerde, puede quitar las columnas de datos y eventos. En lugar de en ambos iniciando y sucesos completados, elija uno. Si los SPID de bloqueo no son procedimientos almacenados, quitar el
    SP: iniciando o SP: completado eventos; los eventos SQLBatch y RPC mostrará la llamada al procedimiento. Ver sólo 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 anteriores, la columna duración mostrará el tiempo de ejecución de la consulta. Esto puede ayudarle a identificar las consultas de larga ejecución que causan el bloqueo. Para determinar por qué la consulta está realizando lentamente, ver la CPU, lecturay escribe columnas, así como los eventos de Plan de ejecución .

Categorización de escenarios comunes de bloqueo

La siguiente tabla asigna los síntomas comunes a sus causas probables. El número indicado en la columna de escenario corresponde al número en la sección "Common bloqueo escenarios y resoluciones" de este artículo a continuación. Las columnas Waittype, Open_Trany estado consulte información de sysprocesses . El resuelve? columna indica si el bloqueo se resolverá en su propio.

EscenarioWaittypeOpen_TranEstado¿Resuelve?Otros síntomas
1Distinto de cero> = 0puede ejecutarSí, cuando finaliza la consulta.Columnas Physical_IO, CPU o Memusage aumentará con el tiempo. Duración de la consulta será alto finalizada.
20x0000> 0modo de suspensiónNo, pero puede ser sacrificado SPID.Puede aparecer una señal de atención en la traza del analizador para el SPID que indica un tiempo de espera de la consulta o se ha producido la cancelación.
30x0000> = 0puede ejecutarNo No se resolverá hasta que el cliente obtiene todas las filas o cierra la conexión. SPID puede ser sacrificado, pero puede tardar hasta 30 segundos.Si open_tran = 0 y el SPID mantiene bloqueos mientras el nivel de aislamiento de transacción está predeterminado (leer COMMMITTED), esto es una causa probable.
4Varía> = 0puede ejecutarNo No se resolverá hasta que el cliente cancela las consultas o cierra las conexiones. SPID pueden ser sacrificados, pero pueden tardar hasta 30 segundos.La columna hostname en sysprocesses para el SPID al principio de una cadena de bloqueo será el mismo que el SPID está bloqueando.
50x0000> 0reversiónSí.Puede aparecer una señal de atención en la traza del analizador para el SPID que indica un tiempo de espera de la consulta se ha producido la cancelación o simplemente una instrucción rollback emitida.
60x0000> 0modo de suspensiónFinalmente. Cuando Windows NT determina que la sesión ya no está activa, se interrumpirá la conexión de SQL Server.El valor last_batch en sysprocesses es mucho antes que la hora actual.

Escenarios de bloqueo y soluciones comunes

Los escenarios siguientes tendrá las características enumeradas en la tabla anterior. Esta sección proporciona detalles adicionales cuando sea aplicable, así como métodos para la resolución.
  1. Bloqueo causado por una ejecución normalmente consulta con un tiempo de ejecución

    Solución:
    La solución a este tipo de problema de bloqueo es buscar formas de optimizar la consulta. En realidad, esta clase de problema de bloqueo puede sólo un problema de rendimiento y requieren que perseguir como tal. Para obtener información sobre solución de problemas de una consulta de ejecución lenta específica, consulte el siguiente artículo de Microsoft Knowledge Base:
    243589 cómo solucionar problemas de consultas de ejecución lenta en SQL Server 7.0 o versiones posteriores

    Para la solución de problemas de rendimiento de aplicación general, consulte el siguiente artículo de Knowledge Base:
    224587 Cómo: solucionar problemas de rendimiento de aplicaciones con SQL Server

    Para obtener más información, vea el tema de libros en pantalla de SQL Server 2008 temas de optimización y supervisión del rendimiento en el siguiente sitio Web de MSDN:Si tiene una consulta de larga ejecución que está bloqueando otros usuarios y no se pueden optimizar, considere la posibilidad de mover desde un entorno OLTP a un sistema de soporte de toma de decisiones.
  2. Bloqueo causado por un SPID inactivo que ha perdido el seguimiento del nivel de anidamiento de transacción

    Este tipo de bloqueo a menudo puede identificarse por un SPID que está inactivo o en espera de un comando, pero cuyo nivel de anidamiento de transacción (@@TRANCOUNT, open_tran de sysprocesses) es mayor que cero. Esto puede ocurrir si la aplicación experimenta un tiempo de espera de la consulta o emite un cancelar sin emitir también el número requerido de instrucciones ROLLBACK y COMMIT. Cuando un SPID recibe un tiempo de espera de consulta o en Cancelar, expirarán la consulta actual y el lote, pero no automáticamente deshacer o confirmar la transacción. La aplicación es responsable de esto, como SQL Server no puede suponer que una transacción completa debe deshacerse simplemente debido a una sola consulta que se canceló. El tiempo de espera de consulta o cancelar aparecerá como un evento de señal de atención para el SPID en la traza del analizador.

    Para demostrar esto, escriba la siguiente consulta simple 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 color rojo botón Cancelar . Después de cancela la consulta, seleccione @@TRANCOUNT indica que el nivel de anidamiento de transacción es uno. Esto fue una eliminación o una consulta de actualización o hubiera utilizado en la selección, todos los bloqueos adquiridos todavía se retenía HOLDLOCK. Incluso con la consulta anterior, si había adquirido y se mantiene bloqueos anteriormente en la transacción, otra consulta ¿todavía se disponían de cuando se ha cancelado la selección anterior.

    Resoluciones:

    • Las aplicaciones deben administrar correctamente los niveles de anidamiento de transacción o pueden causar un problema de bloqueo tras la cancelación de la consulta de esta manera. Esto se puede lograr de varias maneras:
      1. En el controlador de errores de la aplicación cliente enviar IF @@TRANCOUNT > 0 es TRAN ROLLBACK siguiente cualquier error, incluso si la aplicación cliente no considera que una transacción abierta. Esto es necesario, ya que un procedimiento almacenado que se llama durante el proceso se haya iniciado una transacción sin el conocimiento de la aplicación cliente. Tenga en cuenta que ciertas condiciones, como cancelar la consulta, evitar que el procedimiento ejecutar después de la instrucción actual, incluso que si el procedimiento tiene lógica para comprobar IF @@ERROR <> 0 y anular la transacción, no se ejecutará este código reversión de tal casos.
      2. Utilice SET XACT_ABORT ON para la conexión, o en cualquier procedimiento almacenado que comienzan las transacciones y no limpiar después de un error. En el caso de un error en tiempo de ejecución, esta configuración se anular las transacciones abiertas y devolver el control al cliente. Tenga en cuenta que no se ejecutará instrucciones T SQL que sigue a la instrucción que provocó el error.
      3. Si se utiliza la agrupación de conexiones en una aplicación que abre la conexión y se ejecuta un número reducido de consultas antes de liberar la conexión de nuevo a la agrupación, por ejemplo, una aplicación basada en Web, deshabilitar temporalmente la agrupación de conexiones puede ayudar a aliviar la problema hasta que se modifica la aplicación cliente para controlar los errores de forma adecuada. Al deshabilitar la agrupación de conexiones, liberando la conexión provocará un cierre de sesión física de la conexión de SQL Server, dando como resultado el servidor deshaciendo las transacciones abiertas.
      4. Si está habilitada la agrupación de conexiones y el servidor de destino es SQL Server 2000, puede ser beneficioso actualizar el equipo cliente a MDAC 2.6 o posterior. Esta versión de los componentes de MDAC agrega código para el controlador ODBC y el proveedor OLE DB para que la conexión debería ser "Restablecer" antes de ser reutilizado. Esta llamada a sp_reset_connection para aceptar anula las transacciones iniciadas por el servidor (las transacciones de DTC iniciadas por la aplicación de cliente no se ven afectadas), se restablece la base de datos predeterminada, opciones SET y así sucesivamente. Tenga en cuenta que la conexión no se restablece hasta que se reutiliza de la agrupación de conexiones, por lo que es posible que un usuario puede abrir una transacción y, a continuación, liberar la conexión a la agrupación de conexiones, pero no se puede reutilizar para varios segundos, durante el cual la transacción podría permanecer abierta. Si la conexión no se reutiliza, se anulará la transacción cuando la conexión se agota y se quita de la agrupación de conexiones. Por lo tanto, resulta óptimo para la aplicación cliente anular transacciones en su controlador de errores o utilice SET XACT_ABORT ON para evitar este retraso potencial.
    • En realidad, esta clase de problema de bloqueo puede también ser un problema de rendimiento y debe perseguir, como tal. Si el tiempo de ejecución de consulta puede disminuir el tiempo de espera de consulta o cancelar no se produciría. Es importante que la aplicación pueda controlar el tiempo de espera o cancelar escenarios se produzcan, sino también puede beneficiarse de examinar el rendimiento de la consulta.
  3. Bloqueo causado por un SPID cuya aplicación de cliente correspondiente no recuperar todas las filas de resultados hasta su finalización

    Después de enviar una consulta al servidor, todas las aplicaciones deben recuperar inmediatamente todas las filas de resultados hasta su finalización. Si una aplicación no recupera todas las filas de resultados, pueden quedar bloqueos en las tablas, bloquear a otros usuarios. Si está utilizando una aplicación que envía instrucciones SQL al servidor de forma transparente, la aplicación debe recuperar todas las filas del resultado. Si no es así (y no se puede configurar para hacerlo), quizá no pueda resolver el problema de bloqueo. Para evitar el problema, puede restringir las aplicaciones mal comportamiento a un informe o una base de datos de la toma de decisiones.

    Solución:

    La aplicación debe vuelven a escribir 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 es detectable usando el Administrador de bloqueos RDBMS. Esto es debido a que sólo uno de los recursos implicados en el interbloqueo es un bloqueo de SQL Server. El otro lado del interbloqueo es en el nivel de aplicación de cliente, sobre la que SQL Server no tiene ningún control. Las siguientes son dos ejemplos de cómo esto puede suceder y formas posibles de la aplicación pueden evitarlo.

    1. Cliente/servidor distribuidos interbloqueo con un subproceso de cliente único
      Si el cliente tiene varias conexiones abiertas y un único subproceso de ejecución, puede producirse el interbloqueo distribuido siguiente. Para mayor brevedad, el término "dbproc" utilizado aquí se refiere a la estructura de conexión de cliente.


      SPID1------blocked on lock------->SPID2
      /\ (waiting to write results
      | back to client)
      | |
      | | Server side
      | ================================|==================================
      | <-- single thread --> | Client side
      | \/
      dbproc1 <------------------- dbproc2
      (waiting to fetch (effectively blocked on dbproc1, awaiting
      next row) single thread of execution to run)
      En el caso anterior, un subproceso de la aplicación cliente tiene abiertas dos conexiones. Envía asincrónicamente una operación de SQL en dbproc1. Esto significa que no se espera en la llamada a devolver antes de continuar. A continuación, la aplicación envía otra operación de SQL en dbproc2 y aguarda los resultados para comenzar a procesar los datos devueltos. Cuando inicia viniendo datos (sea cual sea dbproc primero responde--asume esto es dbproc1), procesa completamente todos los datos devuelven en ese dbproc. Recupera los resultados de dbproc1 hasta SPID1 obtiene bloqueado en un bloqueo mantenido por SPID2 (porque las dos consultas están ejecutando asincrónicamente en el servidor). En este momento, dbproc1 esperará indefinidamente a más datos. SPID2 no está bloqueado en un bloqueo, pero intenta enviar datos a su cliente, dbproc2. Sin embargo, dbproc2 está bloqueado en dbproc1 en el nivel de aplicación como el único subproceso de ejecución de la aplicación está en uso por dbproc1. Esto da como resultado un interbloqueo que SQL Server no puede detectar ni resolver porque sólo uno de los recursos implicados es un recurso de SQL Server.
    2. Cliente/servidor distribuidos interbloqueo con un subproceso por conexión

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


      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, a excepción de dbproc2 y SPID2 ejecutan una instrucción SELECT con la intención de realizar procesamiento de fila en el momento y entregar cada fila a través de un búfer a dbproc1 para una instrucción INSERT, UPDATE, o eliminar en la misma tabla. Finalmente, SPID1 (realizar el INSERT, UPDATE o DELETE) se bloquea en un bloqueo mantenido por SPID2 (realizando la selección). SPID2 escribe una fila en la dbproc2 cliente. Dbproc2 intenta pasar la fila en un búfer para dbproc1, pero encuentra dbproc1 está ocupado (está bloqueada esperando SPID1 para terminar la INSERCIÓN actual, que se bloquea en SPID2). En este punto, dbproc2 está bloqueado en el nivel de aplicación por dbproc1 cuyo SPID (SPID1) está bloqueado en el nivel de base de datos por SPID2. Otra vez, esto da como resultado un interbloqueo que SQL Server no puede detectar ni resolver porque sólo uno de los recursos implicados es un recurso de SQL Server.
    Ambos ejemplos A y B son aspectos fundamentales que deben tener en cuenta los programadores de aplicaciones. Código de aplicaciones para controlar estos casos de forma adecuada.

    Resoluciones:

    Dos soluciones confiables son utilizar un tiempo de espera de consulta o conexiones enlazadas.

    • Tiempo de espera de consulta
      Cuando se ha proporcionado un tiempo de espera de consulta, si se produce el interbloqueo distribuido, será roto cuando, a continuación, pasa el tiempo de espera. Consulte la DB-Library o la documentación de ODBC para obtener más información acerca de cómo utilizar un tiempo de espera de consulta.
    • Conexiones enlazadas
      Esta característica permite a un cliente tener varias conexiones enlazarlos en un espacio de transacción única, por lo que las conexiones no bloquean entre sí. Para obtener más información, vea el tema "Utilizar conexiones enlazadas" en libros en pantalla de SQL Server 7.0.
  5. Bloqueo causado por un SPID que está en "Oro" o Rollback, estado

    Una consulta de modificación de datos que está muerta o cancelado fuera de una transacción definida por el usuario, se revertirá. Esto también puede ocurrir como un efecto secundario del reinicio del equipo cliente y su desconexión de sesión de red. Del mismo modo, una consulta seleccionada como víctima del interbloqueo se revertirán. Una consulta de modificación de datos a menudo no se puede deshacer cualquier más rápido de los cambios se aplicaron inicialmente. Por ejemplo, si se ejecutaba una instrucción DELETE, INSERT o UPDATE para una hora, podría lograr al menos una hora para deshacer. Éste es el comportamiento esperado, porque los cambios realizados deben poder deshacer completamente o se pondría en peligro la integridad transaccional y física de la base de datos. Dado que esto debe ocurrir, SQL Server marca el SPID en un estado "oro" o deshacer (lo que significa que no puede ser sacrificado o seleccionado como víctima del interbloqueo). A menudo puede identificarse mediante la observación de la salida de sp_who, lo que puede indicar el comando ROLLBACK. La columna estado de sys.sysprocesses indicará un estado de restauración, también aparecerán en la salida de sp_who o en Monitor de actividad de SQL Server Management Studio.
    Solución:

    Debe esperar a que el SPID terminar de deshacer los cambios realizados.

    Si el servidor se cierra en medio de esta operación, será la base de datos en modo de recuperación al reiniciar y será inaccesible hasta que todos los abiertos se procesan las transacciones. Recuperación de inicio tarda esencialmente la misma cantidad de tiempo por transacción como recuperación de tiempo de ejecución, y la base de datos es inaccesible durante este período. Así, el forzar al servidor a revisión un SPID en un estado de reversión a menudo será contraproducente.

    Para evitar esta situación, no realizar grandes lotes INSERT, UPDATE o eliminar operaciones durante las horas de disponibilidad en los sistemas OLTP. Si es posible, realizar estas operaciones durante los períodos de baja actividad.
  6. Bloqueo causado por una conexión huérfana

    Si se reinicia las capturas de la aplicación de cliente o la estación de trabajo cliente, la sesión de red en el servidor no se puede cancelar inmediatamente en algunas condiciones. Desde la perspectiva del servidor, el cliente parece estar presentes todavía y aún pueden conservarse los bloqueos adquiridos. Para obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

    137983 cómo solucionar problemas de conexiones huérfanas en SQL Server


    Solución:

    Si la aplicación cliente ha desconectado sin limpiar sus recursos de forma adecuada, puede terminar el SPID mediante el comando KILL. El comando KILL toma el valor SPID como entrada. Por ejemplo, para kill SPID 9, simplemente emita el comando siguiente:

    KILL 9

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

Implicación de la aplicación en problemas de bloqueo

Puede haber una tendencia a concentrarse en los problemas de optimización y plataforma de servidor cuando se enfrenta a un problema de bloqueo. Sin embargo, esto normalmente no conduzca a una resolución y puede absorber tiempo y energía que mejor se dirige a examinar la aplicación de cliente y las consultas que se envía. Independientemente del nivel de visibilidad de la aplicación expone con respecto a las llamadas de base de datos que se realizan, un problema de bloqueo, no obstante, suele requerir que se inspeccionen las instrucciones SQL exactas que envía la aplicación y la aplicación exacta comportamiento en cuanto a la cancelación de la consulta, administración de conexiones, la obtención de todas las filas de resultados y así sucesivamente. Si la herramienta de desarrollo no permite un control explícito sobre administración de conexiones, cancelación de consultas, tiempo de espera de consulta, la obtención de resultados etc., es podrán que no pueda resolver los problemas bloqueo. Este potencial debe examinarse estrechamente antes de seleccionar una herramienta de desarrollo de aplicaciones para SQL Server, especialmente para entornos OLTP críticos para el negocio.

Es vital que se ejerza gran cuidado durante la fase de diseño y construcción de la base de datos y la aplicación. En particular, el consumo de recursos, el nivel de aislamiento y la longitud de la ruta de acceso de transacción deben evaluarse para cada consulta. Cada consulta y la transacción deben ser lo más reducida posible. Debe ejercerse la disciplina de administración de conexión es buena. Si no se hace así, es posible que la aplicación puede parecer que tiene un rendimiento aceptable en un número bajo de usuarios, pero el rendimiento puede disminuir significativamente el número de usuarios escalas hacia arriba.

Con una aplicación adecuada y diseño de la consulta, Microsoft SQL Server es capaz de soportar varios miles de usuarios simultáneos en un solo servidor, con bloqueo poco.