Simon Learning SQL Server

Simon Learning SQL Server

En una publicación anterior hablé sobre los niveles de Bloqueo, Bloqueo y Aislamiento. En esta publicación, voy a entrar en más detalles sobre los bloqueos de rango de teclas utilizados en el nivel de aislamiento SERIALIZABLE y también demostraré cómo usar el DMV sys.dm_tran_locks para ver los bloqueos existentes y también cómo averiguar en qué recursos se colocan los bloqueos.

Los bloqueos de rango de teclas solo se utilizan en el nivel de aislamiento SERIALIZABLE y su propósito es detener las lecturas fantasma que son posibles en los otros tres niveles de aislamiento de concurrencia pesimista, a saber, lectura no confirmada, lectura confirmada y lectura repetible. Una lectura fantasma es donde una transacción realiza una lectura en un rango de datos, por ejemplo, usando una consulta con una cláusula WHERE, y otra transacción agrega datos que satisfarán las condiciones de la cláusula WHERE. Si la primera transacción realiza la misma lectura, ahora devolverá los nuevos datos, lo que dará lugar a que aparezcan datos fantasmas en la segunda lectura dentro de la misma transacción. Los bloqueos de rango de teclas se utilizan para evitar que se agreguen datos fantasmas y se colocan en las teclas del índice que se utiliza para recuperar los datos. El siguiente ejemplo usando a la Persona.La tabla de personas de la base de datos AdventureWorks2012 mostrará cómo se utilizan estos bloqueos de rango de teclas.

La Persona.La tabla Person tiene un índice no agrupado IX_Person_LastName_FirstName_MiddleName con la columna LastName como primera columna de la clave de índice. Esto significa que cualquier consulta en la que estemos filtrando en LastName debería usar este índice para recuperar los datos.

Si ejecutamos una consulta para devolver algunas filas de Person.Filtrado de personas en un rango de apellidos, entonces deberíamos ver cómo se usa este índice. Por ejemplo,

SELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'ORDER BY LastName

Devuelve el siguiente conjunto de resultados.

 Rango de teclas 2

Y mirando el plan de ejecución podemos ver que se usa este índice.

 Rango de teclas 3

Ahora, si establecemos el nivel de aislamiento en SERIALIZABLE, abrimos una transacción y ejecutamos la misma instrucción select, deberíamos poder ver qué bloqueos se están reteniendo.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Ahora desde otra conexión podemos consultar el sys.dm_tran_locks del DMV para ver los bloqueos actuales que se están reteniendo. Solo estamos interesados en los bloqueos de los recursos CLAVE, por lo que filtramos en consecuencia.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

Rango de Teclas 4

Obtenemos siete filas retuneadas con dos valores request_mode diferentes. El modo de solicitud » S » corresponde a bloqueos compartidos colocados en las tres filas que se devuelven. Debido a que estamos utilizando el nivel de aislamiento SERIALIZABLE, estos bloqueos compartidos se mantienen hasta que la transacción se confirma o se revierte para evitar lecturas no repetibles. El modo de solicitud «RangoS-S» corresponde a los bloqueos de rango de teclas que se han concedido. Pero ¿por qué hay cuatro filas? Para responder a esto, podemos usar los datos devueltos por sys.dm_tran_locks y usar esto para averiguar qué recursos se han bloqueado realmente.

Las columnas que nos interesan son resource_associated_entity_id y resource_description. Los datos de estas columnas pueden variar en función del tipo de recurso y para más detalles, consulte TechNet. Para los recursos CLAVE, el resource_associated_entity_id corresponde al partition_id de sys.particiones

SELECT *FROM sys.partitionsWHERE partition_id in (72057594057523200, 72057594045595648)

Rango de Teclas 5

El object_id devuelto corresponde a la Persona.Mesa de personas

SELECT object_name(1765581328)

Rango de Teclas 6

Podemos ver que los dos valores de partition_id diferentes corresponden a índices diferentes en Person.Persona

SELECT * FROM sys.indexes WHERE object_id = 1765581328

Rango de Teclas 7

El sistema.la vista de particiones nos dice que las particiones en las que tenemos bloqueos son para los índices PK_Person_BusinessEntityID e IX_Person_LastName_FirstName_MiddleName.

Podemos combinar todo esto en una consulta para mayor claridad.

 SELECT L.resource_type ,L.resource_description ,L.resource_associated_entity_id ,L.request_mode ,L.request_type ,L.request_status ,OBJECT_NAME(I.object_id) AS TableName ,I.object_id ,I.index_id ,I.name AS IndexName ,I.type_desc FROM sys.dm_tran_locks L INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.resource_type = 'KEY' ORDER BY L.request_mode

Rango de Teclas 8

Esto nos muestra que tres bloqueos compartidos se colocan en las propias filas (el nivel de hoja del índice agrupado) y que cuatro bloqueos de rango de teclas se colocan en las teclas dentro del índice no agrupado. Pero esto no ha respondido a la pregunta de por qué tenemos cuatro cerraduras?

Antes de hacer eso, voy a demostrar dos formas de demostrar que los tres bloqueos compartidos se colocan en las filas que fueron devueltas por la consulta original y luego lo usaré para averiguar en qué recursos se colocan los cuatro bloqueos «Rangos-S».

En primer lugar vamos a utilizar los valores %%physloc%% y %%lockres%% junto con el sys.Función fn_PhysLocFormatter () para decirnos el archivo, página y ranura de las filas devueltas por la consulta original. En segundo lugar, utilizamos esta información en el comando DBCC PAGE.

Solo necesitamos actualizar nuestra consulta original de la siguiente manera

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Rango de Teclas 9

Ahora podemos ver que la fila de Dennis Ware se encuentra físicamente en el archivo de datos 1, en la página 2806 y en la ranura 3. También podemos ver que su recurso de bloqueo es (05c585e839d4).

Si miramos a los datos obtenidos a partir de sys.dm_tran_locks podemos ver que uno de los bloqueos compartidos tiene un resource_description de (05c585e839d4), así que esto nos dice que ese bloqueo se coloca en la fila en el índice agrupado.

Ahora podemos ejecutar el siguiente comando de PÁGINA de DBCC para ver realmente información sobre lo que hay en la página, pero para que esto devuelva cualquier resultado, necesitamos activar la bandera de seguimiento 3604.

DBCC TRACEON (3604);DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

Aquí se muestra una gran cantidad de información textual y puede especificar un número diferente en el parámetro de formato para devolver diferentes cantidades de datos. En este caso, el primer bit es el encabezado de la página y luego tenemos el Estado de asignación. Verás que la primera ranura utilizada es la Ranura 0. Hay un valor de desplazamiento que nos dice dónde en la página comienza esta ranura y un valor de longitud que nos dice cuánto tiempo es el registro.

Rango de teclas 10

Si nos desplazamos hacia abajo hasta encontrar la ranura 3, y luego nos desplazamos más allá del bloque de datos, podemos ver los datos físicos de cada columna y podemos confirmar que esta es la fila de Dennis Ware.

Intervalo de teclas 11

Ahora, si nos remontamos hasta el final de la Ranura 3 podemos ver

KeyHashValue = (05c585e839d4)

coincide con el resource_description en sys.dm_tran_locks y el valor devuelto por %%lockres%%.

Pero, ¿cómo hacemos algo similar para las cerraduras de rango de llave? ¿Cómo averiguamos qué recursos están bloqueando? Una forma sería usar sys.dm_db_database_page_allocations para averiguar qué páginas pertenecen a nuestro índice no agrupado. Pasamos el database_id, el object_id y el index_id del índice IX_Person_LastName_FirstName_MiddleName y devolverá una fila por cada página utilizada dentro de ese índice. Luego podemos ejecutar la PÁGINA DBCC con los valores allocated_page_page_id suministrados y encontrar la página que contiene Dennis Ware.

SELECT allocated_page_page_id, *FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')WHERE is_allocated = 1

Luego podemos ejecutar DBCC PAGE con el valor suministrado y encontrar la página que contiene Dennis Ware.

DBCC PAGE (10, 1, 24745, 3); -- DB_ID, FileId, PageId, Format

Lo útil es que en este caso la PÁGINA DBCC devuelve los datos de la página en un conjunto de resultados para que podamos encontrar rápidamente la página correcta, en este caso la página 24745.

 Rango de teclas 12

Ahora si usamos los valores resource_description para nuestros cuatro bloqueos de rango de teclas en sys.dm_tran_locks podemos ver que los bloqueos» RangeS-S » se han concedido a las filas que contienen datos de Dennis Ware, Victoria Ware, Clay Warten y Aaron Washington. Pero Aaron Washington no está incluido en el conjunto de resultados, entonces, ¿por qué está presente este cuarto candado de llave? Es porque cuando se toma un rango de teclas en una tecla, evitará que se agreguen datos inmediatamente antes de una tecla con un bloqueo de rango de teclas. En otras palabras, no se pueden agregar datos entre, por ejemplo, Victoria Ware y Clay Warthen, por ejemplo, Jennifer Warner.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (888888, 'VC', 'Jennifer', 'Warner')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

Rango de Teclas 13

Aquí podemos ver la cerradura de rango de llave en espera.

También significa que no se pueden agregar datos entre Timothy Ward y Aaron Washington y significa que los bloqueos de rango de teclas en realidad bloquean más datos que los que podrían satisfacer la consulta en la transacción abierta. Por ejemplo, no podríamos insertar una fila para Simon Wartz que se colocaría entre Clay Warthen y Aaron Washington, a pesar de que no se devolvería en la consulta original en la transacción abierta.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (999999, 'VC', 'Simon', 'Wartz')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

Rango de Teclas 14

Ahora que sabemos cómo se usan los bloqueos de rango de teclas, podemos averiguar qué recursos tienen los bloqueos de rango de teclas sin la necesidad de buscar en las páginas del índice no agrupado. Simplemente podemos incluir una sugerencia de tabla para devolver parte de los datos que necesitamos.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)WHERE LastName BETWEEN 'Ware' AND 'Warthen'

Rango de Teclas 18

Como puede ver, esto nos da los mismos datos de archivo, página y ranura y el mismo resource_description para las tres filas devueltas por la consulta. Ahora sabemos que el cuarto candado «RangeS-S» se coloca en la llave del índice inmediatamente después de la llave para el Warthen de arcilla, es decir, la ranura 140. De nuevo podemos usar la PÁGINA DBCC para ver los datos en la página 25065.

Ahora en la demostración que acabo de ejecutar, resulta que tenemos un índice no agrupado con la primera columna en la clave de agrupamiento siendo la que estamos usando en nuestra cláusula WHERE. Pero, ¿qué pasa si eliminamos el índice no agrupado? O en otras palabras, ¿qué sucede si ejecutamos una consulta contra otro filtrado de tabla en una columna no incluida en una clave de índice? Para demostrarlo fácilmente, podemos eliminar el índice no agrupado.

DROP INDEX ON .BEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Recuperamos las mismas filas que esperábamos, pero esta vez SQL Server ha tenido que usar el índice agrupado para obtener los datos.

 Rango de teclas 15

¿Qué cerraduras tenemos? Esta vez no hay un bloqueo de rango de teclas que se pueda aplicar, por lo que debe colocarse un bloqueo compartido en toda la mesa.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_modeSELECT object_name(1765581328)

Rango de Teclas 16

Debido a que este bloqueo compartido se mantiene hasta que se cierra la transacción, esto significa que no se pueden actualizar los datos de la tabla hasta que se complete la transacción, aunque solo hayamos leído tres filas.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (777777, 'VC', 'Simon', 'ZZZZZ')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_mode

Rango de Teclas 17

Esto es algo que debe tener en cuenta si usa el nivel de aislamiento SERIALIZABLE.

Resumen

Si se utiliza una columna de claves para filtrar datos en una consulta, los bloqueos de rango de claves se pueden tomar contra las claves de ese índice. Se tomarán contra todas las claves que satisfagan la consulta más la siguiente clave secuencialmente fuera de la consulta. Esto evita que se agreguen datos que inserten una nueva llave inmediatamente antes de una llave con un bloqueo de rango de teclas.

Deja una respuesta

Tu dirección de correo electrónico no será publicada.