SET TRANSACTION isolation level READ uncommitted - is it necessary to explicitly use transaction begin and end?

Question

Does this query below work or do I have use explicitly transaction being and end?

Yes I know the dangers of the read uncommitted

SET TRANSACTION isolation level READ uncommitted 

SELECT TOP 100 tblguilds.guild_id, 
               tblguilds.guildname, 
               tblguilds.leaderuserid, 
               tblusersprofile.username 
FROM   tblguilds 
       LEFT JOIN tblusersprofile 
              ON tblusersprofile.userid = tblguilds.leaderuserid 
WHERE  tblguilds.guild_id NOT IN (SELECT guildcode 
                                  FROM   tblguildapplied 
                                  WHERE  userid = 1) 
ORDER  BY Newid()

My understanding is that transaction begin/end around selects don't do anything. Selects along with updates/inserts does do something but selects _only_ inside begin/end is meaningless. Perhaps someone could confirm that. — Nick.Mc, Jul 27 '16 at 01:25

score 2 · Answer 1 · edited May 23 '17 at 12:14

2

Yes that will work but be aware that it will be in effect for the entire session. Meaning any SQL that you execute after that select will be using that isolation level. If you want to restrict that to certain tables in the select stmt then consider using the NOLOCK hint

See more over here : WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The Begin transaction and commit ( there is no END transaction) applies only to insert/updates/deletes

On a side note I would modify that query to use a left outer join instead of a NOT IN to improve performance.

edited May 23 '17 at 12:14

Community

1
1

answered Jul 27 '16 at 00:32

objectNotFound

1,683
2
18
25

so we can say that each connection = each session right? – Furkan Gözükara Jul 28 '16 at 16:14
@MonsterMMORPG yes but to be technically accurate everything that runs in the same SPID under which that "set isolation level .... " command was executed and it will stay in effect till that SPID (connection) is closed or the isolation level is explicitly reset/changed to something else. – objectNotFound Jul 28 '16 at 16:18

SET TRANSACTION isolation level READ uncommitted - is it necessary to explicitly use transaction begin and end?

1 Answers1