Erland,
Thanks so much for your quick and verbose response.
Indexing is important. Make sure that all involved queries uses Index
Seek or Clustered Index Seek, so the queries do not require table locks.
I'm not using queries, but rather ADO.NET to pull data from tables by
filtering on IDs. Can I take that to mean I should make sure that any
IDs in my WHERE statements should be indexed? All records in my DB have
a primary key, clustered index. But in one-to-many tables, I filter
heavily on that related table to the primary key in another table. For
example, tblWOS.FactoryOrderID is the reference in a child table to PK
in tblFactoryOrders.FactoryOrderID:
SELECT * FROM tblWOS WHERE FactoryOrderID=10
Are you implying that I should make sure tblWOS.FactoryOrderID should
be indexed, too?
I'll be a bit more explicit. The web app passes a series of discrete
SQL commands via an ADO.NET connection object:
BEGIN TRANS
-- Update primary table (one record)
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
-- Update related child table (many records)
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS
tblFactoryOrders has one record in it (primary record), and tblWOS
could have 5-20 related to tblFactoryOrders.
These transactions can happen for any primary record at any time from
the web by 100 users, but generally each primary record gets hit just a
couple times a day.
Meanwhile, there's a .NET app which periodically (2-10 times daily)
loads a set of data from tblFactoryOrders and tblWOS, about 900 records
from the first and 5,000 records from the second. It loads this way:
SELECT FactorOrderID, * FROM tblFactoryOrders WHERE ...
and then a series of for each record in tblFactoryOrders
SELECT * FROM tblWOS WHERE FactoryOrderID=...
The issue is that while this load is happening, someone on the web
doing an UPDATE on records unrelated to the loaded values cause a
deadlock on the app's SELECT. Is this an ISOLATION LEVEL issue?
It's OK if someone on the web updates while this load is happening.
I'd like to resolve:
- if two folks on the web hit the same FactoryOrderID object, I'd like
them not to deadlock, but one to wait on the other.
- if someone is loading the application, I'd like it not to deadlock
when someone on the web does a transaction.
- the app can also write back to tblWOS (again, records not available
by the web app), and not deadlock the web app or the save process.
Would it be wise to stick a:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANS
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS
thank you, Leaf