views:

93

answers:

2

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

+5  A: 

I believe it's because you don't have a primary key, which I think is resulting in the locks being escalated, hence blocking out the SELECT. If you add a PRIMARY KEY onto the ID column, you will notice that if you try again, the SELECT will return the other 3 rows now - no WITH (NOLOCK) hint needed.

AdaTheDev
+1  A: 

Repeating tests after

--3)
create index IX_aaa_ID on aaa(id)

SELECT 2) is still blocked

--4)
drop index IX_aaa_ID on aaa
create unique index IX_aaa_ID on aaa(id)
--or adding primary key constraint   

SELECT 2) is NOT blocked

If to modify 2) as

--2b)
select * from aaa 
    where id=3 
    --or as
    --WHERE id=2 

shows that 2b) is not blocked even in absence of any index or PK.

Though, 2b), without any indexes, is blocked after modifying 1) UPDATE to run under serializable but not under REPEATABLE READ or lower

--1c)  
set transaction isolation level serializable;
--set transaction isolation level REPEATABLE READ;

begin transaction;
update aaa set Name ='bbb' 
    where id=1;
--rollback

So, it looks like multiple row selection attempts to acquire non-shareable lock?

Update:
Well, in all cases of SELECT being blocked it is waiting to acquire LCK_M_IS
Good reason to uderstand this cuisine

Update2:
Well, it is not UPDATE lock that is escalated on the table, it is SELECT (shared) locks (when SELECT tries to read multiple rows) are escalated to a table lock and cannot be granted because table has already exclusive (UPDATE) lock.

And presence or absence of index was unrelated to my primary question

I shift the discussion of this topic to my submitted suggestion "Intent rowlocks should not be escalated to a table lock if a table already contains exclusive lock"

vgv8