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
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.
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.
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]
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.
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.
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
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.
