5

I'm using an MS SQL database that is written/read constantly, as well as the databases that it is linked to through Linked Server connection. This is why I'm using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in every query that I'm running. My question is, will this transaction level be used for the queries that are run on the Linked Server? Say my server is called General and it is linked to the server Linked. If I run the following query:

select *
from General.TableName
join Linked.[Database].dbo.AnotherTableName on 1 = 1

will the Linked.Database.dbo.AnotherTableName be (IS) locked?

Alexandr Kapshuk
  • 1,380
  • 2
  • 13
  • 29

1 Answers1

3

Yes, it uses same isolation level as in your "original" server. For example, see this query to test it:


-- Test various isolation levels and compare them
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT CASE ss.transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
,CASE ss2.transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL_LINKED 
, ss2.*
FROM sys.dm_exec_sessions  ss
inner join Linked.master.sys.dm_exec_sessions ss2
    ON  ss2.session_id >= 50
    and ss2.database_id = db_id('master')
--  and ss2.host_name = ss.host_name
where ss.session_id = @@SPID

This query attempts to show your session id in the linked server, and also show the other processes and their isolation level. You see that "your" isolation level on linked server will change when you change the level on original server

siggemannen
  • 3,884
  • 2
  • 6
  • 24
  • What about deadlock priority setting? Does that flow to the linked server as well? – Alex Mar 11 '23 at 16:58
  • One can check the deadlock_priority column of the same table. According to my tests, it *doesn't* get copied to the linked server session, so it has the default value even if you change your original connection – siggemannen Mar 11 '23 at 17:07