title: How do you lock tables in SQL Server 2005, and should I even do it?
body: <p>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:</p>
  
  <pre><code>--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
  ...
  ...
  </code></pre>
  
  <p>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? </p>
  
  <p>Wow, did any of this even make sense? It's a bit difficult to put to words...</p>