SQL server lock issue

[janfoster]

I have an application that runs on many workstations that all pull the next record from a sql table. I use a stored procedure to located the next record and give it to the agent. I use a transaaction and locks, and the record is checked out at the time it is selected so that no other agent can get the record but from time to time 2 agents get the same record, as if the locking does not work.

 
 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 
insert #nextlead 
        select  l.[project ID] ,recordid,l.[lead id] ,call_type,@startpage as startpage,phone 
        from Preview_Call_Leads l with (rowlock)
         inner join Preview_Call_Queue q with (rowlock) on q.[lead ID] = l.[lead id]
         where l.[project id] = @projectID
        and call_type  <> 1 and call_type  <> 5
        and [checked out] = 0 and subcode = 0
            and [Next Call Time]< = getdate() 
    and datepart(hh,dateadd(hh,(8-TimeZoneOffset),getdate()))< 18
    and datepart(hh,dateadd(hh,(8-TimeZoneOffset),getdate()))>= 8
        order by call_type 
 
 
update preview_call_queue WITH(ROWLOCK) set [checked out] = 1 where [lead id]= @leadid
 
 

[iburyak]

I think you have shared lock in your first statement no mater what your hint is.

Use
BEGIN TRAN
COMMIT TRAN
instead.