seleccionadas - SELECCIONAR PARA ACTUALIZAR con SQL Server

update from sql server 2008

Question

Estoy usando una base de datos de Microsoft SQL Server 2005 con el nivel de aislamiento READ_COMMITTED y READ_COMMITTED_SNAPSHOT=ON .

Ahora quiero usar:

SELECT * FROM <tablename> FOR UPDATE

... para que otras conexiones de bases de datos se bloqueen al intentar acceder a la misma fila "PARA ACTUALIZAR".

Lo intenté:

SELECT * FROM <tablename> WITH (updlock) WHERE id=1

... pero esto bloquea todas las demás conexiones incluso para seleccionar una identificación que no sea "1".

¿Cuál es la sugerencia correcta para hacer una SELECT FOR UPDATE como se conoce para Oracle, DB2, MySql?

EDITAR 2009-10-03:

Estas son las declaraciones para crear la tabla y el índice:

CREATE TABLE example ( Id BIGINT NOT NULL, TransactionId BIGINT, 
    Terminal BIGINT, Status SMALLINT );
ALTER TABLE example ADD CONSTRAINT index108 PRIMARY KEY ( Id )
CREATE INDEX I108_FkTerminal ON example ( Terminal )
CREATE INDEX I108_Key ON example ( TransactionId )

Una gran cantidad de procesos paralelos hacen esto SELECT :

SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ?

EDITAR 2009-10-05:

Para una mejor visión general, he escrito todas las soluciones probadas en la siguiente tabla:

mechanism | SELECT on different row blocks | SELECT on same row blocks -----------------------+--------------------------------+-------------------------- ROWLOCK | no | no updlock, rowlock | yes | yes xlock,rowlock | yes | yes repeatableread | no | no DBCC TRACEON (1211,-1) | yes | yes rowlock,xlock,holdlock | yes | yes updlock,holdlock | yes | yes UPDLOCK,READPAST | no | no I''m looking for | no | yes

Answer 1

¿Has probado READPAST?

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

Answer 2

¿Qué hay de intentar hacer una simple actualización en esta fila primero (sin cambiar realmente ningún dato)? Después de eso, puede continuar con la fila como si fue seleccionado para la actualización.

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

Editar : debe envolverlo en una transacción de curso

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

Answer 3

Crea una actualización falsa para aplicar el rowlock.

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

Si eso no está bloqueando tu fila, Dios sabe lo que será.

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

Answer 4

Intenta usar:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

Esto debería hacer que el bloqueo sea exclusivo y mantenerlo mientras dure la transacción.

Answer 5

La respuesta completa podría profundizar en las partes internas del DBMS. Depende de cómo funciona el motor de consulta (que ejecuta el plan de consulta generado por el optimizador de 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 ningún índice en la columna ID, por lo que cualquier proceso que intente generar una consulta con '' WHERE id = ? ''en él termina haciendo un escaneo secuencial de la tabla, y ese escaneo secuencial golpea la cerradura que su proceso aplicó. También puede encontrarse con problemas si el DBMS aplica el bloqueo de nivel de página de manera predeterminada; Al bloquear una fila se bloquea toda la página y todas las filas en esa página.

Hay algunas formas en que podrías desacreditar esto como fuente de problemas. Mira el plan de consulta; estudiar los índices; pruebe su SELECCIONAR con ID de 1000000 en lugar de 1 y vea si otros procesos todavía están bloqueados.

Answer 6

Los bloqueos de aplicaciones son una forma de hacer rodar su propio bloqueo con granularidad personalizada al tiempo que evitan la escalada de bloqueo "útil". Ver sp_getapplock .

Answer 7

No puede tener aislamiento de instantáneas y lecturas de bloqueo al mismo tiempo. El objetivo del aislamiento de instantáneas es evitar el bloqueo de lecturas.

Answer 8

OK, una sola selección wil usa de forma predeterminada 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 la transacción 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, de forma predeterminada, 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 bloqueo de tabla

Puede desactivar esta escalada configurando "indicador de traza" 1211t, consulte BOL para obtener más información

Answer 9

Pregunta: ¿se ha demostrado que este caso es el resultado de la escalada de bloqueo (es decir, si rastrea con Profiler para eventos de escalamiento de bloqueo, ¿eso es definitivamente lo que está sucediendo para causar el bloqueo)? Si es así, hay una explicación completa y una solución (bastante extrema) al habilitar un indicador de seguimiento en el nivel de instancia para evitar la escalada de bloqueo. Consulte http://support.microsoft.com/kb/323630 indicador de traza 1211

Pero, eso probablemente tendrá efectos secundarios involuntarios.

Si está bloqueando deliberadamente una fila y manteniéndola bloqueada durante un período prolongado, entonces el uso del mecanismo de bloqueo interno para transacciones no es el mejor método (al menos en SQL Server). Toda la optimización en SQL Server está orientada a transacciones cortas: ingrese, haga una actualización, salga. Esa es la razón de la escalada de bloqueo en primer lugar.

Por lo tanto, si la intención es "verificar" una fila durante un período prolongado, en lugar de bloqueo transaccional, es mejor utilizar una columna con valores y una simple declaración de actualización para marcar las filas como bloqueadas o no.

Answer 10

Pruebe (updlock, rowlock)

Answer 11

Recientemente tuve un problema de interbloqueo porque el servidor Sql bloquea más de lo necesario (página). Realmente no puedes hacer nada en contra de eso. Ahora estamos atrapando excepciones de punto muerto ... y desearía tener Oracle en su lugar.

Editar: estamos utilizando el aislamiento de instantáneas mientras tanto, lo que resuelve muchos, pero no todos los problemas. Desafortunadamente, para poder utilizar el aislamiento de instantáneas, debe ser permitido por el servidor de la base de datos, lo que puede causar problemas innecesarios en el sitio de los clientes. Ahora no solo estamos detectando excepciones de interbloqueos (que aún pueden ocurrir, por supuesto) sino también problemas de simultaneidad de instantáneas para repetir transacciones de procesos en segundo plano (que el usuario no puede repetir). Pero esto todavía funciona mucho mejor que antes.

Answer 12

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

Answer 13

Revise todas sus consultas, tal vez tenga alguna consulta que seleccione sin la sugerencia ROWLOCK / FOR UPDATE de la misma tabla que seleccionó SELECT FOR UPDATE.

MSSQL a menudo escala esos bloqueos de fila a bloqueos de nivel de página (incluso bloqueos a nivel de tabla, si no tiene índice en el campo que está consultando), consulte esta explanation . Como usted solicita FOR UPDATE, podría suponer que necesita robustez a nivel transaccional (por ejemplo, financiera, de inventario, etc.). Entonces el consejo en ese sitio no es aplicable a su problema. Es solo una idea de por qué MSSQL escala bloqueos .

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

PD
Estoy usando PostgreSQL, también usa MVCC para FOR UPDATE, no encuentro el mismo problema. Lock escalations es lo que resuelve MVCC, por lo que me sorprendería que MSSQL 2005 siga escalando bloqueos en la tabla con cláusulas WHERE que no tienen índice en sus campos. Si eso (escalada 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.

Answer 14

Según este artículo , la solución es usar la sugerencia WITH (REPEATABLEREAD).

Answer 15

Supongo que no desea que ninguna otra sesión pueda leer la fila mientras se está ejecutando esta consulta específica ...

Envolver su SELECT en una transacción mientras utiliza la instrucción de bloqueo WITH (XLOCK, READPAST) obtendrá los resultados que desee. Solo asegúrate de que esas otras lecturas simultáneas NO estén usando WITH (NOLOCK). READPAST permite que otras sesiones realicen el mismo SELECT 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

Answer 16

Tengo un problema similar, quiero bloquear solo 1 fila. Hasta donde yo 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 estarán bloqueadas. En tu ejemplo:

Asume que tienes una tabla llamada TBL con un campo de id . Desea bloquear la fila con id=10 . Debe definir un índice para la identificación de campo (o cualquier otro campo que esté involucrado en su selección):

CREATE INDEX TBLINDEX ON TBL ( id )

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

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

Si no usa 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 se bloquearán.

Answer 17

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

Answer 18

quizás hacer que mvcc sea permanente podría resolverlo (a diferencia del lote específico solamente: SET TRANSACTION ISOLATION LEVEL SNAPSHOT):

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[EDIT: 14 de octubre]

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

Cuando la opción de base de datos READ_COMMITTED_SNAPSHOT está activada, los mecanismos utilizados para respaldar la opción se activan inmediatamente. Al configurar la opción READ_COMMITTED_SNAPSHOT, solo la conexión que ejecuta el comando ALTER DATABASE está permitida en la base de datos. No debe haber ninguna otra conexión abierta en la base de datos hasta que se complete ALTER DATABASE. La base de datos no tiene que estar en modo de usuario único.

He llegado a la conclusión de que necesita establecer dos banderas para activar el MVCC de mssql de forma permanente en una base de datos determinada:

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