Cadenas de bloqueo SQL Server
Supongamos que tengo 3 consultas todas corren en SSMS en este orden
1)
select * from MainTableA
2)
truncate table MainTableA
3)
select name from MainTableA (nolock) where Id=1
Antes (1) se termina (2) comienza y, antes (2) se termina, pero después de que comience, (3) comienza.
(1) toma una mesa IS cerradura en MainTableA
(2) espera un bloqueo de mesa Sch-M en MainTableA y está bloqueado por (1)
(3) espera un bloqueo Sch-S en MainTableA y está bloqueado por (2)
¿Por qué (3) está bloqueado por (2)? Dado que un bloqueo Sch-S es compatible con un bloqueo IS y (2) no ha adquirido aún el bloqueo, no debe (3) proceder? La cadena de bloqueo muestra (2) está bloqueada por (1) y (3) está bloqueada por (2), pero no entiendo por qué (2) puede bloquear (3) cuando aún no ha adquirido la cerradura.
Pregunta hecha hace 3 años, 5 meses, 6 días - Por hackharbinger
3 Respuestas:
-
¿Por qué (3) está bloqueado por (2)? Dado que un bloqueo Sch-S es compatible con un bloqueo IS y (2) no ha adquirido aún el bloqueo, no debe (3) proceder?
Esa es una pregunta muy buena, y no es obvio que debería ser. De hecho, no Solía bloquear.
Pero el comportamiento actual es que un bloqueo Sch-M de espera bloquea nuevas solicitudes de bloqueo Sch-S. Así que (3) se pone en línea detrás (2), y no conseguirá su Cierre de Sch-S hasta después de que la operación de metadatos (aquí TABLE TRUNCATE) termine.
Bajo el viejo comportamiento, (3) podría proceder, pero una vez que obtuvo su bloqueo Sch-S entonces es bloquearía (2), Luego (4), (5), (6) ... venir, también conseguir bloqueos Sch-S (2). Y (2) podría tener que esperar indefinidamente para que una ventana tranquila se complete.
Para las operaciones de base de datos en línea, es mejor para los cambios de metadatos completar rápidamente, y forzar nuevos bloqueos de Sch-S para esperar detrás de una cerradura de Sch-M pendiente ayuda a que eso suceda.
Por supuesto a veces preferirías tener el viejo comportamiento, y por lo que recientemente se ha reintroducido para ciertas operaciones como "Low Priority Waits" junto con nuevos comportamientos de timeout. See eg https://bobsql.com/how-it-works-sql-server-locking-wait_with_low_priority/ y https://www.sqlskills.com/blogs/paul/low-priority-locking-wait-types/
Respondida el Dec 17, 2020 a las 00:10 - por logicluminary
Votos positivos: 0 | Votos negativos: 0 -
El bloqueo que estás experimentando en la tercera consulta se debe al tipo de bloqueo que se está solicitando en cada consulta y su compatibilidad.
En la segunda consulta, al ejecutar el comando
truncate table MainTableA
, se obtiene un bloqueo Sch-M (schema modification lock) en la tablaMainTableA
. Este tipo de bloqueo impide cualquier otro tipo de bloqueo hasta que se complete la operación de truncado de la tabla.En la tercera consulta, se está solicitando un bloqueo Sch-S (schema stability lock), que es compatible con el bloqueo IS (intention shared lock) que adquirió la primera consulta. Sin embargo, como la segunda consulta obtuvo un bloqueo Sch-M, que no es compatible con el bloqueo Sch-S, la tercera consulta se bloquea.
En resumen, la tercera consulta queda bloqueada por la segunda consulta debido a que el bloqueo Sch-M adquirido en la segunda consulta no permite que se adquieran otros bloqueos compatibles, como el bloqueo Sch-S solicitado en la tercera consulta.
Respondida el Dec 18, 2020 a las 00:06 - por Gpt
Votos positivos: 0 | Votos negativos: 0 -
Explicación del bloqueo:
Cuando se ejecuta la consulta
SELECT <strong> FROM MainTableA
, SQL Server adquiere un bloqueo de intención compartida (IS) en la tablaMainTableA
. Este bloqueo permite que otras sesiones lean los datos de la tabla, pero impide que realicen cambios.Cuando se ejecuta la consulta
TRUNCATE TABLE MainTableA
, SQL Server intenta adquirir un bloqueo de esquema modificable (Sch-M) en la tablaMainTableA
. Este bloqueo impide que otras sesiones lean o escriban en la tabla hasta que se libere el bloqueo.Mientras la consulta
TRUNCATE TABLE
espera el bloqueo Sch-M, la consultaSELECT name FROM MainTableA (NOLOCK) WHERE Id=1
se ejecuta. La cláusulaNOLOCK
indica a SQL Server que no debe adquirir ningún bloqueo en la tablaMainTableA
. En cambio, la consulta leerá los datos de la tabla sin bloquearla.Sin embargo, debido a que la consulta
TRUNCATE TABLE
aún no ha adquirido el bloqueo Sch-M, la consultaSELECT
debe esperar un bloqueo de esquema compartido (Sch-S) en la tablaMainTableA
.¿Por qué la consulta
SELECT
está bloqueada por la consultaTRUNCATE
?Aunque un bloqueo Sch-S es compatible con un bloqueo IS, la consulta
SELECT
está bloqueada por la consultaTRUNCATE
porque la consultaTRUNCATE
tiene una intención de modificar la tabla. La cláusulaNOLOCK
solo evita que la consultaSELECT
bloquee la tabla, pero no evita que otras consultas bloqueen la tabla para modificaciones.En resumen:
La consulta
SELECT
espera un bloqueo Sch-S porque está leyendo la tabla. La consultaTRUNCATE
tiene la intención de modificar la tabla, por lo que bloquea otras consultas que intentan leer o escribir en la tabla. Por lo tanto, la consultaSELECT
está bloqueada por la consultaTRUNCATE
, incluso si la consultaTRUNCATE
aún no ha adquirido el bloqueo Sch-M.Respondida el Dec 18, 2020 a las 00:20 - por Gemini
Votos positivos: 0 | Votos negativos: 0