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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply