Estás trabajando sin conexión, espera a que vuelva la conexión a Internet

INF: Comprender y resolver los problemas de bloqueo de SQL Server

IMPORTANTE: Este artículo ha sido traducido por un software de traducción automática de Microsoft (http://support.microsoft.com/gp/mtdetails) en lugar de un traductor humano. Microsoft le ofrece artículos traducidos por un traductor humano y artículos traducidos automáticamente para que tenga acceso en su propio idioma a todos los artículos de nuestra base de conocimientos (Knowledge Base). Sin embargo, los artículos traducidos automáticamente pueden contener errores en el vocabulario, la sintaxis o la gramática, como los que un extranjero podría cometer al hablar el idioma. Microsoft no se hace responsable de cualquier imprecisión, error o daño ocasionado por una mala traducción del contenido o como consecuencia de su utilización por nuestros clientes. Microsoft suele actualizar el software de traducción frecuentemente.

Haga clic aquí para ver el artículo original (en inglés): 224453
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 suelen conocer como proceso, aunque no es un contexto de proceso separado en sentido. En su lugar, cada SPID consiste en los recursos del servidor y las 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 más conexiones. Desde la perspectiva de SQL Server, no hay ninguna diferencia entre conexiones múltiples desde una aplicación de cliente en un único equipo cliente y conexiones múltiples desde varias aplicaciones de cliente o de 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 la simultaneidad de bloqueos. 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 en el mismo recurso. Normalmente, el intervalo de tiempo para que el primer SPID 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 varias 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 (no hay sugerencias de bloqueo se utilizan y), los bloqueos de las instrucciones SELECT sólo se celebrará un recurso en el momento en que realmente se leen, no para la duración de la consulta. Para las instrucciones INSERT, UPDATE y DELETE, se mantienen los bloqueos para 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 el hecho de bloquear sugerencias se utilizan en la consulta. Para obtener una descripción de bloqueo, las sugerencias de bloqueo y niveles de aislamiento, consulte los siguientes temas en libros en pantalla de SQL Server:
  • El bloqueo en el motor de base de datos
  • Bloqueo de personalización y control de 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 en el punto donde hay un efecto negativo en el rendimiento del sistema, suele ser debido a uno de los siguientes motivos:
  • Un SPID mantiene bloqueos en un conjunto de recursos para un extendedperiod de tiempo antes de soltarlos. Este tipo de bloqueo resuelve itselfover tiempo, pero puede provocar una degradación del rendimiento.
  • Un SPID mantiene bloqueos en un conjunto de recursos y nunca releasesthem. Este tipo de bloqueo no se resuelve e impide el acceso a los recursos de afectado que se 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 distinta en diferentes 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 consultas SQL individuales. La segunda situación se produce en un estado coherente que puede ser más fácil de diagnosticar.

Recopilando 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 copias 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 con secuencias de comandos SQL, consulte los siguientes artículos 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 bloquea mediante el uso de las 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 se expande la transacción, el informe mostrará las transacciones que están bloqueadas por la transacción principal. Este informe también mostrará la "declaración SQL bloqueo" y la "declaración de SQL bloqueado".
    • Utilice DBCC INPUTBUFFER (<spid>) para buscar la última instrucción enviada por un SPID.</spid>
  2. Encontrar el nivel de anidamiento de transacción y el estado de proceso de los SPID bloqueo.
    El nivel de anidamiento de la transacción de un SPID está disponible en la variable global de the@@TRANCOUNT. Sin embargo, se puede determinar desde el exterior theSPID consultando la tabla sysprocesses de la siguiente manera:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>go						
    El valor devuelto es el valor de @@TRANCOUNT para el SPID. Esta transacción de showsthe nivel de anidamiento para el SPID bloqueo, que a su vez puede explicar por qué se mantiene bloqueos. Por ejemplo, si el valor es mayor que cero, es theSPID en medio de una transacción (en la que se espera que itretains determinados bloquea el caso ha adquirido, dependiendo de la transacción isolationlevel).

    También puede comprobar para ver si cualquier a largo plazo abierto transactionexists en la base de datos mediante el uso de DBCC OPENTRANdatabase_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 de bloqueos que aún se mantienen. Una traza del analizador podrá ver todos los comandos ejecutados por un SPID en la transacción actual. Los pasos siguientes ayudarán 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. También, asegúrese de que el 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 eventos adicionales que se enumeran en la tabla 2 para obtener más información. Si está ejecutando en un entorno de producción de gran volumen, puede utilizar sólo los eventos en la tabla 1, como son normalmente suficientes para solucionar los problemas bloqueo más. Incluyendo los eventos adicionales en la tabla 2 puede hacerlo 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 seguridadInicio de sesión de auditoría
Auditoría de seguridadCierre de sesión de auditoría
SesionesConexión existente
Procedimientos almacenadosRPC: inicio
TSQLSQL: BatchStarting

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

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 descripció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 bloqueo las secuencias de comandos en el artículo 271509 (mencionados anteriormente) para capturar información sobre los SPID bloqueo y ha realizado un seguimiento del analizador con los eventos mencionados anteriormente.

Visualizar la salida de secuencia de comandos de bloqueo

Examine la salida sys.sysprocesses para determinar las cabezas de las cadenas de bloquea
Si no se especificó un modo rápido para las secuencias de comandos bloqueo, habrá una sección titulada "SPID a la cabeza de las cadenas bloquea" thatlists 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 theblocking cabezas mirando la salida sys.sysprocesses y siguiendo la jerarquía de los SPID que se indica en la columna bloqueada.
Examine la salida sys.sysprocesses para obtener información sobre los SPIDs al principio de la cadena de bloquea.
Es importante evaluar los siguientes campos sys.sysprocesses :

Estado

Esta columna muestra el estado de un SPID determinado. Normalmente, un estado de suspensión se indica que el SPID ha finalizado su ejecución y está esperando la aplicación enviar otro lote o consulta. 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 se está ejecutando actualmente. Normalmente, esto indica que el SPID está en espera de un comando de la aplicación.
EjecutaEl SPID se está ejecutando en un programador.
Puede ejecutar.El SPID está en la cola ejecutable de un programador y esperando a que el tiempo del 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 del 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 la transacción de los 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 último waittype que el SPID. Si waittype no es cero, el valor de lastwaittype indica el waittype actual el 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 de 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 de waittime puede utilizarse para determinar si está progresando el SPID. Cuando una consulta en la tabla sys.sysprocesses devuelve un valor en la columna waittime que es menor que el valor de waittime de una consulta anterior de sys.sysprocesses, esto indica que se ha adquirido y liberado el bloqueo anterior y está esperando una cerradura nueva (suponiendo que waittime distinto de cero). Esto se puede comprobar mediante la comparación de la waitresource entre sys.sysprocesses salida.

Waitresource

Este campo indica el recurso que está esperando un SPID. La tabla siguiente muestra los formatos de 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 261575970 ID de objeto es la tabla titles y 1 es el índice agrupado.
PáginaDatabaseID:FileID:PageIDPÁGINA: 5:1:104
En este caso, es de 5 ID de la base de datos 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 observe la 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, es de 5 ID de la base de datos Pubs, Hobt_ID 72057594044284928 corresponde a no agrupado index_id 2 para el id de 261575970 (tabla detítulos ). Utilice la vista de catálogo sys.partitions para asociar el hobt_id a un identificador de índice en particular y el identificador de objeto. No hay ninguna manera de unhash el hash de clave de índice en un valor de clave de índice específico.
FilaDatabaseID:FileID:PageID:Slot(row)RID: 5:1:104:3

En este caso, es de 5 ID de la base de datos pubs, ID 1 es el archivo de datos principal, 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.
CompilarDatabaseID:ObjectID [[compilación]]FICHA: 5:834102012 [[compilación]] no es un bloqueo de tabla, pero en su lugar una compilación bloquea en un procedimiento almacenado. Identificador de la base de datos 5 es pubs, 834102012 ID de objeto es el procedimiento almacenado usp_myprocedure. Ver 263889 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 una perspectiva de la raíz de un problema. Su utilidad varía dependiendo de las circunstancias del problema. Por ejemplo, puede determinar si el problema ocurre sólo desde ciertos clientes (nombre del host), en ciertas bibliotecas de red (net_library), cuando el último lote enviado por un SPID (last_batch) y así sucesivamente.
Examine la salida de DBCC INPUTBUFFER.
Para cualquier SPID al principio de una cadena de bloquea o con el tipo de anon cero, la secuencia de comandos bloqueo ejecutará DBCC INPUTBUFFER paradeterminar la consulta actual para ese SPID.

En muchos casos, ésta es la consulta que está causando el que son bloqueos bloquear a otros usuarios a realizarse. Sin embargo, si el SPID está en atransaction, los pueden se han adquirido bloqueos por una consulta ejecutada con anterioridad, no es el que actual. Por lo tanto, debería ver la salida del generador de perfiles para el SPID, no sólo el inputbuffer.

Nota: Dado que la secuencia de comandos bloqueo consta de varios pasos, ispossible que un SPID puede aparecer en la primera sección como la cabeza de un blockingchain, pero en el momento en que se ejecuta la consulta de DBCC INPUTBUFFER no es longerblocking y no se captura el INPUTBUFFER. Esto indica que el blockingis resolución de sí mismo para dicho SPID y puede o no puede ser un problema. En este punto, puede usar la versión rápida de la secuencia de comandos bloqueo para tratar para garantizar que se capture el inputbuffer, antes de que se borra (aunque sigue siendo noguarantee) o ver los datos de ese periodo de tiempo para determinar el SPID de whatqueries se estaba ejecutando el generador de perfiles.

Ver los datos del generador de perfiles

Ver datos del analizador de forma eficaz es extremadamente valiosa en la resolución de problemas de bloqueo. Lo más importante a tener en cuenta es que no tiene que mirar todo lo que 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), el analizador permite limitar los datos mostrados por quitar columnas de datos o eventos, agrupar (ordenar) en 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 la tabla) y ejecutar consultas SQL contra él.

Tenga cuidado de que realizar el filtrado en un archivo de traza guardado anteriormente. Si realiza estos pasos en un seguimiento activo, se arriesga a perder los datos que se ha capturado desde que se inició el seguimiento. Guardar una traza activa a un archivo o tabla en primer lugar (en el menú archivo , haga clic en Guardar como) y vuelva a abrir (en el menú archivo , haga clic en Abrir) antes de continuar. Al trabajar en un archivo de traza guardado, el filtrado no quitar permanentemente los datos que se filtran, simplemente no muestra todos los datos. Puede agregar y quitar eventos y las columnas de datos según sea necesario para centrarse las búsquedas.

Lo que debe buscar:
  • ¿Qué comandos tiene el SPID a la cabeza de un bloqueo chainexecuted dentro de la transacción actual.
    Filtrar los datos de seguimiento de particular SPID que está a la cabeza de una cadena de bloquea (en el menú archivo , haga clic en Propiedades, luego en la ficha filtros , especifique el valor SPID). Puede examinar al antes tiene que ejecutar comandos el tiempo estaba bloqueando otros SPID. Si incluye los eventos theTransaction, puede identificar fácilmente cuando se inicia una transacción.De lo contrario, puede buscar la columna de texto inicial, guardar, COMMIT o ROLLBACK TRANSACTIONoperations. Utilice el valor open_tran de la tabla sysprocesses para asegurarse de que captura todos los eventos de la transacción.Conocer los comandos ejecutados y el contexto de transacción le permitirá paradeterminar ¿por qué bloquean un SPID.

    Recuerde que puede removeevents y los datos de las columnas. En lugar de mirar a partir y completedevents, elija uno. Si los SPID bloqueo no son procedimientos almacenados, quitar elSP: iniciando o SP: completado eventos; los eventos SQLBatch y RPC mostrará la llamada al procedimiento. Sólo ver la cuando eventos SP necesita ver ese nivel de detalle.
  • ¿Cuál es la duración de las consultas para los SPID en el bloqueo de cadenas de jefe?
    Si se incluyen los anteriores eventos completados, la columna duración mostrará el tiempo de ejecución de la consulta. Esto puede ayudar a identificar las consultas de larga duración que están causando el bloqueo. Para determinar por qué está realizando lentamente la unión, ver la CPU, lecturay escribe columnas, así como el evento de Plan de ejecución .

Clasificació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 de la sección "Bloqueo de escenarios y resoluciones comunes" de este artículo. Consulte información de sysprocesses las columnas Waittype, Open_Trany el estado . El se resuelve? columna indica si el bloqueo se resolverá en su propia.

EscenarioWaittypeOpen_TranEstado¿Resuelve?Otros síntomas
1Distinto de cero&gt; = 0puede ejecutar.Sí, cuando finaliza la consulta.Columnas Physical_IO, CPU o Memusage aumentará con el tiempo. Duración de la consulta será alto cuando completa.
20 x 0000&gt; 0modo de suspensiónNo, pero puede ser sacrificado SPID.Una señal de aviso se puede observar en la traza del analizador para SPID, que indica un tiempo de espera de la consulta o se ha producido la cancelación.
30 x 0000&gt; = 0puede ejecutar.No. 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 es el predeterminado (leer COMMMITTED), es probable que la causa.
4Varía&gt; = 0puede ejecutar.No. 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 bloquea será el mismo que el SPID está bloqueando.
50 x 0000&gt; 0reversiónSí.Una señal de aviso se puede observar en la traza del analizador para SPID, que indica un tiempo de espera de la consulta o se ha producido la cancelación o simplemente una instrucción rollback emitida.
60 x 0000&gt; 0modo de suspensiónCon el tiempo. 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 las rutas de acceso a 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 forways para optimizar la consulta. En realidad, esta clase de problema de bloqueo puede tengo un problema de rendimiento y deberá continuar como tal. Para 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 performancetroubleshooting general de la aplicación, consulte el siguiente artículo de Knowledge Base:
    224587 Cómo: Solucionar problemas de Performance de aplicaciones con SQL Server
    Para obtener más información, consulte el tema de libros en pantalla de SQL Server 2008 de supervisión del rendimiento y optimización de temas "Cómo..." en el siguiente sitio Web de MSDN: Si tiene una consulta de larga duración que los usuarios de blockingother y no se pueden optimizar, considere la posibilidad de mover desde un OLTPenvironment a un sistema de soporte de toma de decisiones.
  2. Bloqueo causado por un sueño SPID que ha perdido el control del nivel de anidamiento de transacción

    Este tipo de bloqueo a menudo se pueden identificar por un SPIDthat 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 el tiempo de espera de consulta, applicationexperiences o problemas de cancelación sin emitir también el número deber de instrucciones ROLLBACK y COMMIT. Cuando recibe un SPID aquery tiempo de espera o cancelar, finalizará la consulta actual y el lote, butdoes automáticamente deshacer o confirmar la transacción. El isresponsible de la aplicación, como SQL Server no puede suponer que un transactionmust completo se deshagan simplemente debido a una sola consulta se cancela. El querytimeout o cancelar aparecerá como un evento de señal de atención para el SPID de seguimiento theProfiler.

    Para demostrar esto, ejecute el analizador de consultas de queryfrom simple siguiente:

    BEGIN TRAN SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2-- Issue this after canceling querySELECT @@TRANCOUNTROLLBACK TRAN						
    Mientras se ejecuta la consulta, haga clic en el color rojo botón Cancelar . Después de cancela la consulta, seleccione @@TRANCOUNT indica el nivel de anidamiento de la transacción es uno. Haber sido una eliminación o un UPDATEquery o hubiera utilizado en la seleccionar HOLDLOCK, realizará todos los seguirían bloqueos adquiridos. Incluso con la consulta anterior, si otra consulta había adquirido bloqueos andheld anteriormente en la transacción, podría todavía retiene cuando se canceló el aboveSELECT.

    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 &gt; 0 es TRAN ROLLBACK siguiendo cualquier error, incluso si la aplicación cliente no considera que una transacción abierta. Esto es necesario, ya que un procedimiento almacenado llamado durante el lote podría haber comenzado una transacción sin conocimiento de la aplicación cliente. Nota que ciertas condiciones, como cancelar la consulta, impedir que el procedimiento de ejecución después de la instrucción actual, incluso si el procedimiento tiene lógica para comprobar IF @@ERROR <> 0 y anulación de la transacción, en tales casos no se ejecutará este código para deshacer.
      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 de 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án las declaraciones T-SQL que sigue a la instrucción que causó 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 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 de forma adecuada. Al deshabilitar la agrupación de conexiones, liberando la conexión provocará una desconexión física de la conexión de SQL Server, lo que produce el servidor deshaciendo las transacciones abiertas.
      4. Si esta opción está habilitada y el servidor de destino es SQL Server 2000, puede ser beneficioso actualizar el equipo cliente para 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 debe ser "Restablecer" antes de que se vuelve a utilizar. Esta llamada a sp_reset_connection para aceptar anula las transacciones iniciadas por el servidor (transacciones DTC iniciadas por la aplicación de cliente no se ven afectadas), restablece la base de datos predeterminada, establecer las opciones y así sucesivamente. Tenga en cuenta que no se restablece la conexión hasta que se vuelve a utilizar el grupo de conexiones, por lo que es posible que un usuario podría abrir una transacción y, a continuación, suelte la conexión a la agrupación de conexiones, pero no puede utilizarse durante varios segundos, durante el cual la transacción podría permanecer abierta. Si no se vuelve a utilizar la conexión, la transacción se anulará cuando el tiempo de espera de la conexión 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 también puede ser un problema de rendimiento y que se requieren que investigarlo como tal. Si el tiempo de ejecución de la consulta se puede disminuir el tiempo de espera de consulta o en Cancelar no se produciría. Es importante que la aplicación pueda controlar el tiempo de espera o cancelar los escenarios se presentan, pero también puede que se beneficie de examinar el rendimiento de la consulta.
  3. Bloqueo causado por un SPID cuya aplicación cliente correspondiente no recupera todas las filas de resultados hasta su finalización

    Después de enviar una consulta al servidor, todas oficina10 recuperar inmediatamente todas las filas de resultados hasta su finalización. Si una aplicación recupera todas las filas de resultados, pueden quedar bloqueos en las tablas, bloqueo de otherusers. Si está utilizando una aplicación que envía SQLstatements al servidor de forma transparente, la aplicación debe recuperar todas las filas del resultado. Si hace no (y si no se puede configurar para hacerlo), es posible que no se puede toresolve el problema de bloqueo. Para evitar este problema, puede restrictpoorly-comportado aplicaciones a un informe o una decisión-supportdatabase.

    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 deadlockis distribuidos no detectables mediante el RDBMS bloquear el administrador. Esto es debido a la sólola de hecho uno de los recursos implicado en el interbloqueo es un bloqueo de SQL Server. Otro lado del interbloqueo es en el nivel de aplicación de cliente, que SQL Server no tiene ningún control. Éstos son dos ejemplos de cómo esto puede suceder, y pueden evitar formas posibles de la aplicación.

    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 siguiente interbloqueo distribuido. 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. La aplicación espera los resultados para comenzar a procesar los datos devueltos y, a continuación, envía otra operación de SQL en dbproc2. Cuando los datos comienzan a volver (sea cual sea el dbproc primero responde, suponga que esta es dbproc1), procesa completamente todos los datos se devuelven en ese dbproc. Recupera los resultados desde dbproc1 hasta SPID1 se bloquea en un bloqueo mantenido por SPID2 (ya que las dos consultas se ejecutan asincrónicamente en el servidor). En este punto, dbproc1 espera indefinidamente más datos. SPID2 no está bloqueada por 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 produce 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

      Aunque existe un subproceso independiente para cada conexión en el cliente, una variación de este interbloqueo distribuido 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 (realizando el INSERT, UPDATE o DELETE) se bloquea en un bloqueo mantenido por la SPID2 (realizando la selección). SPID2 escribe una fila en la dbproc2 del cliente. Dbproc2, a continuación, intenta pasar la fila en un búfer a dbproc1, pero dbproc1 busca está ocupado (está bloqueada esperando en SPID1 para terminar la INSERCIÓN actual, que se bloquea en SPID2). En este momento, dbproc2 está bloqueado en el nivel de aplicación por dbproc1 SPID2 bloquea cuyo SPID (SPID1) en el nivel de base de datos. De nuevo, esto produce 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 se deben conocer los desarrolladores de thatapplication. Deben codificar aplicaciones a casos de handlethese correctamente.

    Resoluciones:

    Dos soluciones confiables son usar un querytimeout o conexiones enlazadas.

    • Tiempo de espera de consulta
      Cuando se ha proporcionado un tiempo de espera de la consulta, si se produce el interbloqueo distribuido, perderá 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 sobre el uso de un tiempo de espera de la consulta.
    • Conexiones enlazadas
      Esta característica permite a un cliente tener varias conexiones enlazarlos con un espacio de transacción única, por lo que las conexiones no bloquean entre sí. Para obtener más información, vea el tema "Usar conexiones enlazadas" en los libros en pantalla de SQL Server 7.0.
  5. Bloqueo causado por un SPID que se encuentra en "Oro" o Rollback, estado

    Una consulta de modificación de datos que es KILLed o canceledoutside de una transacción definida por el usuario, se detendrá. También puede occuras un efecto secundario del reinicio del equipo cliente y su sessiondisconnecting de red. Asimismo, una consulta que se selecciona como víctima del interbloqueo será revertido. Una consulta de modificación de datos a menudo no se puede deshacer todo más rápido de lo que inicialmente se aplicaron los cambios. Por ejemplo, si se ejecutaba un DELETE, INSERT o UPDATEstatement durante una hora, podría llevar al menos una hora para deshacer. Este es el comportamiento esperado, debido a los cambios realizados deben ser completelyrolled atrás o integridad transaccional y física en la base de datos que becompromised. Debido a esto debe ocurrir, SQL Server marca el SPID en un estado de "oro" o rollback (lo que implica que no puede ser sacrificado o seleccionado como un deadlockvictim). A menudo pueden identificarse mediante la observación de la salida de sp_who, lo que puede indicar el comando Deshacer. La columna estado de sys.sysprocesses indica un estado de restauración, también aparecerán en la salida de sp_who o en el Monitor de actividad de SQL Server Management Studio.
    Solución:

    Debe esperar a que el SPID que termine 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 se procesen todas las transacciones abiertas. Startuprecovery toma esencialmente la misma cantidad de tiempo por transacción como timerecovery de ejecución, y la base de datos es inaccesible durante este período. Por lo tanto, servidor de forcingthe hasta un SPID en un estado de reversión de la corrección será becounterproductive a menudo.

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

    Si las capturas de la aplicación de cliente o el clientworkstation se reinicia, la sesión de red en el servidor puede no beimmediately cancelado en algunas condiciones. Desde la perspectiva del servidor, Elcliente todavía parece estar presente y los bloqueos adquiridos pueden seguir beretained. Para obtener más información, haga clic en el siguiente número de artículo 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 withoutappropriately limpiar sus recursos, puede terminar el SPID comando KILL de uso. El comando KILL toma el valor de SPID como entrada. Por ejemplo, para kill SPID 9, simplemente emita el siguiente comando:

    KILL 9						

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

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

Puede haber una tendencia a centrarse en problemas de ajuste y la plataforma del servidor frente a un problema de bloqueo. Sin embargo, esto normalmente no conduzca a una resolución y puede absorber el tiempo y energía que mejor se dirige a examinar la aplicación de cliente y las consultas que se envía. No importa qué nivel de visibilidad de la aplicación expone con respecto a las llamadas de base de datos se realiza, un problema de bloqueo, no obstante, con frecuencia requerir que se inspeccionen las instrucciones SQL exactas que envía la aplicación y el comportamiento exacto de la aplicación con respecto a la cancelación de la consulta, administración de conexiones, la obtención de todas filas de resultados y así sucesivamente. Si la herramienta de desarrollo no permite un control explícito sobre administración de conexiones, cancelación, 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 los entornos OLTP críticos para el negocio.

Es vital que tenerse mucho 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 debe ser lo más reducida posible. Debe ejercerse la disciplina de administración de conexión es buena. Si no lo hace, 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 la escala de los usuarios 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 poco de bloqueo.

Advertencia: este artículo se tradujo automáticamente

Propiedades

Id. de artículo: 224453 - Última revisión: 02/01/2015 05:01:00 - Revisión: 8.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Workgroup

  • kbsqlsetup kbhowto kbtshoot kbexpertiseinter kbinfo kbmt KB224453 KbMtes
Comentarios
nit();