July 16, 2003 at 1:24 am
Hi,
One of our development team wrote a very bad SP.
When running this SP it created a deadlock in the Tempdb database.
Because of that we had a very strange phenomenon that whatever other application was using Tempdb for temporary tables with inserts of updates, this application was blocking to. Even if it has nothing to do with the fist application.
My question: Is it normal that this deadlock has such a major impact on al other applications ?
I already dealt with some deadlocks but they never had an impact on other applications, why this one, is this normal.
I know tempdb is used in many cases but I should think that the deadlocks should have impact on table level an not on Db level.
Blokking process
================
Exec UspGetGBSTblDest2Pmgctbldest
Blokked
=======
Select SD.SDName SalesDestinationName From dbCbuPmgc..tblSalesDestination SD Where DATEDIFF(day,SDSalesDestinationCreationDate, getDate())=0
This was the SP
===============
CREATE PROCEDURE [dbo].[UspGetGBSTblDest2Pmgctbldest]
AS
BEGIN
SET ANSI_DEFAULTS ON
SET NOCOUNT ON
Declare @NextSalesdestinationId integer
Declare @DestIbisCode varchar(30)
Declare @DestName varchar(255)
Declare @strSalesMail varchar(100)
Declare @strAdminMail varchar(100)
Declare @strMail varchar(200)
select @NextSalesdestinationId = ''
select @DestName = ''
DECLARE IBIS_Cursor CURSOR FOR
SELECT DestIbisCode, DestName FROM tbldestinations
where (DestIbisCode is not null and DestIbisCode !='')
Open IBIS_Cursor
IF (@@ERROR <> 0)
GOTO QuitWithRollback
Fetch Next From IBIS_Cursor into @DestIbisCode, @DestName
IF (@@ERROR <> 0)
GOTO QuitWithRollback
WHILE @@FETCH_STATUS = 0
BEGIN
if not exists (Select 1 from tblDesttoSalesDest
where upper(DestinationIBIS_Id) = upper(@DestIbisCode))
Begin
select @NextSalesdestinationId = isnull(max(PKSalesdestinationId),0) + 1
from tblsalesdestination
IF (@@ERROR <> 0)
GOTO QuitWithRollback
Insert into tblSalesdestination (PKSalesDestinationId, SDName,SDSalesDestinationCreationDate)
Select @NextSalesdestinationId, @DestName, getdate()
IF (@@ERROR <> 0)
GOTO QuitWithRollback
Insert into tblDesttoSalesDest(FKSalesDestinationId, DestinationIBIS_Id)
Select @NextSalesdestinationId, @DestIbisCode
IF (@@ERROR <> 0)
GOTO QuitWithRollback
End
Fetch Next From IBIS_Cursor into @DestIbisCode, @DestName
END
CLOSE IBIS_Cursor
DEALLOCATE IBIS_Cursor
/*Sending auto mail to sales users*/
SELECT @strSalesMail=SPValue FROM tblSysparameters WHERE SPDescription='Sales Mail'
SELECT @strAdminMail=SPValue FROM tblSysparameters WHERE SPDescription='Admin Mail'
SET @strMail=@strSalesMail + ','+ @strAdminMail
IF NOT ISNULL(@strSalesMail,'')=''
BEGIN
EXEC sp_SQLSMTPMail @vcTo = @strMail, @vcBody = 'New Destinations added today', @vcQuery = 'Select SD.SDName SalesDestinationName From dbCbuPmgc..tblSalesDestination SD Where DATEDIFF(day,SDSalesDestinationCreationDate, getDate())=0'
END
/*end of sending auto mail*/
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK
Return @@error
EndSave:
COMMIT
Return @@error
END
July 21, 2003 at 12:00 pm
This was removed by the editor as SPAM
July 21, 2003 at 1:01 pm
Deadlocks are bad no matter where they happen. You might try making the cursor local and read only.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply