5

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.

Alan
  • 1,378
  • 2
  • 19
  • 24
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69

2 Answers2

3

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.

Sergey Menshov
  • 3,856
  • 2
  • 8
  • 19
3

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

michal.jakubeczy
  • 8,221
  • 1
  • 59
  • 63
  • 1
    Which would also make that query read any other uncommitted data in the application, so be careful. – Magnus Dec 15 '17 at 09:43
  • 1
    @Magnus true, dirty reads might be dangerous so careful with them. If there's a chance you can try to avoid them. – michal.jakubeczy Dec 15 '17 at 10:05
  • 2
    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 – sepupic Dec 15 '17 at 10:19