Подсказка оптимизатору READPAST

Подсказка оптимизатору READPASTАвтор: Alexey Knyazev

Сегодня я хочу поговорить о табличной подсказке 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


Теперь попробуем обновить записи таблицы от 0 до 10 в транзакции:

Не закрывая транзакцию, в другом окне прочитаем записи, где поле val имеет значение от 0 до 20, указав при этом табличную подсказку READPAST:


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

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


Во втором окне, как и прежде попробуем выбрать значения val between 0 and 20:

Помня о фразе из BOL: Операции чтения, в которых используется аргумент READPAST, не блокируются, мы ожидаем увидеть значения таблицы, где val <= 10…но, на наше удивление, «натыкаемся» на табличную блокировку IS.

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

Этот пример иллюстрирует, что мы всё-таки можем быть заблокированы в результате укрупнения блокировки. Для того, чтобы этого избежать, мы можем воспользоваться инструкцией (начиная с SQL Server 2008)

или отключить эскалацию на всём сервере с помощью флага трассировки 1211 (начиная с SQL Server 2005).

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

Запись опубликована в рубрике Hints с метками . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: