March 1, 2012 at 10:12 pm
Hi all,
i was reading an article about Using tables as Queues
and trying to implement the solution but what i don't undertand is why with CTE it's using rowlock and readpast as indicated below and with a traditional fifo method the sample using rowlock, updlock and readpast?
Can someone explain please why we don't need updlock when using CTE? Thank you.
create procedure usp_dequeueFifo
as
set nocount on;
with cte as (
select top(1) Payload
from FifoQueue with (rowlock, readpast)
order by Id)
delete from cte
output deleted.Payload;
go
delete top(1) from FifoQueue
output deleted.Payload
where Id = (
select top(1) Id
from FifoQueue with (rowlock, updlock, readpast)
order by Id)
March 2, 2012 at 1:30 am
CTE is needed only if you have recursive query or a query that calls a subquery/inline view more than once.
All other cases (such as this one) do not need to use CTE at all.
If you use the READPAST hint, then locked rows are skipped. You've used ROWLOCK, you avoid lock escalation. UPDLOCK locks a row so you can update it.
If you do not have multiple sql statement between begin tran and end tran, there is no much point in using "UPDLOCK" hint, since lock will be held during a transaction.
Consider using service broker, it is a queuing solution already there for you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply