sql server - with - Confundido sobre UPDLOCK, HOLDLOCK

with nolock sql server

Question

Mientras investigaba el uso de Table Hints , encontré estas dos preguntas:

• ¿Qué consejos de bloqueo debo usar (T-SQL)?

• ¿Qué efecto tiene HOLDLOCK en UPDLOCK?

Las respuestas a ambas preguntas dicen que al usar (UPDLOCK, HOLDLOCK) , otros procesos no podrán leer datos en esa tabla, pero no vi esto. Para probar, creé una tabla y comencé dos ventanas SSMS. Desde la primera ventana, ejecuté una transacción que se seleccionó de la tabla usando varias sugerencias de tabla. Mientras se ejecutaba la transacción, desde la segunda ventana ejecuté varias declaraciones para ver cuál se bloquearía.

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 1 de SSMS:

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

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

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 sobre instrucciones 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 cometí un error en mis pruebas? Si no, ¿por qué (UPDLOCK, HOLDLOCK) (HOLDLOCK) (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 se modifiquen los datos en esa tabla mientras la estoy procesando. No modifico esos datos y me gustaría permitir que ocurran las lecturas.

Esta respuesta dice claramente que (UPDLOCK, HOLDLOCK) bloqueará las lecturas (no lo que yo quiero). Los comentarios sobre esta respuesta implican que es HOLDLOCK que impide las lecturas. Para tratar de comprender mejor los efectos de las sugerencias de la tabla y ver si UPDLOCK solo haría lo que yo 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 haber cometido un error o haber pasado por alto algo que volverá a afectarme en el futuro, de ahí esta pregunta.

Answer 1

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

En cuanto a la sugerencia de HOLDLOCK, la documentación dice:

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

...

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

y el tema Nivel de aislamiento de transacciones explica lo que significa SERIALIZABLE:

Ninguna otra transacción puede modificar los datos que ha leído la transacción actual hasta que se complete la transacción actual.

Otras transacciones no pueden insertar nuevas filas con valores clave que corresponderían al rango de claves leídas por cualquier instrucción en la transacción actual hasta que se complete la transacción actual.

Por lo tanto, el comportamiento que ve se explica perfectamente en la documentación del producto:

• UPDLOCK no bloquea SELECT o INSERT concurrentes, pero bloquea cualquier UPDATE o DELETE de las filas seleccionadas por T1
• HOLDLOCK significa SERALIZABLE y, por lo tanto, permite SELECTS, pero bloquea UPDATE y DELETES de las filas seleccionadas por T1, así como cualquier INSERT en el rango seleccionado por T1 (que es la tabla completa, por lo tanto, cualquier inserción).
• (UPDLOCK, HOLDLOCK): su experimento no muestra qué bloquearía además del caso anterior, es decir, otra transacción con UPDLOCK en T2 :
  SELECT * FROM dbo.Test WITH (UPDLOCK) WHERE ...
• TABLOCKX no necesita explicaciones

La verdadera pregunta es ¿qué estás tratando de lograr ? Jugar con pistas de bloqueo sin una comprensión absoluta completa del 110% de la semántica de bloqueo es mendigar problemas ...

Después de la edición de OP:

Me gustaría seleccionar filas de una tabla y evitar que se modifiquen los datos en esa tabla mientras la estoy procesando.

Debería usar uno de los niveles de aislamiento de transacción más altos. REPEATABLE READ evitará que se modifiquen los datos que lee. SERIALIZABLE evitará que se modifiquen los datos que usted lee y que se inserten nuevos datos. El uso de niveles de aislamiento de transacción es el enfoque correcto, en lugar de utilizar sugerencias de consulta. Kendra Little tiene un lindo afiche que expone los niveles de aislamiento .

Answer 2

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

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

Utiliza UPDLOCK cuando desea evitar que otras sesiones cambien las filas que ha bloqueado. Restringe su capacidad de actualizar o eliminar filas bloqueadas.

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 nuevamente y ver los mismos resultados.