6

Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages?

E.g. using ISOLATION LEVEL READ UNCOMMITTED, what effect (if any) do the ALLOW_PAGE_LOCKS or ALLOW_ROW_LOCKS on an index have??

ALTER INDEX IX_FirstName ON Employee
SET (ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)

I can't seem to find a definitive answer anywhere - the MSDN documentation on the transaction isolation levels really only talks about the data pages....

  • What problem are you solving? Maybe snapshot isolation is a better solution? – A-K Feb 22 '12 at 15:03
  • Just a question by a coworker :-) for now – marc_s Feb 22 '12 at 15:35
6

No. It doesn't affect anything regarding the internal management within SQL Server. You're setting that connection for you, for your queries. SQL Server manages it's own locking it's own way.

Why would you turn off page and row locking on an index? You're more likely to see more severe locking than if you let SQL Server manage that index as it sees fit. By setting both those values to OFF, you just told SQL Server to take a table lock out. Considering you're also messing with read_uncommitted, I'll bet you don't want that.

If you're asking does read_uncommitted allow you to get dirty reads on indexes, yes. It does.

  • 1
    OK, thanks - that sort of / partially confirms my gut feeling :) – marc_s Feb 22 '12 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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