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

[{'answer_id': 57637, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': -1, 'selected': False, 'text': '<p>You want to wrap your code in a transaction, then SQL server will handle locking the appropriate rows or tables.</p>\n\n<pre><code>begin transaction\n\n--Grab the next message id\ndeclare @MessageId uniqueidentifier\nset @MessageId = (select top(1) ActionMessageId from UnacknowledgedDemands);\n\n--Acknowledge the message\nupdate ActionMessages\nset AcknowledgedTime = getdate()\nwhere ActionMessageId = @MessageId\n\ncommit transaction\n\n--Select the entire message\n...\n</code></pre>\n'}, {'answer_id': 57649, 'author': 'rpetrich', 'author_id': 4007, 'author_profile': 'https://Stackoverflow.com/users/4007', 'pm_score': 0, 'selected': False, 'text': "<p>Should you really be processing things one-by-one? Shouldn't you just have SQL Server acknowledge all unacknowledged messages with todays date and return them? (all also in a transaction of course)</p>\n"}, {'answer_id': 57653, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': 1, 'selected': False, 'text': '<p>@Kilhoffer:</p>\n\n<p>The whole SQL batch is parsed before execution, so SQL knows that you\'re going to do an update to the table as well as select from it.</p>\n\n<p>Edit: Also, SQL will not necessarily lock the whole table - it could just lock the necessary rows. See <a href="http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx" rel="nofollow noreferrer">here</a> for an overview of locking in SQL server.</p>\n'}, {'answer_id': 57664, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 4, 'selected': True, 'text': '<p>Something like this</p>\n\n<pre><code>--Grab the next message id\nbegin tran\ndeclare @MessageId uniqueidentifier\nselect top 1 @MessageId =   ActionMessageId from UnacknowledgedDemands with(holdlock, updlock);\n\n--Acknowledge the message\nupdate ActionMessages\nset AcknowledgedTime = getdate()\nwhere ActionMessageId = @MessageId\n\n-- some error checking\ncommit tran\n\n--Select the entire message\n...\n...\n</code></pre>\n'}, {'answer_id': 57671, 'author': 'Jarrett Meyer', 'author_id': 5834, 'author_profile': 'https://Stackoverflow.com/users/5834', 'pm_score': 0, 'selected': False, 'text': '<p>Read more about SQL Server Select Locking <a href="http://www.quest-pipelines.com/newsletter-v3/0202_F.htm" rel="nofollow noreferrer">here</a> and <a href="http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx" rel="nofollow noreferrer">here</a>. SQL Server has the ability to invoke a table lock on a select. Nothing will happen to the table during the transaction. When the transaction completes, any inserts or updates will then resolve themselves.</p>\n'}, {'answer_id': 126970, 'author': 'Constantin', 'author_id': 20310, 'author_profile': 'https://Stackoverflow.com/users/20310', 'pm_score': 1, 'selected': False, 'text': '<p>Instead of explicit locking, which is often escalated by SQL Server to higher granularity than desired, why not just try this approach:</p>\n\n<pre><code>declare @MessageId uniqueidentifier\nselect top 1 @MessageId = ActionMessageId from UnacknowledgedDemands\n\nupdate ActionMessages\n  set AcknowledgedTime = getdate()\n  where ActionMessageId = @MessageId and AcknowledgedTime is null\n\nif @@rowcount &gt; 0\n  /* acknoweldge succeeded */\nelse\n  /* concurrent query acknowledged message before us,\n     go back and try another one */\n</code></pre>\n\n<p>The less you lock - the higher concurrency you have.</p>\n'}, {'answer_id': 6746381, 'author': 'T.J. Crowder', 'author_id': 157247, 'author_profile': 'https://Stackoverflow.com/users/157247', 'pm_score': 2, 'selected': False, 'text': '<p>This seems like the kind of situation where <a href="http://msdn.microsoft.com/en-us/library/ms177564.aspx" rel="nofollow"><code>OUTPUT</code></a> can be useful:</p>\n\n<pre><code>-- Acknowledge and grab the next message\ndeclare @message table (\n    -- ...your `ActionMessages` columns here...\n)\nupdate ActionMessages\nset    AcknowledgedTime = getdate()\noutput INSERTED.* into @message\nwhere  ActionMessageId in (select top(1) ActionMessageId from UnacknowledgedDemands)\n  and  AcknowledgedTime is null\n\n-- Use the data in @message, which will have zero or one rows assuming\n-- `ActionMessageId` uniquely identifies a row (strongly implied in your question)\n...\n...\n</code></pre>\n\n<p>There, we update and grab the row in the same operation, which tells the query optimizer <strong>exactly</strong> what we\'re doing, allowing it to choose the most granular lock it can and maintain it for the briefest possible time. (Although the column prefix is <code>INSERTED</code>, <code>OUTPUT</code> is like triggers, expressed in terms of the <code>UPDATE</code> being like deleting the row and inserting the new one.)</p>\n\n<p>I\'d need more information about your <code>ActionMessages</code> and <code>UnacknowledgedDemands</code> tables (views/TVFs/whatever), not to mention a greater knowledge of SQL Server\'s automatic locking, to say whether that <code>and AcknowledgedTime is null</code> clause is necessary. It\'s there to defend against a race condition between the sub-select and the update. I\'m certain it wouldn\'t be necessary if we were selecting from <code>ActionMessages</code> itself (e.g., <code>where AcknowledgedTime is null</code> with a <code>top</code> on the <code>update</code>, instead of the sub-select on <code>UnacknowledgedDemands</code>). I expect even if it\'s unnecessary, it\'s harmless.</p>\n\n<p>Note that <code>OUTPUT</code> is in SQL Server 2005 and above. That\'s what you said you were using, but if compatibility with geriatric SQL Server 2000 installs were required, you\'d want to go another way.</p>\n'}]
['https://Stackoverflow.com/questions/57625', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5469/']