Cross Apply

Question

Cross Apply

TST1

SSCommitted

Points: 1798
More actions
November 25, 2010 at 7:19 am

#146374

Hi,
I would like to know if it's possible for NOLOCK hint to work on a cross database query?
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Before answering that, I have one for you. Do you know what nolock does?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TST1 SSCommitted Points: 1798 More actions · Answer 2

TST1

SSCommitted

Points: 1798

November 25, 2010 at 8:09 am

#1255582

yep and I don't mind if I am getting dirty data.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

And you're also fine with possibly missing rows or reading rows twice?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TST1 SSCommitted Points: 1798 More actions · Answer 4

TST1

SSCommitted

Points: 1798

November 26, 2010 at 3:49 am

#1255771

Gail,

That's also fine.

rs80 SSCrazy Points: 2312 More actions · Answer 5

rs80

SSCrazy

Points: 2312

November 26, 2010 at 12:18 pm

#1255870

Gail, why would you get duplicate rows?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

rs80 (11/26/2010)
Gail, why would you get duplicate rows?

Non-techincal answer: When dirty reads are allowed, it is possible to read a row and then have someone update that row which can cause that same row to be read again. Bottom line, you end up with the same row twice with different values.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (11/26/2010)
rs80 (11/26/2010)
Gail, why would you get duplicate rows?
Non-techincal answer: When dirty reads are allowed, it is possible to read a row and then have someone update that row which can cause that same row to be read again. Bottom line, you end up with the same row twice with different values.

and another possibility is a page split which can allow the same data to be read twice...

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 8

reading the same row twice can also happen with read committed.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Ninja's_RGR'us (11/26/2010)
Jeff Moden (11/26/2010)
rs80 (11/26/2010)
Gail, why would you get duplicate rows?
Non-techincal answer: When dirty reads are allowed, it is possible to read a row and then have someone update that row which can cause that same row to be read again. Bottom line, you end up with the same row twice with different values.
and another possibility is a page split which can allow the same data to be read twice...

You'll have to show me that one. With read committed, I believe that's impossible,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

Nils Gustav Stråbø (11/26/2010)
reading the same row twice can also happen with read committed.

Same thing here. Can you show me at least a white paper on the subject because the whole idea of read committed is to keep such things from happening.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Nils Gustav Stråbø (11/26/2010)
reading the same row twice can also happen with read committed.

True, but you need more specific circumstances than with read uncommitted. There are allowed anomalies in every isolation level up to serialisable. It's always a trade off. The reason I'm so pushy on nolock is that most people I've seen using it don't realise that it's a trade off. They don't realise what they're giving up in order to read through locks. They think it's a free lunch.

(High level) Technical time.

In read uncommitted, a scan is done in allocation order (order of pages in the file). Any data modification that splits a page (insert or update that grows the row) can result in the scan missing or duplicate reading rows as half of the page is split and moves from behind the scan to in-front of the scan, or vis versa.

In read committed, allocation order scan isn't allowed (unless there's a table lock or a few other conditions that ensure that the table cannot change during the scan). To get dup read/mis read in read committed I believe you need an update of the table that changes the value of the key for the index you are reading for one or more rows. The key value changes so the index row moves from behind the scan to in front of the scan (or vis versa). Possible in read committed because locks are released as soon as the read is done. Not possible in repeatable-read because in that isolation level the locks are held until the end of the transaction.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Jeff Moden (11/26/2010)
Ninja's_RGR'us (11/26/2010)
Jeff Moden (11/26/2010)
rs80 (11/26/2010)
Gail, why would you get duplicate rows?
Non-techincal answer: When dirty reads are allowed, it is possible to read a row and then have someone update that row which can cause that same row to be read again. Bottom line, you end up with the same row twice with different values.
and another possibility is a page split which can allow the same data to be read twice...
You'll have to show me that one. With read committed, I believe that's impossible,

I was still in the nolock conversation :w00t:.