Use of WITH(NOLOCK)

Question

Use of WITH(NOLOCK)

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Iulian -207023 (11/10/2011)
Thank you Ninja's_RGR'us and GilaMonster for the very good examples: charts with aproximate values and session tables.
Jared I don't think that NOLOCK can generate the timeouts you mention but to be sure it might worth checking the locks with sp_lock, sp_who and sp_who2.
Regards,
Iulian

For me, it will show up as a timeout error on the FreeTDS side when there is a read error. Error handling sucks in FreeTDS. The Read Errors would come from a page split or a record being deleted as the NOLOCK query is running.

Thanks,

Jared

Jared
CE - Microsoft

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 2

Iulian -207023

SSCertifiable

Points: 7639

November 10, 2011 at 2:02 pm

#1406009

Page splits might be due to index fragmentation

Iulian

Jared SSC Guru Points: 61793 More actions · Answer 3

Iulian -207023 (11/10/2011)
Page splits might be due to index fragmentation
Iulian

Actually, I believe that is reversed... index fragmentation comes from page splits. However, that's besides the point. I'm sorry I'm not being clear. I am referring to the Read Errors section of this article: http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Jared

Jared
CE - Microsoft

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Trying to clear up the air :

You get the data movement error... when there's data movement. That means pagesplit or possibly if a record is moved to another page because the PK is updated (I never tested that specific case but it makes sense in theory from what I know of the internals).

So is there any one left who still needs help with this issue?

Jared SSC Guru Points: 61793 More actions · Answer 5

Ninja's_RGR'us (11/10/2011)
Trying to clear up the air :
You get the data movement error... when there's data movement. That means pagesplit or possibly if a record is moved to another page because the PK is updated (I never tested that specific case but it makes sense in theory from what I know of the internals).
So is there any one left who still needs help with this issue?

Case closed 😉

Jarede

Jared
CE - Microsoft