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, 0 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 tabla MainTableA. 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 tabla MainTableA. 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 tabla MainTableA. 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 consulta SELECT name FROM MainTableA (NOLOCK) WHERE Id=1 se ejecuta. La cláusula NOLOCK indica a SQL Server que no debe adquirir ningún bloqueo en la tabla MainTableA. 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 consulta SELECT debe esperar un bloqueo de esquema compartido (Sch-S) en la tabla MainTableA.

    ¿Por qué la consulta SELECT está bloqueada por la consulta TRUNCATE?

    Aunque un bloqueo Sch-S es compatible con un bloqueo IS, la consulta SELECT está bloqueada por la consulta TRUNCATE porque la consulta TRUNCATE tiene una intención de modificar la tabla. La cláusula NOLOCK solo evita que la consulta SELECT 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 consulta TRUNCATE 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 consulta SELECT está bloqueada por la consulta TRUNCATE, incluso si la consulta TRUNCATE 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