SELECCIONAR PARA ACTUALIZAR con SQL Server

Tengo un problema similar, quiero bloquear solo 1 fila. Por lo que sé, con la opción UPDLOCK, SQLSERVER bloquea todas las filas que necesita leer para obtener la fila. Por lo tanto, si no define un índice para acceder directamente a la fila, todas las filas precedidas se bloquearán. En su ejemplo:

Asuma que tiene una tabla llamada TBL con un campo id. Desea bloquear la fila con id=10. Debe definir un índice para el id de campo (o cualquier otro campo que esté involucrado en usted seleccionar):

CREATE INDEX TBLINDEX ON TBL ( id )

Y luego, su consulta para bloquear SOLO las filas que lee es:

SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

Si no utiliza la opción INDEX(TBLINDEX), SQLSERVER necesita leer todas las filas desde el principio de la tabla para encontrar su fila con id=10, por lo que esas filas estarán bloqueadas.

No puede tener lecturas de bloqueo y aislamiento de instantáneas al mismo tiempo. El propósito del aislamiento de instantáneas es evitar que bloquee las lecturas.

Try (updlock, rowlock)

La respuesta completa podría profundizar en los aspectos internos del DBMS. Depende de cómo funciona el motor de consultas (que ejecuta el plan de consultas generado por el optimizador SQL).

Sin embargo, una posible explicación (aplicable al menos a algunas versiones de algunos DBMS-no necesariamente a MS SQL Server) es que no hay un índice en la columna ID, por lo que cualquier proceso que intente trabajar una consulta con 'WHERE id = ? ' en ella termina haciendo un análisis secuencial de la tabla, y que el análisis secuencial que su proceso aplicado. También puede tener problemas si el DBMS aplica bloqueo a nivel de página de forma predeterminada; bloquear una fila bloquea toda la página y todas las filas de esa página.

Hay algunas maneras en que podrías desacreditar esto como la fuente de problemas. Mire el plan de consulta; estudie los índices; pruebe su SELECT con ID de 1000000 en lugar de 1 y vea si otros procesos aún están bloqueados.

Tal vez hacer mvcc permanente podría resolverlo (a diferencia de solo un lote específico: ESTABLECER INSTANTÁNEA DE NIVEL DE AISLAMIENTO DE TRANSACCIÓN):

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[EDITAR: Octubre 14]

Después de leer esto: Mejor concurrencia en Oracle que SQL Server? y esto: http://msdn.microsoft.com/en-us/library/ms175095.aspx

Cuando el READ_COMMITTED_SNAPSHOT la opción de base de datos está activada, mecanismos utilizados para apoyar la opción se activan inmediatamente. Cuando establecer el READ_COMMITTED_SNAPSHOT opción, solo la conexión ejecutándose el comando ALTER DATABASE está permitido en la base de datos. No debe haber otra conexión abierta en la base de datos hasta que ALTER BASE DE DATOS esté completa. El la base de datos no tiene que estar en modo de usuario único.

He llegado a la conclusión de que es necesario establecer dos banderas con el fin de activar MVCC de mssql de forma permanente en una base de datos dada:

ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

OK, un solo select wil por defecto usa el aislamiento de transacción "Read Committed" que bloquea y por lo tanto detiene las escrituras en ese conjunto. Puede cambiar el nivel de aislamiento de transacciones con

Set Transaction Isolation Level { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
Begin Tran
  Select ...
Commit Tran

Estos se explican en detalle en SQL Server BOL

El siguiente problema es que, por defecto, SQL Server 2K5 escalará los bloqueos si tiene más de ~2500 bloqueos o usa más del 40% de la memoria 'normal' en la transacción de bloqueo. La escalada va a la página, luego a la tabla lock

Puede desactivar esta escalada configurando "trace flag" 1211t, consulte BOL para obtener más información

Asumo que no desea que ninguna otra sesión pueda leer la fila mientras se ejecuta esta consulta específica...

Envolver su SELECT en una transacción mientras usa (XLOCK,READPAST) locking hint obtendrá los resultados que desea. Solo asegúrese de que esas otras lecturas concurrentes NO estén usando CON (NOLOCK). READPAST permite que otras sesiones realicen la misma selección pero en otras filas.

BEGIN TRAN
  SELECT *
  FROM <tablename> WITH (XLOCK,READPAST) 
  WHERE RowId = @SomeId

  -- Do SOMETHING

  UPDATE <tablename>
  SET <column>=@somevalue
  WHERE RowId=@SomeId
COMMIT

Los bloqueos de aplicaciones son una forma de enrollar su propio bloqueo con granularidad personalizada mientras evita la escalada de bloqueo "útil". Véase sp_getapplock .

Crea una actualización falsa para hacer cumplir el rowlock.

UPDATE <tablename> (ROWLOCK) SET <somecolumn> = <somecolumn> WHERE id=1

Si eso no es bloquear su fila, dios sabe qué lo hará.

Después de este "UPDATE" puede hacer su SELECT (ROWLOCK) y las actualizaciones posteriores.

Intenta usar:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

Esto debería hacer que el bloqueo sea exclusivo y mantenerlo durante la duración de la transacción.

De acuerdo con este artículo, la solución es usar la sugerencia WITH(REPEATABLEREAD).

Vuelva a visitar todas sus consultas, tal vez tenga alguna consulta que seleccione sin ROWLOCK/FOR UPDATE hint de la misma tabla que ha SELECCIONADO PARA ACTUALIZAR.

MSSQL a menudo escala esos bloqueos de fila a bloqueos de nivel de página (incluso bloqueos de nivel de tabla, si no tiene índice en el campo que está consultando), consulte esta explicación. Puesto que usted pide para LA ACTUALIZACIÓN, podría asumir que usted necesita la robustez del transacion-nivel(e.g. financiero, inventario, etc). Así que el consejo en ese sitio no es aplicable a su problema. Es solo una idea de por qué MSSQL aumenta los bloqueos.

Si ya está usando MSSQL 2005 (y arriba), están basados en MVCC, creo que no debería tener ningún problema con el bloqueo a nivel de fila usando ROWLOCK/UPDLOCK hint. Pero si ya está usando MSSQL 2005 y versiones posteriores, intente verificar algunas de sus consultas que consultan la misma tabla que desea ACTUALIZAR si escalan los bloqueos comprobando los campos en su cláusula WHERE si tienen índice.

P. S.
Estoy usando PostgreSQL, también usa MVCC para ACTUALIZAR, no encuentro el mismo problema. Escalaciones de bloqueo es lo que MVCC resuelve, así que me sorprendería si MSSQL 2005 todavía escalan bloqueos en la tabla con cláusulas WHERE que no tienen índice en sus campos. Si ese (escalamiento de bloqueo) sigue siendo el caso para MSSQL 2005, intente verificar los campos en las cláusulas WHERE si tienen índice.

Descargo de responsabilidad: mi último uso de MSSQL es solo la versión 2000.

Tiene que lidiar con la excepción en el momento de la confirmación y repetir la transacción.

Pregunta - ¿se ha demostrado que este caso es el resultado de la escalada de bloqueo (es decir, si rastrea con profiler para eventos de escalada de bloqueo, es definitivamente lo que está sucediendo para causar el bloqueo)? Si es así, hay una explicación completa y una solución alternativa (bastante extrema) al habilitar un indicador de seguimiento a nivel de instancia para evitar la escalada de bloqueos. Véase http://support.microsoft.com/kb/323630 trace flag 1211

Pero, eso probablemente tendrá efectos secundarios no deseados.

Si usted es bloquear deliberadamente una fila y mantenerla bloqueada durante un período prolongado, luego usar el mecanismo de bloqueo interno para las transacciones no es el mejor método (al menos en SQL Server). Toda la optimización en SQL Server está orientada a transacciones cortas: entrar, hacer una actualización, salir. Esa es la razón de la escalada de bloqueo en primer lugar.

Así que si la intención es "retirar" una fila durante un período prolongado, en lugar de bloqueo transaccional, es mejor usar una columna con valores y un instrucción simple ol ' update para marcar las filas como bloqueadas o no.

Resolví el problema de rowlock de una manera completamente diferente. Me di cuenta de que sql Server no era capaz de administrar tal bloqueo de una manera satisfactoria. Elegí resolver esto desde un punto de vista programático mediante el uso de un mutex... waitForLock... releaseLock...

¿Has probado READPAST?

He usado UPDLOCK y READPAST juntos al tratar una tabla como una cola.

¿Qué tal intentar hacer una simple actualización en esta fila primero (sin cambiar realmente ningún dato)? Después de que usted puede proceder con la fila como en fue seleccionado para la actualización.

UPDATE dbo.Customer SET FieldForLock = FieldForLock WHERE CustomerID = @CustomerID
/* do whatever you want */

Editar: usted debe envolver en una transacción por supuesto

Editar 2: otra solución es usar el nivel de aislamiento SERIALIZABLE