READPAST блокировки уровня строк будут пропускаться

Tsql теория > READPAST блокировки уровня строк будут пропускаться
19.12.2018 20:58:42



Статья:

Сегодня я хочу поговорить о табличной подсказке READPAST, которая появилась впервые в SQL Server 2005. Эта подсказка указывает, что компонент Database Engine не считывает строки и страницы, заблокированные другими транзакциями. Если указан аргумент READPAST, то блокировки уровня строк будут пропускаться. Компонент Database Engine будет пропускать строки вместо блокировки текущей транзакции до тех пор, пока блокировки не будут сняты. 

Например, предположим, что в таблице T1 есть один целочисленный столбец со значениями 1, 2, 3, 4, 5. Если транзакция A изменит значение 3 на 8, но еще не будет зафиксирована, то инструкция SELECT * FROM T1 (READPAST) возвратит значения 1, 2, 4, 5. Параметр READPAST главным образом используется для устранения конфликта блокировок при реализации рабочей очереди, использующей таблицу SQL Server. Средство чтения очереди, использующее аргумент READPAST, пропускает прошлые записи очереди, заблокированные другими транзакциями, до следующей доступной записи очереди, не дожидаясь, пока другие транзакции снимут свои блокировки. 

Аргумент READPAST можно задать для любой таблицы, к которой обращается инструкция UPDATE или DELETE, и к любой таблице, на которую ссылается предложение FROM. Если аргумент READPAST задан в инструкции UPDATE, он применяется только при считывании данных для идентификации подлежащих обновлению записей вне зависимости от того, где он указан в инструкции. Аргумент READPAST для таблиц из предложения INTO инструкции INSERT задать нельзя. Операции чтения, в которых используется аргумент READPAST, не блокируются. Операции обновления или удаления, использующие аргумент READPAST, могут блокироваться либо при считывании внешних ключей или индексированных представлений, либо при изменении вторичных индексов. 

Аргумент READPAST можно указывать только в транзакциях, выполняемых на уровнях изоляции READ COMMITTED или REPEATABLE READ. При указании подсказки READPAST в транзакциях, выполняемых с уровнем изоляции SNAPSHOT, она должна использоваться в сочетании с другими табличными подсказками, требующими блокировки, например UPDLOCK или HOLDLOCK. 

Табличная подсказка READPAST не может быть указана, если параметр базы данных READ_COMMITTED_SNAPSHOT установлен в ON и выполняется одно из следующих условий.

  • Уровень изоляции транзакций сеанса имеет значение READ COMMITTED.
  • В запросе также указана табличная подсказка READCOMMITTED.
Чтобы в этих случаях указать подсказку READPAST, удалите табличную подсказку READCOMMITTED (если существует) и включите в запрос табличную подсказку READCOMMITTEDLOCK. 


Всё, что написано выше является выдержкой из BOL, при этом хотелось бы особо подчеркнуть фразу, которую я выделил жирным: 
Операции чтения, в которых используется аргумент READPAST, не блокируются.
Но так ли всё на самом деле? Или возможны исключения? Об этом чуть ниже. 

Для демонстрации создадим тестовую таблицу с одним полем и значениями от 0 до 99999
--Создадим таблицу с одним полем "val"
if object_id ( N''dbo.test_table01'', N''U'' ) is not null
drop table dbo.test_table01;
go

create table dbo.test_table01 ( val int primary key clustered );
go

--Наполним таблицу 10 000 записей
with cte
as
( 
select 0 as i
union all
select i + 1 from cte where i < 99
)
insert into dbo.test_table01 ( val )
select t1.i * 100 + t2.i 
  from cte t1, cte t2
  order by t1.i * 100 + t2.i;
Теперь попробуем обновить записи таблицы от 0 до 10 в транзакции:
begin tran
select @@trancount;

update t
  set val = val
  from dbo.test_table01 t
  where val between 0 and 10;
Не закрывая транзакцию, в другом окне прочитаем записи, где поле val имеет значение от 0 до 20, указав при этом табличную подсказку READPAST:
select * from dbo.test_table01 with (readpast)
  where val between 0 and 20;

 

Всё, как описано в документации, мы прочитали только незаблокированные записи. А теперь изменим запрос в первом окне и обновим записи от 0 до 20, так же в транзакции, и попробуем прочитать эти записи с подсказкой READPAST в другом окне. Мы увидим пустой рекордсет, как и ожидалось. 

Самое интересное: обновим все строки таблицы, где значение поля val > 10
begin tran
select @@trancount;

update t
  set val = val
  from dbo.test_table01 t
  where val > 10;
Во втором окне, как и прежде попробуем выбрать значения val between 0 and 20:
select * from dbo.test_table01 with (readpast)
  where val between 0 and 20;
Помня о фразе из BOL: Операции чтения, в которых используется аргумент READPAST, не блокируются, мы ожидаем увидеть значения таблицы, где val <= 10...но, на наше удивление, "натыкаемся" на табличную блокировку IS. 

Причиной такого поведения стала эскалация блокировки в первом окне до табличной монопольной (X), а мы помним, что блокировки IS и Х - несовместимы. 

Этот пример иллюстрирует, что мы всё-таки можем быть заблокированы в результате укрупнения блокировки. Для того, чтобы этого избежать, мы можем воспользоваться инструкцией (начиная с SQL Server 2008)
alter table dbo.test_table01 set ( lock_escalation = disable );
или отключить эскалацию на всём сервере с помощью флага трассировки 1211 (начиная с SQL Server 2005).

После этого наш второй запрос сможет вернуть записи от 0 до 10, как мы и хотели.