We have a lock pileup problem on a customer site. By a combination of tools we managed to locate the root locker. It is definitely running this particular SQL statement at the time of the lock pileup:
INSERT INTO #Gather
SELECT (columns omitted for brevity)
FROM dbo.vScheduleInsuranceRuleViolations WITH(rowlock) OPTION (MAXDOP 1)
Lock hierarchy analysis leads to one unavoidable conclusion:
WITH (rowlock) isn't doing anything useful here.
The view is very long and rightly takes a long time to evaluate. There are several callers that differ only in there filtering WHERE clause (most of the DB or a few records) and lock/concurrency demand. In this particular caller, we have the worst filter and the lowest lock demand (
WITH (rowlock) within
READ COMMITTED --
READ UNCOMMITTED won't work here).
Before I copy & paste the view definition into the caller I want to know why this doesn't work if possible; because I'm expecting the
WITH (rowlock) to apply to every table read in the view.