sql server - with - ¿Qué efecto tiene HOLDLOCK en UPDLOCK?

with nolock sql server

Question

He visto muchos ejemplos de la sugerencia de HOLDLOCK que se usa en combinación con UPDLOCK ( como esto ). Sin embargo, la documentación de Microsoft para estos consejos hace que parezca que HOLDLOCK debería ser redundante, ya que UPDLOCK ya persiste el bloqueo hasta el final de la transacción. (También parece decir que HOLDLOCK solo se aplica a los bloqueos compartidos de todos modos).

¿Cómo afecta HOLDLOCK a la consulta, en todo caso?

Answer 1

Tiene un gran impacto.

El bloqueo de actualización tiene un bloqueo de actualización en la fila, una actualización de la intención en la página y un bloqueo compartido en la tabla / base de datos.

Esto no impide que otras consultas accedan a los datos dentro de la tabla, ya que los bloqueos en la página / base de datos son simplemente bloqueos compartidos. Es posible que no puedan bloquear bloqueos contra la fila / página / tabla individual al intentar realizar una operación que contradiga los bloqueos. Si eso ocurriera, la solicitud se pondría en cola detrás de los bloqueos actuales y esperaría a que esté disponible antes de que pudiera continuar.

Al utilizar el bloqueo de retención, la consulta se está forzando a ser serializada, bloqueando la tabla exclusivamente hasta que la acción haya finalizado. Esto evita que cualquiera lea la tabla a menos que se use la sugerencia nolock, permitiendo una lectura potencialmente sucia.

Para ver el efecto, genere una tabla de ejemplo ''foo'' y ponga algunos datos de basura en ella.

begin tran

select * from foo with (updlock)
where tableid = 1
-- notice there is no commit tran

Abre otra ventana y prueba:

select * from foo

Las filas regresan, ahora confirme la transacción de consulta original. Vuelva a ejecutarlo modificado para usar también el bloqueo de espera:

begin tran

select * from foo with (updlock, holdlock)
where tableid = 1

Regrese a la otra ventana e intente seleccionar los datos nuevamente, la consulta no devolverá los valores ya que está bloqueada por el bloqueo exclusivo. Confirme la transacción en la primera ventana y los resultados de la segunda consulta aparecerán ya que ya no están bloqueados.

La prueba final es usar nolock, ejecutar la transacción nuevamente usando updlock y holdlock. A continuación, ejecute lo siguiente en la segunda ventana:

select * from foo (nolock)

Los resultados volverán automáticamente, ya que ha aceptado el riesgo de una lectura sucia (lectura no confirmada).

Por lo tanto, se considera que tiene un gran impacto, ya que está forzando acciones contra esa tabla para que se serialicen, lo que podría ser lo que usted desea (dependiendo de la actualización que se realice) o creará un cuello de botella muy grande en esa tabla. Si todos hicieran eso con una mesa ocupada con transacciones de larga ejecución, esto causaría retrasos significativos en una aplicación.

Al igual que con todas las funciones SQL, cuando se usan correctamente, pueden ser potentes, pero el mal uso de una característica / sugerencia puede causar problemas importantes. Prefiero usar las sugerencias como último recurso cuando tengo que anular el motor, no como un enfoque predeterminado.

Editar según lo solicitado: probado en SQL 2005, 2008, 2008R2 (All Enterprise): todo instalado en prácticamente la configuración predeterminada, base de datos de prueba creada con todos los valores predeterminados (solo se ingresó el nombre de la base de datos).

Answer 2

La respuesta de Andrew es correcta según la documentación de MSDN, sin embargo, probé en 2008R2 y 2012 y no veo este comportamiento, así que por favor PRUEBA

El comportamiento que estoy viendo es el siguiente:

Primero ejecuta esto en una base de datos de juego.

CREATE TABLE [dbo].[foo](
    [tableid] [int] IDENTITY(1,1) NOT NULL,
    [Col2] [varchar](100) NOT NULL,
    CONSTRAINT [PK_foo] PRIMARY KEY CLUSTERED 
    (
        [tableid] ASC
    )
)

... y poner algunas filas en.

Ahora pegue este código en dos pestañas de consulta (cambie el texto ''tab one'' en la pestaña dos):

begin tran

select * from foo with (UPDLOCK, HOLDLOCK)
where tableid = 1

UPDATE foo SET Col2 = ''tab one''
where tableid = 1

commit tran

Y pon esto en otra pestaña 3 :

select * from foo
where tableid = 1

1. Asegúrese de que está apuntando a su base de datos de juego donde está la mesa.

2. Resalte todo ANTES de la instrucción de actualización en la pestaña 1 y ejecute.

3. Haga lo mismo en la pestaña 2 , encontrará que la pestaña 2 NO se completará y aún se está ejecutando.

4. Ahora ejecute el SELECT simple en la pestaña 3 de mi entorno y complete.

5. Resalte la instrucción de actualización en la pestaña 1 y ejecútela (NO haga la confirmación todavía), verá que la pestaña 2 todavía se está ejecutando.

6. Continúe y ejecute la confirmación en la pestaña 1 ... la pestaña 2 ahora completará la selección ... puede ejecutar el resto.