SQL Server Performance

NOLOCK vs. READPAST

Discussion in 'General Developer Questions' started by derfrent, Nov 21, 2005.

  1. derfrent New Member

    Does anyone have a preference between NOLOCK and READPAST? I have noticed that maybe NOLOCK will cause errors while reading a transaction that rollsback. Will READPAST do the same?

    Thanks,

    Jeremy
  2. satya Moderator

    Checkhttp://www.sql-server-performance.com/reducing_locks_hints.asp for more informatino.

    You can use query hints with a transaction but you're looking at scalability issues. You could use the READPAST locking hint. Keep in mind that this is onlyusefull in some very specific circumstances. If you use this locking hint in a statistical or reporting query, you'll miss some data without having any indication if and how much data you miss.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. derfrent New Member

    Thanks satya. I've read that, but My NOLOCKs are hanging because of rolled back transactions. I am hoping readpast will solve it. I want to make sure before I change hundreds of SELECT statements.
  4. vsnreddi New Member

    quote:Originally posted by derfrent

    Does anyone have a preference between NOLOCK and READPAST? I have noticed that maybe NOLOCK will cause errors while reading a transaction that rollsback. Will READPAST do the same?

    Thanks,

    Jeremy

    Check with SQL Server BooksOnline
    NOLOCK-
    READPAST-> LockingHints

    VSNREDDY

    Be pure to do good.[Vivekananda]
  5. derfrent New Member

    Vsnreddi,

    SQL Server Books Online doesn't discuss problems with using NOLOCK while another transaction is rolling back. So it isn't much help in discussing whether a READPAST would help.
  6. satya Moderator

    True, BOL gives information about them and other Technet articles & information on web can help in this regard.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. SQLDBcontrol New Member

    Jeremy,

    I would caution against using READPAST unless you are happy that you understand its implications. When using READPAST, it will skip any rows that are locked, meaning that you will not get all of the rows you would have been expecting. At least with NOLOCK, you will get all of your rows back - even if the data is potentially dirty.

    I'm curious about the errors you are referring to when a statement rolls back. Can you expand on the errors you are seeing - I've not experienced this behaviour before with NOLOCK.



    Karl Grambow

    www.sqldbcontrol.com
  8. derfrent New Member

    Karl,

    When my SELECT statements specifying NOLOCK are running and someone's transaction rollsback. The SELECTs start firing off hundreds of page and table locks, begins to block everying, and the server grinds to a hault. I kill the SELECT with NOLOCKS and everything perks right up.

    I'm fine with the ramifications of data loss with READPAST, just as long as it doesn't hang on rollbacks. Microsoft has admitted this problem in 6.5 and 7.0, but I can't find anything in 2000. I'm running 2000.

Share This Page