• Artículo

Concentración de bloqueos (motor de base de datos)

La extensión de bloqueo es el proceso de convertir muchos bloqueos concretos en menos bloqueos más generales, lo que reduce la sobrecarga del sistema al tiempo que aumenta la probabilidad de contención de simultaneidad.

A medida que SQL Server Database Engine (Motor de base de datos de SQL Server) adquiere bloqueos de bajo nivel, también coloca bloqueos de intención en los objetos que contienen los objetos de nivel más bajo:

  • Al bloquear filas o intervalos de clave de índice, Motor de base de datos coloca un bloqueo de intención en las páginas que contienen las filas o claves.

  • Al bloquear páginas, Motor de base de datos coloca un bloqueo de intención en los objetos de más alto nivel que contienen las páginas. Además del bloqueo de intención en el objeto, los bloqueos de página de intento se solicitan en los objetos siguientes:

    • Páginas de nivel de hoja de índices no clúster

    • Páginas de datos de los índices clúster

    • Páginas de datos de montón

Puede que Motor de base de datos realice bloqueos de fila y página para una misma instrucción a fin de minimizar el número de bloqueos y reducir la probabilidad de que sea necesario realizar la extensión de bloqueo. Por ejemplo, Motor de base de datos podría colocar bloqueos de página en un índice no clúster (si se seleccionan suficientes claves contiguas del nodo del índice como para satisfacer la consulta) y bloqueos de fila en los datos.

Para concentrar bloqueos, Motor de base de datos intenta cambiar el bloqueo de la tabla al correspondiente bloqueo completo; cambiar, por ejemplo, un bloqueo con intención exclusivo (IX) a un bloqueo exclusivo (X) o un bloqueo con intención compartido (IS) a un bloqueo compartido (S). Si el intento de extensión de bloqueo se realiza correctamente y se adquiere el bloqueo de toda la tabla, se liberan todos los bloqueos de montón o árbol B, página (PAGE) o fila (RID) retenidos por la transacción en el montón o el índice. Si no se puede adquirir el bloqueo completo, no se produce ninguna concentración de bloqueos en el momento y Motor de base de datos continúa para adquirir bloqueos de fila, clave o página.

Motor de base de datos no concentra bloqueos de filas o intervalos de claves en bloqueos de páginas, sino que los concentra directamente en bloqueos de tablas. De manera similar, los bloqueos de páginas se concentran siempre en bloqueos de tablas. En SQL Server 2008, el bloqueo de las tablas con particiones puede extenderse al nivel HoBT para la partición asociada en lugar de hacerlo al bloqueo de la tabla. Un bloqueo de nivel HOBT no bloquea necesariamente los HoBTs alineados para la partición.

Nota

Los bloqueos de nivel HOBT normalmente aumentan la simultaneidad, pero suponen un riesgo potencial de interbloqueos cuando cada una de las transacciones que están bloqueando diferentes particiones quiere ampliar sus bloqueos exclusivos a las demás particiones. En algunos casos poco habituales, la granularidad de bloqueo TABLE dará mejores resultados.

Si un intento de extensión de bloqueo no se produce debido a conflictos de bloqueo retenidos por transacciones simultáneas, Motor de base de datos intentará realizar de nuevo la concentración de bloqueos por cada 1.250 bloqueos adicionales adquiridos por la transacción.

Cada evento de concentración funciona principalmente en una sola instrucción de Transact-SQL. Cuando el evento se inicia, Motor de base de datos intenta concentrar los bloqueos pertenecientes a la transacción actual que se encuentren en alguna de las tablas a las que se hace referencia en la instrucción activa, siempre que cumpla los requisitos de umbral de concentración. Si el evento de concentración se inicia antes de que la instrucción tenga acceso a la tabla, no se hace ningún intento de concentrar los bloqueos de la tabla. Si la extensión de bloqueo se realiza correctamente, los bloqueos adquiridos por la transacción en una instrucción anterior que sigan retenidos en el momento en que se inicia el evento se concentrarán, si se hace referencia a la tabla en la instrucción actual y se incluye la tabla en el evento de concentración.

Por ejemplo, suponga que la sesión realiza estas operaciones:

  • Inicia una transacción.

  • Actualiza TableA. Esto genera bloqueos exclusivos de fila en TableA que se retienen hasta que se completa la transacción.

  • Actualiza TableB. Esto genera bloqueos exclusivos de fila en TableB que se retienen hasta que se completa la transacción.

  • Ejecuta una instrucción SELECT que combina TableA con TableC. El plan de ejecución de la consulta llama a las filas que se van a recuperar de TableA antes de que se recuperen las filas de TableC.

  • La instrucción SELECT desencadena la extensión de bloqueo mientras recupera filas de TableA y antes de tener acceso a TableC.

Si la extensión de bloqueo se realiza correctamente, solo se concentran los bloqueos retenido por la sesión de TableA. Esto incluye los bloqueos compartidos de la instrucción SELECT y los bloqueos exclusivos de la instrucción UPDATE anterior. Aunque para determinar si se debe realizar la extensión de bloqueo solo se cuentan los bloqueos que la sesión adquirió en TableA para la instrucción SELECT, cuando se lleva a cabo la concentración, todos los bloqueos retenidos por la sesión en TableA se concentran en un bloqueo exclusivo en la tabla y se liberan los demás bloqueos de granularidad inferior, incluidos los bloqueos con intención, de TableA.

No se intentan concentrar bloqueos en TableB porque no había una referencia activa a TableB en la instrucción SELECT. De manera similar, no se intenta concentrar los bloqueos de TableC, ya que no están concentrados porque aún no se había tenido acceso a esta tabla cuando se produjo la concentración.

Umbrales de extensión de bloqueo

La extensión de bloqueo se activa cuando dicha operación no se ha deshabilitado en la tabla mediante el uso de la opción ALTER TABLE SET LOCK_ESCALATION, y cuando se da cualquiera de las siguientes condiciones:

  • Una sola instrucción Transact-SQL adquiere al menos 5.000 bloqueos en un solo índice o tabla sin particiones.

  • Una sola instrucción Transact-SQL adquiere al menos 5.000 bloqueos en una sola partición de una tabla o índice con particiones y la opción ALTER TABLE SET LOCK_ESCALATION tiene asignado el valor AUTO.

  • El número de bloqueos en una instancia de Motor de base de datos supera los umbrales de memoria o de configuración.

Si los bloqueos no se pueden concentrar debido a conflictos de bloqueo, el Motor de base de datos desencadena periódicamente la extensión de bloqueo cada 1.250 nuevos bloqueos adquiridos.

Umbral de concentración para una instrucción Transact-SQL

Cuando el Motor de base de datos comprueba las posibles extensiones en los 1250 bloqueos recientemente adquiridos, se producirá una extensión de bloqueo si y solo si una instrucción Transact-SQL ha adquirido al menos 5000 bloqueos en una referencia única de una tabla. La extensión de bloqueo se activa cuando una instrucción Transact-SQL adquiere al menos 5.000 bloqueos en una referencia única de una tabla. Por ejemplo, la extensión de bloqueo no se desencadena si una instrucción adquiere 3.000 bloqueos en un índice y 3.000 bloqueos en otro índice de la misma tabla. De forma similar, la extensión de bloqueo no se desencadena si una instrucción tiene una autocombinación en una tabla y cada referencia a la tabla solo adquiere 3.000 bloqueos de la tabla.

La extensión de bloqueo solo se produce para tablas a las que se ha obtenido acceso en el momento en que se desencadena la concentración. Suponga que una instrucción SELECT única es una combinación que tiene acceso a tres tablas por este orden: TableA, TableB y TableC. La instrucción adquiere 3.000 bloqueos de fila del índice clúster de TableA y, al menos, 5.000 bloqueos de fila del índice clúster de TableB, pero todavía no ha tenido acceso a TableC. Cuando Motor de base de datos detecta que la instrucción ha adquirido, al menos, 5.000 bloqueos de fila de TableB, intenta concentrar todos los bloqueos retenidos por la transacción actual de TableB. También intenta concentrar todos los bloqueos retenidos por la transacción actual de TableA, pero como el número de bloqueos de TableA es < 5.000, no se realiza la concentración. No se intenta ninguna extensión de bloqueo en TableC porque aún no se había tenido acceso a ella cuando se produjo la concentración.

Umbral de concentración para una instancia del motor de base de datos

Siempre que el número de bloqueos sea mayor que el umbral de memoria para la extensión de bloqueo, Motor de base de datos desencadena la concentración de bloqueos. El umbral de memoria depende del valor de la opción de configuración locks:

  • Si la opción locks tiene asignado el valor predeterminado de 0, el umbral de extensión de bloqueo se alcanza cuando la memoria que utilizan los objetos del bloqueo es un 24 por ciento de la memoria que utiliza Motor de base de datos, sin contar la memoria AWE. La estructura de datos que se utiliza para representar un bloqueo tiene aproximadamente una longitud de 100 bytes. Este umbral es dinámico debido a que Motor de base de datos adquiere y libera memoria dinámicamente para ajustarse a las variaciones de las cargas de trabajo.

  • Si la opción locks es un valor distinto de 0, el umbral de extensión de bloqueo es 40 por ciento (o menos si existe alguna presión de memoria) del valor de la opción.

Motor de base de datos puede elegir cualquier instrucción activa de cualquier sesión para la extensión; además, por cada 1.250 nuevos bloqueos, elegirá instrucciones para extensión siempre y cuando la memoria de bloqueo utilizada en la instancia se mantenga por encima del umbral.

Concentrar tipos de bloqueo mixtos

Cuando se produce una extensión de bloqueo, el bloqueo seleccionado para el montón o índice es lo suficientemente fuerte como para cumplir con los requisitos del bloqueo de bajo nivel más restrictivo.

Por ejemplo, suponga que una sesión:

  • Inicia una transacción.

  • Actualiza una tabla que contiene un índice clúster.

  • Emite una instrucción SELECT que hace referencia a la misma tabla.

La instrucción UPDATE adquiere estos bloqueos:

  • Bloqueos exclusivos (X) en las filas de datos actualizadas.

  • Bloqueos con intención exclusivos (IX) en las páginas del índice clúster que contienen estas filas.

  • Un bloqueo IX en el índice clúster y otro en la tabla.

La instrucción SELECT adquiere estos bloqueos:

  • Bloqueos compartidos (S) en todas las filas de datos que lee, a no ser que la fila esté ya protegida por un bloqueo X de la instrucción UPDATE.

  • Bloqueos con intención compartidos en todas las páginas de índice clúster que contienen dichas filas, a no ser que la página esté ya protegida por un bloqueo IX.

  • Ningún bloqueo en el índice clúster o la tabla porque ya están protegidos por bloqueos IX.

Si la instrucción SELECT adquiere suficientes bloqueos como para desencadenar la extensión de bloqueo y ésta se realiza correctamente, el bloqueo IX de la tabla se convierte en un bloqueo X y se liberan todos los bloqueos de fila, página e índice. Las actualizaciones y las lecturas están protegidas por el bloqueo X de la tabla.

Reducir bloqueos y extensiones

En la mayoría de los casos, Motor de base de datos presta el mejor rendimiento cuando funciona con la configuración predeterminada para bloqueo y extensión de bloqueo. Si una instancia de Motor de base de datos genera gran cantidad de bloqueos y se producen extensiones de bloqueo frecuentes, considere la posibilidad de reducir la cantidad de bloqueos mediante:

  • El uso de un nivel de aislamiento que no genere bloqueos compartidos para operaciones de lectura.

    • Un nivel de aislamiento READ COMMITTED si la opción de base de datos READ_COMMITTED_SNAPSHOT está en ON.

    • Un nivel de aislamiento SNAPSHOT.

    • Un nivel de aislamiento READ UNCOMMITTED. Este nivel solo se puede utilizar en sistemas que puedan funcionar con lecturas no actualizadas.

Nota

Cambiar el nivel de aislamiento afecta a todas las tablas en la instancia de Motor de base de datos.

  • El uso de las sugerencias de tabla PAGLOCK o TABLOCK para que Motor de base de datos utilice bloqueos de página, montón o índice en lugar de bloqueos de filas. Esta opción, sin embargo, aumenta los problemas derivados de usuarios que bloquean a otros usuarios al intentar obtener acceso a los mismos datos y solo se debe utilizar en sistemas con pocos usuarios simultáneos.

  • Para las tablas con particiones, utilice la opción de LOCK_ESCALATION de ALTER TABLEpara extender los bloqueos al nivel HoBT en lugar de hacerlo al nivel de la tabla o para deshabilitar la extensión de bloqueo.

También se pueden utilizar las marcas de seguimiento 1211 y 1224 para deshabilitar todas las extensiones de bloqueo o solo algunas. Para obtener más información, vea Marcas de seguimiento (Transact-SQL). Además, supervise la extensión de bloqueo mediante el uso del evento Lock:Escalation de SQL Server Profiler; y vea Usar el Analizador de SQL Server.