April 21, 2006 at 8:16 am
Hi,
When This stored procedure is executed there are blocks in
database. I am pasting the code of the stored procedure.
Can help is appreciated.
CREATE PROCEDURE dbo.queue_get_next_entry (@piQueueId NUMERIC,
@piLockUser VARCHAR(64),
@piLockSession VARCHAR(64),
@poQueueEntryId1 VARCHAR(38) OUTPUT,
@poMessage VARCHAR(255) OUTPUT ) AS
DECLARE @Error int, @poQueueEntryId numeric (38), @SortVal CHAR(1)
BEGIN
SET @poQueueEntryId = 0
SET @poQueueEntryId1 = 0
SET @Error = 0
SET @SortVal = 'N'
SET @poMessage = '@Entry'
SELECT @SortVal = sortval_yn FROM queue WHERE queue_id = @piQueueId
SET @Error = @@Error
IF @Error <> 0 GOTO ErrorHandler
BEGIN TRAN
IF @SortVal = 'Y'
BEGIN
UPDATE queue_entry WITH (UPDLOCK, ROWLOCK)
SET lock_user = @piLockUser,
lock_session = @piLockSession,
lock_time = getdate(),
@poQueueEntryId = queue_entry_id
WHERE queue_entry_id = (SELECT TOP 1 queue_entry_id
FROM queue_entry WITH (UPDLOCK, ROWLOCK)
WHERE queue_id = @piQueueId
AND lock_user IS NULL
ORDER BY SORTVAL)
END
ELSE
BEGIN
UPDATE queue_entry WITH (UPDLOCK, ROWLOCK)
SET lock_user = @piLockUser,
lock_session = @piLockSession,
lock_time = getdate(),
@poQueueEntryId = queue_entry_id
WHERE queue_entry_id = (SELECT TOP 1 queue_entry_id
FROM queue_entry
WHERE queue_id = @piQueueId
AND lock_user IS NULL
ORDER BY ENTRY_SEQ)
END
SET @Error = @@Error
IF @Error <> 0 GOTO ErrorHandler
IF @poQueueEntryId > 0
BEGIN
SET @poQueueEntryId1 = CAST(@poQueueEntryId as VARCHAR(38))
SET @poMessage = '0'
END
ELSE
BEGIN
SET @poQueueEntryId = 0
SET @poMessage = 'No more entries in the queue.'
END
COMMIT TRAN
RETURN
ErrorHandler:
SET @poQueueEntryId = 0
SET @poQueueEntryId1 = CAST(@poQueueEntryId as VARCHAR(38))
SET @poMessage = 'No more entries in the queue.'
RETURN -- exit stored procedure
END
April 21, 2006 at 8:49 am
well any explicit transaction will block for the duration of the transaction - is the proc blocking itself or other sql?
I note that your proc doesn't include any rollback in case of an error -
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 21, 2006 at 8:53 am
Thanks for the Information. when two spids call the stored procedure then they are blocking each other.
April 21, 2006 at 11:09 am
that'll be it then! Transactions are best made as small and as quick as possible
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 21, 2006 at 11:41 am
I am going removed the Begin trans and create the missing
index on QUEUE_ID in Queue_entry table.Any more suggestions .
Thanks
April 21, 2006 at 3:54 pm
I would recommend creating the index, but not removing the BEGIN TRAN keyword. As Colins stated, transactions are best made small and quick. I do not feel that removing the explicit transaction will buy you anything because, outside of a few logical constructs, the transaction consists of running one UPDATE. That same UPDATE ran outside of an explicit transaction will block just as long as it will inside an explicit transaction. You need to make changes to speed up the UPDATE statement. Is there a reason why you need lock hints here? I would suggest letting SQL Server handle your locking unless you've got a really good reason why not.
Also, as Colin stated, you could use a ROLLBACK TRAN statement in your error handler. Without it, an error will force the stored procedure to end, but the transaction that you began will remain open. This will result in prolonged locking of resources and could be part of your problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply