471,089 Members | 1,725 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

SQL server lock issue

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.


Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  4.  
  5. insert #nextlead 
  6.         select  l.[project ID] ,recordid,l.[lead id] ,call_type,@startpage as startpage,phone 
  7.         from Preview_Call_Leads l with (rowlock)
  8.          inner join Preview_Call_Queue q with (rowlock) on q.[lead ID] = l.[lead id]
  9.          where l.[project id] = @projectID
  10.         and call_type  <> 1 and call_type  <> 5
  11.         and [checked out] = 0 and subcode = 0
  12.             and [Next Call Time]< = getdate() 
  13.     and datepart(hh,dateadd(hh,(8-TimeZoneOffset),getdate()))< 18
  14.     and datepart(hh,dateadd(hh,(8-TimeZoneOffset),getdate()))>= 8
  15.         order by call_type 
  16.  
  17.  
  18. update preview_call_queue WITH(ROWLOCK) set [checked out] = 1 where [lead id]= @leadid
  19.  
  20.  
Jan 15 '07 #1
0 707

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

19 posts views Thread by Thue Tuxen Sørensen | last post: by
reply views Thread by Eddie Borden | last post: by
15 posts views Thread by Michael Rybak | last post: by
94 posts views Thread by Samuel R. Neff | last post: by
11 posts views Thread by fritzcwdev | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.