Подсказки блокировки

Подсказки блокировки – это ключевые слова T-SQL, которые используются с операторами SELECT, INSERT, UPDATE и DELETE, чтобы определить для SQL Server использование предпочтительного типа блокировки на уровне таблицы для определенного оператора.

Можно использовать подсказки блокировки для переопределения принятого по умолчанию уровня изолированности.

Устно. Полезность использование подсказок блокировки может быть, например, в следующем случае:

Предположим, что для всех транзакций используется принятый по умолчанию уровень изолированности read uncommitted (чтение незафиксированных данных). При этом уровне изолированности по данному ресурсу захватывается разделяемая блокировка (S-locks) только до завершения чтения, и затем она освобождается. Если какая-либо транзакция читает одни и те же данные дважды, то результаты чтения могут не совпасть, поскольку другая транзакция могла захватить блокировку между первым и вторым чтениями и модифицировать эти данные.

Чтобы избежать проблемы повторяемости чтения, можно задать уровень изолированности serializable (упорядочиваемость), но это вынудит SQL Server захватить все разделяемые блокировки (S-locks), необходимые для операторов SELECT во всех транзакциях, пока не будет завершена каждая транзакция. Иными словами, для целостности транзакций разделяемые блокировки (S-locks) будут захвачены по таблице, указанной в операторе SELECT любой транзакции.

Если нет необходимости поддерживать упорядочиваемость для всех транзакций, то можно добавить какую-либо подсказку блокировки для определенного запроса.

Описание существующих подсказок блокировки на уровне таблиц.

1. HOLDLOCK. Захватывает разделяемую блокировку до завершения транзакции, а не освобождает ее сразу после того, как уже не требуется соответствующая таблица, страница или строка данных. Эквивалентно использованию подсказки блокировки SERIALIZABLE.

2. NOLOCK. Применяется только к оператору SELECT. Не получает разделяемых блокировок и не поддерживает монопольных блокировок; читает данные, которые монопольно захвачены другой транзакцией. Эта подсказка позволяет читать нефиксированные данные (dirty read).

3. PAGLOCK. Используется для блокировки на уровне страницы там, где обычно используется блокировка на уровне таблицы.

4. READCOMMITTED. Выполняется считывание с тем же поведением по блокировкам, как у транзакций, использующих уровень изолированности read committed (принятый по умолчанию уровень изолированности для SQL Server).

5. READPAST. Применяется только к оператору SELECT и только к строкам, блокированным с помощью блокировки на уровне строк. Пропускаются строки, блокированные другими транзакциями, которые обычно включаются в набор результатов; возвращает результаты без этих блокированных строк. Может использоваться только с транзакциями, выполняемыми на уровне изолированности read committed.

6. READUNCOMMITTED. Эквивалентно NOLOCK.

7. REPEATABLEREAD. Выполняется считывание с тем же поведением по блокировкам, как у транзакций, использующих уровень изолированности repeatable read.

8. ROWLOCK. Используются блокировки на уровне строк вместо блокировки на уровне страниц или на уровне таблиц.

9. SERIALIZABLE. Выполняется считывание с тем же поведением по блокировкам, как у транзакций, использующих уровень изолированности serializable. Эквивалентно HOLDLOCK.

10. TABLOCK. Используется блокировка на уровне таблиц вместо блокировки на уровне страниц или на уровне строк. SQL Server захватывает эту блокировку до завершения оператора.

11. TABLOCKX. Используется монопольная блокировка по таблице. Внимание! Эта подсказка препятствует доступу других транзакций к этой таблице.

12. UPDLOCK. Используются блокировки изменения вместо монопольных блокировок при чтении таблицы. Эта подсказка позволяет другим пользователям только читать данные и позволяет текущей транзакции изменять эти данные; тем самым никакой другой пользователь не сможет модифицировать эти данные после того, как текущая транзакция в последний раз прочитали их.

Например. Подсказка блокировки HOLDLOCK в операторе SELECT указывает SQL Server захват всех разделяемых блокировок (S-locks) по таблице, заданной в операторе транзакции SELECT, вплоть до конца этой транзакции – независимо от уровня изолированности. Тем самым при повторном чтении будет наблюдаться согласованность данных (они не будут изменены другой транзакцией). Использование подсказок блокировки не влияет на уровень изолированности для других транзакций.

Можно объединять совместимые подсказки блокировки, такие как TABLOCK и REPEATABLEREAD, но нельзя объединять конфликтующие подсказки, такие как REPEATABLEREAD и SERIALIZABLE.

Чтобы задать подсказку блокировки на уровне таблиц, необходимо заключить эту подсказку в круглые скобки после имени таблицы в операторе T-SQL.

Следующая последовательность является примером использования подсказок TABLOCKX в операторе SELECT:

USE pubs

SELECT COUNT(ord_num)

FROM sales (TABLOCKX)

WHERE ord_date > ‘Sep 13 1994’

GO

Подсказка TABLOCK сообщает SQL Server, что нужно захватить монопольную блокировку на уровне таблице sales, пока не будет выполнен данный оператор. Эта подсказка обеспечивает, что никакая другая транзакция не сможет модифицировать данные в таблице sales, пока данный запрос подсчитывает заказы из этой таблицы.

Замечание. Оптимизатор запросов SQL Server автоматически определяет наиболее эффективный план исполнения и типы блокировок для запроса. И поскольку оптимизатор запросов автоматически выбирает подходящие тип или типы блокировок, подсказки блокировки следует использовать, если только они хорошо изучены и когда это абсолютно необходимо, иначе они могут отрицательным образом повлиять на параллельно выполняемые задачи.