deadlock issue - 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

SQL Server Development (2000)

deadlock issue

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-01 : 08:04:08

David writes "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

"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-01 : 10:48:29

I believe the problem is that 2 different processes have grabbed the same unprocessed_message_id and are waiting for the other to release the lock it so they can continue with the update, resulting in deadlock.

What you should do is construct you sp logic SOMETHING like this:


--with default isolation level (read committed)
--Reserve the next available (unreserved) record
UPDATE unprocessed_message SET 
	state = @Reserved
	,win_service_name = @WinServiceName
	,machine_name = @MachineName
WHERE	unprocessed_message_id = 
		(SElect min(unprocessed_message_id)
		From unprocessed_message
		Where state = @unprocessed)

--Now you have a reserved record without holding a lock
--and without colliding with another process


--Return reserved record for processing
Select	<colList>
From	unprocessed_message
Where	state = @reserved
AND	win_service_name = @WinServiceName
AND	machine_name = @MachineName

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources