По материалам статьи Andres Taylor Advanced
SQL Server Locking
Перевод Виталия
Степаненко
Я думал, что знаю SQL Server достаточно хорошо. Я использую этот продукт уже больше 6 лет, и мне нравится знать об используемых мною инструментах все. Когда я преподавал на курсах программирования SQL Server, я заметил, что в материалах Microsoft представлена таблица совместимости блокировок. Та же таблица была представлена и в MSDN. Рассматривая эту таблицу, я удивился - неужели здесь нет блокировки Intent Update? Это привело меня к исследованию блокировок. Эта статья и есть результат этого исследования. Я написал эту статью для определенного читателя - для того, кто понимает уровни изоляции, блокировки намерения, мертвые блокировки и уровни блокировок. Если вы недостаточно уверенно разбираетесь в этих областях, вам нужно сначала ознакомиться с ними перед чтением этой статьи.
- Update
блокировки
Уровни блокировок
Флаги трассировки блокировок
Блокировки столбцов
Расширенная таблица совместимости блокировок
Заключение
Краткая биография
Я надеюсь, что я расширю ваше понимание блокировок в SQL
Server и, возможно, научу вас некоторым приемам, которые вы
сможете использовать во время программирования на SQL
Server.
Должен сказать, что вы можете вполне успешно
работать с SQL Server долгое время и не знать, как он
блокирует свои ресурсы, и в то же время писать
высококачественные код и схемы баз данных. Но если вы похожи
на меня и хотите знать внутреннее строение вещей или если вы
работаете с системой, которая требует хотя бы небольшого
прироста производительности, то я могу научить вас кое-чему
полезному.
Я уверен, что вы слышали о мертвых блокировках, когда
системный процесс Х блокирует ресурс А и ждет, чтобы
заблокировать ресурс B, в то время как системный процесс Y
блокирует процесс B и ждет освобождения процесса А. Если не
слышали, то поищите дополнительную информацию об этом в
MSDN.
Итак, представьте, что типов мертвых блокировок
больше. Рассмотрим следующую ситуацию: системный процесс Х
имеет shared блокировку на ресурсе А, и системный процесс Y
тоже. Это не проблема, так как две shared блокировки
совместимы друг с другом. Теперь процесс Х хочет превратить
shared блокировку в эксклюзивную блокировку, чтобы обновить
ресурс. Для этого процесс Х должен подождать, пока процесс Y
снимет shared блокировку, и пока процесс Х ждет, процесс Y
решает сделать то же самое, т.е. превратить свою блокировку в
эксклюзивную блокировку. Процесс Х ждет процесс Y, а процесс Y
ждет процесс Х. Это и есть мертвая блокировка. Такие
блокировки называются мертвые блокировки конвертации
(conversion deadlocks).
Это довольно обычная ситуация, и
чтобы ее избежать, были введены update блокировки. Update
блокировки позволяют соединению читать ресурс, но в то же
время показывают свое намерение блокировать его позже, чтобы
изменить ресурс. Проблема с update блокировками заключается в
том, что SQL Server заранее не знает, хочет ли транзакция
превратить shared блокировку в эксклюзивную блокировку, если
только это не одиночная команда SQL, как команда UPDATE,
которая должна сначала прочитать таблицу, прежде чем обновлять
ее.
Чтобы использовать update блокировки в команде SELECT,
вы должны явно определить, что вы хотите, чтобы SQL Server
использовал их, и это можно сделать при помощи хинта
блокировки UPDLOCK.
Мне нравится иллюстрировать то, о чем я говорю, примерами кода. В окне QA я запускаю следующий пакет:
Заметьте, что я открываю транзакцию, но не закрываю ее. Это
сделано для удержания блокировок.
Если другое соединение
попытается наложить другую update блокировку на ту же строку,
то эта блокировка будет отложена, пока не будет снята первая
блокировка - две update блокировки одного ресурса
несовместимы.
Результат SP_LOCK с нужными строками и столбцами показан ниже:
Как и ожидалось, ключ, который мы блокируем, OrderId,
заблокирован update блокировкой. Странное значение в столбце
Resource (89003da47679) - это хэшированное значение нашего
ключа, 10633. SQL Server использует у себя хэш таблицу как
таблицу блокировки, и это то, что мы видим здесь.
Страница,
содержащая эту строку, как и ожидалось, заблокирована с
помощью Intent Update блокировки. Значение столбца Resource
(1:242) показывает, что страница размещена в первом файле базы
данных и ее номер - 242 в этом файле. И, наконец, хотя и не
ожидалось, SQL Server накладывает IX блокировку на уровень
таблицы. SQL Server никогда не использует U/IU блокировки на
уровне таблицы, так что мы увидим только X/IX блокировки на
этом уровне.
Когда вы выполняете обновление с выражением
where, SQL Server должен прочитать таблицу и/или индексы,
чтобы определить, какие строки требуют изменения. Перед тем,
как он прочитает всю информацию из таблицы/индекса, SQL Server
блокирует объект, к которому требуется доступ. Т.к. SQL Server
знает, что вы выполняете обновление, то он выберет update
блокировку, а не shared блокировку. Это нужно для избежания
возникновения мертвой блокировки, о которой я говорил ранее -
о мертвой блокировке конвертации.
Когда SQL Server нашел
строку или строки для обновления, то он превращает update
блокировку строки в эксклюзивную блокировку на RID, если
таблица является кучей, или на KEY, если таблица имеет
кластерный индекс. Это значит, что обычно вы не видите update
блокировок, когда выполняете команду UPDATE - они почти сразу
превращаются в эксклюзивные блокировки.
Но, конечно, есть
некоторые исключения из этого правила. Если SQL использует
индекс для поиска строк, то он блокирует страницы и ключи
индекса при помощи update блокировок. Если обновление не
изменяет никаких столбцов, содержащихся в индексе, то update
блокировки не превратятся в эксклюзивные блокировки. Приведу
пример:
Region - это таблица-куча с уникальным некластерным первичным ключом по столбцу RegionId. Чтобы заполнить этот запрос, SQL Server просматривает индекс по RegionId и блокирует страницы и ключи индекса при помощи update блокировок. Наконец, он найдет строку, требующую изменения. Т.к. команда обновления не изменяет RegionId, то ей не нужно накладывать эксклюзивную блокировку на индекс. Результат из SP_LOCK показан ниже:
Мы видим IX блокировку на таблице и X блокировку на RID
обновляемой строки. KEY блокировка наложена на индекс по
RegionId. Это понятно по столбцу Indid. Как видно, у нас все
еще сохраняется update блокировка на индексе. Это один из
немногих случаев, когда вы можете непосредственно увидеть
update блокировку.
И, чтобы закончить, у нас есть две
страничные блокировки - одна на странице индекса (1:306) и
одна на таблице-куче (1:300). Откуда я это знаю? Посмотрите на
столбец Indid. Id индекса, равный 0, всегда обозначает
таблицу-кучу.
SQL Server имеет несколько типов блокировок, но он также
выбирает и разные уровни блокировок.
Если вы запускали
SP_LOCK хотя бы раз или смотрели текущее состояние Enterprise
Manager, то вы точно видели по крайней мере 4 или 5 разных
типов блокировок. Я быстро пройду по каждому из них.
Database (DB): Это блокировка сессии - т.е. она не относится ни к какой транзакции, а только к пользователю, подключенному к определенной базе данных. Это нужно, чтобы предотвратить удаление базы данных, когда к ней подключены один или более пользователей. Заметьте, что SQL Server знает, что базы данных master и tempdb не могут быть удалены, и не накладывает DB блокировку на эти базы данных.
Table (TAB): Это самая грубая логическая блокировка, которую может использовать SQL Server. Часто вы обнаруживаете intent блокировки на этом уровне. (Не помните точно, что такое intent блокировки? Читайте дальше.)
Extent (EXT): Эти блокировки не используются для блокирования логических строк, а используются, когда SQL Server создает новые таблицы или расширяет существующие, также вы можете их видеть, когда файл увеличивается в размере.
Page (PAG): Когда SQL Server требуется заблокировать одновременно множество строк, а свободные слоты блокировок заканчиваются, то он может использовать страничные блокировки. Чаще всего вы будете видеть intent блокировки на этом уровне. До SQL Server версии 6.5 включительно это была самая лучшая блокировка.
Key (KEY): Лучший уровень блокировки, возможный в SQL Server, вместе с RID блокировкой. KEY блокировки используются в индексах, а RID блокировки - в таблицах-кучах. Изучая в настоящее время поведение блокировок в SQL Server, я обнаружил, что SQL Server в большинстве случаев ценит конкурентность выше, чем скорость. Высокая конкурентность означает, что множество пользователей может работать одновременно. По возможности это достигается путем небольших блокировок, чтобы не блокировать без необходимости данные, нужные другим пользователям. С другой стороны, высокая скорость может быть достигнута при помощи больших блокировок, что быстрее, чем накладывание множества маленьких блокировок.
SQL Server 2000 может также поднимать уровень ваших
блокировок, если он заметит, что вы блокируете все больше и
больше строк. В этом случае SQL Server применяет табличную
блокировку и удаляет все индивидуальные блокировки на
страницах, ключах и RID. Заметьте - поднятие уровня блокировки
приводит к установлению табличных блокировок. SQL Server не
повышает уровень RID/KEY блокировок до страничных
блокировок.
Когда же SQL Server поднимает уровень
блокировок? Его не интересует, насколько большой процент
таблицы заблокирован; единственная вещь, которая имеет
значение - это общее количество блокировок в системе. Когда
блокировки начинают использовать достаточно большой процент
памяти, SQL Server 2000 пытается поднять уровень блокировок
всех транзакций во всех соединениях. Также он пытается поднять
уровень блокировок, если заканчиваются слоты блокировок. Вы
можете сами это попробовать, уменьшив количество слотов
блокировок, которые использует SQL Server, при помощи
процедуры SP_CONFIGURE.
SQL Server пытается удерживать
высокую конкурентность при помощи малых блокировок, насколько
это возможно. Но иногда вы знаете о своих данных то, что SQL
Server не знает, и эта информация влияет на уровень
блокировок, который вы хотите использовать. Например,
существует большая таблица для поиска, на которой производится
только чтение данных. Вместо накладывания множества key
блокировок вы хотите заблокировать целую таблицу за один шаг.
В этом случае вы можете использовать хинты блокировки или
SP_INDEXOPTION.
Хинты блокировки хорошо известны и хорошо
документированы в BOL, так что я не буду повторять их здесь.
Системная хранимая процедура SP_INDEXOPTION - это хороший путь
для указания SQL Server использовать определенный размер
блокировок.
Используя SP_INDEXOPTION, вы можете выключить
блокировку уровня строки или уровня страницы. Это значит, что
вам не нужны хинты блокировки - все блокировки таблицы/индекса
будут иметь заданный вами размер. Хотя BOL и утверждает, что
эта процедура используется для выбора уровня блокировки на
индексах, она также может использоваться и для таблицы-кучи.
Передавайте название таблицы в переменную @IndexNamePattern.
Очень хороший и практически неизвестный способ.
Количество
влияющих факторов на этом не заканчивается. Если вы
используете один из двух высших уровней изоляции и у вас нет
ни одного индекса по критерию выборки, то SQL Server вынужден
блокировать большую часть, если не всю таблицу, чтобы
выполнить запрос. Приведу пример.
Теперь, в другом окне, давайте проверим результат sp_lock. На моем компьютере я отследил 853 блокировки, относящиеся к соединению, на котором я запустил UPDATE. Таблица Orders в базе Northwind имеет 830 строк, так что я заблокировал каждую строку. Откатите транзакцию и попытайтесь снова применить update после создания индекса, как показано ниже.
Теперь результат SP_LOCK показывает всего 25 блокировок. Эта часть производительности часто не замечается. Даже если вы используете READ COMMITTED, уровень изоляции по умолчанию, то вы все равно увидите большую разницу - снижение количества блокировок со 136 до 24 после создания индекса.
Существует несколько флагов трассировки блокировок, которые
помогут настроить ваши блокировки и обнаружить проблемы с
мертвыми блокировками.
Флаги трассировки используются,
чтобы включать или выключать определенное поведение в SQL
Server. Вы можете установить флаг трассировки, используя DBCC
TRACEON, и если вы хотите, чтобы флаг трассировки включался
при каждом запуске SQL Server, то вы можете определить его как
стартовый параметр, используя '-T'.
1200: Показывает все блокировки по всем соединениям.Очень объемный результат, я рекомендую использовать его в контролируемой среде, где работает только одно соединение.
1204: Показывает множество информации об участниках мертвой блокировки. Вот пример, улучшенный специально для вас.
KEY: означает, что ресурс, заблокированный мертвой блокировкой, является ключом индекса. Этот ресурс также может быть любым другим типом блокировки, таким, как страница, RID, таблица, и т.д.
ECID взят из master.dbo.sysprocesses. Он используется для различения блокировок, наложенных разными потоками. Режим - это режим блокировки, приведшей к мертвой блокировке, такой, как S, X или U.
Строка "6:885578193:2" означает: база данных с Id 6, объект
с Id 885578193, и индекс с Id 2. Число в скобках - это
хэшированное значение, используемое, чтобы определить
блокировку. Вы можете увидеть это в столбце rsc_text таблицы
master.dbo.syslockinfo. К сожалению, это односторонний хэш,
что значит, что вы не можете найти заблокированную строку
только при помощи хэшированного значения. Spid - это, конечно,
системный процесс, наложивший блокировку.
Чтобы прочитать
результат, нужно сначала посмотреть на узлы 1 и 2, которые
показывают вам блокировки, уже разрешенные и вовлеченные в
мертвую блокировку. После этого вы увидите две блокировки в
очереди ожидания, помеченные "Requested By:".
1205: Выводит информацию о работе менеджера блокировок. Каждый раз, когда активизируется поиск мертвых блокировок, этот флаг трассировки заставляет менеджера блокировок выводить информацию о поиске. Работает, только если установлен флаг трассировки 1204.
1211: Отключает все поднятия уровня блокировок. Этот флаг трассировки не дает менеджеру блокировок поднимать уровень блокировок, даже если заканчиваются слоты блокировок.
Как вы знаете, самой маленькой единицей блокировки в SQL
Server 2000 является блокировка строки. Блокировка столбцов
недоступна напрямую в SQL Server. Я покажу вам, как можно
использовать блокировку индекса для имитации блокировки уровня
столбца.
Блокировки столбцов часто считаются слишком
медленными для практического использования, и SQL Server не
является исключением. Но т.к. блокировка строки не означает
автоматически, что индекс таблицы заблокирован, то вы всегда
можете использовать данные страниц индекса, чтобы работать с
заблокированными данными. Опять же, я объясню это, используя
таблицу Region базы данных Northwind.
Таблица Region - это
таблица-куча с двумя столбцами, RegionDescription и RegionId.
Уникальный некластерный индекс существует на столбце
RegionId.
Сейчас мы изменим значение RegionDescription у одной из строк при помощи простой команды update, как показано ниже:
Чтобы выполнить этот запрос, SQL Server не может использовать индекс - индекс не покрывает столбец RegionDescription. Поэтому SQL Server использует полное сканирование таблицы, чтобы найти строку, требующую изменения. Когда он находит эту строку, он превращает update блокировку этой строки в эксклюзивную блокировку. Чтобы убедиться в этом, запустите SP_LOCK в другом окне Query Analyzer. Вы должны увидеть эксклюзивную RID блокировку на соответствующем объекте. В том же окне, в котором вы запустили SP_LOCK, вы можете выполнить следующий select:
Если только вы не указали READPAST или не установили
уровень изоляции транзакции как read uncommitted, то вы будете
ждать, пока первое соединение не снимет блокировку.
Это
ожидаемый результат. Но вот где неожиданность:
В этот раз вы ждать не будете. Если вы похожи на меня, то вы любите смотреть на план выполнения, и именно план выполнения объясняет, почему нам не пришлось ждать.
Как видите, SQL Server выбрал сканирование индекса, чтобы
получить нужные данные для выполнения запроса. И т.к. вы
запросили информацию, которая может быть предоставлена чтением
индекса, то SQL Server'у не нужно читать данные из
таблицы-кучи. Этот тип запроса называется покрывающим
запросом.
Существуют два критерия для этого случая. Первый
- это то, что все нужные запросу данные содержатся в индексе.
Помните, что если таблица имеет кластерный индекс, то все
некластерные индексы содержат столбцы самого индекса и столбцы
кластерного индекса.
Второй критерий - это то, что команда
UPDATE не должна изменять любой из столбцов, содержащихся в
индексе. Если индекс изменяется, то он должен быть
заблокирован эксклюзивно и этот трюк не сработает.
Расширенная таблица совместимости блокировок
Доступные сейчас в BOL и MSDN таблицы не очень полны в плане совместимости различных блокировок. Из-за этого я включил в эту статью более полную таблицу. Думаю, вы найдете ее полезной.
Я действительно нашел ускользающую intent update блокировку, и еще много чего, когда искал ее. Блокировки и поведение блокировок плохо документированы в BOL, что сделало это исследование гораздо более трудным, чем это должно было быть. Я узнал много нового во время написания это статьи, и надеюсь, что поделился частью этих знаний с вами.
Andres Taylor пытается уделять одинаковое внимание его любимым занятиям: изучению SQL Server, Бразильскому Джиу-Джитсу и его красивой жене Мими. Он сдал 20 экзаменов MCP, является MCT и работает в Dotway в качестве консультанта по .NET/SQL Server.