June 10, 2008 at 11:23 am
I'm trying to implement a very basic version of a priority queue with multiple processes potentially accessing the queue concurrently. A single process should always acquire a unique work item. If possible, I want to reduce locking as much as possible. However, acquiring the unique work item is the most important priority.
What's the best method to acquire and update a given priority queue work item?
Here are some possible options:
1.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
-- SELECT statement that acquires the most important "to-be-worked-on" item and stores it in a variable
-- UPDATE statement that updates the "to-be-worked-on" item based on the variable
END TRANSACTION
2.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
-- UPDATE statement that acquires the most important "to-be-worked-on" item
END TRANSACTION
3.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- SELECT statement that acquires the most important "to-be-worked-on" item and stores it in a variable
-- UPDATE statement that updates the "to-be-worked-on" item based on the variable
END TRANSACTION
4.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- UPDATE statement that acquires the most important "to-be-worked-on" item
END TRANSACTION
Thanks in advance for your help!
June 11, 2008 at 6:38 am
Also, would using a table hint (i.e., updlock, serializable, etc.) be beneficial? Or is changing the transaction isolation level (i.e., serializable) and/or query statement order be sufficient?
Thanks!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply