Transaction Isolations level in SQL Server

Question

I am trying to update table, which controlls application (application performs some select statements). I would like to update the table in transaction with isolation level set to read uncommited, so if application doesn't work as expected I can rollback transactions.

But following code doesn't work:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
go

begin transaction 
go

update [DB].[dbo].[Table] 
set ID = ID - 281 
where ID > 2

When I open another query window, I cannot query this table... I thought, that with such transaction level I would be able to query the table without rolling back/commiting transaction.

Platter

Are you not forgetting a commit or rollback at the end? – Deleterious

did u tried giving same hint in select query instead of update query – Grocery

Answer 1

Isolation level works in another way as you suppose.

You can only read uncommitted data, but others still cannot see what you done within transaction until you commit.

If you want to see uncommitted data from this transaction in your select you need to set

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

to this select

Murex

Which would also make that query read any other uncommitted data in the application, so be careful. – Deleterious

@Deleterious true, dirty reads might be dangerous so careful with them. If there's a chance you can try to avoid them. – Murex

I think Magnus wanted to say that if you want to see uncommitted data from only one table you should not change the isolation level of the whole transaction, you can just use with(nolock) hint with the table of interest – Hew

Answer 2

You need to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED from a session which reads data.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT *
FROM [DB].[dbo].[Table]

This query will execute immediately without lock. And you'll see the dirty data.

Bivalent

Thanks for an answer. It solves the problem, but I cannot accept two answers :( But upvoted. – Nucleolated