Concurrency question

Question

Hi,

I need to make sure that 2 connections to the table never get the same id for processing... For example:

create table dbo.concurrency(id int identity, task varchar(80), status varchar(80))
go

insert dbo.concurrency(task, status) values('task A','active')
insert dbo.concurrency(task, status) values('task B','active')
insert dbo.concurrency(task, status) values('task C','active')
insert dbo.concurrency(task, status) values('task D','active')
go

Both connections run the same transaction:

begin tran
declare @id int
select @id = min(id) from dbo.concurrency where status = 'active'
update dbo.concurrency set status = 'pending' where id = @id
commit tran

I want to make sure that the moment 1st connection starts transaction and choses id = 1 for update (at the select statement, and before update and commit), 2nd transaction cannot chose id = 1, but the next one, id = 2 for update of status.

This cannot be achieved by setting transaction isolation level (any of them), as even if we chose the most restrictive one of them, SERIALIZABLE, if we run the 2nd transaction before update statement of the 1st transaction, both will update id = 1 row: connection 1 will update it once, and connection 2 will update it 2nd time as its select was not blocked by 1st connection select.

Any idea?

Answer 1

How about an UPDLOCK on the SELECT? That should hold an update lock on the row, forcing the other one to wait.

One could even imagine using READPAST, making the other ignore the row(s) with conflicting lock (pick the "next" one) - but you'd want to play a bit with that to make sure my reasoning isn't just because lack of coffee (yet).

Answer 2

Hi,

One SELECT cannot block another SELECT. Before executing the update in 1st connection, the 2nd connection can always read the same id.

But you can consider adding the WITH (TABLOCKX) in 1st connection,it specifies that an exclusive lock is taken on the table..

select @id = min(id) from dbo.concurrency WITH (TABLOCKX) where status = 'active'

Answer 3

I tried UPDLOCK even before I saw this (google is my friend), and it works. TABLOCK does not block reader, so it does not work.

Thanks!