|
|
Posted by Simon Hayes on 09/06/05 17:20
Adding a primary key is certainly the best place to start, and would be
better than using query hints. Without a primary key, MSSQL has no way
to know that itemid is unique, so each process must always scan the
whole table, in case there are more rows with that itemid. Even if each
process is looking for a different itemid, they can't guarantee a
consistent update without locking the whole table, which seems to be
what is causing your problem - each process acquires more and more
locks until they run into a deadlock.
With the key, MSSQL will know that each process can lock only one row,
so if the itemid values are different there should be no problem. Even
if they are the same, process B will simply wait for the row or key
lock from process A to be freed, because it knows it doesn't need to
look for any more rows.
And of course adding a primary key is essential for data integrity, as
well as making your data model more explicit.
Simon
Navigation:
[Reply to this message]
|