una - ¿Cómo se bloquean las tablas en SQL Server 2005, y debería incluso hacerlo?

para que sirven los bloqueos en sql server

Question

Este tomará un poco de explicación. Lo que he hecho es crear una cola de mensajes personalizada específica en SQL Server 2005. Tengo una tabla con mensajes que contienen marcas de tiempo tanto para confirmación como para finalización. El procedimiento almacenado que ejecutan los llamadores para obtener el siguiente mensaje en su cola también reconoce el mensaje. Hasta aquí todo bien. Bueno, si el sistema está experimentando una gran cantidad de transacciones (miles por minuto), ¿no es posible que un mensaje sea confirmado por otra ejecución del procedimiento almacenado mientras otro está preparado para hacerlo? Permítanme ayudar mostrando mi código SQL en el proceso almacenado:

--Grab the next message id
declare @MessageId uniqueidentifier
set @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);
--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId
--Select the entire message
...
...

En el código anterior, ¿no podría otro procedimiento almacenado ejecutándose al mismo tiempo obtener el mismo ID e intentar reconocerlo al mismo tiempo? ¿Podría (o debería) implementar algún tipo de bloqueo para evitar que otro proceso almacenado reconozca los mensajes que otro proceso almacenado está consultando?

Wow, ¿algo de esto tenía sentido? Es un poco difícil de poner en palabras ...

Answer 1

¿Deberías procesar las cosas una a una? ¿No debería simplemente hacer que SQL Server reconozca todos los mensajes no reconocidos con la fecha de hoy y los devuelva? (todo también en una transacción por supuesto)

Answer 2

@Kilhoffer:

Todo el lote de SQL se analiza antes de la ejecución, por lo que SQL sabe que va a hacer una actualización de la tabla y seleccionarla.

Editar: Además, SQL no necesariamente bloqueará toda la tabla, simplemente podría bloquear las filas necesarias. Consulte aquí para obtener una descripción general del bloqueo en el servidor SQL.

Answer 3

Algo como esto

--Grab the next message id
begin tran
declare @MessageId uniqueidentifier
select top 1 @MessageId = ActionMessageId from UnacknowledgedDemands with(holdlock, updlock);
--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId
-- some error checking
commit tran
--Select the entire message
...
...

Answer 4

Obtenga más información sobre bloqueo de selección de SQL Server aquí y aquí . SQL Server tiene la capacidad de invocar un bloqueo de tabla en una selección. Nada sucederá en la mesa durante la transacción. Cuando la transacción se complete, las inserciones o actualizaciones se resolverán solos.

Answer 5

Si desea ajustar el código en una transacción, SQL Server controlará el bloqueo de las filas o tablas correspondientes.

begin transaction
--Grab the next message id
declare @MessageId uniqueidentifier
set @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);
--Acknowledge the message
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId
commit transaction
--Select the entire message
...

Answer 6

En lugar de bloqueo explícito, que a menudo SQL Server eleva a una granularidad mayor de la deseada, ¿por qué no probar este enfoque?

declare @MessageId uniqueidentifier
select top 1 @MessageId = ActionMessageId from UnacknowledgedDemands
update ActionMessages
set AcknowledgedTime = getdate()
where ActionMessageId = @MessageId and AcknowledgedTime is null
if @@rowcount > 0
/* acknoweldge succeeded */
else
/* concurrent query acknowledged message before us,
go back and try another one */

Cuanto menos bloquee, mayor será la concurrencia que tenga.

Answer 7

Este parece el tipo de situación donde OUTPUT puede ser útil:

-- Acknowledge and grab the next message
declare @message table (
-- ...your `ActionMessages` columns here...
)
update ActionMessages
set AcknowledgedTime = getdate()
output INSERTED.* into @message
where ActionMessageId in (select top(1) ActionMessageId from UnacknowledgedDemands)
and AcknowledgedTime is null
-- Use the data in @message, which will have zero or one rows assuming
-- `ActionMessageId` uniquely identifies a row (strongly implied in your question)
...
...

Allí, actualizamos y tomamos la fila en la misma operación, que le dice al optimizador de consultas exactamente lo que estamos haciendo, lo que le permite elegir el bloqueo más granular que pueda y mantenerlo por el tiempo más breve posible. (Aunque el prefijo de la columna está INSERTED , OUTPUT es como disparadores, expresado en términos de que la UPDATE es como eliminar la fila e insertar la nueva.)

Necesitaría más información sobre sus tablas ActionMessages y ActionMessages (views / TVFs / whatever), sin mencionar un mayor conocimiento del bloqueo automático de SQL Server, para decir si eso and AcknowledgedTime is null cláusula and AcknowledgedTime is null . Está ahí para defenderse contra una condición de carrera entre la selección secundaria y la actualización. Estoy seguro de que no sería necesario si seleccionáramos desde ActionMessages mismo (por ejemplo, where AcknowledgedTime is null con una top en la update , en lugar de la sub selección en ActionMessages no where AcknowledgedTime is null ). Espero que incluso si es innecesario, es inofensivo.

Tenga en cuenta que OUTPUT está en SQL Server 2005 y posteriores. Eso es lo que dijiste que estabas usando, pero si se requería compatibilidad con las instalaciones geriátricas de SQL Server 2000, querrías ir por otro camino.