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

Seleccione idioma Seleccione idioma
Id. de artículo: 224453 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

En este artículo, el término "conexión" hace referencia 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 conoce a menudo como un proceso, aunque no es un contexto de proceso independiente en el sentido habitual. En su lugar, cada SPID consiste en las estructuras de datos y recursos de servidor es necesario atender las solicitudes de una única conexión desde un cliente dado. A aplicación de cliente solo puede tener una o varias conexiones. Desde el 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 el múltiplo de conexiones desde varias aplicaciones de cliente o varios equipos cliente. Uno conexión puede bloquear otra conexión, independientemente de si procede desde la misma aplicación o aplicaciones diferentes de dos clientes distintos equipos.

Más información

El bloqueo es una característica inevitable de cualquier relacional sistema de administración de bases de datos (RDBMS) con la simultaneidad en función de 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 en 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 gratuita al adquirir su propietario de bloqueo en el recurso y continuar el proceso. Este comportamiento es normal y puede suceder muchas veces a lo largo del curso de un día con ningún efecto apreciable en el rendimiento del sistema.

El contexto de transacciones y la duración de un consulta determinar cuánto tiempo se mantienen sus bloqueos y, por lo tanto, su impacto en otros consultas. Si la consulta no se ejecuta dentro de una transacción (y no hay sugerencias de bloqueo se utilizan), sólo se mantendrán los bloqueos para las instrucciones SELECT en un recurso en el el tiempo que es lo que se está leyendo en realidad, no para la duración de la consulta. Para INSERT, UPDATE e instrucciones DELETE, los bloqueos se mantienen durante la duración de la la consulta, tanto por motivos de coherencia de datos y para permitir que la consulta se puede deshacer Si es necesario.

Para las consultas ejecutadas dentro de una transacción, la duración para los que se mantienen los bloqueos están determinados por el tipo de consulta, el nivel de aislamiento de transacción, y si se utilizan sugerencias de bloqueo en el consulta. Para obtener una descripción de bloqueo, sugerencias de bloqueo y aislamiento de la transacción niveles, vea los temas siguientes en los libros en pantalla de SQL Server:
  • El bloqueo en el motor de base de datos
  • Personalizar el bloqueo y control de versiones de fila
  • Modos de bloqueo
  • Compatibilidad de bloqueos
  • Niveles de aislamiento basado 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 de aumento hasta el punto donde hay un efecto perjudicial sobre el rendimiento del sistema, suele deberse a uno de los razones siguientes:
  • Un SPID mantiene bloqueos en un conjunto de recursos para un total período de tiempo antes de soltarlos. Este tipo de bloqueo se resuelve con el tiempo, pero puede causar una degradación del rendimiento.
  • Un SPID mantiene bloqueos en un conjunto de recursos y nunca libera ellos. Este tipo de bloqueo no se resuelve a sí mismo e impide el acceso a la indefinidamente todos los recursos afectados.
En el primer escenario, se resuelve el problema de bloqueo sí mismo con el tiempo como el SPID libera los bloqueos. Sin embargo, puede ser la situación muy fluido como diferentes SPIDs causan bloqueo en diferentes recursos con el tiempo, creación de un destino de movimiento. Por este motivo, estas situaciones pueden ser difíciles solucionar problemas al utilizar 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 la solución de problemas de bloqueo problemas, un administrador de base de datos puede utilizar secuencias de comandos SQL que se supervisan constantemente el estado de bloqueo y el bloqueo en SQL Server. Pueden proporcionar estas secuencias de comandos las instantáneas de instancias específicas con el tiempo, conduce a una visión general de la problema. Para obtener una descripción de cómo supervisar el bloqueo con secuencias de comandos SQL, consulte el artículos siguientes en Microsoft Knowledge Base:
271509 Cómo supervisar los bloqueos en SQL Server 2005 y SQL Server 2000
Las secuencias de comandos en el presente artículo llevará a cabo las tareas a continuación. Siempre que sea posible, el método para obtener esta información desde Se da de SQL Server Management Studio.
  1. Identificar el SPID (identificador de sesión) al principio de la cadena bloqueada y la instrucción SQL.
    Además de utilizar las secuencias de comandos en el artículo de Knowledge Base mencionado anteriormente, usted puede identificar la cabeza de la cadena de bloquea mediante el uso de características que 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ándary, a continuación, haga clic en Actividad: todas las transacciones de bloqueo. Este informe muestra las transacciones al principio de la cadena de bloqueo. Si expande la transacción, el informe mostrará las transacciones que están bloqueadas por la transacción principal. Este informe también mostrará el "bloqueo de SQL Statement" y "bloqueado SQL."
    • 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.
    La transacción de nivel de anidamiento de un SPID está disponible en el Variable global @@TRANCOUNT. Sin embargo, puede estar determinado por fuera de la 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. Aquí se muestra el nivel de anidamiento de transacción para el SPID bloqueo, que a su vez puede explicar ¿Por qué bloquean. Por ejemplo, si el valor es mayor que cero, el SPID está en medio de una transacción (en cuyo caso se espera que TI conserva ciertos bloqueos que ha adquirido, según el aislamiento de transacciones nivel).

    También puede comprobar para ver si cualquier a largo plazo de transacciones abiertas existe en la base de datos con DBCC OPENTRANdatabase_name.

Recopilación de información de traza 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 de fondo investigar un problema de bloqueo en SQL Server. Si un SPID ejecuta múltiples instrucciones de una transacción, sólo la última declaraciónque fue enviado se mostrarán en la informe, búfer de entrada o 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, podrá ver todos los comandos ejecutados por un SPID dentro de 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 Nuevoy, 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 traza debe escribirse en un disco rápido local o compartido. Evitar el seguimiento en una unidad de disco o de red lenta. Además, asegúrese de que se seleccionan datos de seguimiento de procesos de servidor.
  4. En el Selección de eventos Haga clic para seleccionar la Mostrar todos los eventos y el Mostrar todas las columnas casillas de verificación.
  5. En la ficha de 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 decidir utilizar sólo los eventos en la tabla 1, como son normalmente suficientes para solucionar problemas 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 de punto de entrada y 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 seguimiento.
La tabla 1: Tipos de eventos
Contraer esta tablaAmpliar esta tabla
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: iniciando
TSQLSQL: BatchStarting

La tabla 2: Tipos de sucesos adicionales
Contraer esta tablaAmpliar esta tabla
TítuloEvento
TransaccionesDTCTransaction
TransaccionesSQLTransaction
Procedimientos almacenadosRPC: completado
TSQLSQL: BatchCompleted
Procedimientos almacenadosSP: StmtStarting
Procedimientos almacenadosStmtCompleted

Para obtener más información acerca del uso a de SQL Server Profiler, vea SQL Server Libros en pantalla.

Identificar y resolver situaciones de bloqueo de común

Mediante el examen de la información anterior, puede determinar la causa de la mayoría de los problemas bloquea. El resto de este artículo es una explicación de cómo Utilice esta información para identificar y resolver algunos escenarios comunes de bloqueo. Este documento se asume que se han utilizado las secuencias de comandos bloqueo en el artículo 271509 (mencionado anteriormente) para obtener información acerca de los SPID bloqueo y se realizado una traza del analizador con los eventos descritos anteriormente.

Visualizar la salida de secuencia de comandos de bloqueo

Examine la salida de sys.sysprocesses para determinar los cabezales de las cadenas de bloqueo
Si no se especificó un modo rápido para las secuencias de comandos bloqueo habrá una sección titulada "SPIDs situado al principio de bloqueo de las cadenas" Enumera los SPID que están bloqueando otros SPID en la salida de secuencia de comandos.
SPIDs at the head of blocking chains
Si especifica la opción fast, puede determinar la bloqueo cabezas mirando a la salida de sys.sysprocesses y siguiendo la jerarquía de los SPID que se indica en la columna bloqueada.
Examine la salida de sys.sysprocesses para obtener información sobre los SPID 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. Por lo general, un estado inactivo indica que el SPID ha finalizado su ejecución y es en espera de la aplicación enviar otro lote o consulta. A se puede ejecutar, ejecutando, o sos_scheduler_yield el estado indica que el SPID está procesando una consulta. La la tabla siguiente proporciona explicaciones breves acerca del estado de varios valores.
Contraer esta tablaAmpliar esta tabla
EstadoSignificado
Segundo planoEl SPID está ejecutando un fondo tarea, como la detección de interbloqueos.
Modo de suspensiónNo se está ejecutando actualmente el SPID. Esto suele indicar que el SPID está esperando un comando desde el aplicación.
EjecutandoEl SPID se está ejecutando en un programador.
Se puede ejecutarEl SPID está en la cola se puede ejecutar de un programador y que esperan obtener tiempo del programador.
Sos_scheduler_yieldSe estaba ejecutando el SPID, pero ha cedido voluntariamente su espacio de tiempo en el programador para permitir que otro SPID adquirir la hora de programador.
SuspendidoEl SPID está esperando un evento, como un bloqueo o un pestillo.
ReversiónEl SPID es de reversión de una transacción.
DefwakeupIndica que el SPID está esperando un recurso que está en el proceso que se está liberando. El campo waitresource debe indicar el recurso en cuestión.

Open_tran

Este campo indica a la transacción nivel de anidamiento de los SPID. Si este valor es mayor que 0, el SPID es dentro de una transacción abierta y puede estar reteniendo bloqueos adquiridos por cualquier instrucción dentro 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 actual waittype del SPID.

Para una breve descripción de los diferentes lastwaittype y waittype valores, consulte el siguiente artículo de Microsoft 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 de waittime puede utilizarse para determinar si el SPID está realizando un progreso. Cuando una consulta en la tabla de sys.sysprocesses devuelve un valor en la columna waittime es menor que el valor de waittime de una consulta anterior de sys.sysprocesses, esto indica que el bloqueo previo fue adquirido y liberado y Ahora está esperando un bloqueo nuevo (suponiendo que waittime distinto de cero). Esto se puede comprobar comparando el waitresource entre la salida de sys.sysprocesses .

Waitresource

Este campo indica el recurso que está esperando un SPID. La tabla siguiente muestra los formatos de waitresource comunes y su significado:
Contraer esta tablaAmpliar esta tabla
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 de 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 pertenecen 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 para la clave de índice)CLAVE: 5:72057594044284928 (3300a4f361aa)

En este caso, es de 5 Id. de la base de datos Pubs, 72057594044284928 de Hobt_ID 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 la hobt_id a un id de índice en particular y el identificador de objeto. No hay ninguna manera de descriptor el hash de la 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, página 104 es una página que pertenecen a la tabla titles y ranura 3 indica la posición de la fila en la página.
CompilaciónDatabaseID: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 base de datos 5 es pubs, 834102012 de 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 columnas restantes de sys.sysprocesses pueden proporcionar una perspectiva de la raíz de un problema así. Su utilidad varía dependiendo de las circunstancias del problema. Para ejemplo, puede determinar si el problema ocurre sólo de ciertos 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.
Examine la salida de DBCC INPUTBUFFER.
Para cualquier SPID al principio de una cadena de bloquea o con un waittype distinto de cero, la secuencia de comandos bloqueo ejecutará DBCC INPUTBUFFER a determinar la consulta actual para dicho SPID.

En muchos casos, se trata de la consulta que está causando los bloqueos son bloquear a otros usuarios que se llevará a cabo. Sin embargo, si el SPID está dentro de un transacción, los bloqueos pueden haber sido adquirida por una consulta ejecutada con anterioridad, no es el uno actual. Por lo tanto, también debe ver la salida del generador de perfiles el SPID, no sólo el inputbuffer.

Nota Dado que la secuencia de comandos bloqueo se compone de varios pasos, es posible que un SPID puede aparecer en la primera sección que la punta de un bloqueo la cadena, pero en el momento en que se ejecuta la consulta de DBCC INPUTBUFFER, ya no es bloqueo y no se captura el INPUTBUFFER. Esto indica que el bloqueo está resolviendo propia para dicho SPID y pueden o no pueden ser un problema. En este punto, puede utilizar la versión rápida de la secuencia de comandos bloqueo para intentar Asegúrese de que capturar el inputbuffer antes de que borra (aunque no hay aún ninguna garantizar), o ver los datos del generador de perfiles de ese periodo de tiempo para determinar qué las consultas se estaba ejecutando el SPID.

Visualización de los datos del generador de perfiles

Ver datos del generador de perfiles eficaz es extremadamente valiosa en resolución de problemas de bloqueo. El más importante que debe realizar es que lo haga no tiene que mirar todo lo captura; sea selectivo. Proporciona el generador de perfiles capacidades de forma eficaz permite ver los datos capturados. En el cuadro de diálogo de Propiedades (en el menú archivo , haga clic en Propiedades), el analizador permite limitar los datos mostrados mediante la eliminación las columnas de datos o eventos, agrupar (ordenar) por las columnas de datos y aplicación filtros. Puede buscar el rastro completo o sólo una columna específica para específicos los valores (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 en el menú archivo , elija Guardar como y, a continuación, haga clic en tabla) y ejecutan consultas SQL contra él.

Tenga cuidado de que puede realizar el filtrado sólo en un archivo de traza guardada anteriormente. Si realiza estos pasos en una traza activa, se riesgo de pérdida de datos que se ha capturado desde que se inició la traza. Guardar un activa a un archivo de seguimiento o en primer lugar de la tabla (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 el filtrado no quitar permanentemente los datos que se filtran, simplemente hace No mostrar todos los datos. Puede agregar y quitar eventos y las columnas de datos como es necesario para ayudar a centrar las búsquedas.

Lo que busca:
  • ¿Qué comandos tiene el SPID al principio de una cadena de bloquea ¿ejecutar dentro de la transacción actual?
    Filtrar los datos de seguimiento para una SPID determinado que está al principio de una cadena de bloquea (en el menú archivo , haga clic en Propiedades; a continuación, en la ficha filtros , especifique el valor SPID). A continuación, puede examinar los comandos ha ejecutado antes de la hora en que fue bloqueando otros SPID. Si incluyen la Eventos de transacción, puede identificar fácilmente cuando se inicia una transacción. De lo contrario, puede buscar la columna de texto para comenzar, guardar, COMMIT o ROLLBACK TRANSACTION operaciones. Utilice el valor de open_tran de la tabla sysprocesses para asegurarse de que captura todos los eventos de transacción. Al saber que los comandos ejecutados y el contexto de transacción, podrá determinar por qué un SPID está reteniendo bloqueos.

    Recuerde, puede quitar columnas de datos y eventos. En lugar de en ambos iniciar y completar eventos, elija uno. Si los SPID bloqueo no son procedimientos almacenados, quite el SP: a partir o SP: completado eventos; los eventos SQLBatch y RPC le mostrará la llamada al procedimiento. Ver sólo los eventos de SP cuando debe ver ese nivel de detalle.
  • ¿Cuál es la duración de las consultas para los SPID en la cabeza ¿de bloqueo de cadenas?
    Si se incluyen los anteriores eventos completados, 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é el consulta está realizando lentamente, ver la CPU, lecturay escribe columnas, así como el evento 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 el "Common bloqueo Sección de escenarios y soluciones"de este artículo más abajo. Las columnas Waittype, Open_Trany el estado hacen referencia a la información de sysprocesses . El resuelve? columna indica si se permite o no el bloqueo se resolverá en su propio.

Contraer esta tablaAmpliar esta tabla
EscenarioWaittypeOpen_TranEstado¿Resuelve?Otros Síntomas
1Distinto de cero>= 0se puede ejecutarSí, cuando finaliza la consulta.Physical_IO, la CPU o las columnas de Memusage aumentará con el tiempo. Duración de la consulta será alta finalizada.
20 x 0000> 0modo de suspensiónNo, pero se puede eliminar los SPID.Una señal de atención puede verse en el generador de perfiles se ha producido el seguimiento para SPID, que indica un tiempo de espera de consulta o en Cancelar.
30 x 0000> = 0se puede ejecutarNo. No se resolverá hasta que el cliente obtiene todas las filas o cierra la conexión. Puede SPID ser sacrificados, pero puede tardar hasta 30 segundos.Si open_tran = 0, y el SPID mantiene bloqueos mientras el aislamiento de transacciones nivel es predeterminada (leer COMMMITTED), ésta es una causa probable.
4Varía> = 0se puede ejecutarNo. No se resolverá hasta que el cliente las consultas cancela o cierra las conexiones. Pueden SPID eliminado, pero puede tardar hasta 30 segundos.La columna hostname en sysprocesses para el SPID al principio de una cadena de bloquea será igual a uno de los SPID está bloqueando.
50 x 0000> 0reversiónSí.Un señal de atención puede aparecer también en la traza del analizador para SPID, que indica un se ha producido el tiempo de espera de consulta o en Cancelar, o ha sido simplemente una instrucción rollback emitido.
60 x 0000> 0modo de suspensiónAl final. Cuando Windows NT determina la sesión no es activo más tiempo, el de SQL Server se perderá la conexión.El valor de last_batch en sysprocesses es mucho anterior a la hora actual.

Escenarios de bloqueo y resoluciones comunes

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

    Resolució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 simplemente ser un problema de rendimiento y requieren que persiguen como tal. Para obtener información en una consulta específica de ejecución lenta de la solución de problemas, consulte el siguiente artículo de Microsoft Knowledge Base:
    243589 Cómo solucionar problemas de consultas de bajo rendimiento en SQL Server 7.0 o versiones posteriores
    Para obtener un rendimiento general de las aplicaciones solución de problemas, consulte el siguiente artículo de Knowledge Base:
    224587 Cómo: Solucionar problemas de Performance de las aplicaciones con SQL Server
    Para obtener más información, vea 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:
    http://msdn.Microsoft.com/en-us/library/ms187830.aspx
    Si tiene una consulta de larga duración que está bloqueando otros usuarios y no puede ser optimizado, considere la posibilidad de mover desde un OLTP entorno de un sistema de soporte de toma de decisiones.
  2. Bloqueo causado por un SPID de inactividad que ha perdido el rastro del nivel de anidamiento de transacción

    Este tipo de bloqueo a menudo puede identificarse mediante un SPID es decir dormir o esperando 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 consulta o emite un cancelar sin emitir también el número requerido de instrucciones ROLLBACK y COMMIT. Cuando se recibe un SPID un tiempo de espera de consulta o en Cancelar, finalizará 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 se debe deshacer simplemente debido a una sola consulta que se está cancelada. La consulta tiempo de espera o en 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 actualización consulta, o hubiera HOLDLOCK utilizado en la selección, todos los bloqueos adquiridos todavía se mantiene. Ni siquiera con la consulta anterior, si ha adquirido otra consulta y mantiene los bloqueos anteriormente en la transacción, todavía se mantenían cuando lo anterior Seleccione se ha cancelado.

    Resoluciones:

    • Las aplicaciones deben administrar correctamente el anidamiento de transacción los niveles, o bien pueden causar un problema de bloqueo tras la cancelación de la consulta de esta manera. Esto puede hacerse de varias maneras:
      1. En el controlador de errores de la aplicación de cliente enviar una continuación de ROLLBACK TRAN IF @@TRANCOUNT > 0 cualquier error, incluso si el aplicación cliente no considera que una transacción está abierta. Esto es necesario, debido a un procedimiento almacenado llamado durante el lote se ha iniciado un transacción sin el conocimiento de la aplicación cliente. Observe que ciertas condiciones, como cancelar la consulta, impedir la ejecución del procedimiento Después de la instrucción actual, por tanto, incluso si el procedimiento tiene lógica para comprobar si @@ERROR <> 0 y anular la transacción, este código para deshacer no estará se ejecutan en estos casos.
      2. Utilice SET XACT_ABORT ON para la conexión o en cualquiera almacena los procedimientos que comienzan las transacciones y no están limpiando los siguiente un error. En el caso de un error en tiempo de ejecución, esta configuración anulará los abiertos las transacciones y el control se devuelve al cliente. Nota que instrucciones de T-SQL no se ejecutará después de la instrucción que causó el error.
      3. Si se está utilizando la agrupación de conexiones en un aplicación que abre la conexión y se ejecuta un pequeño número de consultas antes de liberar la conexión al grupo, por ejemplo, una aplicación basada en Web, deshabilitar temporalmente la agrupación de conexiones puede ayudar a aliviar el problema hasta que la aplicación cliente se modifica para controlar los errores de forma adecuada. Por deshabilitar la agrupación de conexiones, liberando la conexión provocará un físico Cierre de sesión de la conexión de SQL Server, resultando en el servidor de las sucesivas hacer copia de cualquier las transacciones abiertas.
      4. Si está habilitada la agrupación de conexiones y el servidor de destino es SQL Server 2000, ampliar el equipo cliente a MDAC 2.6 o posterior puede ser beneficioso. Esta versión de los componentes de MDAC agrega código para el controlador ODBC y el proveedor de OLE DB para que la conexión sería "Restablecer" antes de se vuelve a utilizar. Esta llamada a sp_reset_connection para aceptar, anula cualquier transacciones iniciadas por el servidor (las transacciones de DTC iniciadas por la aplicación cliente son no afectado), se restablece la base de datos predeterminada, las opciones SET y así sucesivamente. Nota que no se restablece la conexión hasta que se vuelve a utilizar de la agrupación de conexiones por lo que es posible que un usuario podría abrir una transacción y, a continuación, suelte el conexión a la agrupación de conexiones, pero no pueden volver a varios segundos, durante el cual la transacción debería permanecer abierta. Si la conexión no es volver a utilizar, la transacción se anulará cuando se agota el tiempo de espera de la conexión y se quita de la agrupación de conexiones. Por lo tanto, resulta óptimo para el cliente aplicación para anular las 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 necesita que se persiguen como tal. Si la consulta tiempo de ejecución puede disminuir el tiempo de espera de consulta o en Cancelar no se producirían. Es importante que la aplicación pueda controlar el tiempo de espera o en Cancelar los escenarios se presentan, pero también pueden beneficiarse de examinar el rendimiento de la consulta.
  3. Bloqueo causado por un SPID cuya aplicación de cliente correspondiente recupera todas las filas de resultados hasta su finalización

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

    Resolución:

    La aplicación se debe reescribir para recuperar todas las filas de el 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 la sólo uno de los recursos que participan en el interbloqueo es un bloqueo de SQL Server. La otro lado de un interbloqueo es en el nivel de aplicación cliente, por qué SQL Servidor no tiene ningún control. Los siguientes son dos ejemplos de cómo esto puede suceder, y pueden evitar posibles formas de la aplicación.

    1. Interbloqueo distribuido de cliente/servidor con un único cliente Subproceso
      Si el cliente tiene varias conexiones abiertas y un único subproceso de ejecución, puede producirse un interbloqueo distribuido siguiente. Para mayor brevedad, el término "dbproc" utilizado aquí hace referencia a la estructura de la 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, tiene un subproceso de la aplicación de cliente único dos conexiones abiertas. Forma asincrónica, envía una operación de SQL en dbproc1. Esto significa que no se espera en la llamada para devolver antes de continuar. La aplicación, a continuación, envía otra operación de SQL en dbproc2 y espera la resultados para comenzar a procesar los datos devueltos. Cuando inicie la viniendo de datos (cualquiera que sea dbproc en primer lugar responde--supone esto es dbproc1), procesa para finalización de todos los datos devuelven en ese dbproc. Recupera los resultados de dbproc1 hasta SPID1 se bloquea en un bloqueo de contenidos por SPID2 (porque los dos las consultas se ejecutan asincrónicamente en el servidor). En este momento, será 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 que participan es un SQL Recursos de servidor.
    2. Interbloqueo distribuido de cliente/servidor con un subproceso por Conexión

      Incluso si no existe un subproceso independiente para cada conexión en el cliente, todavía puede producirse una variación de este interbloqueo distribuido tal como se muestra por el texto 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)
      								
      En este caso es similar al ejemplo A, excepto que son dbproc2 y SPID2 ejecuta una instrucción SELECT con la intención de realizar de una fila a la vez procesamiento y entrega de cada fila a través de un búfer a dbproc1 para una INSERCIÓN ACTUALIZAR o eliminar la instrucción en la misma tabla. Finalmente, SPID1 (realizar se bloquea el INSERT, UPDATE o DELETE) en un bloqueo mantenido por SPID2 (con SELECT). SPID2 escribe una fila de resultados en el dbproc2 de cliente. Dbproc2, a continuación, intenta pasar la fila en un búfer a dbproc1, pero encuentra es dbproc1 ocupado (se bloquea en espera en SPID1 para finalizar la INSERCIÓN actual, que es bloqueado en SPID2). En este momento, dbproc2 está bloqueado en el nivel de aplicación dbproc1 cuyo SPID (SPID1) se bloquea en el nivel de base de datos de SPID2. De nuevo, Esto da como resultado un interbloqueo que SQL Server no puede detectar ni resolver porque sólo uno de los recursos que participan es un recurso de SQL Server.
    Ambos ejemplos A y B son fundamentales que emite los desarrolladores de aplicaciones deben ser conscientes de. Código de aplicaciones para administrar estos casos de forma adecuada.

    Resoluciones:

    Dos soluciones fiables que se van a utilizar ya sea una consulta tiempo de espera o las conexiones enlazadas.

    • Tiempo de espera de consulta
      Cuando ha sido un tiempo de espera de consulta proporciona, si se produce el interbloqueo distribuido, se interrumpirá cuando, a continuación, se pasa el tiempo de espera. Vea 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 que un cliente tener varias conexiones para enlazar en una sola área de transacción, por lo que las conexiones se bloquean entre sí. Para obtener más información, consulte "Using Tema de 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 se sacrifiquen, o cancelado fuera de una transacción definida por el usuario, se deshará. Esto puede ocurrir también como efecto secundario de su sesión de red y el reinicio del equipo cliente Desconectando. Del mismo modo, se interrumpe una consulta seleccionada como víctima del interbloqueo de nuevo. Una consulta de modificación de datos a menudo no puede revertirse todo más rápido que el los cambios se aplicaron inicialmente. Por ejemplo, si una eliminación, insertar o actualizar instrucción estaban en ejecución durante una hora, podría tomar al menos una hora para restaurar de nuevo. Este es el comportamiento esperado, porque los cambios realizados deben ser totalmente deshace o sería la integridad transaccional y física en la base de datos pone en peligro. Debido a esto debe ocurrir, SQL Server marca el SPID en un "oro" o estado de retrotracción (lo que significa que no puede ser sacrificado o seleccionados como un interbloqueo víctima). A menudo pueden identificarse mediante la observación de la salida de sp_who, lo que puede indicar que 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.
    Resolución:

    Debe esperar a que el SPID terminar la restauración de la cambios que se han realizado.

    Si el servidor se apaga en medio de Esta operación, que será la base de datos en modo de recuperación al reiniciar y será inaccesible hasta que se procesan todas las transacciones abiertas. Inicio la recuperación toma esencialmente la misma cantidad de tiempo por transacción como tiempo de ejecución recuperación y la base de datos es accesible durante este período. Por lo tanto, forzar suele ser el servidor hacia abajo para fijar un SPID en un estado de reversión contraproducente.

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

    Si las capturas de la aplicación de cliente o el cliente estación de trabajo se reinicia, la sesión de red en el servidor no puede ser Cancela inmediatamente bajo ciertas condiciones. Desde la perspectiva del servidor, el cliente todavía parece estar presente y todavía pueden ser cualquier bloqueos adquiridos retenido. Para obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
    137983Cómo solucionar problemas de conexiones huérfanas en SQL Server

    Resolución:

    Si la aplicación cliente se desconecta sin limpiar adecuadamente sus recursos, puede terminar el SPID mediante el uso de el comando KILL. El comando KILL toma el valor SPID como entrada. Por ejemplo, Para terminar los SPID 9, simplemente emitir el comando siguiente:

    KILL 9
    						

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

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

Puede haber una tendencia a centrarse en la optimización del servidor y problemas de plataforma para enfrentarse a un problema de bloqueo. Sin embargo, esto no normalmente no dar lugar a una resolución y puede absorber el tiempo y energía mejor dirigidos a examen de la aplicación cliente y las consultas envía. No importa qué nivel de visibilidad de que la aplicación se expone con respecto a la base de datos llama a ser hecho, un problema de bloqueo, no obstante, con frecuencia requiere tanto la inspección de las instrucciones SQL exactas que envía la aplicación y la aplicación exacto de comportamiento en cuanto a la cancelación de la consulta, administración de la conexión, la obtención todas las filas de resultados y así sucesivamente. Si la herramienta de desarrollo no permite explícita el control sobre la administración de la conexión, cancelación de la consulta, el tiempo de espera de consulta, resultado pueden que la obtención y así sucesivamente, problemas de bloqueo no pueda resolver. Este potencial deben examinarse estrechamente antes de seleccionar una herramienta de desarrollo de aplicaciones para SQL Server, especialmente para los entornos OLTP críticos para el negocios.

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

Con la correcta aplicación y diseño de la consulta, Microsoft SQL Server es capaz de soportar varios miles de simultáneas usuarios en un único servidor, con el bloqueo de poco.

Propiedades

Id. de artículo: 224453 - Última revisión: lunes, 20 de mayo de 2013 - Versión: 7.0
La información de este artículo se refiere a:
  • 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
Palabras clave: 
kbsqlsetup kbhowto kbtshoot kbexpertiseinter kbinfo kbmt KB224453 KbMtes
Traducción automática
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

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com