transacciones - ¿Qué consejos de bloqueo debo usar(T-SQL)?
transacciones sql server (5)
CON UPDLOCK, HOLDLOCK
Quiero implementar una transacción atómica como la siguiente:
BEGIN TRAN A
SELECT id
FROM Inventory
WITH (???)
WHERE material_id = 25 AND quantity > 10
/*
Process some things using the inventory record and
eventually write some updates that are dependent on the fact that
that specific inventory record had sufficient quantity (greater than 10).
*/
COMMIT TRAN A
El problema es que existen otras transacciones que consumen cantidades de nuestro inventario, por lo que entre el momento en que se selecciona el registro y las actualizaciones se escriben en la transacción A, ese registro podría convertirse en una selección no válida porque su cantidad podría haberse reducido por debajo del umbral en la cláusula WHERE.
Entonces, la pregunta es: ¿qué consejos de bloqueo debo usar en la cláusula WITH para evitar que se modifique el registro de inventario seleccionado antes de terminar mis actualizaciones y comprometer la transacción?
EDITAR: Entonces, gracias a John, una buena solución parece ser establecer el nivel de aislamiento de la transacción en REPEATABLE READ. Esto asegurará que "ninguna otra transacción pueda modificar los datos que haya leído la transacción actual hasta que se complete la transacción actual".
Creo que esto sería UPDLOCK.
En realidad, es mejor configurar el nivel de aislamiento de la transacción en lugar de utilizar una sugerencia de consulta.
La siguiente referencia de Books Online proporciona detalles de cada uno de los diferentes niveles de aislamiento.
http://msdn.microsoft.com/en-us/library/ms173763.aspx
Aquí hay un buen artículo que explica los diversos tipos de comportamiento de bloqueo en SQL Server y proporciona ejemplos también.
http://www.sqlteam.com/article/introduction-to-locking-in-sql-server
MSSQL:
SELECT id
FROM Inventory (UPDLOCK)
WHERE material_id = 25 AND quantity > 10;
http://www.devx.com/tips/Tip/13134
Por casualidad, te interesa PostgreSQL:
SELECT id
FROM Inventory
WHERE material_id = 25 AND quantity > 10
FOR UPDATE;
WITH (HOLDLOCK) permite a otros lectores. UPDLOCK como se sugiere en otra parte es exclusivo.
HOLDLOCK evitará otras actualizaciones pero pueden usar los datos que se actualicen más tarde.
UPDLOCK evitará que cualquiera lea los datos hasta que los confirme o los retrotraiga.
¿Has mirado sp_getapplock ? Esto le permitiría serializar este código (si es el único bit de actualización) sin bloqueo UPDLOCK
Editar: El problema radica principalmente en este código que se ejecuta en 2 sesiones diferentes. Con HOLDLOCk o REPEATABLE_READ, los datos se leerán en la segunda sesión antes de la actualización de la primera sesión. Con UPDLOCK, nadie puede leer los datos en ninguna sesión.