March 30, 2005 at 5:42 pm
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
April 1, 2005 at 3:25 am
Firstly are you sure you got a deadlock? You got the message that your transaction has been chosen as the victim? Just want to be sure as a lot of people confuse blocking with deadlocks.
So if you have a deadlock then i think you need au unclustered index on state at least.
Run the query through query analyser and have a look at what locks are being taken out. Table lock on unprocessed_message ?
Doubt that the deadlock is a deadly embrace as it only effects one table, maybe a deadlock on parrallelism and you may need to set maxdop to 1.
April 1, 2005 at 6:20 am
If several instances of GetNextUnprocessedMessage can run at once, then deadlock can occur because 2 instances can both acquire a shared lock in in the first select statement.
Then each instance will be blocked by the other when they attempt to acquire the exclusive (update) lock.
I think the trick here is to use the UPDLOCK keyword in the first select. One heuristic I use in trying to avoid deadlock is to acquire exclusive locks before shared locks.
April 1, 2005 at 11:02 am
Renato is correct.
I have tested the situation shown in the original proc and Renato is correct. A deadlock can occur in this situation when using HOLDLOCK. Using UPDLOCK (instead of HOLDLOCK) avoids the deadlock by acquiring an exclusive lock during the initial SELECT.
So if you don't need the range lock on the key that is accquired when HOLDLOCK is used then use UPDLOCK.
--ron
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply