1

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.

4
  • Have you considered configuring and testing READ COMMITTED SNAPSHOT for the database in your next release? Commented Mar 18, 2023 at 22:16
  • @DavidBrowne-Microsoft: Don't call me when tempdb fills up. More to the point, we analyzed that when it came out and found what it would do and it wasn't pretty. Redoing the entire application so it does all validations before writing the first row to the DB and building write order so that observing half-committed transactions is still a close enough to consistent model and using transactions only for the no-commit-half feature wasn't easy but it sure paid dividends in performance.
    – Joshua
    Commented Mar 18, 2023 at 23:50
  • "we analyzed that when it came out" Ok, but that was 17 years ago. Commented Mar 19, 2023 at 0:11
  • @DavidBrowne-Microsoft: The application is so old it started on SQL 7 and first went into production on SQL 2000. But its nature has not changed even though we have added so many more features. Setting READ COMMITTED SNAPSHOT instead of READ UNCOMMITTED overruns tempdb.
    – Joshua
    Commented Mar 19, 2023 at 0:17

1 Answer 1

3

starting point

The fun thing about rowlock hints is that the storage engine is free to ignore them. From The Case of the Missing Shared Locks:

One interesting side-effect of the locking optimization described here is that a query that takes page locks may block where the same query issued with a ROWLOCK hint might not. (I say ‘might not’ since ROWLOCK is genuinely a hint, rather than a directive — the storage engine may or may not respect it.)

That’s not to say that you should immediately go out and add ROWLOCK hints to all your SELECT queries — that would probably be a very bad idea — but you should be aware that this locking optimization exists.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.