Forum Discussion

Juergen530's avatar
Juergen530
Copper Contributor
Oct 18, 2024

Strange behaviour using temp tables with a static named index (deadlock) and an individual named idx

Hello,
 
my script looks similar to the following script. This script is getting called very often (parallel). In this version it sometimes results in a deadlock situation. The deadlock happens only between two or more calls of this script. But, if I give ix_tmpA an individual name, like 'ix_tmpA_1', 'ix_tmpA_2', 'ix_tmpA_3'..., for every call, it never results in a deadlock. I don't understand why.

Can someone give me an explaination for it?

Thank you!
 

 

SELECT a.*, CONVERT(BIGINT, NULL) as TableBID, CONVERT(BIGINT, NULL) as TableCID
INTO
#tmpA
FROM
TableA a

CREATE CLUSTERED INDEX ix_tmpA ON #tmpA (ID);

SELECT b.*
INTO
#tmpB
FROM
TableB b

ALTER TABLE #tmpB ADD PRIMARY KEY CLUSTERED (ID)
CREATE SPATIAL INDEX ix_spatialB ON #tmpB (Geo)

SELECT c.*
INTO
#tmpC
FROM
TableC c

ALTER TABLE #tmpB ADD PRIMARY KEY CLUSTERED (ID)
CREATE SPATIAL INDEX ix_spatialB ON #tmpB (Geo)


UPDATE a SET
TableBID =(
	SELECT TOP 1 ID FROM
	#tmpB b WITH(INDEX(ix_spatialB))
	WHERE
	b.Geo.STIntersects(geography::Point(a.Latitude, a.Longitude, 4326) = 1
)
FROM
#tmpA a

UPDATE a SET
TableCID =(
	SELECT TOP 1 ID FROM
	#tmpC c WITH(INDEX(ix_spatialC))
	WHERE
	c.Geo.STIntersects(geography::Point(a.Latitude, a.Longitude, 4326) = 1
)
FROM
#tmpA a

Select * from #tmpA
Select * from #tmpB
Select * from #tmpC


DROP TABLE #tmpA
DROP TABLE #tmpB
DROP TABLE #tmpC​

 

Resources