READPAST vs. NOLOCK

Question

Post reply

READPAST vs. NOLOCK

sergey.gigoyan

Ten Centuries

Points: 1213
More actions
August 3, 2015 at 9:35 pm

#319432

Comments posted to this topic are about the item READPAST vs. NOLOCK
August 4, 2015 at 1:13 am

This was removed by the editor as SPAM
August 4, 2015 at 2:58 am

This was removed by the editor as SPAM
August 4, 2015 at 7:50 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Shiva N Hall of Fame Points: 3125 More actions · Answer 1

Your intention is good, but i am getting 5,5 rows, how i can get 2,5 rows???

Please can you explain this same...

Thanks,
Shiva N
Database Consultant

Stuart Davies SSCoach Points: 19505 More actions · Answer 2

Shiva N (8/3/2015)
Your intention is good, but i am getting 5,5 rows, how i can get 2,5 rows???
Please can you explain this same...

I fell into the same trap as you and got it wrong too. Re-read the question - it says to run upto the commit in one window, and while the waitfor delay is running, run the two queries. Try it and you'll get a different result.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

g.maxfield Default port Points: 1471 More actions · Answer 3

Sorry but when I ran your code on SQL Server 2014 I got 5 rows returned on both queries? 😀

Any ideas what happened?

92945 & 92949?

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

Good question, thanx, using NOLOCK a lot.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

g.maxfield Default port Points: 1471 More actions · Answer 5

Thanks SSCrazy. I ran as you suggested and now get the correct answer 2,5. G;-)

g.maxfield Default port Points: 1471 More actions · Answer 6

g.maxfield

Default port

Points: 1471

August 4, 2015 at 3:14 am

#1817050

Sorry Stewart - it's you to thank. g

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

Thanks, Sergey. Nice question to start the day. As soon as the NOLOCK was spotted, my suspicions were raised. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

Hany Helmy (8/4/2015)
Good question, thanx, using NOLOCK a lot.

You really shouldn't be in the habit of littering your database with that hint unless you TRULY understand what it does. It will ignore locks and possible dirty reads but it is more sinister than that. It can and will return missing and/or duplicate rows. If you use that hint for an insert or update it can corrupt your indexes. As with any table hint they should be used as an absolute last resort when all other tuning methods don't work.

Here are a few articles that explain what is really happening with NOLOCK.

http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

JP Dakota, PRC SSC Eights! Points: 986 More actions · Answer 9

Did you execute queries 1 & 2 in seperate query windows to the initial? Yes

If you did do that, did you execute them while the transaction was running or after it had completed? Completed

if executed while the transaction was still executing, the readpast would have overlooked the 3 locked records and only delivered a result comprising 2 records. It returned 5,5.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

Stewart "Arturius" Campbell (8/4/2015)
JP Dakota, PRC (8/4/2015)
Did you execute queries 1 & 2 in seperate query windows to the initial? Yes
If you did do that, did you execute them while the transaction was running or after it had completed? Completed
if executed while the transaction was still executing, the readpast would have overlooked the 3 locked records and only delivered a result comprising 2 records. It returned 5,5.
try executing queries 1 & 2 while the transaction is busy.

You can use a greater value in the WAITFOR command.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lokesh Vij SSChampion Points: 10836 More actions · Answer 11

Thanks, Sergey. Nice question!

It's always good to learn about hints and how to use them with caution. 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter