Returning a record from a queue using stored procedure
-
Hi,
I am having the following issue:
I have a table that has a list of customers (millions) that need contacting. Agents for my comany need to access this table, get a customer record, and call that customer. The agents get the customer record they need to call from a VB application that executes a stored procedure at the database server. The stored procedure does the following:
1) begins a transaction
2) selects the appropriate customer record to call
3) updates a flag in the customer record so they won't be contacted again
4) transaction is complete
5) return customer id so the agent can call this customer
the problem is is that the same customer id is getting returned if two agents happen to execute the stored procedure at the same time. I can run two instances of the stored proc debugger see what is happening. The select statements are both executed, then one does the update of the flag, and the other does the update again once the first one finishes the transaction. Customers are getting called twice.
The question is: How can I guarantee that an agent will get an unique customer id. My thought was a COM component that is single threaded.
Thanks for any thoughts on this.
- Ken Otto
here is a copy of the actual stored proc in case it helps...
-------------------------------------------
CREATE procedure usp_getnewrecord
@agent varchar(15)
as
declare @lowZone int, @highZone int
SET NOCOUNT ON
select @lowZone = 1
select @highZone = 1
declare @shippernumber varchar(6), @datestamp datetime, @shipperid int, @dailyticketid int
Select @datestamp = getdate()
Begin Transaction
select top 1 @dailyticketid = d.dailyticketid, @shipperid = d.shipperid, @shippernumber = d.shippernumber
from
dailyticket d with (rowlock)
--dailyticket d with (holdlock)
join
shipper s
on
d.shippernumber = s.shippernumber
where
s.zone <= @highZone
and
d.activeflag = 1
order by packagecount desc
update
dailyticket with (rowlock)
--dailyticket with (holdlock)
set
activeflag = 0
where
dailyticketid = @dailyticketid
Commit Transaction
update shipper
set lastupdate = @datestamp
where shipperid = @shipperid
declare @ticketid int
select
@ticketid =
(
select
ticketid
from
ticket
where
shippernumber = @shippernumber
and
status = 1
 
if
(@ticketid is null and @ShipperNumber IS NOT NULL)
begin
insert into
ticket
(
shippernumber,
status,
statusdate
 
values
(
@shippernumber,
1,
getdate()
 
select @ticketid = @@identity
end
select
(
select
shipperid
from
shipper
where
shippernumber = @shippernumber
 
as 'shipperid',@ticketid as 'ticketid'
SET NOCOUNT OFF
GO
-
Have a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_74bw.asp
Choose an isolation level such as read uncommitted and that should allow the second query to read the updated flag whilst the first transaction is still waiting to be committed.
Also have a look at using scope_identity() instead of @@identity and add some error checking to rollback if there are problems ...
cheers
dbgeezer
-
Steve,
Thanks for the reply. I see what you mean, however, there is still a window of opportunity for process #1 and process #2 to end up with the same record. For example, assume process #1 and process #2 both enter the stored procedure at almost the same time. They both execute the select query and get a record ID back (the same record ID). At this point, neither process #1 or process #2 has done the update to set the flag. Now, assume process #1 "wins" and the flag is set. Process #2 has already checked the flag during the initial select statement, so now it is just waiting to set the flag. Process #2 never knew it "lost", so it carries on processing the record. You might be saying "what are the chances of both processes hitting at that moment", but believe me, it's happening quite often.
Also, thanks for the scope_identity() tip! I am implementing it right away.
Cheers - Ken
-
You need a sequential aproach:
because you start a transaction and do all the processing in the same transaction, the flag is being set at the end of your process...(commit)
other calls start on the same record & have this chance since your entire process is waiting to release the update (after commit)
do an update to the flag first & commit, then continue your transaction, making sure your next reads are checking this flag prior to starting the update again, since it's already flagged and now viewable within the next process call.
Hope this helps!
Regards,Coach James
-
spot on
glad to be of help
cheers
dbgeezer
-
What you can do is add an extra column to that table of datatype timestamp.
Whenever you read that row, collect the timestamp data aswell .
Then when you update the row use a where condition where "timestamp column = timestamp value".
Then you can use the row count to check the condition. If the rowcount =0 then that row or a customer record is already picked up by some agent. So you can skip calling the customer.
if rowcount = 1 then agent can place the call to the customer.
Hope this helps
-
sivakumar,
That was exactly what I was looking for! Works like a champ. Thanks to you and to all the others who replied to this thread, I was actually able to use bits from every post to improve my stored procedure (and knowledge of SQL Server). Cheers - Ken
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply