Minette Steynberg

Definición y descripción general interbloqueo de SQL Server

December 16, 2019 by

Introducción

En esta serie, proporcionaré toda la información que necesita comprender para lidiar con los puntos muertos o interbloqueos.

En la parte 1 (de este artículo) explicaré:

  • La definición de interbloqueo o punto muerto
  • Los diferentes tipos de interbloqueos
  • Cómo SQL Server maneja los interbloqueos

Definición de interbloqueo o punto muerto

Se produce un interbloqueo cuando 2 procesos compiten por el acceso exclusivo a un recurso, pero no son capaces de poder obtener acceso exclusivo a él, porque el otro proceso lo impide. Esto da como resultado un enfrentamiento donde ninguno de los procesos puede continuar. La única forma de salir del estancamiento o interbloqueo es que uno de los procesos sea concluido. SQL Server detecta automáticamente cuándo se han producido interbloqueos y toma medidas eliminando uno de los procesos conocido como la víctima.

Los interbloqueos no solo ocurren en los bloqueos, del SQL Server 2012 y en adelante, los interbloqueos también pueden ocurrir con la memoria, y los recursos MARS (Conjuntos de resultados activos múltiples), los subprocesos de trabajo y los recursos relacionados con la ejecución de consultas paralelas.

¿Cómo sé si tengo un interbloqueo?

El primer síntoma que tendrá de un interbloqueo es el siguiente mensaje de error que se mostrará en pantalla al usuario propietario del proceso que fue seleccionado como víctima del interbloqueo.

Msg 1205, Nivel 13, Estado 51, Línea 6
Transacción (ID de proceso 62) se bloqueó en los recursos de bloqueo con otro proceso y se ha elegido como víctima de bloqueo. Vuelva a ejecutar la transacción.

El otro usuario cuyo proceso no fue seleccionado como víctima, probablemente pasará desapercibido y además ignorará por completo que su proceso participó en un interbloqueo.

Tipos de definiciones de interbloqueos

Hay 2 tipos diferentes de interbloqueos.

Definición de bloqueo de ciclo

Un ciclo de interbloqueo sucede cuando un proceso A que mantiene un bloqueo en el recurso X espera obtener un bloqueo exclusivo en el recurso Y, mientras que al mismo tiempo el proceso B mantiene un bloqueo en el recurso Y y espera obtener un bloqueo exclusivo bloquear el recurso X.


Figura 1: Imagen de un bloqueo de ciclo

La conversión bloquea la definición de interbloqueo

Una conversión de interbloqueo Se produce cuando un subproceso intenta convertir un bloqueo de un tipo a otro tipo exclusivo, pero no puede hacerlo porque otro subproceso ya tiene un bloqueo compartido en el mismo recurso.

Hay 3 tipos de bloqueos de conversiones en SQL Server.

Tipo Nombre Descripción
SIU Compartir con intento de actualización El hilo toma algunos de los bloqueos compartidos, pero también tiene actualizaciones en lob bloqueos en algunos componentes (página o fila).
SIX Compartir con intento Exclusivo El hilo incluye un bloqueo compartido y un bloqueo exclusive en algunos componentes (página o fila).
UIX Actualización con Intento Exclusivo El bloqueo U y el bloqueo IX son usados de manera separada, pero son utilizados al mismo tiempo.

Cómo SQL Server maneja los interbloqueos

El administrador de bloqueos en SQL Server busca automáticamente los interbloqueos, este hilo conductor que se llama LOCK_MONITOR busca interbloqueos cada 5 segundos. Mira todos los bloqueos que se hallan en espera para determinar si hay ciclos. Cuando detecta un interbloqueo, elige una de las transacciones como víctima y envía un error 1205 al cliente que posee la conexión. Entonces esta transacción se termina y se revierte, lo que libera todos los recursos en los que se mantuvo un bloqueo, permitiendo que se continúe con la otra transacción involucrada en el interbloqueo.

Si hay muchos interbloqueos, SQL Server ajusta automáticamente la frecuencia de la búsqueda de interbloqueos y los respalda cada 5 segundos si los interbloqueos ya no son tan frecuentes.

¿Cómo elige SQL Server a la víctima?

Hay un par de factores que entran en juego aquí. El primero es la prioridad de interbloqueo. La prioridad de interbloqueo de una transacción se puede establecer con el siguiente comando:

Los valores típicos para la prioridad de interbloqueo son:

Prioridad Valor Resultado
BAJO -5 Si otras transacciones tienen una prioridad de NORMAL o ALTA o numéricamente más alta que -5, esta transacción se elegirá como la víctima del punto muerto
NORMAL 0 Esta es la prioridad predeterminada. La transacción podría elegirse como víctima si otras transacciones tienen una prioridad superior a 0.
ALTO 5 Este proceso no se seleccionará como víctima a menos que haya un proceso con una prioridad numérica superior a 5.
<numérico> -10 to 10 Esto se puede utilizar para gestionar la prioridad del interbloqueo en un nivel más granular

Si las transacciones involucradas en un interbloqueo tienen la misma prioridad de interbloqueo, la prioridad que se revierte es la que tiene el costo más bajo. En el ejemplo en el que se ha utilizado es la que tiene menor cantidad de registro de transacciones, lo que indica que hay menos datos para revertir.

Haciendo seguimiento de los interbloqueos

Hay una serie de varias herramientas que se pueden utilizar para obtener los detalles de interbloqueos. Estos incluyen indicadores de traza 1204 y 1222. Usted también puede capturar el evento de gráfico de interbloqueo utilizando SQL Profiler.

Personalmente, yo encuentro que cuando sospecho que se está produciendo un bloqueo en mi servidor, encuentro que la configuración y la sesión de eventos extendida para registrar el gráfico de bloqueo cada vez que esta ocurre es la más fácil.

Desde SQL Server 2012 en adelante, esto se puede hacer en SQL Server Management Studio en Management\Extended Events:


Figura 2: Configuración de una sesión de eventos extendidos para capturar interbloqueos

Al usar eventos extendidos, usted podrá ver con bastante facilidad con qué frecuencia se producen interbloqueos en su base de datos e inmediatamente usted tendrá disponible el gráfico de interbloqueo en el que se muestra cada interbloqueo que se produjo con el fin de ayudarlo a resolverlo el mismo.

Cómo minimizar los interbloqueos

Aquí hay un par de consejos para minimizar los interbloqueos

  1. Siempre trate de mantener los bloqueos por el menor tiempo posible

  2. Acceda siempre a los recursos en el mismo orden

  3. Asegúrese de no tener que esperar la entrada del usuario en medio de una transacción. Primero obtenga toda la información que necesita y luego envíe la transacción

  4. Intente limitar la escalada de bloqueo, utilizando sugerencias como ROWLOCK, etc.

  5. Utilice LEER EL AISLAMIENTO INSTANTANEO COMPROMETIDO o LA AISLACIÓN INSTANTÁNEA

Resolviendo los interbloqueos

Resolver los interbloqueos puede ser un asunto complicado y está más allá del alcance de este artículo. Esté atento a mis próximos artículos que explican cómo leer el gráfico de interbloqueo que es el más útil para comprender la causa de su interbloqueo y le dará una idea de cómo abordar un interbloqueo.

Otros artículos en esta serie:

Minette Steynberg
Interbloqueos

Acerca de Minette Steynberg

Minette es una entusiasta de SQL Server y tiene más de 15 años de experiencia en SQL Server y tecnologías relacionadas. Ella tiene un grado BSC en Ciencias de Computación y también está certificada en Administración y Desarrollo de Bases de Datos SQL Server. Ella es una creyente firme en el aprendizaje durante toda la vida y es una expositora regular en grupos de usuarios de SQL Server, compartiendo su pasión y conocimiento con cualquier persona que escuche. Ver todas las entradas de Minette Steynberg

1,540 Views