August 24, 2005 at 8:43 pm
I need to select the next available customer from a list and automatically feed it to an agent for processing. Only 1 agent should ever receive the next customer, so when the customer is selected as the next, I am updating a bit field on the customer record indicating that the customer is in use. My code is as follows:
BEGIN TRANSACTION
SELECT @DonorIID = DonorIID
FROM DonorCampaign
WHERE DonorIID = (SELECT TOP 1 DonorIID
FROM DonorCampaign
WHERE CampaignID = @CampaignID
AND (InUseBy IS NULL OR (InUseBy IS NOT NULL AND InUseExpire < GetDate()))
AND (CallDateTime IS NOT NULL AND CallDateTime <= GetDate())
ORDER BY CallDateTime ASC
, DonorIID ASC)
SELECT FName,LName
FROM Donor WHERE DonorIID = @DonorIID
UPDATE DonorCampaign
SET InUseBy = @CompanyUserID
, InUseExpire = DateAdd(n, 15, GetDate())
WHERE DonorIID = @DonorIID
COMMIT TRANSACTION
I thought that by putting the process inside a TRANSACTION, that the row that is selected would be locked so that another user that clicks the Get Next Customer button at the same time wouldn't get the same customer. The problem is that 2 users have received the same customer, so apparently the TRANSACTION doesn't lock the SELECTed row. The 2nd users select happens before the UPDATE happens making the customer unavailable. How would I make sure that the customer isn't sent to more than one user?
August 25, 2005 at 12:46 am
In the default isolation level (READ COMMITTED) read locks (or shared locks as they are called) are not held when the read is finished. The transaction does not do any difference for that, it only makes sure that locks that are held (like the exclusive lock needed for the update) are held for the entire transaction. By using a higher isolation level you can hold shared locks for the entire transaction.
But why not simply do this:
UPDATE DonorCampaign
SET InUseBy = @CompanyUserId
, InUseExpire = DateAdd(n, 15, GetDate())
WHERE DonorIID = (
SELECT DonorIID
FROM DonorCampaign
WHERE DonorIID = (
SELECT TOP 1 DonorIID
FROM DonorCampaign
WHERE CampaignID = @CampaignID
AND (InUseBy IS NULL OR (InUseBy IS NOT NULL AND InUseExpire < GetDate()))
AND (CallDateTime IS NOT NULL AND CallDateTime <= GetDate())
ORDER BY CallDateTime ASC, DonorIID ASC)
)
August 26, 2005 at 8:00 am
Chris, thanks for that explanation and more much more effective solution.
August 26, 2005 at 11:13 am
Chris,
Shouldn't serializable be used even with your query since it is possible for two person to run the query at the exact same time and still get the same record since both haven't commited?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply