En tsql, ¿es seguro un inserto con una instrucción Select en términos de concurrencia?

En mi respuesta a esta pregunta SO , sugiero usar una única statement de inserción, con una selección que incremente un valor, como se muestra a continuación.

Insert Into VersionTable (Id, VersionNumber, Title, Description, ...) Select @ObjectId, max(VersionNumber) + 1, @Title, @Description From VersionTable Where Id = @ObjectId 

Sugerí esto porque creo que esta afirmación es segura en términos de concurrencia, ya que si se ejecuta otra inserción para la misma identificación de objeto al mismo tiempo, no hay posibilidad de tener números de versión duplicados.

¿Estoy en lo correcto?

Como escribe Paul: No, no es seguro , por lo que me gustaría agregar evidencia empírica: Cree una tabla Table_1 con un ID campo y un registro con valor 0 . A continuación, ejecute el siguiente código simultáneamente en dos ventanas de consulta de Management Studio :

 declare @counter int set @counter = 0 while @counter < 1000 begin set @counter = @counter + 1 INSERT INTO Table_1 SELECT MAX(ID) + 1 FROM Table_1 end 

Entonces ejecuta

 SELECT ID, COUNT(*) FROM Table_1 GROUP BY ID HAVING COUNT(*) > 1 

En mi SQL Server 2008, se creó una ID ( 662 ) dos veces. Por lo tanto, el nivel de aislamiento predeterminado aplicado a declaraciones individuales no es suficiente.


EDITAR: Claramente, envolver el INSERT con BEGIN TRANSACTION y COMMIT no lo reparará, ya que el nivel de aislamiento predeterminado para las transacciones sigue siendo READ COMMITTED , que no es suficiente. Tenga en cuenta que establecer el nivel de aislamiento de la transacción en REPEATABLE READ tampoco es suficiente. La única forma de hacer que el código anterior sea seguro es agregar

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

en la cima. Esto, sin embargo, causó lockings de vez en cuando en mis pruebas.

EDITAR: La única solución que encontré que es segura y no produce interlockings (al menos en mis pruebas) es bloquear explícitamente la tabla exclusivamente (el nivel predeterminado de aislamiento de transacción es suficiente aquí). Ten cuidado, sin embargo; esta solución puede matar el rendimiento:

 ...loop stuff... BEGIN TRANSACTION SELECT * FROM Table_1 WITH (TABLOCKX, HOLDLOCK) WHERE 1=0 INSERT INTO Table_1 SELECT MAX(ID) + 1 FROM Table_1 COMMIT ...loop end... 

El aislamiento predeterminado de read commited lo hace inseguro; si dos de estos se ejecutan en paralelo perfecto, obtendrá un duplicado ya que no se aplica ningún locking de lectura.

Necesita niveles de aislamiento REPEATABLE READ o SERIALIZABLE para que sea seguro.

Creo que tu suposición es incorrecta. Cuando consulta la tabla VersionNumber, solo está colocando un locking de lectura en la fila. Esto no impide que otros usuarios lean la misma fila de la misma tabla. Por lo tanto, es posible que dos procesos lean la misma fila en la tabla VersionNumber al mismo tiempo y generen el mismo valor de VersionNumber.

  • Necesita una restricción única en (Id, VersionNumber) para aplicarlo

  • Usaría ROWLOCK, XLOCK para bloquear a otras personas leyendo la fila cerrada donde calcula

  • o envuelva el INSERT en un TRY / CATCH. Si recibo un duplicado, inténtalo de nuevo …