NOLOCK across linked server with join

Question

Post reply

NOLOCK across linked server with join

Xedni

SSCertifiable

Points: 6951
More actions
March 9, 2015 at 12:51 pm

#295851

Hi All,
I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).
I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.
update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id
From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.
Is there some way I can limit the content on sB by my temp table on sA but still use nolock?
Executive Junior Cowboy Developer, Esq.[/url]
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Nevyn SSChampion Points: 14210 More actions · Answer 1

Can we start with a deeper examination of why you feel nolock is necessary?

Xedni SSCertifiable Points: 6951 More actions · Answer 2

Nevyn (3/9/2015)
Can we start with a deeper examination of why you feel nolock is necessary?

No. I'm fully aware of all the implications of NOLOCK and the possibility of dirty reads. It has been strongly established among the DBAs and developers at my company that it is necessary when accessing these tables. Even if you could argue that they're not necessary, fundamentally, my original question is still valid.

Executive Junior Cowboy Developer, Esq.[/url]

Nevyn SSChampion Points: 14210 More actions · Answer 3

What if you create a view on the remote server that queries the table with nolock and then join to that?

Xedni SSCertifiable Points: 6951 More actions · Answer 4

Ill play around with that; thanks. Do you know if theres any way to do it all from one server? its not a huge pain but in a perfect world I could just run one script an not have any setup or cleanup.

Executive Junior Cowboy Developer, Esq.[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

--duplicate post--

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

JeeTee (3/9/2015)
Ill play around with that; thanks. Do you know if theres any way to do it all from one server? its not a huge pain but in a perfect world I could just run one script an not have any setup or cleanup.

Why not create the temp table on the OTHER side of the fence (i.e. where the 500M rows are)? Depending on what goes into that table, sooner or later the query will attempt to start pulling that monstrosity over the wire (i.e. the one day you put a few too many criteria records into the local temp table, and the exec plan decides it needs to scan).

If that's possible then openquery would essentially make sure that the filtering happens FIRST then the content gets pulled over the wire.

http://sqlbits.com/Sessions/Event10/Distributed_Query_Deep_Dive

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Xedni SSCertifiable Points: 6951 More actions · Answer 7

Thanks Matt, now that I think about it, that's probably the right direction to go. That in conjunction with the possibility of setting up nolock'd views is probably enough to get this going in the right direction.

Executive Junior Cowboy Developer, Esq.[/url]

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

JeeTee (3/9/2015)
Nevyn (3/9/2015)
Can we start with a deeper examination of why you feel nolock is necessary?
No. I'm fully aware of all the implications of NOLOCK and the possibility of dirty reads. It has been strongly established among the DBAs and developers at my company that it is necessary when accessing these tables. Even if you could argue that they're not necessary, fundamentally, my original question is still valid.

You seem to already some possibilities at a solution which is awesome. My concern is that NOLOCK has FAR greater implications than dirty reads. You can and will get missing and/or duplicate rows. Again this may not be a huge deal especially if this is summary data with lots of rows. As long you are aren't doing updates or inserts (that can actually cause database corruption).

_______________________________________________________________

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/

Xedni SSCertifiable Points: 6951 More actions · Answer 9

How, hypothetically would database corruption arise from using nolock?

Executive Junior Cowboy Developer, Esq.[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

JeeTee (3/9/2015)
Hi All,
I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).
I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.
update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id
From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.
Is there some way I can limit the content on sB by my temp table on sA but still use nolock?

What makes you think NOLOCK doesn't work across servers? Do you have any documentation to support that?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Xedni SSCertifiable Points: 6951 More actions · Answer 11

I am basing this assumption off the agregate of several articles on MSDN, Stack overflow and SSC

http://www.sqlservercentral.com/Forums/Topic344649-149-1.aspx

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fbc52b71-a084-4315-9bb5-ebcb8e745c0a/with-nolock-across-a-linked-server?forum=sqldatabaseengine

http://stackoverflow.com/questions/9268438/cannot-specify-an-index-or-locking-hint-for-a-remote-data-source-sql-server-2005

plus the article in my OP.

I could be wrong; everyone on these posts could be wrong, and I can't find the MSDN page which specifically says it doesn't work, but the gist I got from this was that table hints do not get sent over the linked server (unless, for example, you use OPENQUERY).

Executive Junior Cowboy Developer, Esq.[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

JeeTee (3/9/2015)
How, hypothetically would database corruption arise from using nolock?

It isn't hypothetical. It is a known issue when doing updates and/or deletes using the NOLOCK hint.

http://www.mssqltips.com/sqlservertip/3172/avoid-using-nolock-on-sql-server-update-and-delete-statements/[/url]

There dozens of other references surrounding these statements and the usage of nolock.

_______________________________________________________________

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/

Xedni SSCertifiable Points: 6951 More actions · Answer 13

Interesting. I guess I've never come across a place where I saw an nolock being used on a DML statement (I didn't even think it was possible). Thanks for the enlightenment. And no, I'm certainly not using nolock DML in any of my code 🙂

Executive Junior Cowboy Developer, Esq.[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

JeeTee (3/9/2015)
I am basing this assumption off the agregate of several articles on MSDN, Stack overflow and SSC
http://www.sqlservercentral.com/Forums/Topic344649-149-1.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fbc52b71-a084-4315-9bb5-ebcb8e745c0a/with-nolock-across-a-linked-server?forum=sqldatabaseengine
http://stackoverflow.com/questions/9268438/cannot-specify-an-index-or-locking-hint-for-a-remote-data-source-sql-server-2005
plus the article in my OP.
I could be wrong; everyone on these posts could be wrong, and I can't find the MSDN page which specifically says it doesn't work, but the gist I got from this was that table hints do not get sent over the linked server (unless, for example, you use OPENQUERY).

There is no documentation there, and I can confirm that table hints work for me across linked servers.

I run this on the "remote" server to lock a row:

begin tran;

select top 1 * from dbo.sl_transfer with(UPDLOCK ROWLOCK);

Then run this on the local server to try and select from the table, which gets blocked by my first transaction:

begin tran;

select top(1) * from LinkedServer.db.dbo.sl_transfer;

Then run this using WITH(NOLOCK) and successfully retrieve the row from the remote table:

begin tran;

select top(1) * from LinkedServer.db.dbo.sl_transfer with(nolock);

I have to do this sort of thing a lot due to pessimistic locking from a third party application on one of our databases, so I know it works (aside from proving it here).

Servers:

Remote:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Local

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);