With nolock sql что это

SQL-Ex blog

Основы использования хинта NOLOCK в SQL Server

Добавил smois on Суббота, 16 марта. 2019

Основная идея механизма блокировок в SQL Server состоит в контроле согласованности транзакций. Согласно этому принципу, если процессу требуется выполнить операции вставки, удаления или обновления, ядро SQL Server блокирует строку или строки и не позволяет другим процессам получить доступ к данным до завершения транзакции. При определенных обстоятельствах этот механизм блокировок может привести к падению производительности, например, при множестве конкурирующих процессов. В результате вы можете столкнуться с проблемой тупиковой ситуации вашей базы данных (это такая ситуация, когда две транзакции требуют доступа к одним и тем же данным в одно и то же время). В этой статье мы уделим внимание тому, как избежать проблем блокировки с помощью хинта NOLOCK. Сначала давайте познакомимся с основными положениями и деталями методологии «грязного чтения», поскольку хинт NOLOCK может приводить к грязному чтению.

Грязное чтение: В этой методологии процесс считывает незафиксированные данные и не обращает внимания на открытые транзакции, поэтому блокировки не вызывают никаких проблем в процессе чтения. Таким образом, этот тип чтения снижает уровень блокировок. Однако грязное чтение имеет как положительные, так и отрицательные стороны, поскольку грязное чтение может вызывать проблемы несогласованности данных в результирующем наборе оператора SELECT. Как отмечалось ранее, этот результирующий набор может включать следы незафиксированных транзакций, и мы должны принимать это в расчет, решая использовать этот вид чтения. Мы не можем быть уверены в реальности строк, которые мы получаем при грязном чтении, поскольку для этих строк может быть выполнен откат. С другой стороны, этот тип чтения позволяет избежать проблем с блокировками и увеличить производительность SQL Server.

NOLOCK: По умолчанию SQL Server использует уровень изоляции Read Committed (чтение зафиксированных транзакций), и этот уровень изоляции не позволяет читать объекты, которые заблокированы незавершенными транзакциями. Кроме того, эти заблокированные объекты могут изменяться в соответствии с эскалацией блокировки.

Представьте себе, что имеется два пользователя базы данных, и эти пользователи хотят выполнить операции update и select. Первый пользователь начинает обновление некоторой строки таблицы, а затем другой пользователь читает ту же строку. Действия этих пользователей иллюстрирует следующий рисунок.

В этом случае User2 ждет, по меньшей мере, 10 секунд, а затем транзакция откатывается пользователем user1, после чего пользователь user2 может прочитать строку, отмеченную зеленым, поскольку блокировка строки снимается пользователем user1. Это поведение по умолчанию уровня изоляции Read Committed в SQL Server.

Теперь продемонстрируем этот случай в SQL Server. Сначала создадим таблицу FruitSales и добавим в неё несколько строк.

CREATE TABLE FruitSales 
(Id INT IDENTITY (1,1) PRIMARY KEY, [Name] Varchar(20) , 
SalesTotal Float) 
GO 
 
INSERT INTO FruitSales VALUES 
('Apple',10) ,('Orange',8), ('Banana',2) 

На первом шаге мы открываем два окна (вкладки) в SQL Server Management Studio и выполняем сначала запрос пользователя user1, а затем — запрос пользователя user2.

---USER1---- 
 
BEGIN TRAN 
UPDATE FruitSales SET SalesTotal =20 WHERE 
WAITFOR DELAY '00:00:10' 
ROLLBACK TRANSACTION 
 
---USER2---- 
SET STATISTICS TIME ON 
SELECT * FROM FruitSales WHERE />

Как вы можете увидеть, второй запрос ожидает до тех, пока пользователь user1 не выполнит откат транзакции.

Теперь мы обсудим детали использования хинта NOLOCK. Хинт NOLOCK — один из наиболее популярных табличных хинтов, который используется разработчиками баз данных и администраторами для решения проблем блокировок в базах данных SQL Server. С помощью табличного хинта NOLOCK мы можем читать заблокированные объекты (строку, страницу или таблицу), которые заблокированы открытыми транзакциями. Хинт NOLOCK отменяет значение по умолчанию оптимизатора запросов SQL Server таким образом, что оператор SELECT может читать заблокированные объекты.

Теперь мы добавим хинт NOLOCK в оператор SELECT пользователя user2, а затем выполним UPDATE пользователя user1 с последующим оператором SELECT пользователя user2.

---USER1---- 
 
BEGIN TRAN 
UPDATE FruitSales SET SalesTotal =20 WHERE 
WAITFOR DELAY '00:00:10' 
ROLLBACK TRANSACTION 
 
---USER2---- 
SET STATISTICS TIME ON 
SELECT * FROM FruitSales WITH(NOLOCK) WHERE />

Итак, User1 выполняет оператор обновления в явной транзакции, а затем пользователь user2 выполняет оператор выборки, и результирующий набор возвращается без задержки на время выполнения транзакции. Это главное назначение NOLOCK — читать заблокированные объекты.

Теперь посмотрим на результат выполнения оператора SELECT. Оператор SELECT пользователя user2 возвращает значение 20 столбца SalesTotal, хотя реальное значение осталось равным 8. Запомните, что если вы используете табличный хинт NOLOCK в запросе на выборку, то можете столкнуться с подобным типом несоответствия результатов.

Совет. Ключевое слово «WITH» является устаревшим, поэтому Майкрософт рекомендует не использовать его в новых проектах баз данных и удалить из текущих разработок. Вы можете использовать хинт NOLOCK без слова «WITH».

---USER1---- 
BEGIN TRAN 
UPDATE FruitSales SET SalesTotal =20 WHERE 
WAITFOR DELAY '00:00:10' 
ROLLBACK TRANSACTION 
SELECT * FROM FruitSales WHERE /> 
--USER2--- 
SELECT * FROM FruitSales (NOLOCK) WHERE />

Кроме этого, табличный хинт READUNCOMMITTED эквивалентен хинту NOLOCK, и мы можем использовать его вместо NOLOCK.

SELECT * FROM FruitSales (READUNCOMMITTED) WHERE /> Несмотря на это, существует случай, когда хинт NOLOCK не в состоянии преодолеть барьер блокировки. Если некоторый процесс изменяет структуру таблицы, NOLOCK не может изменить тип блокировки и не позволит продолжить операцию чтения. Причина заключается в том, что хинт NOLOCK ориентирован на блокировки Sch-S (стабильность схемы), а оператор ALTER TABLE накладывает Sch-M блокировку (модификация схемы), так что имеет место конфликт.

Сначала мы определим Object_id (идентификатор объекта) таблицы FruitSales с помощью следующего запроса.

select OBJECT_ID('FruitSales')

Запустите следующий запрос user1, а затем запрос user2. В результате запрос user2 будет ожидать завершения процесса изменения таблицы пользователем user1.

--USER1--- 
BEGIN TRAN 
ALTER TABLE FruitSales 
ADD ColorofFruit varchar(200) 
WAITFOR DELAY '00:00:35 
GO 
COMMIT TRAN 
 
--USER2--- 
SELECT * FROM FruitSales (NOLOCK) WHERE />

Откройте новое окно запроса и выполните следующий код. Этот запрос поможет выяснить тип блокировки запросов user1 и user2.

SELECT Resource_type, 
Resource_database_id, 
Resource_description, 
Resource_associated_entity_id, 
Resource_lock_partition, 
Request_mode, 
Request_type, 
Request_status, 
Request_session_id, 
Request_request_id, 
Request_owner_type, 
Request_owner_id, 
Lock_owner_address 
FROM sys.dm_tran_locks 
where resource_associated_entity_id =647673355 

Теперь мы сверимся с матрицей совместимости блокировок для SCH-M и SCH-S. Матрица указывает на конфликт между SCH-M и SCH-S.

Заключение

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

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Аноним on Четверг, 7 октября. 2021 :

В статье допущена небольшая смысловая ошибка.

«Совет. Ключевое слово «WITH» является устаревшим, поэтому Майкрософт рекомендует не использовать его в новых проектах баз данных и удалить из. «

В справке Microsoft указано следующее:
Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server.

Автор не разрешил комментировать эту запись

WITH (NOLOCK) vs READ UNCOMMITTED: советы по оптимизации SQL

WITH (NOLOCK) получает основные различия при работе с конкретными таблицами. Это ваш выход, если важна скорость выполнения и нет строгой необходимости в точности данных. Ваш запрос будет выглядеть примерно так:

SELECT * FROM YourTable WITH (NOLOCK)

Когда же необходимо избежать блокировок на уровне всей сессии, используйте SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM YourTable

Соответственно, для операций непосредственно с таблицами выбирайте WITH (NOLOCK), а для работы на уровне сессии – SET. READ UNCOMMITTED. Однако, учтите, оба варианта могут при неосторожном использовании привести к нарушению целостности данных.

Выбор подхода: производительность и соблюдение целостности данных

Контроль над чтением данных

В отличие от WITH (NOLOCK), который применяется напрямую к таблице, READ UNCOMMITTED действует на всю сессию чтения данных.

Целостность данных как ключевой аспект

Использование NOLOCK позволяет увидеть изменения данных практически в реальном времени. Однако, это можно сравнить с чтением неокончательных черновиков. В результате текучего состояния данных, их актуальность может быстро меняться, приводя к несоответствию реальности.

Баланс между производительностью и точностью

Выбор между получением точных данных и скоростью их обработки напоминает ходьбу по канату. Используя NOLOCK и READ UNCOMMITTED, можно избежать блокировок и повысить скорость выполнения запросов, данный подход подойдет в задачах, требующих оперативного решения.

Альтернативные подходы

Использование уровней изоляции Snapshot и Serializable

Если актуальность и точность данных для вас на первом месте, подойдут SNAPSHOT или SERIALIZABLE. Эти уровни изоляции обеспечат стабильный набор данных, минуя «грязные» чтения. Но стоит учесть, что высокая точность может потребовать больше ресурсов для выполнения транзакций.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT SELECT * FROM YourTable

MVCC как гарант стабильности чтения

Multiversion Concurrency Control (MVCC) в SQL Server позволяет осуществлять чтение данных без малейшего беспокойства об их изменении. Именно MVCC обеспечивает вам мгновенный снимок ситуации и безопасное чтение, независимо от параллельных процессов изменения данных.

Визуализация

Представим основные подходы к чтению в библиотеке:

Различия между NOLOCK и READUNCOMMITTED в SQL Server

В SQL Server подсказки NOLOCK и READ UNCOMMITTED выполняют аналогичные функции. С их помощью запрос может игнорировать стандартные блокировки и работать с неподтвержденными данными. Это может ускорить выполнение запроса, однако увеличивает вероятность использования некорректных данных из-за возможности «грязного чтения».

Скопировать код

-- Быстрое чтение: какие правила здесь работают? SELECT * FROM Employees WITH (NOLOCK)

Вышеуказанный синтаксис позволяет извлекать из таблицы Employees данные, утверждение которых еще не подтверждено.

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

Влияние блокировок в действии

Подсказки NOLOCK и READ UNCOMMITTED изменяют стратегию блокировок в SQL Server:

• NOLOCK применяется непосредственно к таблице и действует только в рамках одного запроса.
• READ UNCOMMITTED, используемый как уровень изоляции, влияет на все запросы в рамках соответствующей транзакции.

Использование этих подсказок может вызвать следующие артефакты:

• Неповторяемое чтение: выполнение одного и того же запроса несколько раз может вернуть разные результаты в рамках одной транзакции.
• Фантомные строки: строки, которые могут исчезнуть после завершения транзакции.
• Потеря обновлений: риск перезаписи данных из-за конфликтующих транзакций, которые впоследствии могут быть отменены.

Альтернативы и лучшие практики

NOLOCK и READ UNCOMMITTED создают иллюзию улучшения производительности запросов, но не забывайте о рисках для целостности данных. Их использование можно сравнить с быстрым судном, у которого в днище пробоина: быстро, но опасно.

Прежде чем применять их, проанализируйте ситуацию:

• Критичность транзакции: используйте эти подсказки только тогда, когда это приемлемо, например, для фоновых процессов или выгрузки отчетов.
• Альтернативы: рассмотрите другие варианты блокировки, такие как уровень изоляции SNAPSHOT , который позволяет избежать грязного чтения.
• Уровень изоляции: ориентируйтесь на строгие уровни изоляции, такие как REPEATABLE READ или SERIALIZABLE , обеспечивающие наиболее высокую надежность данных.

Визуализация

NOLOCK и READ UNCOMMITTED можно представить как два туннеля:

WITH NOLOCK зло?

Там где я работаю в хранимых процедурах в sql довольно активно используется конструкция WITH(NOLOCK) . Она используется для данных, которые могут очень часто запрашиваться и как мне сказали ее использование оправдано из-за соображений производительности. Насколько я знаю WITH(NOLOCK) — это аналог уровня изоляции READ UNCOMMITTED и означает отсутствие блокировок таблицы при выполнении запросов. Однако на Хабре в статье «7 вещей, которые разработчик должен знать о SQL Server» пишут, что во-первых WITH(NOLOCK) не гарантирует отсутствие блокировок, а во-вторых цитирую:

по поводу WITH NOLOCK. Поразило не то что будут блокировки, а то, что кто-то этой анафемой не только всерьез пользуется, но еще и рассчитывает, что при этом не будет блокировок

Вопрос: является ли злом использование WITH(NOLOCK) ? Является ли это злом абсолютным или же нет? Когда его использование может быть оправдано?

Отслеживать
32.1k 19 19 золотых знаков 80 80 серебряных знаков 106 106 бронзовых знаков
задан 16 апр 2015 в 15:08
JuniorThree JuniorThree
841 3 3 золотых знака 9 9 серебряных знаков 17 17 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

WITH (NOLOCK) не является абсолютным злом. Это просто хинт. Зло — это использование его не по назначению. Надо просто точно представлять себе все последствия его применения — чтения строк, которые никогда не станут строками, фантомные чтения, возможные дубликаты одного и того же значения при сканах — чтобы понять, что в живом приложении его использовать не стоит.

У NOLOCK / READ UNCOMMITTED есть одно явное предназначение — чтение невкомитанных данных. Мне, например, приходится им регулярно пользоваться при дебаге (в виде SET TRANSACTION ISOLATION LEVEL ), когда код остановлен в середине транзакции, и надо выяснить что с точки зрения кода сейчас происходит в базе.

Для всех остальных случаев, в которых раньше использовали NOLOCK / READ UNCOMMITTED , сейчас намного лучше подходит READ_COMMITTED_SNAPSHOT .

P.S. А вот SNAPSHOT — это действительно зло!