Strange behaviour using temp tables with a static named index (deadlock) and an individual named idx
Can someone give me an explaination for it?
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
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 Read More