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  SQL Server Development (2000)  deadlock issue

Author  Topic  zphillm Starting Member 1 Post Posted - 2005-03-30 : 13:22:49 We're currently developing a multithreading windows service which is simply dispatching work to the server farm. The deadlock occured in one of the stored procedures. The sp is pretty simple, it first gets the minimum id in the table, then set machine name and windows service name for that row. Finally return that row. here is the sp: ----------------------------------------------------- CREATE PROCEDURE dbo.GetNextUnprocessedMessage @StateNew varchar(20), @StateNext varchar(20), @WinServiceName varchar(20), @MachineName varchar(20) AS DECLARE @id int BEGIN TRANSACTION SET @id = -1; -- grab the next available message, if present SET @id = ( SELECT MIN(unprocessed_message_id) FROM unprocessed_message WITH (HOLDLOCK, ROWLOCK) WHERE state = @StateNew ) -- update the state of the row we just selected (and locked) so that it can't -- be grabbed by anyone else using this procedure UPDATE unprocessed_message WITH(ROWLOCK) SET state = @StateNext, win_service_name = @WinServiceName, machine_name = @MachineName WHERE unprocessed_message_id = @id IF (@@ERROR > 0) BEGIN ROLLBACK TRANSACTION RAISERROR('Error fetching next message', 15, -1, @@ERROR) END -- return the newly updated row SELECT * FROM unprocessed_message WHERE unprocessed_message_id = @id COMMIT TRANSACTION GO -------------------------------------------------- we set clustered index (unprocessed_message_id) on unprocessed_message, and the isolation level is default (read committed). please help me on this issue. Thank you. David