SQL-Ex blog

Грязное чтение: В этой методологии процесс считывает незафиксированные данные и не обращает внимания на открытые транзакции, поэтому блокировки не вызывают никаких проблем в процессе чтения. Таким образом, этот тип чтения снижает уровень блокировок. Однако грязное чтение имеет как положительные, так и отрицательные стороны, поскольку грязное чтение может вызывать проблемы несогласованности данных в результирующем наборе оператора 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 Id=2 

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION



	---USER2----

SET STATISTICS TIME ON

SELECT * FROM FruitSales WHERE Id=2

Как вы можете увидеть, второй запрос ожидает до тех, пока пользователь 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 Id=2 

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION



			---USER2----

SET STATISTICS TIME ON

SELECT * FROM FruitSales WITH(NOLOCK) WHERE Id=2

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

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

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

---USER1----

BEGIN TRAN 

UPDATE FruitSales SET SalesTotal =20 WHERE Id=2 

WAITFOR DELAY '00:00:10'

ROLLBACK TRANSACTION

SELECT * FROM FruitSales WHERE Id=2



   --USER2---

SELECT * FROM FruitSales (NOLOCK) WHERE Id=2

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

SELECT * FROM FruitSales (READUNCOMMITTED) WHERE Id=2

Несмотря на это, существует случай, когда хинт 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 Id=2

Откройте новое окно запроса и выполните следующий код. Этот запрос поможет выяснить тип блокировки запросов 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.