December 23, 2005 at 9:33 am
I have an application that blood centers use to make reminder calls for appointments. There are up to 40 users at one time accessing the application and calling on the same set of reminders. The application should feed a caller the next available reminder (similar to a predictive dialer). Obviously it is important that only one caller get any particular reminder.
My current process is to determine the next reminder and tag it as InUse. I do that by putting the caller's UserID in the InUse column. And then I query the table to get the reminder that I just flagged and send it back to the caller. My problem is that on occasion, multiple callers will get the same reminder. It seems to happen most when there is a high load on the db server. My stored procedure is below. How can I ensure that only one person is going to get that row.
(
@DonorID INT OUTPUT,
@DonorIID INT OUTPUT,
@user-id INT,
@ReminderListID INT
)
AS
BEGIN TRANSACTION
UPDATE DonorCampaign
SET InUseBy = @UserId,
InUseExpire = DATEADD(n, 15, GetDate())
FROMDonorCampaign dc
INNER JOIN (SELECT TOP 1 DonorIID
FROM DonorCampaign
WHERE ReminderListID = @ReminderListID
AND InUseBy IS NULL
AND (CallDateTime IS NOT NULL AND CallDateTime <= GetDate())
ORDER BY CallDateTime, DonorIID
) dc2 ON dc.DonorIID = dc2.DonorIID
COMMIT TRANSACTION
SELECT@DonorIID = DonorIID,
@DonorID = DonorID
FROMDonorCampaign
WHEREInUseBy = @user-id
ANDInUseExpire = @InUseExpire
ANDCampaignID = @CampaignID
December 23, 2005 at 10:02 am
I usually put the update and select inside the transaction.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply