Прочитайте уровень обязательной изоляции на сервере SQL для одного оператора

Question

Скажем, у меня есть таблица персонажа, и она имеет только 1 строку -

id = 1, name = 'foo'

По одному соединению

select p1.id, p1.name, p2.name
from person p1 
join person p2 on p1.id = p2.id

На другом соединении одновременно:

update person set name = 'bar' where person.id = 1

Q1: возможно ли когда-либо, чтобы мой выбор возвращал результат, подобный этому, основанный на времени утверждения обновления:

id = 1, p1.name = 'foo', p2.name = 'bar'

Ни одно соединение не использует явную транзакцию, и оба используют уровень изоляции транзакции по умолчанию READ COMMITTED.

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

Q2: изменится ли ответ на вопрос, если set read_committed_snapshot on установлен на дб?

Answer 1

Q1: Да, это вполне возможно, по крайней мере, в теории. read committed просто гарантирует, что вы не читаете грязные данные, не дает никаких обещаний по поводу согласованности. На уровне фиксации чтения общие блокировки снимаются, как только считываются данные (не в конце транзакции или даже в конце оператора)

Q2: Да, ответ на этот вопрос изменится, если read_committed_snapshot включен Тогда вам будет гарантирована согласованность на уровне заявления. Мне трудно найти онлайн-источник, который однозначно заявляет об этом, но цитирую стр.648 "Microsoft SQL Server 2008 Internals"

Оператор в RCSI видит все, что было совершено до его начала. Каждый новый оператор в транзакции собирает самые последние зафиксированные изменения.

Также см. Этот пост в блоге MSDN

Скрипт установки

CREATE TABLE person 
(
id int primary key,
name varchar(50)
)

INSERT INTO person
values(1, 'foo');

Соединение 1

while 1=1
update person SET name = CASE WHEN name='foo' then 'bar' ELSE 'foo' END

Соединение 2

DECLARE @Results TABLE (
  id    int primary key,
  name1 varchar(50),
  name2 varchar(50))

while( NOT EXISTS(SELECT *
                  FROM   @Results) )
  BEGIN
      INSERT INTO @Results
      Select p1.id,
             p1.name,
             p2.name
      from   person p1
             INNER HASH join person p2
               on p1.id = p2.id
      WHERE  p1.name <> p2.name
  END

SELECT *
FROM   @Results  

Результаты

id          name1 name2
----------- ----- -----
1           bar   foo

Глядя на другие типы соединений в Profiler, выясняется, что эта проблема не может возникнуть ни при merge присоединиться или nested loops планировать этот конкретный запрос (блокировки не снимаются до тех пор, пока не будут получены все), но суть остается read committed просто гарантирует, что вы не читаете грязные данные, не дает никаких обещаний по поводу согласованности. На практике вы можете не получить эту проблему для точного отправленного вами запроса, поскольку SQL Server не будет выбирать этот тип соединения по умолчанию. Однако тогда вы просто полагаетесь на детали реализации, чтобы получить желаемое поведение.

NB: Если вам интересно, почему какой-то уровень строки S блокировки, по-видимому, отсутствуют, это объяснение оптимизации здесь.