Multi-Processing Queues? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multi-Processing Queues?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-19 : 08:25:35

Dean writes "I have a need to have multiple "copies" of the same program reading the same table (from different PC's) in order to drain down a "queue" of tasks that are stored in that table. But each task in the table must be "grabbed" by one (and only ONE!) of the program copies running on various PC's.

The code I attempted to use was this:

UPDATE pending_tasks WITH (ROWLOCK)
SET
task_processor = 'MyID'
WHERE task_id IN (SELECT TOP 1
task_id
FROM pending_tasks WITH (ROWLOCK, READPAST)
WHERE task_processor IS NULL)

SELECT (blah...blah...blah)
FROM pending_tasks
WHERE task_processor = 'MyID'

My thinking was that this statement would shove "MyID" into a task record (while locking out any other task processors, as well as ignoring any records THEY had locked by READing PAST them), and I would then be able to go back and SELECT the entry with MyID and process it.

The pending_tasks table is very simple (for the sake of discussion), just task_id, task_todo, and task_processor columns.

The problem is...I'm routinely suffering deadlocks on this system in this table. I'm pretty sure I'm overlooking something straightforward about my locking logic, but I'm not seeing it, and so I thought I'd ask the SQL Team about it.

Thanks in advance for any light you might shed!"

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-19 : 13:55:32

Dean,
Ive had success using a GUID stored in a process_id column to "lock" the row. Heres a simple example:

SET NOCOUNT ON

DECLARE @table TABLE (test_id INT, test_name VARCHAR(15), process_id UNIQUEIDENTIFIER)

INSERT INTO @table
SELECT 1, 'Nathan', NULL UNION SELECT 2, 'Dean', NULL UNION SELECT 3, 'Michael', NULL


DECLARE @process_id UNIQUEIDENTIFIER,
	@row_count INT,
	@error_code INT

SELECT	@process_id = NEWID(),
	@row_count = 1

---------------------------------------------------
-- mark rows for processing
---------------------------------------------------
SET ROWCOUNT @row_count
UPDATE @table
SET process_id = @process_id
WHERE process_id IS NULL
SET ROWCOUNT 0


---------------------------------------------------
-- perform your processing here 
---------------------------------------------------
UPDATE @table
SET test_name = UPPER(test_name)
WHERE process_id = @process_id
SELECT @error_code = @@error

---------------------------------------------------
-- if fails nullify process_id so it can picked up again
---------------------------------------------------
IF @error_code <> 0 
BEGIN
	UPDATE @table
	SET process_id = NULL
	WHERE process_id = @process_id
END

SELECT * FROM @table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources