SQL query locks down

Question

SQL query locks down

Bas Geerdink

Grasshopper

Points: 10
More actions
December 10, 2004 at 9:06 am

#86114

Hi all,
I use an update-query in a DTS package to change some norm values. It changes about 5000 lines in a table 'AgeSetItem' of about 1.000.000 lines, selected by a WHERE clause. The problem is that after some time, the query locks down and the table is stuck! This happens all the time, when I run it in the DTS or in Query Analyzer. But.. When I split the query in to, e.g. … WHERE AgeSetItem.ID < ...ID and WHERE AgeSetItem.ID >= ...ID, the query performs OK!
Has anybody experienced this kind of problem before? Our server is a 2GB, 8-processor SAP server. The query is:
UPDATE AgeSetItem
SET AgeSetItem.Norm =
IsNull((SELECT Norm FROM Item
INNER JOIN ItemSub5 on Item.Item5Id = ItemSub5.ID
INNER JOIN NormenCP on ItemSub5.Name = NormenCP.mutatie
INNER JOIN ItemSub6 on Item.Item6Id = ItemSub6.ID and ItemSub6.Number = NormenCP.Connect
WHERE Item.LableID = 8
AND AgeSetItem.ItemID = Item.ID), 0)
WHERE AgeSetID = 9
AND Norm IS NULL
In short, the tables Item, ItemSub5, ItemSub6 form part of the organisation structure of our client, NormenCP contains the norm values, grouped by 'mutatie' and 'Connect'
Many thanks in advance!
Bas

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

You're not being clear enough...

Is the update being blocked by another process (live lock)? Or is it (perhaps) the only thing running at the time when it hangs?

Then supposing you are real certain it is just the update itself... you need to provide more specifics about your system (processors & config...)

Bas Geerdink Grasshopper Points: 10 More actions · Answer 2

There are several queries that run at the same time using the same tables. 3-5 DTS packages update and insert into the tables AgeSetItem and Item, and some users will perform SELECT queries on them to generate reports.

All the specs I know of the server system is that it's got 8 processors, 2 GB internal memory. If it'll help I can figure out more...

Bas

john-132685 SSCertifiable Points: 6134 More actions · Answer 3

And so?

did you run sp_who when the process was "hanging" and see if it was blocked by another process?

Not all dead lock conditions are automatically detected by sqlserver and live locks will stay forever...