First, create the test table and populate it with some data. The data itself isn’t too important; we just want a table that will be updated while also concurrently be queried and then deleted.
drop table if exists [dbo].[Queue];
create table [dbo].[Queue](
QueueID int identity( 1, 1 ) primary key,
SomeAction varchar(100) not null
)
go
insert into [dbo].[Queue]( [SomeAction] )
select top 1000
ac.[name]
from [master].[sys].[all_columns] ac
where ac.[column_id] between 1 and 100
group by ac.[name]
order by newid();
Now open a second tab and paste this next query. This one will make random changes to the queue table every tenth of a second, while holding a transaction on the modified rows.
again:
begin tran;
with numbers as (
select top 10
ac.[column_id]
from [master].[sys].[all_columns] ac
where ac.[column_id] between 1 and 100
group by ac.[column_id]
order by newid()
)
update q with(updlock) set
q.[SomeAction] = convert( varchar(100), newid() )
from [dbo].[Queue] q
join numbers n on q.[QueueID] = n.[column_id]
waitfor delay '00:00:00.1';
commit tran;
goto again;
Now, open two more tabs and paste this SQL into each.
Try more tabs if you want to test the behavior.
set nocount on;
declare @queueid int;
waitfor delay '00:00:01';
while exists(select * from [dbo].[Queue] with (updlock, readpast) ) begin;
begin tran;
select top 1 @queueid = q.[QueueID]
from [dbo].[Queue] q with(updlock, readpast)
print 'processing queueid # ' + cast(@queueid as varchar)
-- Simulate some slow processing..
waitfor delay '00:00:00.1';
delete q
from [dbo].[Queue] q
where q.[QueueID] = @queueid
commit tran;
end;
Execute all 3+ tabs at once to simulate the load an application might place on the Queue table, along with 2+ queue consumers.
If everything works well, each unique QueueID should only show up once across all running consumer tabs in the results.
How does this work? Well.. here’s my simplified English explanation.
The with(updlock) query hint tells the SQL engine that, “Hey, place some locks on these rows that I’m going to modify inside this transaction! Don’t let anyone else update them!”
And the with(readpast) query hint tells the SQL engine that, “Hey, go ahead and just skip past any rows that are locked. Just show me the ones that are not locked.”
Someone with better wording and the proper terminology (of what the locks are called) can give you a better understanding of what’s going on in each query.
I’m just hoping that these queries (my simplified re-telling) helps you to understand how to modify a queue while also consuming rows from the very same table, but without blocking and without deadlocking.
Any feedback? Questions?
Better practices I’ve glanced over? (Such as setting the isolation level..)
)