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.