您如何锁定 SQL Server 2005 中的表，我什至应该这样做吗?

问题描述

这个需要一些解释.我所做的是在 SQL Server 2005 中创建一个特定的自定义消息队列.我有一个包含确认和完成时间戳的消息表.调用者为获取队列中的下一条消息而执行的存储过程也会确认该消息.到现在为止还挺好.好吧，如果系统正在经历大量事务(每分钟数千次)，是否有可能一条消息被另一个存储过程的执行确认而另一个准备好自己这样做?让我通过在存储过程中显示我的 SQL 代码来提供帮助:

This one will take some explaining. What I've done is create a specific custom message queue in SQL Server 2005. I have a table with messages that contain timestamps for both acknowledgment and completion. The stored procedure that callers execute to obtain the next message in their queue also acknowledges the message. So far so good. Well, if the system is experiencing a massive amount of transactions (thousands per minute), isn't it possible for a message to be acknowledged by another execution of the stored procedure while another is prepared to so itself? Let me help by showing my SQL code in the stored proc:

--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
...
...

在上面的代码中，不能同时运行的另一个存储过程获取相同的id并尝试同时确认它吗?我可以(或应该)实施某种锁定以防止另一个存储的 proc 确认另一个存储的 proc 正在查询的消息?

In the above code, couldn't another stored procedure running at the same time obtain the same id and attempt to acknowledge it at the same time? Could I (or should I) implement some sort of locking to prevent another stored proc from acknowledging messages that another stored proc is querying?

哇，这有什么道理吗?有点难以用语言表达...

Wow, did any of this even make sense? It's a bit difficult to put to words...

推荐答案

类似这样的事情

--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
...
...

这篇关于您如何锁定 SQL Server 2005 中的表，我什至应该这样做吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！