concurrency - Trying to implement an Ordered Job Queue in Sql Server 2012 -

i implement ordered job queue in sql server 2012. here context:

1. many agents concurrently taking n jobs queue, n may different every agent (depends on load of particular agent).
2. the jobs should taken in order. suppose primary key order (in reality different). so, agents should prefer older jobs.
3. some jobs have restricted concurrency. example, if jobs , b belong same concurrency group, ok queue both of them, forbidden run both of them @ same time. so, 1 of them runs first (according defined order) , can second job run.

please, have @ following sql fiddle - http://sqlfiddle.com/#!6/ca1a2f/1

ideally, use single update top (n) statement readpast hint , output clause. however, update not promise order, hence utilize different approach:

1. an ordered select top (n) temp table updlock , readpast hints. in effect, reserve these records.
2. a regular update output clause.

but then, such bulk update may fail, because job in bulk concurrency restricted. in case fall iterating on reserved records , try mark them running 1 one, silently skipping fail concurrency restriction.

here complete query sql fiddle:

begin tran  exec sp_executesql n' declare @running table (workitemid bigint)  select top (@count) workitemid #candidates  backgroundjobwork (updlock, readpast)  status = 0  order workitemid  begin try     print '' *** batch *** ''     update backgroundjobwork set status = 3     output inserted.workitemid @running     backgroundjobwork      workitemid in (select workitemid #candidates) end try begin catch     print '' *** 1 one *** ''     declare @workitemid bigint     declare c cursor fast_forward      select workitemid #candidates order workitemid      open c     fetch next c @workitemid      while @@fetch_status = 0     begin         begin try             update backgroundjobwork set status = 3             output inserted.workitemid @running             backgroundjobwork              workitemid = @workitemid         end try         begin catch         end catch          fetch next c @workitemid     end      close c     deallocate c end catch  select * @running ',n'@count int',@count=6  rollback 

(rolling testing purposes, status 0 means received, status 3 means running)

so, there 2 cases:

1. no concurrency restriction - bulk updates not fail
2. there concurrency restriction - falling updating 1 one cursor

my goal test 2 agents running query @ same time not interfere each other, i.e. none locked out waiting other finish.

i simulate presence of concurrency restrictions running following query first:

update backgroundjobwork  set concurrencygroupname = convert(nvarchar(2), case    when workitemid % 2 = 0 null    else workitemid % 4  end) status < 100 

this yields following result:

select workitemid,status,concurrencygroupname backgroundjobwork  status < 100 order workitemid  workitemid  status  concurrencygroupname 1           0       1 2           0       null 3           0       3 4           0       null 5           0       1 6           0       null 7           0       3 8           0       null 9           0       1 10          0       null 11          0       3 12          0       null 

as can see:

• jobs 1,5,9 belong concurrency restriction 1
• jobs 3,7,11 belong concurrency restriction 3
• jobs 2,4,6,8,10,12 not concurrency restricted

update backgroundjobwork set concurrencygroupname = null status < 100 removes concurrency restrictions.

unfortunately, not know how demo 2 agents in sql fiddle. here how in ssms:

1. two sql script windows, each 1 containing query.
2. in first window comment out rollback statement.
3. run sql first window. note, transaction still open, i.e. locks still in place.
4. now run sql second window.
5. at end rollback transaction first window executing rollback statement.

bulk updates work great - second window not locked out open transaction started in first window. can see jobs 1,2,3,4,5,6 in first window , 7,8,9,10,11,12 - in second.

however, when simulate concurrency restrictions (using aforementioned query) second window locked , awaits first 1 release locks.

i puzzled it. after all, each window updates records has reserved respective select statements! these sets disjoint - updlock , readpast guarantee it.

appending - locks held query

i checking locks held (in yet ssms window) using following query:

declare @locks table (spid int,                       dbid int,                       objid bigint,                       indid int,                       type nvarchar(10),                       resource nvarchar(128),                       mode nvarchar(10),                       status nvarchar(32)) insert @locks execute sp_lock  select spid, object_name(objid) objectname, i.name, l.type, mode, count(1) count @locks l left join sys.indexes on i.index_id = l.indid , i.object_id = l.objid mode not in ('s','is') , dbid = db_id('747_dfcontrol2') group spid,object_name(objid),l.type,i.name,mode order spid,object_name(objid),l.type,i.name,mode 

(747_dfcontrol2 name of database)

if run when there no concurrency restrictions (i.e. bulk updates successful) following output:

spid    objectname          name                    type    mode    count 60      backgroundjobwork   ix_status               key     x       12 60      backgroundjobwork   pk_backgroundjobwork    key     x       6 60      backgroundjobwork   ix_status               pag     ix      1 60      backgroundjobwork   pk_backgroundjobwork    pag     ix      1 60      backgroundjobwork   null                    tab     ix      1 

where spid 60 corresponds first window (the 1 open transaction). not see second window - rolled back.

and here result when concurrency restrictions enabled , second window (spid 63) waiting release of locks:

spid    objectname          name                        type    mode    count 60      backgroundjobwork   ix_concurrencyrestriction   key     x       2 60      backgroundjobwork   ix_status                   key     x       12 60      backgroundjobwork   pk_backgroundjobwork        key     x       6 60      backgroundjobwork   ix_concurrencyrestriction   pag     ix      1 60      backgroundjobwork   ix_status                   pag     ix      1 60      backgroundjobwork   pk_backgroundjobwork        pag     ix      1 60      backgroundjobwork   null                        tab     ix      1 63      backgroundjobwork   ix_concurrencyrestriction   key     x       1 63      backgroundjobwork   ix_status                   key     x       12 63      backgroundjobwork   pk_backgroundjobwork        key     x       6 63      backgroundjobwork   ix_concurrencyrestriction   pag     ix      1 63      backgroundjobwork   ix_status                   pag     ix      1 63      backgroundjobwork   pk_backgroundjobwork        pag     ix      1 63      backgroundjobwork   null                        tab     ix      1 

that not tell me much.

can explain me why second instance of query locked out?

edit

from question unclear why bulk update fail when turn on concurrency restriction. clear, though, sql fiddle - there conditional unique index on backgroundjobwork table:

create unique nonclustered index ix_concurrencyrestriction on backgroundjobwork (concurrencygroupname)  (status=3 , concurrencygroupname not null)