Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The question is pretty simple, as the title says. However, I cannot find any references to confirm or contradict this.

The problem leading me to this question is that I have a view joining several other views and tables. One of these tables (let's call it table A) did not have a covering non-clustered index on columns in join conditions and a clustered index scan was issued instead.

The effect was that all other queries that also used table A AND used a clustered index scan to be made were waiting after the first query (no deadlock yet, since I have a large timeout). As soon as I created a non-clustered index on table A for the join columns, the clustered index was no longer used and queries started working fine in parallel.

Anyway, if my assumption is correct, is there a way to detect the lock? I haven't tried yet enabling any locking-related trace flags? I only used 'sp_who2' and a few other queries found around here for now.

If you have stumbled across this before, then please share your thoughts.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

There are many many many more factors at play.

  • isolation level. locking behavior differs wildly between isolation levels. Some don't lock at all (read uncommitted, snapshot, rcsi). The default read committed transiently locks rows it reads as long as is necessary. Repeatable read and Serializable hold on to locks and end up locking everything, and many developers deploy serializable without ever realizing they do so.
  • scan purpose. Scans for read are compatible with each other so they don't block, no matter what they lock. If you have scans blocking other scans they must be scans for update, which are incompatible with each other. Again, snapshot isolation level (including rcsi) do not block even when doing a scan for update.
  • lock granularity, based on cardinality estimates. Scans may choose row, page or rowset level granularity. A table scan will likely choose page locks.
  • index page locks/row locks configuration, which you so inadvertently changed. You should revert the change, since is not based on any measurement and root cause analysis. Guts feeling has no role in investigation.
  • lock escalation
  • row stability requirements when off-row LOB data is present
  • other

I suggets you follow the procedure described in Capturing wait stats for a single operation to capture the wait stats of the scans you observed as blocking. Se if indeed they block on locks held by the table scan operation. IF the scenario is truly as you described (read scan vs. other read operations) then there is no reason for blocking so something else will be at play. You can also give sp_whoisactive a shot.

share|improve this answer
    
Thanks! Should've mentioned that it's an implicit transaction. I'll give your suggestions a try and get back with some results. –  Marcel N. Nov 26 '12 at 9:49
    
@Remus: Can you please clarify? " If you have scans blocking other scans they must be scans for update, which are incompatible with each other." Does this mean all scans for update are incompatible with read scans? Or some update scans are incompatible with some other update scans? –  ypercube Nov 26 '12 at 14:15
    
See the U vs. S and U vs. U in Lock Compatibility Matrix –  Remus Rusanu Nov 26 '12 at 14:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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