Having the table, defined by script [1], I execute scripts in 2 windows of SSMS
--1) first in first SSMS window
set transaction isolation level READ UNCOMMITTED;
begin transaction;
update aaa set Name ='bbb'
where id=1;
-- results in "(1 row(s) affected)"
--rollback
and after 1)
--2)after launching 1)
select * from aaa --deleted comments
where id<>1
--is blocked
Independently on transaction isolation level in 1) window, the SELECT in 2) is blocked.
Why?
Does isolation level for UPDATE have any influence on statements on other transactions?
The highest isolation level is default READ COMMITTED in 2).
No range locks are attributed, SELECT should have suffered from COMMITTED READS (NONREPEATABLE READs) and PHANTOM READS (Repeatable Reads) problems [2]
How to make it suffer?
How can UPDATE be made without blocking SELECT?
[1]
CREATE TABLE aaa
(
Id int IDENTITY(1,1) NOT NULL,
Name varchar(13) NOT NULL
)
insert into aaa(Name)
select '111' union all
select '222' union all
select '333' union all
select '444' union all
select '555' union all
select '666' union all
select '777' union all
select '888'
[2]
Copy&paste or add trailing ) upon clicking
http://en.wikipedia.org/wiki/Isolation_(database_systems)
Update:
SELECT WITH(NOLOCK) is not blocked...
Update2:
or with, what is the same, READ UNCOMMITTED
Note that UPDATE is on different from SELECT row.
Even, if on the same, this behavior contradicts to description of isolation levels [2]
The points are that:
- suppose I cannot know who else is going to SELECT from the same (UPDATE-d) table but on unrelated to update rows
- to understand isolation levels [2]
SQL Server 2008 R2 Dev