Does Using a Linked Server Affect Isolation Levels

105 Views Asked by At

I have a query running on one server (let's call it Server B). It looks something like the below - using a linked server to query data from another server ('Server A')

-- in SERVER B
CREATE PROC [foo]
AS
INSERT [Table]
SELECT this, that
FROM [LnkServer Server A].[database].[schema].[table];

Server B has is_read_committed_snapshot_on = 1, while Server A does not - would the sPROC foo read snapshots on Server A, or wait for transactions to complete?

Similarly to the above, If I were to change foo to include the line SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, would it read uncommitted data from Server A, or would it still be reading as before?

I would like to change the way the scripts are written so they look more like this:

------------- In Server A --
CREATE PROC [bar]
AS 

-- I have included a local isolation set just as an example, Read uncommitted may not always be appropriate and I know the risks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT this, that
FROM [database].[schema].[table];
GO

--------------- Server B --
CREATE PROC [foo]
AS

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM OPENQUERY([LnkServer Server A],''EXEC [database].[schema].[bar]'')';

INSERT [Table]
    EXEC sp_executesql @SQL;
GO

I feel like using OPENQUERY like this should result in better execution plans and would allow the isolation level to be a bit more visible, but there are loads of queries like the first example and I worry that they might be causing a problem.

Everything I search for on the web tells me lots of information about when to use each isolation level and why, but I can't see anything about how it interacts with linked servers and I worry that it might be causing some of the problems we are seeing...

0

There are 0 best solutions below