Почему UPDATE блокирует SELECT для несвязанных строк?

Имея таблицу, определенную скриптом [1], я выполняю скрипты в 2-х окнах 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

и после 1)

--2)after launching 1)
select * from aaa --deleted comments
where id<>1
--is blocked

Независимо от уровня изоляции транзакции в 1) окне, SELECT в 2) заблокирован.
Почему?

Влияет ли уровень изоляции для UPDATE на операторы других транзакций?

Наивысший уровень изоляции по умолчанию READ COMMITTED в 2).
Блокировки диапазона не приписываются, SELECT должен был пострадать от проблем COMMITTED READS (НЕПОВТОРИМЫЕ ЧТЕНИЯ) и PHANTOM READS (повторяющиеся чтения) [2]
Как заставить его страдать?

Как можно выполнить ОБНОВЛЕНИЕ, не блокируя ВЫБОР?

[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]
Скопируйте и вставьте или добавьте завершение) при нажатии
http://en.wikipedia.org/wiki/Isolation_(database_systems).

Обновление:
SELECT WITH (NOLOCK) не заблокирован ...

Update2:
или с, что то же самое, ПРОЧИТАЙТЕ НЕОБХОДИМО

Обратите внимание, что UPDATE находится в строке, отличной от строки SELECT.
Даже если в одной и той же строке, такое поведение противоречит описанию уровней изоляции [2]

Дело в том, что:

  • предположим, я не могу знать, кто еще будет ВЫБРАТЬ из той же таблицы (UPDATE-d), но не связан с обновлением строк
  • чтобы понять уровни изоляции [2]

SQL Server 2008 R2 Dev


sql-server tsql transactions nonblocking database-locking
person Gennady Vanin Геннадий Вани&    schedule 24.10.2010    source источник
comment
Вы хотели - с подсказкой?   -  person    schedule 25.10.2010
comment
Что делать, если вы воспользуетесь подсказкой об обновлении уключины? (и если предположить, что это не будет эскалацией ...)   -  person    schedule 25.10.2010

Ответы (2)


arrow_upward
6
arrow_downward

Я считаю, что это потому, что у вас нет первичного ключа, что, как я думаю, приводит к усилению блокировок и, следовательно, блокировке SELECT. Если вы добавите ПЕРВИЧНЫЙ КЛЮЧ в столбец ID, вы заметите, что если вы попытаетесь снова, SELECT вернет остальные 3 строки сейчас - подсказка WITH (NOLOCK) не требуется.

person AdaTheDev    schedule 24.10.2010

arrow_upward
2
arrow_downward

Повторные тесты после

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

SELECT 2) все еще заблокирован

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

ВЫБОР 2) НЕ заблокирован

Если изменить 2) как

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

показывает, что 2b) не блокируется даже при отсутствии индекса или PK.

Тем не менее, 2b) без каких-либо индексов блокируется после изменения 1) UPDATE для запуска под сериализуемым, но не под REPEATABLE READ или ниже

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

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

Итак, похоже, несколько попыток выбора строки для получения блокировки без совместного использования?

Обновление:
Что ж, во всех случаях блокировки SELECT он ожидает приобретения LCK_M_IS
Хорошая причина, чтобы понять эту кухню

Обновление2:
Ну, это не блокировка UPDATE, которая нарастает в таблице, это блокировки SELECT (общие) (когда SELECT пытается прочитать несколько строк) повышаются до блокировки таблицы и не могут быть предоставлены, потому что таблица уже имеет эксклюзивную (ОБНОВЛЕНИЕ) блокировку.

И наличие или отсутствие индекса не имело отношения к моему основному вопросу.

Я переношу обсуждение этой темы на свое представленное предложение " Намеренные блокировки строк не должны преобразовываться в блокировку таблицы, если таблица уже содержит эксклюзивную блокировку "

person Gennady Vanin Геннадий Вани&    schedule 25.10.2010