Synchronization Issue

[jimdefruscio]

Hi,

I have developed a ASP.NET 2.0 web application and am having a data
access synchronization issue. I would appreciate anybody's
experience. My application is a multi-user application where each
person is working from a queue of work items. They open the app and
click a button which gets the next unprocessed item on the queue and
locks the record for the user by setting an isLocked flag on the
database record. When they are done with the request, they click the
approved button, which changes the processed flag and releases the
lock. My problem is that two people are getting the same work item
and not knowing it (but it is only locked for one of them). Then they
both approve it and the one with the true lock has success and the one
that did not have the lock failed. The database procedure that gets
the next item is as follows:

-- clear all locks for the user
UPDATE work
SET isLocked = 0, -- false / unlock
expirationTime = NULL,
locked_by = NULL
WHERE locked_by = <user>


UPDATE assetholder_address
SET isLocked = 1, -- true
locked_by = <user>,
expirationTime = DateAdd(mi, 30, getdate())
WHERE assetholder_address_id = (
SELECT MIN(work_id)
FROM work aa with (nolock), work_status_status aas with (nolock)
WHERE aa.work_id = aas.work_id
AND aas.address_status_id = @address_status_id -- not processed
AND aa.isLocked = 0
)

-- return the locked address
select @nextAddressID = assetholder_address_id
from assetholder_address
where locked_by = @user

I have a class method call in my ASP.NET component that calls the
procedure that executes the code above.

It looks like this...
nextWorkID= workDBMap.getNextWorkID();

and the getNextWorkID() procedure calles the stored procedure using
ADO.NET.

I know this is a synchronization issue, does anyone have any
strategies for handling things like this in a ASP.NET, SQL Server 2000
environment.

Thanks a lot!