Confundido sobre UPDLOCK, HOLDLOCK


Mientras investigaba el uso de Consejos de tabla , me encontré con estas dos preguntas:

Las respuestas a ambas preguntas dicen que al usar (UPDLOCK, HOLDLOCK), otros procesos no podrán leer los datos en esa tabla, pero no vi esto. Para probar, creé una tabla e inicié dos ventanas SSMS. Desde la primera ventana, ejecuté una transacción que seleccionó de la tabla utilizando varias sugerencias de tabla. Mientras la transacción se estaba ejecutando, desde la segunda ventana ejecuté varias declaraciones para ver cuáles serían bloqueadas.

La tabla de prueba:

CREATE TABLE [dbo].[Test](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](50) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Desde la ventana SSMS 1:

BEGIN TRANSACTION

SELECT * FROM dbo.Test WITH (UPDLOCK, HOLDLOCK)
WAITFOR DELAY '00:00:10'

COMMIT TRANSACTION

Desde la ventana SSMS 2 (ejecutó una de las siguientes opciones):

SELECT * FROM dbo.Test
INSERT dbo.Test(Value) VALUES ('bar')
UPDATE dbo.Test SET Value = 'baz' WHERE Value = 'bar'
DELETE dbo.Test WHERE Value= 'baz'

Efecto de diferentes sugerencias de tabla en sentencias ejecutadas en la ventana 2:

           (UPDLOCK)       (HOLDLOCK)    (UPDLOCK, HOLDLOCK)    (TABLOCKX)
---------------------------------------------------------------------------
SELECT    not blocked      not blocked       not blocked         blocked
INSERT    not blocked        blocked           blocked           blocked
UPDATE      blocked          blocked           blocked           blocked
DELETE      blocked          blocked           blocked           blocked

¿Entendí mal las respuestas dadas en esas preguntas, o cometer un error en mis pruebas? Si no, ¿por qué usar (UPDLOCK, HOLDLOCK) vs (HOLDLOCK) solo?


Explicación adicional de lo que estoy tratando de lograr:

Me gustaría seleccionar filas de una tabla y evitar que los datos de esa tabla se modifiquen mientras los estoy procesando. No estoy modificando esos datos, y me gustaría permitir que ocurran lecturas.

Esta respuesta dice claramente que (UPDLOCK, HOLDLOCK) bloqueará las lecturas (no lo que quiero). Los comentarios sobre esta respuesta implica que es HOLDLOCK lo que impide las lecturas. Para tratar de entender mejor los efectos de las sugerencias de la tabla y ver si UPDLOCK solo haría lo que quería, hice el experimento anterior y obtuve resultados que contradicen esas respuestas.

Actualmente, creo que (HOLDLOCK) es lo que debería usar, pero me preocupa que pueda haber cometido un error o pasado por alto algo que volverá a morderme en el futuro, de ahí esta pregunta.

Author: Community, 2011-10-21

2 answers

¿Por qué el bloque UPDLOCK selecciona? La Matriz de Compatibilidad de Bloqueos muestra claramente N para la contención S/U y U/S, como en Sin conflicto.

En cuanto a la pista HOLDLOCK la documentación dice:

HOLDLOCK: Es equivalente a SERIALIZABLE. Para obtener más información, consulte SERIALIZABLE más adelante en este tema.

...

SERIALIZABLE: ... El análisis se realiza con la misma semántica que una transacción que se ejecuta en el Nivel de aislamiento SERIALIZABLE...

Y el tema Transaction Isolation Level explica lo que significa SERIALIZABLE:

Ninguna otra transacción puede modificar los datos que han sido leídos por el transacción actual hasta que se complete la transacción actual.

Otras transacciones no pueden insertar nuevas filas con valores clave que caída en el rango de claves leídas por cualquier instrucción en la corriente transacción hasta la transacción actual completo.

Por lo tanto, el comportamiento que ve está perfectamente explicado por la documentación del producto:

  • UPDLOCK no bloquea la SELECCIÓN concurrente ni la INSERCIÓN, sino que bloquea cualquier ACTUALIZACIÓN o ELIMINACIÓN de las filas seleccionadas por T1
  • HOLDLOCK significa SERALIZABLE y por lo tanto permite SELECTS, pero bloquea la ACTUALIZACIÓN y ELIMINA las filas seleccionadas por T1, así como como cualquier INSERTO en el rango seleccionado por T1 (que es la tabla completa, por lo tanto cualquier insertar).
  • (UPDLOCK, HOLDLOCK): su experimento no muestra lo que bloquearía además del caso anterior, a saber, otra transacción con UPDLOCK en T2:
    SELECT * FROM dbo.Test WITH (UPDLOCK) WHERE ...
  • TABLOCKX no hay necesidad de explicaciones

La verdadera pregunta es: ¿qué estás tratando de lograr? Jugar con pistas de bloqueo w / o una comprensión completa absoluta 110% de la semántica de bloqueo está pidiendo problemas...

Después de OP edit:

Me gustaría para seleccionar filas de una tabla y evitar los datos en esa tabla de ser modificado mientras lo estoy procesando.

Debe usar uno de los niveles de aislamiento de transacciones más altos. LA LECTURA REPETIBLE evitará que los datos que lea se modifiquen. SERIALIZABLE evitará que los datos que lea se modifiquen y se inserten nuevos datos. El uso de niveles de aislamiento de transacciones es el enfoque correcto, en lugar de usar sugerencias de consulta. Kendra Little tiene un buen poster exlaining the isolation levels .

 80
Author: Remus Rusanu,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-21 14:55:36

UPDLOCK se utiliza cuando se desea bloquear una fila o filas durante una instrucción select para una futura instrucción update. La actualización futura podría ser la siguiente declaración en la transacción.

Otras sesiones todavía pueden ver los datos. Simplemente no pueden obtener bloqueos que son incompatibles con el UPDLOCK y/o el HOLDLOCK.

Utiliza UPDLOCK cuando wan para evitar que otras sesiones cambien las filas que ha bloqueado. Restringe su capacidad para actualizar o eliminar bloqueado filas.

Utiliza HOLDLOCK cuando desea evitar que otras sesiones cambien cualquiera de los datos que está viendo. Restringe su capacidad de insertar, actualizar o eliminar las filas que ha bloqueado. Esto le permite ejecutar la consulta de nuevo y ver los mismos resultados.

 18
Author: Scott Bruns,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-10-21 00:40:51