Блокировки в SQL Server
Я думал, что знаю SQL Server достаточно хорошо. Я использую этот продукт уже больше 6 лет, и мне нравится знать об используемых мною инструментах все. Когда я преподавал на курсах программирования SQL Server, я заметил, что в материалах Microsoft представлена таблица совместимости блокировок. Та же таблица была представлена и в MSDN. Рассматривая эту таблицу, я удивился — неужели здесь нет блокировки Intent 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.
Если вы уже когда-либо писали схемы блокировок на других языках баз данных для преодоления недостатка блокировок (как я), то у вас могло остаться чувство, что обязательно нужно самому заниматься блокировками. Позвольте вас заверить, что диспетчеру блокировок можно полностью доверять. Тем не менее SQL Server предлагает несколько методов управления блокировками, о которых мы детально поговорим в этом разделе.
Не применяйте параметры блокировки и не изменяйте уровни изоляции случайным образом — доверьте менеджеру блокировок SQL Server выполнять балансировку конкуренции и целостности транзакций. Только если вы абсолютно уверены, что схема базы данных хорошо настроена, а программный код буквально отшлифован, можете слегка подкорректировать работу диспетчера блокировок, чтобы решить конкретную проблему. В некоторых случаях настройка запросов select на отсутствие блокировок способна решить большинство проблем.
Установка уровня изоляции подключения
Уровень изоляции определяет продолжительность общей или эксклюзивной блокировки подключения. Установка уровня изоляции оказывает влияние на все запросы и все таблицы, используемые на протяжении всего подключения или до явной замены одного уровня изоляции другим. В следующем примере устанавливается более плотная изоляция, чем задана по умолчанию, и предотвращаются неповторяющиеся чтения:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Допустимыми уровнями изоляции являются:
? read uncommited ? serializable
? read commited ? snapshot
Текущий уровень изоляции можно проверить с помощью команды проверки целостности базы данных (DBCC):
Результаты будут следующими (сокращенно):
Set Option Value
isolation level repeatable read
Уровни изоляции могут быть также установлены на уровне запроса или таблицы с помощью параметров блокировки.
Использование изоляции уровня снимков базы данных
Существуют два варианта уровня изоляции снимков базы данных: snapshot и read commited snapshot. Изоляция snapshot работает подобно repeatable read, не занимаясь вопросами блокировки. Изоляция read commited snapshot имитирует установленный по умолчанию в SQL Server уровень read commited, так же снимая вопросы блокировки.
В то время как изоляцию транзакций, как правило, устанавливают на уровне подключений, изоляция snapshot должна конфигурироваться на уровне базы данных, поскольку она
эффективно отслеживает версионность строк в базе. Версионностъ строк — это технология, которая создает для обновления копии строк в базе данных TempDB. Кроме основной загрузки базы TempDB, версионность строк также добавляет 14-байтовый идентификатор строки.
Использование изоляции Snapshot
В следующем фрагменте включается уровень изоляции snapshot. Для корректировки базы данных и включения уровня изоляции snapshot к этой базе не должны быть установлены другие подключения.
ALTER DATABASE Aesop
SET ALLOW_SNAPSHOT_ISOLATION ON
| Для проверки того, включена ли в базе данных изоляция snapshot, выполните SVS следующий запрос: SELECT name, snapshot_isolation_state_desc FROM [ * sysdatabases.
Теперь первая транзакция начинает чтение и остается открытой (т.е. не подтвержденной): USE Aesop
SET TRANSACTION ISOLATION LEVEL Snapshot;
BEGIN TRAN SELECT Title FROM FABLE WHERE FablelD = 2
Будет получен следующий результат:
The Bald Knight
В это время вторая транзакция начинает обновление той же строки, которая открыта первой транзакцией:
SET TRANSACTION ISOLATION LEVEL Snapshot;
BEGIN TRAN UPDATE Fable
SET Title = ‘Rocking with Snapshots’
WHERE FablelD = 2;
SELECT * FROM FABLE WHERE FablelD = 2
Rocking with Snapshots
He правда ли, удивительно? Вторая транзакция смогла обновить строку, даже несмотря на то, что первая транзакция осталась открытой. Вернемся к первой транзакции и увидим исходные данные:
SELECT Title FROM FABLE WHERE FablelD = 2
The Bald Knight
Если открыть третью и четвертую транзакции, то они увидят все то же исходное значение The Bald Knight:
Даже после того как вторая транзакция подтвердит изменения, первая будет по-прежнему видеть исходное значение, а все следующие транзакции — новое, Rocking with Snapshots.
Использование ИЗОЛЯЦИИ Read Commited Snapshot
Изоляция Read Commited Snapshot включается с помощью аналогичного синтаксиса:
ALTER DATABASE Aesop
SET READ_COMMITTED_SNAPSHOT ON
Подобно изоляции Snapshot, данный уровень для снятия вопросов блокировок также использует версионность строк. Если взять за основу пример, описанный в предыдущем разделе, то в данном случае первая транзакция увидит изменения, выполненные второй, как только они будут подтверждены.
Так как Read Commited является уровнем изоляции, принятым в SQL Server по умолчанию, требуется только установка параметров базы данных.
Разрешение конфликтов записи
Транзакции, записывающие данные при установленном уровне изоляции Snapshot, могут быть заблокированы предыдущими неподтвержденными транзакциями записи. Такая блокировка не заставит новую транзакцию ожидать — просто будет сгенерирована ошибка. Для обработки подобных ситуаций используйте выражение try. . . catch, выждите пару секунд и попробуйте повторить транзакцию снова.
Использование параметров блокировки
Параметры блокировки позволяют вносить временную коррекцию в статегию блокировки. В то время как уровень изоляции оказывает влияние на подключение в целом, параметры блокировки специфичны для каждой таблицы в конкретном запросе (табл. 51.5). Параметр WITH (параметр_блокировки) помещается после имени таблицы в предложении FROM запроса. Для каждой таблицы можно задать несколько параметров, разделяя их запятыми.
Таблица 51.5. Параметры блокировки
Уровень изоляции. He устанавливает и не удерживает блокировку. Равносилен отсутствию блокировок
Уровень изоляции, установленный для транзакций по умолчанию
Уровень изоляции. Удерживает общую и эксклюзивную блокировки до момента подтверждения транзакции
Уровень изоляции. Удерживает общую блокировку до завершения транзакции
Пропуск заблокированных строк вместо ожидания
Включение блокировки на уровне строк вместо уровня страницы, экстента или таблицы
Включение блокировки на уровне страниц вместо уровня таблицы
Автоматическая эскалация блокировок уровня строк, страниц и экстента до гранулярности уровня таблицы
Неприменение и неудержание блокировок. То же, что и ReadUnCommited
Включение эксклюзивной блокировки таблицы. Запрет другим транзакциям работать с таблицей
Удержание общей блокировки до подтверждения транзакции (аналогично Serializable)
Использование блокировки обновления вместо общей и ее удержание. Блокировка других записей в данные между изначальными операциями чтения и записи
Удержание эксклюзивной блокировки данных до подтверждения транзакции
В следующем примере в предложении FROM инструкции UPDATE использован параметр блокировки, запрещающий диспетчеру эскалировать гранулярность блокировки:
USE OBXKites UPDATE Product
FROM Product WITH (RowLock)
SET ProductName = ProductName + ‘ Updated 1
Если запрос содержит подзапросы, не забывайте, что обращение к таблице каждого из запросов генерирует блокировку, которой можно управлять с помощью параметров.
Ограничения блокировок уровня индексов
Уровни изоляции и параметры блокировки применяются на уровне подключений и запросов. Единственным способом управления блокировками на уровне таблицы является ограничение гранулярности блокировок на основе конкретных индексов. С помощью системной хранимой процедуры sp_indexoption блокировки строк и/или страниц можно отключить для конкретного индекса, используя следующий синтаксис: sp_indexoption ‘имя_индекса 1 ,
AllowRowlocks или AllowPagelocks,
Это может пригодиться в ряде особых случаев. Если таблица часто вызывает ожидания по причине блокировок страниц, то установка для параметра allowpagelocks значения off установит блокировку на уровне строк. Уменьшенная гранулярность блокировок положительно скажется на конкуренции. К тому же, если таблица редко обновляется, но часто считывается, блокировки на уровне строк и страниц нежелательны; в этом случае оптимальным является уровень блокировки на уровне таблиц. Если обновления выполняются нечасто, то эксклюзивная блокировка таблиц не приведет к большим проблемам.
Хранимая процедура Sp_indexoption предназначена для тонкой настройки схемы данных; именно поэтому в ней используется блокировка на уровне индексов. Для ограничения блокировок по первичному ключу таблицы используйте sp_help имя_ та блицы, чтобы найти имя индекса первичного ключа.
Следующая команда конфигурирует таблицу ProductCategory как редко обновляемый классификатор. Вначале команда sp_help выводит имя индекса первичного ключа таблицы: sp_help ProductCategory
Результат (усеченный) таков:
index index index
name description keys
PK_____________ ProductCategory 79A814 03 nonclustered, ProductCategorylD
unique, primary key located on PRIMARY
Имея в наличии реальное имя первичного ключа, системная хранимая процедура может установить параметры блокировки индекса:
‘ProductCategory.РК__ ProductCategory_______ 7 9А814 03′,
‘AllowRowlocks’, FALSE EXEC sp_indexoption
‘ProductCategory.PK__ ProductCategory_______ 79A81403′,
Управление временем ожидания блокировок
Если транзакция ожидает блокировку, то это ожидание будет продолжаться до тех пор, пока блокировка не станет возможной. По умолчанию не существует предела времени ожидания — чисто теоретически оно может длиться вечно.
К счастью, вы можете установить время ожидания блокировки с помощью параметра подключения set lock_timeout. Установите для этого параметра количество миллисекунд или, если хотите не ограничивать время, установите для него значение -1 (оно принято по умолчанию). Если для этого параметра установлено значение 0, то транзакция будет немедленно отклонена при наличии какой-либо блокировки. В этом случае приложение будет исключительно быстродействующим, но малоэффективным.
В следующем запросе время ожидания блокировки устанавливается в две секунды (2000 миллисекунд):
SET Lock_Timeout 2 00 0
Если транзакция выходит за пределы установленного предельного времени ожидания, то генерируется ошибка с номером 1222.
Настоятельно рекомендую устанавливать предельное время ожидания блокировки на уровне подключения. Эта величина выбирается в зависимости от обычной производительности базы данных. Я предпочитаю устанавливать пятисекундное время ожидания.
Оценка производительности конкуренции в базе данных
Очень легко создать базу данных, в которой не решаются вопросы соревнования блокировок и конкуренции при тестировании на группе пользователей. Реальный тест — это когда несколько сотен пользователей одновременно обновляют заказы.
Тестирование конкуренции нужно правильно организовать. На одном уровне он должен содержать одновременное использование множеством пользователей одной и той же конечной формы. Может оказаться полезной и программа .NET, которая постоянно имитирует
просмотр пользователем данных и их обновление. Хороший тест должен запускать 20 экземпляров сценария, который постоянно загружает базу данных, а затем дать возможность команде тестировщиков использовать приложение. Количество блокировок поможет увидеть монитор производительности, о котором говорилось в главе 49.
Многопользовательскую конкуренцию лучше тестировать в процессе разработки несколько раз. Как говорится в экзаменационном руководстве MCSE, “не допускайте, чтобы тест в реальных условиях был первым”.
SQL Server использует очень сложную схему блокировок. Иногда процесс или ресурс, отличный от данных, требует блокировки. Например, может быть необходимо запускать процедуру, которая наносит вред, если другой пользователь запустил еще один экземпляр той же процедуры.
Несколько лет назад я написал программу разводки кабелей в проектах атомных электростанций. Когда геометрия предприятия была спроектирована и протестирована, инженеры вводили состав кабельного оборудования, его местоположение и типы используемых кабелей. После того как вводилось несколько кабелей, программа формировала маршрут их прокладки так, чтобы он оказался как можно более коротким. Процедура также учитывала вопросы безопасности кабельной проводки и разделяла несовместимые кабели. В то же время, если множество процедур прокладки запускалось одновременно, каждый экземпляр пытался маршрутизировать одни и те же кабели, в результате чего результат оказывался некорректным. Блокировка приложений стала отличным решением этой проблемы.
Блокировка приложений открывает целый мир блокировок SQL, предназначенных для использования в приложениях. Вместо использования данных в качестве блокируемого ресурса, блокировки приложений блокируют использование всех пользовательских ресурсов, объявленных в хранимой процедуре sp__GetAppLock.
Блокировка приложений может применяться в транзакциях; при этом может быть объявлен режим блокировки Shared, Update, Exclusive, IntentExclusice или IntentShared. Возвращаемое процедурой значение указывает, успешным ли было применение блокировки.
? 0. Блокировка установлена успешно.
? 1. Блокировка была установлена, когда другая процедура сняла свою блокировку.
? -1. Запрос на блокировку не был реализован из-за превышения времени ожидания.
? -2. Запрос на блокировку не был реализован по причине отмены.
? -3. Запрос на блокировку не был реализован из-за взаимоблокировки.
? -999. Блокировка не была установлена по другой причине.
Хранимая процедура sp_ReleaseApLock снимает блокировку. В следующем примере продемонстрировано, как блокировка приложения может использоваться в пакете или процедуре: DECLARE @ShareOK INT EXEC @ShareOK = sp_GetAppLock
4. Во втором окне аналогично выполните код Шаг 2.
5. Вернитесь в первое окно и выполните код блока Шаг 3.
6. Через короткий промежуток времени SQL Server обнаружит взаимоблокировку и автоматически устранит ее.
Ниже приведен программный код примера.
– Транзакция 1 — Шаг 1 USE OBXKites BEGIN TRANSACTION UPDATE Contact
SET LastName = ‘Jorgenson’
WHERE ContactCode = 401′
Puc. 51.12. Создание ситуации взаимоблокировки в Management Studio с помощью двух подключений (их окна расположены вверху)
Теперь первая транзакция установила эксклюзивную блокировку на запись со значением 101 в поле ContactCode. Вторая транзакция установит эксклюзивную блокировку строки со значением 1001 в поле ProductCode, а затем попытается эксклюзивно заблокировать запись, уже заблокированную первой транзакцией (ContactCode=101).
– Транзакция 2 — Шаг 2 USE OBXKites BEGIN TRANSACTION UPDATE Product SET ProductName
= ‘DeadLock Repair Kit’
WHERE ProductCode = ‘1001’
SET FirstName = ‘Neals’
WHERE ContactCode = ‘101’
Пока еще взаимоблокировки не существует, поскольку транзакция 2 ожидает завершения транзакции 1, но транзакция 1 пока еще не ожидает завершения транзакции 2. В этой ситуации, если транзакция 1 завершит свою работу и выполнит инструкцию COMMIT TRANSACTION, ресурс данных будет освобожден, и транзакция 2 благополучно получит возможность необходимой ей блокировки и продолжит свои действия.
Проблема возникает, когда транзакция 1 попытается обновить строку с ProductCode=l. Однако необходимую для этого эксклюзивную блокировку она не получит, поскольку эта запись заблокирована транзакцией 2:
– Транзакция 1 — Шаг 3 UPDATE Product SET ProductName
= ‘DeadLock Identification Tester’
WHERE ProductCode = ‘1001’
Транзакция 1 вернет следующее текстовое сообщение об ошибке спустя пару секунд. Возникшую взаимоблокировку можно также увидеть в SQL Server Profiler (рис. 51.13):
Server: Msg 1205, Level 13,
State 50, Line 1 Transaction (Process ID 51) was
deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Транзакция 2 завершит свою работу, будто бы проблемы и не существовало:
(1 row(s) affected)
(1 row(s) affected)
Рис. 51.13. SQL Server Profiler позволяет выполнять мониторинг взаимоблокировок с помощью события Locks:Deadlock Graph и выявлять ресурс, вызвавший взаимоблокировку
Автоматическое выявление взаимоблокировок
Как было продемонстрировано в приведенном выше коде, SQL Server автоматически выявляет ситуацию взаимоблокировки, проверяя блокирующие процессы и откатывая транзакции,
выполнившие наименьший объем работы. SQL Server постоянно проверяет существование перекрестных блокировок. Задержка выявления взаимоблокировок может варьироваться от нуля до двух секунд (на практике дольше всего мне приходилось ожидать этого пять секунд).
Когда происходит взаимоблокировка, подключение, выбранное в качестве “жертвы” взаимоблокировки, должно повторить свою транзакцию. Так как работа должна быть переделана, хорошо, что откатывается именно та транзакция, которая успела выполнить наименьший объем работы, — именно она будет повторена сначала.
Ошибка с кодом 12 05 должна перехватываться клиентским приложением, которое и должно перезапускать транзакцию. Если все происходит как нужно, пользователь даже не заподозрит о том, что произошла взаимоблокировка.
Вместо того чтобы позволять самому серверу решать, какую из транзакций выбирать на роль “жертвы”, самой транзакции можно “сыграть в поддавки”. Следующий код, будучи помещенным в транзакцию, информирует SQL Server о том, что в случае возникновения взаимоблокировки данную транзакцию следует откатить:
SET DEADLOCKJPRIORITY LOW
Даже несмотря на то, что взаимоблокировки легко выявить и обработать, лучше все-таки их избегать. Приведенные ниже рекомендации помогут вам избежать взаимоблокировок.
? Старайтесь делать транзакции короткими и не содержащими излишнего кода. Если некоторый код не обязательно должен присутствовать в транзакции, он должен быть выведен из нее.
? Никогда не ставьте код транзакции в зависимость от ввода пользователя.
? Старайтесь создавать пакеты и процедуры, устанавливающие блокировки, в одном и том же порядке. Например, вначале обрабатывается таблица А, затем таблицы Б, В и т.д. Таким образом, одна процедура будет ожидать второй и взаимоблокировки не смогут возникнуть по определению.
? Планируйте физическую схему так, чтобы хранить одновременно отбираемые данные как можно ближе на страницах данных. Для этого используйте нормализацию и продуманно выбирайте кластеризованные индексы. Уменьшение разброса блокировок поможет избежать их эскалации. Небольшие блокировки помогут избежать их конкуренции.
? Не увеличивайте уровень изоляции, если в этом нет необходимости. Более строгий уровень изоляции увеличивает продолжительность блокировок.
Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.
Для отслеживания sql блокировок в SQL Server существует команда sp_who2. В результате выполнения данной команды мы получим список соединений с SQL Server, но вот находить блокировщиков придется вручную, анализируя колонку BlkBy.
В этой колонке мы увидим блокирующий процесс, который можно принудительно "убить" с помощью команды KILL ПРОБЕЛ SPID убиваемого процесса (напимер, KILL 22).
Скрипт для удобного использования команды sp_who2 при отслеживании sql блокировок
Однако если активных соединений с SQL Server очень много, то sql блокировки отслеживать трудно: приходится прокручивать весь список и вручную выбирать самых-самых "блокировщиков". Гораздо удобно создать временную таблицу и сделать сортировку по полю BlkBy. Готовый скрипт для обнаружения и устранения sql блокировок можно взять ниже.
DECLARE @Table TABLE( SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT)
INSERT INTO @Table EXEC sp_who2
SELECT DISTINCT BlkBy FROM @Table
На картинке не видно sql блокировок, потому что их действительно нет. В реальной ситуации sql блокировки есть всегда, и данный скрипт облегчит вам жизнь. Вот так коротко. Удачи.
Как узнать выполняемый блокирующим процессом скрипт или бонус к отслеживанию sql блокировок
Данную статью я решил дополнить очень, на мой взгляд, полезной вещью.
Увидели мы блокирующий процесс. А дальше что? Убить блокировщика, вызвавшего sql блокировку, всегда успеем. Но ведь нужно смотреть в корень и предупредить возникновение блокировок. А для этого надо узнать, какой скрипт выполняется под блокировщиком.
Сделать это можно через монитор активности, в котором можно лишь просмотреть последние ресурсоемкие запросы. А вот чтобы подсмотреть выполняемый блокировщиком скрипт потребуется отдельная хранимая процедура.
CREATE PROCEDURE PrintCurrentCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE sp >
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end — @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
Для запуска процедуры выполняем команду
Exec PrintCurrentCode SPID процесса
Команда выведет выполняемый блокировщиком код на языке TSQL, который мы с легкостью сможем проанализировать.
Удачи в отслеживании sql блокировок и отсутствия долгих транзакций!
Понравилась статья? Не очень? Тогда возможно, что вы напишите свою гораздо лучше. Просто перейдите по ссылке Размещение статей и ознакомьтесь с правилами публикации статей на сайте MultiBlog67.RU .
