Transaction and Locking

[rac2]

I have a table which is a list of work to do, and multiple processes looking at the table to get the next task. It takes a relatively long time to finish the task, and after it is finished the process marks the task as completed. Only one process should find and handle a task. While a task is being handled it is marked as busy. So tasks are ready, busy, and completed.

It is the transition from ready to busy that concerns me. If process A gets the oldest task which is ready and marks it as busy, I need to prevent process B from finding the same task before process A has marked it as busy.

I am thinking this can be handled by a transaction, but do I need to do more such as using a locking hint for the read?

BEGIN TRANSCACTION

SELECT @nextTask = MIN(task_id) FROM WorkToDO
WHERE status = 'Ready' [COLOR=blue]WITH (READPAST)[/color]

UPDATE WorkTodo SET status = 'Busy'
WHERE task_id = @nextTask

COMMIT TRANSACTION

 

[genomon]

I think WITH(ROWLOCK) will give your process exclusive access to the selected record (at least woth a shot)....
Happy Thanksgiving!

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[ESquared]

I think the correct optimizer hint is

WITH (UPDLOCK)

but why do it in two steps? Do it in one and you don't have to worry about this at all:

create table blah (blahid int constraint pk_blah primary key clustered, status varchar(10))
insert blah select 1, 'ready' union select 2, 'ready'

select * from blah

declare @i int

set rowcount 1
update blah set @i = blahid, status = 'busy' where status = 'ready'
set rowcount 0
select @i
update blah set status = 'completed' where blahid = @i

select * from blah

set rowcount 1
update blah set @i = blahid, status = 'busy' where status = 'ready'
set rowcount 0
select @i
update blah set status = 'completed' where blahid = @i

select * from blah

drop table blah

You can update the row and return the value into a variable of the row to work on in one step.

Note the primary clustered index... it can be helpful in preventing blocks & deadlocks by avoiding early greedy lock escalation.

 

[ESquared]

oh... you might want to check @@rowcount after the update ='ready' statement to see if you actually got work to do. Or set @i = null each time before attempting to get work to do. If it's null after the update, no rows are 'ready' to process.