Deadlock on Tempdb - 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 on Tempdb

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-17 : 07:40:25

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

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-17 : 13:33:16

Anything that holds locks on system tables in tempdb will probably block most things on the server and probably prevent new logons.
It used to be very common when new people read that select * into #tbl was a lot faster than an insert - what they didn't realise was that it also stopped everything else on the server. It has been changed now so that unless it is in dynamic sql it is ok.

Don't know why this sp is locking system tables in tempdb but I don't know the affect of cursors now - don't consider it worth the effort as there is no reason to use them. In v6.5 they were very resource intensive and their creation could cause problems but they are better implemented now.

>> deadlocks should have impact on table level an not on Db level
They affect the spid. The deadlock is caused by locking though and that same locking would affect the whole database - other spids are just being blocked instead of deadlocked as they don't holda lock that the problem spid needs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources