Выявление и разрешение проблем блокировки в DB2 для Linux, UNIX и Windows

В этой статье описываются проблемы, связанные с ожиданиями блокировки, взаимными блокировками и эскалациями блокировки, а также порядок мониторинга и устранения указанных проблем при использовании продукта DB2® для Linux, UNIX и Windows. В состав статьи включены примеры соответствующих скриптов для проведения мониторинга.

Билл Уилкинс, сотрудник группы поддержки бизнес-партнеров по направлению IBM Information Management, IBM

В настоящее время Билл Вилкинс является старшим техническим специалистом в группе поддержки бизнес-партнеров IBM в Торонто. До этого он на протяжении шести лет занимался анализом производительности DB2 в исследовательской лаборатории IBM в Торонто. Вилкинс имеет статус «Сертифицированный технический эксперт IBM с углубленным уровнем знаний по продукту DB2 UDB» и участвовал в написании нескольких книг по DB2. Его общий стаж работы в ИТ-отрасли превышает двадцать шесть лет.



Ясир Варрах, консультант по базам данных, IBM

Ясир Варрах является консультантом по базам данных в исследовательской лаборатории IBM в Торонто и работает преимущественно с бизнес-партнерами IBM. Варрах работает в IBM девять лет, из которых на протяжении восьми лет он являлся сотрудником группы по расширенной технической поддержке DB2, а в настоящее время работает консультантом по настройке производительности баз данных и по миграции с реляционных СУБД на продукт DB2.



25.07.2008

Введение

Эта статья представляет собой практическое руководство по устранению проблем, связанных с блокировками в СУБД DB2 для Linux, UNIX и Windows. Она не может служить всеобъемлющим справочником по вопросам блокировки, а скорее является дополнением к существующим источникам информации. Для чтения этой статьи необходимо предварительное знакомство со следующими источниками, перечисленными в разделе «Ссылки»:

  • Раздел по блокировке в документе DB2 Performance Guide (Руководстве по поддержанию производительности DB2). Подробно рассматриваются типы блокировок, ситуации возникновения блокировок и совместимость между различными блокировками.
  • Разделы Учебных пособий по определению проблем, посвященные блокировкам. В этих разделах изложены базовые пошаговые инструкции по выявлению проблем блокировки.

Цитата из Руководства выявлению проблем процессора СУБД (DB2 Engine Problem Determination): «DB2 – это многопользовательская СУБД, поэтому ее процессор баз данных поддерживает механизм блокировки, позволяющий избегать конфликтов при доступе к ресурсам и обеспечивать целостность данных». Для многих приложений механизм блокировки работает совершенно прозрачно, но у остальных приложений могут возникать такие проблемы, как ожидание блокировки, взаимные блокировки и эскалация блокировок.

В этой статье описываются проблемы, связанные с ожиданиями блокировки, взаимными блокировками и эскалациями блокировки, и описываются действия при возникновении таких проблем. В статье не рассматриваются другие проблемы, связанные с блокировками, такие как обращение к несохраненным данным, невоспроизводимое чтение и фантомные строки. Последствия этих проблем – неожиданные или нежелательные результаты – принципиально отличаются от обсуждаемых в этой статье проблем ухудшения производительности. Указанные проблемы могут быть решены посредством выбора надлежащего уровня изоляции, как описано в Руководстве по поддержанию производительности. Эта статья ориентирована преимущественно на среды разработки приложений. Большая часть материала применима и к рабочим средам, однако эти среды обычно предоставляют значительно меньше возможностей для устранения проблем блокировки посредством внесения изменений в приложения.


Краткий обзор проблем блокировки

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

Эскалация блокировок

Каждая блокировка использует определенный объем памяти. Эскалация блокировок происходит в том случае, когда DB2 автоматически заменяет блокировки нескольких строк в таблице одной блокировкой уровня таблицы, высвобождая таким образом память блокировок. Хотя эскалация расходует ресурсы центрального процессора (не только на высвобождение блокировок, но и в первую очередь на доступ к ним), в следующих случаях могут возникать проблемы параллелизма:

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

Ожидания блокировок

Ожидание блокировки происходит в том случае, когда одна транзакция пытается установить блокировку, тип которой находится в противоречии с типом блокировки, установленной другой транзакцией. См. таблицу совместимости типов блокировок в Главе 5 Руководства по поддержанию производительности. Две транзакции могут иметь друг с другом конфликты блокировки, даже если они порождены одним и тем же приложением, запущенным под одним и тем же авторизационным именем, и даже если они являются потоками одного и того же процесса. Если работа DB2 выполняется на разных подключениях (отдельных единицах работы), то может иметь место конфликт блокировок.

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

Ожидание блокировки продолжается до тех пор, пока не будут высвобождены блокировки, которые удерживаются транзакцией, вызывающей конфликт. Обычно это происходит после фиксации транзакции, а также при откате транзакции, например, при принудительном завершении приложения посредством команды FORCE APPLICATION или по тайм-ауту блокировки. Блокировка может продолжаться долгое время, с очевидным отрицательным влиянием на время отклика ожидающей транзакции, поэтому ожидания блокировок обычно являются самыми важными проблемами блокировки, с которыми приходится иметь дело.

Тайм-аут блокировки – это один из способов борьбы с ожиданиями блокировок. Изменяя значение конфигурационного параметра базы данных LOCKTIMEOUT со значения по умолчанию -1 («ждать постоянно») на 0 или выше, вы инициируете откат транзакций (SQLCODE -911 с кодом причины 68) после того, как они будут находиться в состоянии ожидания блокировки на протяжении указанного числа секунд. Изменение значения параметра LOCKTIMEOUT позволяет избежать длительного ожидания блокировки, однако:

  • Это не устраняет проблему, вызывающую ожидания блокировок;
  • Приложение не всегда может надлежащим образом обрабатывать команду 911 и выполнять откаты транзакций;
  • При повторном выполнении «откаченной» транзакции приложение может столкнуться с таким же ожиданием блокировки.

По этим причинам при разработке или тестировании приложения рекомендуется перед изменением параметра LOCKTIMEOUT сначала попытаться устранить ожидание блокировки. Если вы все же изменили этот параметр, проверьте, как будет реагировать приложение. Параметр LOCKTIMEOUT может также быть задан на сеансовом уровне с помощью следующей команды: SET CURRENT LOCK TIMEOUT WAIT X, где x - число секунд. Эта команда позволяет для базы данных менять значение LOCKTIMEOUT и повышает степень контролируемости. Для разных приложений и сеансов можно использовать разные значения параметра LOCKTIMEOUT.

Взаимные блокировки

Взаимная блокировка – это особый случай ожидания блокировки, когда два или несколько приложений не могут продолжить свою работу, поскольку каждое из них ждет окончания блокировки, установленной другим приложением из этой группы. Такие блокировки устраняются автоматически детектором взаимоблокировок DB2, который активируется с частотой, заданной значением конфигурационного параметра базы данных DLCHKTIME (в миллисекундах).

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


Инструменты для мониторинга блокировок – какую информацию они сообщают?

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

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

(1) Команда LIST APPLICATIONS

Эта команда DB2, при наличии параметра SHOW DETAIL, показывает состояние каждого приложения. Выполнение этой команды – хороший первый шаг, если вы подозреваете, что существует проблема с ожиданием блокировок.

(Альтернативный вариант на основе графического интерфейса пользователя: Control Center > instance name > Applications)

где instance name – имя экземпляра. Следует, однако, помнить, что интерфейс Control Center отображает не все столбцы команды LIST APPLICATIONS SHOW DETAIL)

Вывод команды (с параметром SHOW DETAIL) имеет ширину более 240 байтов, поэтому каких-либо примеров здесь не приведено. Применительно к задачам этой статьи необходимо обратить внимание на следующие столбцы вывода:

  • Status (статус). Статус Lock-wait означает, что на приложение наложена блокировка, установленная другим приложением. Статус UOW Waiting не должен вводить в заблуждение – он означает, что приложение (единица работы) исполняется, т.е. не заблокировано, но в данный момент не выполняет какой-либо работы. Однако если происходит конфликт блокировок, то приложение, наложившее блокировки на другие приложения, также может иметь статус UOW Waiting, так что этот статус не всегда является «безобидным».
  • Status Change Time (момент изменения статуса). Этот параметр особенно интересен для приложений со статусом Lock-wait: он показывает, когда началось ожидание блокировки. Обратите внимание, что для отображения этого значения времени предварительно должен быть активирован ключ монитора UOW.
  • Appl. Handle (дескриптор приложения). Целое число, которое служит двум основным целям:
    • Позволяет коррелировать выходную информацию команды LIST APPLICATIONS с выходной информацией мониторов снимков текущего состояния и мониторов событий, описываемых ниже.
    • Это значение вы можете использовать в команде FORCE APPLICATION для принудительной остановки приложения, наложившего блокировки, которые вызывают конфликтные ситуации.
    • Другие столбцы идентифицируют подключение и приложение, включая название базы данных.

(2) Мониторинг снимков текущего состояния (snapshot)

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

  • С помощью команды UPDATE MONITOR SWITCHES включите все ключи мониторов.
  • Выполните команду RESET MONITOR ALL для сброса счетчиков. Это упростит сравнение различных снимков текущего состояния и поиск различий в процессе проведения теста. Например, увеличивается ли число ожиданий блокировок в конце теста по сравнению с его началом?
  • Выждите стандартный отрезок времени (1 – 5 минут) и затем выполните команду GET SNAPSHOT FOR ALL ON <имя базы данных>.
  • Чтобы получить несколько снимков текущего состояния для сравнения повторите два предыдущих шага.

Теперь рассмотрим результирующую информацию команды GET SNAPSHOT FOR ALL ON <имя базы данных>. Эта команда последовательно выведет снимки текущего состояния следующих компонентов. Показанные полужирным шрифтом компоненты имеют к блокировкам самое ближайшее отношение, поэтому сосредоточимся именно на них:

  • Database snapshot (снимок текущего состояния базы данных)
  • Bufferpool snapshot (снимок текущего состояния буферного пула)
  • Dynamic SQL snapshot (снимок текущего состояния динамических SQL-запросов)
  • Application snapshot (снимок текущего состояния приложения, по одному на каждое подключенное приложение)
  • Tablespace snapshot (снимок текущего состояния табличной области)
  • Database lock snapshot (снимок текущего состояния блокировок базы данных)
  • Table snapshot (снимок текущего состояния таблицы)

(Вы можете получать определенные компоненты с помощью отдельных команд GET SNAPSHOT, однако менее полезные компоненты, как правило, являются и самыми короткими, поэтому такой выборочный подход не приведет к существенной "экономии")

Информация снимка текущего состояния базы данных

Снимок текущего состояния базы данных – это лучшее место для начала исследования проблем с блокировками. Нас будут интересовать следующие строки, имеющие отношение к блокировкам.

Locks held currently = 8
Lock waits = 0
Time database waited on locks (ms) = 315704
Lock list memory in use (Bytes) = 1692
Deadlocks detected = 0

В случае необходимости обратитесь к документу System Monitor Guide and Reference (Руководство и справочник по компоненту DB2 System Monitor). Рассмотрим важнейшие строки.

Agents currently waiting on locks (Агенты, находящиеся в данный момент в состоянии блокировки)

Если это число больше нуля, то несколько приложений находится в состоянии ожидания блокировки. Для каждого из них вы сможете увидеть снимок текущего состояния приложения со статусом Lock-wait.

Lock waits (Число ожиданий блокировок) и Time database waited on locks (Время, затраченное базой данных на ожидания блокировок, мс)

Эти параметры говорят о том, какие последствия имели ожидания блокировок на протяжении промежутка измерений (промежуток измерений – это разность между значениями времени в приведенных ниже строках и соответствующими значениями в предыдущем снимке текущего состояния. В данном примере эта разность составила примерно 13 секунд).

Last reset timestamp (метка времени последнего перезапуска) = 08-15-2003 
14:30:30.648433
Snapshot timestamp (метка времени снимка текущего состояния) = 08-15-2003 
14:30:43.414574)

Помните, что время «waited on» - это общее время для всех подключенных приложений, поэтому значимость этого параметра зависит от числа пользователей. Объем усилий, которые следует уделить ожиданиям блокировок, зависит от числа ожиданий блокировки, от времени «waited on» и от требований ко времени отклика конкретного приложения. Если имеет место небольшое число ожиданий блокировки и с малой продолжительностью, а тестирующая среда имеет столько же подключений к базе данных, сколько их будет в рабочей среде, то тратить много времени на устранение блокировок нецелесообразно. С другой стороны, эти показатели могут указывать на наличие очевидной проблемы или, по крайней мере, на тенденцию, согласно которой конфликты блокировок будут приобретать все большую остроту по мере роста числа пользователей данного приложения.

Снимки текущего состояния приложения

Как и следует из его названия, каждый снимок текущего состояния приложения отражает текущее состояние подключенного приложения. Ниже приведена наиболее значимая информация для приложения, находящегося в ситуации ожидании блокировки, с соответствующими комментариями. Строки показаны в том порядке, в котором они появляются в снимке текущего состояния.

Application handle = 14
Application status = Lock-wait
Status change time = 08-15-2003 14:30:36.907312
Snapshot timestamp = 08-15-2003 14:30:43.414574
Time application waited on locks (ms) = 6507
Total time UOW waited on locks (ms) = 6507
UOW start timestamp = 08-15-2003 14:30:36.889356
Statement start timestamp = 08-15-2003 14:30:36.890986
Dynamic SQL statement text:
select * from org
ID of agent holding lock = 13
Application ID holding lock = *LOCAL.DB2.011905182946

Lock name =
0x02000200000000000000000054
Lock attributes = 0x00000000
Release flags = 0x00000001
Lock object type = Table
Lock mode = Exclusive Lock (X)
Lock mode requested = Intention Share Lock (IS)
Name of tablespace holding lock = USERSPACE1
Schema of table holding lock = WILKINS
Name of table holding lock = ORG
Lock wait start timestamp = 08-15-2003 14:30:36.907318

Дескриптор приложения (application handle) – это уникальный идентификатор подключения. Он соответствует номеру дескриптора, который выводит команда LIST APPLICATIONS, и вместе с другой информацией, которая здесь не показана, позволяет сопоставить приложение с вызвавшей его программой или с пользователем.

Параметры Application status и Status change time имеют тот же смысл, что и выходные параметры команды LIST APPLICATIONS, описанные выше.

Метки времени (timestamp) позволяют получить общую картину приложений, находящихся в состоянии ожидания:

  • Единица работы была запущена в момент времени 14:30:36.889356
  • Вскоре после этого, в 14:30:36.890986, началось исполнение ожидающего оператора (select * from org). В этом простом примере ожидающий оператор является первым в своей единице работы.
  • Единица работы получила свой текущий статус (Lock-wait) в момент времени 14:30:36.907312
  • Снимок текущего состояния был сделан в момент времени 14:30:43.414574
  • Параметры Time application waited on locks и Total time UOW waited on locks (в данном случае оба имеют значение 6,5 с) соответствуют разности между моментом изменения статуса и моментом данного снимка текущего состояния, поскольку текущее ожидание блокировки является первым в текущей единице работы (UOW), и на текущем подключении до этого не было каких-либо единиц работы. Ситуация выглядела бы по-другому, если бы в текущей единице работы существовало более раннее ожидание блокировки (т.е., значение параметра Total time UOW waited on locks было бы выше, чем время ожидания для текущего ожидания блокировки), или если существовали более ранние единицы работы (с ожиданием блокировок), выполняемые на том же самом подключении (время ожидания приложения было бы выше, чем время Total time UOW waited on locks).

Набор из двенадцати строк с отступом предоставляет информацию, связанную с ожиданием блокировки данного приложения:

  • Приложение (14) ждет окончания блокировки, установленной агентом (приложением) с идентификатором 13. Вы должны рассмотреть снимки текущего состояния приложение и блокировки для приложения 13 на предмет выявления возможной причины ожидания блокировки (этот вопрос будет более подробно рассмотрен позднее).
  • Lock name – это шестнадцатеричное число, которое является уникальным именем блокировки. Вам нет необходимости знать, из каких компонентов состоит имя блокировки (они не документированы), тем не менее в Приложении C приведено краткое описание этих компонентов.
  • Атрибуты блокировки и флаги освобождения описаны в файле sqlmon.h (в разделе sqllib/include), но обычно они не имеют большой ценности для изучения.
  • Остальные значения не нуждаются в разъяснениях.
  • Все значения в наборе, за исключением одного, описывают уже установленную блокировку. Этим исключением является параметр Lock mode requested (запрошенный режим блокировки). Он обозначает режим, затребованный приложением, для которого сделан данный снимок текущего состояния (14). В приведенном выше примере приложение 13 наложило X-блокировку на таблицу ORG, и приложение 14 неспособно наложить IS-блокировку, которая необходима ему для выполнения оператора SELECT с этой таблицей.

При анализе снимка текущего состояния приложения мы сосредоточились на приложениях, находящихся в состоянии ожидания блокировки. Не менее важны и снимки текущего состояния приложений, которых ждут другие приложения. Основная цель рассмотрения этих снимков текущего состояния состоит в следующем: определить, не удерживает ли приложение больше блокировок, чем следует, или слишком долго. Самые полезные элементы в этих снимках несколько отличаются от элементов, которые использовались в снимках приложений со статусом Lock-wait. Ниже показан фрагмент снимка текущего состояния приложения с дескриптором 13 – это приложение, которого ждет приложение с дескриптором 14 (см. выше): Соответствующие элементы описаны после данного фрагмента.

Application handle = 13
Application status = UOW Waiting
Status change time = 08-15-2003 14:30:34.954543
Application idle time = 9
Snapshot timestamp = 08-15-2003 14:30:43.414574
Locks held by application = 4
Lock waits since connect = 0
Time application waited on locks (ms) = 0
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of SQL requests since last commit = 2
UOW start timestamp = 08-15-2003 14:30:34.651601
UOW stop timestamp =
Most recent operation = Execute Immediate
Most recent operation start timestamp = 08-15-2003 14:30:34.652262
Most recent operation stop timestamp = 08-15-2003 14:30:34.954535

Dynamic SQL statement text (текст динамического SQL-оператора):
lock table org in exclusive mode
  • UOW start timestamp (Метка времени начала единицы работы). Разность между моментом (меткой времени) снимка текущего состояния (Snapshot timestamp) и этим параметром говорит о том, как долго функционировала данная единица работы UOW, и соответственно, на протяжении какого времени она удерживала блокировки. Очевидно, что чем дольше удерживаются блокировки, тем больше вероятность того, что другое приложение вступит с ними в конфликт. В этом примере единица работы начала работу в 14:30:34.651601, а снимок текущего состояния был сделан в 14:30:43.414574. Таким образом, к моменту снимка текущего состояния эта единица работы UOW проработала почти девять секунд.
  • Most recent operation stop timestamp (Самая последняя метка времени остановки операции). Разность между временной меткой снимка текущего состояния и этим параметром говорит о том, как долго единица работы находилась в состоянии простоя после завершения своего предыдущего SQL-оператора. Если эта разность относительно велика, то главная причина конфликта блокировок может состоять в слишком большом количестве времени, потраченном внутри приложения, в результате чего блокировки удерживались слишком долго. В этом примере самый последний оператор (lock table org in exclusive mode) завершился в 14:30:34.954535, т.е. почти за девять секунд до данного снимка текущего состояния. Эта девятисекундная разность также отражена в значении параметра Application idle time (продолжительность простоя приложения). В чувствительной к параллелизму ситуации девять секунд – это слишком много для того, чтобы реальное приложение могло находиться в состоянии простоя, удерживая при этом блокировки. Можно также сравнить метку времени Most recent operation stop timestamp = 14:30:34.954535 (остановка самой последней операции) с параметром Status change time (Момент изменения статуса) в снимке текущего состояния ждущего приложения (дескриптор 14; 14:30:36.907312) и увидеть, что оператор LOCK TABLE был выполнен приблизительно за две секунды до начала ожидания блокировки.
  • Сложность с обеими вышеупомянутыми метками времени состоит в том, что синхронизация снимков текущего состояния может маскировать определенную проблему: единица работы UOW может продолжать свою работу (и, соответственно, удерживать блокировки) на протяжении долгого времени после совершения снимка текущего состояния, выполняя множество других SQL-операторов или тратя много времени внутри самого приложения. В этой ситуации может быть полезно сделать еще один или несколько дополнительных снимков текущего состояния, однако гарантированный способ получения (для каждого оператора и для каждой единицы работы) моментов старта/останова и длительности функционирования заключается в использовании монитора событий для операторов (который будет описан позже). Еще одна возможность – использование трассировки с помощью интерфейса командной строки на клиентском компьютере; однако этот способ, не столь прямой, как мониторинг событий, не рассматривается в данной статье.
  • Number of SQL requests since last commit (Число SQL-запросов после последней фиксации). Данный параметр показывает объем работы, выполненный в данной единице работы UOW. Этот объем может оказаться слишком большим для одной единицы UOW. В этом примере число SQL-запросов равно всего двум, однако режим LOCK TABLE IN EXCLUSIVE MODE – это «смерть» для параллелизма. В приведенном выше фрагменте снимка текущего состояния приложения не показан такой элемент, как число обновленных, вставленных, удаленных и прочитанных строк, который также может служить хорошим индикатором работы, проделанной единицей UOW. В частности, если используется уровень изоляции CS, то число прочитанных строк говорит о том, сколько строк было заблокировано и затем освобождено до данного снимка текущего состояния (за исключением обновленных или удаленных строк, которые по-прежнему подвергаются X-блокировке).
  • Locks held by application (Блокировки, удерживаемые приложением). Это очевидный фактор при оценке того, насколько данное приложение ответственно за создание конфликтов блокировки. Тем не менее, если блокировки применены к таблице, они могут создавать состязательные проблемы даже при небольшом количестве таких блокировок, поэтому следует рассмотреть снимок текущего состояния блокировок для приложения, вызвавшего ожидание. В данном случае имеется всего четыре удерживаемых блокировки, однако одна из них вызвана оператором LOCK TABLE. Другая связанная с блокировкой информация, такая как Time application waited on locks (время ожидания блокировок приложением) и Lock escalations (количество эскалаций блокировок), позволяет определить, возникали ли у этого приложения проблемы блокировки, даже если они имели место не во время данного снимка текущего состояния.
  • The statement being executed (выполняемый SQL-оператор; идентифицируется строкой «Dynamic SQL statement text» или статическим пакетом SQL и идентификатором секции). В общем случае этому оператору не следует уделять слишком много внимания. С не меньшей вероятностью блокировку, вызвавшую конфликт, удерживает другой оператор, выполненный ранее в данной единице UOW. Тем не менее знание выполняемого в данный момент оператора помогает понять контекст проблемы. В этом примере источником проблемы действительно является оператор LOCK TABLE.

Выше были рассмотрены случаи ждущего приложения и вызвавшего ожидание приложения, однако достаточно часто одно и то же приложение одновременно выступает в обеих этих ролях. Например, приложение A может ждать приложения B, которое, в свою очередь, ждет приложения C. В этом случае приложение B одновременно является ожидающим и виновником ожидания, поэтому рекомендуется рассматривать снимок текущего состояния приложения с обеих точек зрения.

Еще одна ситуация, которую необходимо рассмотреть, связана с несколькими приложениями, каждое из которых находится в состоянии ожидания одно и того же приложения, и возможно, по причине одной и той же блокировки. Быстрый способ выявления этой ситуации состоит в применении команды grep или findstr к файлу снимка текущего состояния. Найдите строку «ID of agent holding lock» и посмотрите, не появляется ли один и тот же дескриптор приложения несколько раз. Очевидно, что такое приложение является хорошим объектом для начала вашего анализа.

Снимок текущего состояния блокировок

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

  1. Заголовок, идентифицирующий приложение и предоставляющий обобщенную информацию о блокировках, в особенности число удерживаемых блокировок и полное время ожидания блокировок.
  2. Если приложение имеет статус ожидания блокировки, то в его разделе будет присутствовать подраздел, описывающий это ожидание блокировки. Как правило, этот подраздел выглядит точно так же, как аналогичный подраздел в снимке текущего состояния приложения, за исключением редких случаев, когда информация о блокировке изменилась после совершения снимка текущего состояния приложения и до совершения снимка текущего состояния блокировок.
  3. Подробности относительно каждой блокировки, которая была успешно установлена и удерживается в данный момент времени. Эти подробности выгладят практически так же, как было показано для ожидания блокировки, за исключением отсутствия какой-либо информации, связанной с конфликтом блокировок. Наиболее важный среди дополнительных элементов информации – это параметр Lock Count (счетчик блокировок): обычно значение этого параметра равно единице, однако оно может быть и больше единицы, если одну и ту же блокировку удерживают разные приложения. (Например, два приложения могут каждое удерживать NS-блокировку на одной и той же строке). Кроме того, значение 255 представляет особое событие – блокировку, удерживаемую на протяжении длительного периода времени, например, вызванная оператором LOCK TABLE.

Очень важно иметь в виду, что используемый для приложения уровень изоляции в значительной степени определяет, какие блокировки удерживаются и соответственно будут отображены в снимке текущего состояния блокировок. Например, при использовании уровня изоляции UR не должно быть никаких блокировок чтения (NS или S), примененных к пользовательским таблицам, но то же самое приложение, исполняющееся с уровнем изоляции RR или RS, может иметь большое количество блокировок в пользовательских таблицах. (Блокировки таблиц каталога осуществляются, когда этого требует DB2, поэтому уровень изоляции приложения не влияет на них непосредственно). При использовании устанавливаемого по умолчанию уровня изоляции CS вы, как правило, для каждой таблицы в любой момент времени будете видеть не более одной удерживаемой блокировки чтения, однако вполне возможно, что до момента совершения данного снимка текущего состояния имело место большое количество установленных и высвобожденных блокировок чтения.

Если используется уровень изоляции RS или RR или в ходе транзакции выполняются операции обновления, вставки или удаления, может иметь место достаточное количество удерживаемых блокировок, благодаря чему вы сможете получить хорошее представление о результатах работы этой транзакции. Начните с нижней части снимка текущего состояния блокировок и продвигайтесь по направлению к его началу, поскольку информация о блокировках представляется в обратном хронологическом порядке. Обычно несколько первых (нижних) элементов представляют собой внутренние блокировки, относящиеся к используемому пакету приложений.

Выше них обычно следует IS- или IX-блокировка таблицы, после которой следует одна (или более) NS- или X-блокировка строки в этой таблице, а затем следует повторение этого шаблона для других таблиц. В качестве упражнения читателю предлагается посмотреть, как шаблон блокировок для оператора SELECT с соединениями (join) меняется в зависимости от типа плана доступа (nested loop join, merge scan или hash join). Поскольку приложение находится в состоянии ожидания блокировки, верхняя (конечная) блокировка на снимке является последней успешно установленной блокировкой. Во многих случаях это IS- или IX-блокировка, а ожидание блокировки возникло при последующей попытке блокировать какую-либо строку в той же самой таблице.

Вкратце рассмотрим внутренние блокировки, которые присутствуют в снимках текущего состояния блокировок, но не описаны подробно в руководствах по DB2. DB2 устанавливает эти блокировки по разнообразным причинам, но прежде всего применяет их к пакетам приложений и к выполняющимся разделам с целью предотвращения их сбрасывания другими подключениями. Ожидания блокировок, связанные с внутренними блокировками, встречаются достаточно редко и обычно только в том случае, если оператор DROP PACKAGE выполняется в неудачный момент. Таким образом, вам не следует особенно беспокоиться о внутренних блокировках.

Снимок текущего состояния динамических SQL-операторов

Этот снимок демонстрирует каждый динамический SQL-оператор, который в данный момент находится в кэше пакетов DB2, и предоставляет информацию об использовании и производительности по всем пользователям в совокупности за интервал подсчета. Основное назначение этого снимка при решении проблем блокировки – подтверждение предположения о том, что определенные SQL-операторы страдают от ожидания блокировок. В этом случае общая продолжительность работы оператора будет существенно превышать процессорное время, затраченное на его выполнение. Если деятельность этого оператора не связана с большим объемом ввода/вывода или сетевого трафика, то обычным объяснением указанной разности является наличие ожидания блокировки. Пример такой ситуации показан в Приложении A.

(3) Табличные функции, процедуры и представления снимка текущего состояния

Для получения информации, связанной с блокировками, можно воспользоваться различными административными функциями, процедурами и представлениями SQL, в том числе:

  • табличная функция snap_get_lock и представление snaplock
  • табличная функция snap_get_lockwait и представление snaplockwait
  • представление locks_held
  • представление lockwaits
  • процедура am_get_lock_chn_tb
  • процедура am_get_lock_chns
  • процедура am_get_lock_rpt

Обратите внимание, что в версии V9 вместо функции snapshot_lock используются табличная функция snap_get_lock и представление snaplock. Аналогично, вместо функции snapshot_lockwait используются табличная функция snap_get_lockwait и представление snaplockwait. Подробности относительно использования и выходной информации описаны в руководстве Administrative SQL Routines and views manual (Административные SQL-процедуры и представления DB2 V9).

Функция snap_get_lock формирует одну строку для каждой удерживаемой блокировки, а функция snap_get_lockwait – одну строку для каждого условия ожидания блокировки. Каждая строка содержит такие же данные, какие предоставляет в виде нескольких строк выходной информации команда «get snapshot for locks». В Приложении B приведены примеры скриптов с использованием функции snapshot_lockwait.

Важное примечание: Функция snap_get_lockwait не возвращает строки, если конфигурационному параметру DFT_MON_LOCK не присвоено значение ON.

Выбор между использованием команды get snapshot for locks, табличных функций (snap_get_lock и snap_get_lockwait) или административных представлений (snaplock и snaplockwait) – это в значительной степени вопрос личных предпочтений. Первый вариант упрощает получение "общей картины", а второй и третий варианты предоставляют результаты, с которым проще иметь дело с программной точки зрения. Эти табличные функции могут быть применены к любой базе данных, поскольку при использовании любой из этих табличных функций необходимо указывать имя базы данных. Представления – это объекты в базе данных, поэтому нет необходимости в указании имени базы данных, однако для обращения к какому-либо представлению необходимо иметь подключение к соответствующей базе данных. Использование этих представлений эквивалентно использованию команды "db2 get snapshot for locks on <имя базы данных>".

Представления snaplock и snaplockwait очень похожи на представления locks_held и lockwaits соответственно. Элементы Application name (appl_name) и auhorization id (AUTHID) доступны только в представлениях locks_held и lockwaits. Если интересуют вас эти элементы, то следует использовать представления locks_held/lockwaits.

Кроме того, в версии V9 встроены три хранимые процедуры, помогающие при мониторинге и исследовании блокировок. Процедура Am_get_lock_rpt предоставляет всю связанную с блокировками информацию для данного приложения в одном месте. Выходная информация этой процедуры разбита на 3 раздела: первый раздел содержит общую информацию о приложении, второй раздел содержит подробности по всем блокировкам, которые удерживает данное приложение, а третий раздел содержит подробности по всем блокировкам, которых ждет данное приложение. Хотя эта информация достаточно полезна, она не показывает, является ли данное приложение элементом сложной цепочки блокировок.

Процедура Am_get_lock_chn_tb and am_get_lock_chns предоставляют информацию о цепочках блокировок. По существу эти процедуры весьма похожи друг на друга, за исключением того, что первая предоставляет информацию в табличной форме, а вторая – в обычном текстовом формате. Рассмотрим сценарий, в котором приложение A ждет приложения B, приложение B ждет приложения C, а приложение C ждет приложения D. До версии V9 для выявления всей цепочки нужно было бы сделать несколько снимков текущего состояния и вручную объединить всю эту информацию. В версии V9 можно вызвать любую из указанных процедур с дескриптором одного из приложений в цепочке, в результате чего будет выведена вся цепочка. Соответствующий пример показан в разделе данной статьи под названием «Разрешение ожидания блокировки».

(4) Мониторинг событий

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

  • Предоставляет информация о потоке транзакций и о синхронизации, связанной с ожиданием блокировок. Хотя снимок текущего состояния в состоянии сообщить о том, что приложение находится в состоянии ожидания блокировки, обычно он не предоставляет достаточной информации о том, что данное приложение делало до ожидания блокировки, и что делало другое приложение (приложения), имеющее отношение к этому ожиданию блокировки. Монитор событий для операторов – это идеальный инструмент, поскольку он показывает активность каждой транзакции, включая начало и окончание каждого SQL-оператора. Каждый момент окончания отражает наличие задержек, вызванных ожиданием блокировки. Обратите внимание, что для каждого динамического SQL-оператора обычно существует несколько мониторов событий для операций различного типа: для оператора SELECT – события Prepare, Open, and Close; для оператора INSERT, UPDATE или DELETE – события Prepare и Execute. Применительно к проблемам ожидания блокировки наибольший интерес представляют события Close и Execute, поскольку их продолжительность (Exec Time:) покажет наличие задержек, обусловленных ожиданием блокировки при выполнении данного оператора.

    Применительно к OLTP-приложениям накладные расходы на выполнение мониторинга событий для операторов весьма высоки, а объемы выводимой информации огромны. Следует избегать чрезмерно длительной работы монитора событий. Даже несколько секунд его работы могут породить мегабайты выходной информации.

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

    Накладная нагрузка монитора событий для взаимоблокировок является очень небольшой, поэтому монитор этого типа можно применять на протяжении длительного периода времени. Вы можете использовать команду FLUSH EVENT MONITOR для сброса на диск всей информации по взаимоблокировке, собранной к моменту анализа (без прекращения работы монитора).

Существует два способа для получения выходной информации монитора событий в удобочитаемой форме:

  • Команда db2evmon, выводящая информацию в формате ASCII. Для подготовки к использованию команды db2evmon создайте монитор событий с опцией write to file (писать в файл). Команда db2evmon переформатирует полученные от монитора двоичные данные и запишет их в файл, в котором события будут представлены в хронологическом порядке.
  • Команда db2eva, обеспечивающая графический вывод. Для подготовки к использованию команды db2eva создайте монитор событий с опцией write to table (писать в таблицу). Команда db2eva предоставляет ту же самую информацию, что и команда db2evmon, но с возможностями сортировки, глубинного анализа и т.д.

Выбор используемого инструмента диктуется личными предпочтениями. Преимущество команды db2evmon состоит в том, что она упрощает выявление совпадений между событиями данного приложения и событиями какого-либо другого приложения. Тем не менее анализ выходной информации команды db2evmon может оказаться затруднительным, если вы не знакомы с такими командами, как grep и sort. Примеры использования команд grep и sort показаны в Приложении A.

(5) Журнал административных уведомлений (notify log)

Данный журнал используется для хранения полезной для клиентов диагностической информации, в отличие от журнала db2diag.log, информация которого предназначена для обслуживающего персонала IBM.

На платформах UNIX® журнал административных уведомлений представляет собой текстовый файл с именем <instance_name>.nfy. Этот файл находится в каталоге, путь к которому задается параметром DIAGPATH. На платформах Windows® сообщения административных уведомлений DB2 могут быть просмотрены с помощью инструмента Event Viewer (в журнале Application Log).

Чтобы гарантировать, что в журнале notify log будет регистрироваться вся доступная информация об эскалациях блокировок, необходимо присвоить конфигурационному параметру менеджера базы данных значение notifylevel = 4. (В Главе 5 документа Performance Guide подробно описано, какая информация доступна при более низких значениях параметра notifylevel).

Далее приводится два примера записей в журнале notify log применительно к эскалации блокировки. В этих примерах были опущены некоторые из менее значимых строк.

====================================================================
Event Type: Information
Description:
2003-09-10-15.32.23.058000 Instance:DB2 Node:000
PID:1156(db2syscs.exe) TID:2088 Appid:*LOCAL.DB2.011980193214
data management sqldEscalateLocks Probe:1 Database:SAMPLE
ADM5501I DB2 is performing lock escalation. The total number of
locks
currently held is "47", and the target number of locks to hold is
"23". The current statement being executed is "insert into org
select * from org".
====================================================================
Event Type: Warning
Description:
2003-09-10-15.32.23.098000 Instance:DB2 Node:000
PID:1156(db2syscs.exe) TID:2088 Appid:*LOCAL.DB2.011980193214
data management sqldEscalateLocks Probe:3 Database:SAMPLE
ADM5502W The escalation of "43" locks on table "WILKINS .ORG" to
lock intent "X" was successful.
====================================================================

(6) Инструмент Health Center

Инструмент Health Center в первую очередь предназначен для рабочих баз данных, однако вы можете воспользоваться им в процессе разработки приложения. В категории Application Concurrency (одновременная работа приложений) этот инструмент предоставляет следующие четыре индикатора: частота эскалации блокировок, использование списка блокировок, доля приложений, ждущих блокировок и частота взаимоблокировок. Инструмент Health Center позволяет задавать для указанных индикаторов пороговые значения для выдачи предупреждений и аварийных сообщений, активировать индикаторы и задавать действия, которые должны предприниматься при достижении пороговых значений, например, выполнение снимка текущего состояния.

(7) Средства операционной системы

Хотя инструменты операционной системы не дают непосредственной информации по проблемам блокировки, их использование на сервере базы данных иногда может предоставить полезные сведения. В частности, если загрузка центрального процессора неожиданно находится на низком уровне, то объяснение может состоять в том, что некоторые приложения находятся в состоянии простоя вследствие ожидания блокировки. Для просмотра загрузки центрального процессора в среде UNIX или Linux можно использовать команду vmstat, а в среде Windows – диспетчер задач.


Разрешение проблем блокировки

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

Сокращение количества возникающих блокировок

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

  1. Используйте более слабый уровень изоляции для пакетов или для выполняющихся приложений. По степени возрастания от самого слабого до самого сильного уровни изоляции распределяются следующим образом: UR, CS, RS, RR. Помните, что уровень изоляции должен быть выбран уже на этапе проектирования приложения, чтобы не поставить под угрозу его целостность (если уровень окажется слишком слаб для данного приложения). Чтобы установить уровень изоляции для приложений различного типа, можно использовать следующие методы:
    • Интерфейс JDBC: Connection.setTransactionIsolation()
    • Интерфейс CLI: SQLSetConnectAttr
    • Интерфейс CLP: Выполните команду "change isolation to <уровень>" (перед подключением к базе данных).
    • Статические пакеты: Используйте выражение "isolation" в командах BIND или PREP/PRECOMPILE.
    • Команда SET CURRENT ISOLATION <УРОВЕНЬ ИЗОЛЯЦИИ> может использоваться для задания уровня изоляции на сеансовом уровне. Уровень изоляции можно задавать в сессиях командного процессора CLP или внедрять непосредственно в приложения.
  2. Если вас по каким-либо причинам не устраивает вариант с изменением уровня изоляции для всего приложения или подключения, вы можете изменить его для определенных операторов. Уровень изоляции на уровне оператора задается с помощью выражения WITH. Следующие SQL-операторы поддерживают изоляцию на уровне операторов: SELECT, SELECT INTO, Searched DELETE, INSERT, Searched UPDATE и DECLARE CURSOR: Пример. "SELECT * FROM org WITH UR".
  3. С помощью таких механизмов, как создание индексов или добавление предикатов, можно внести в план доступа такие изменения, которые сократят количество строк, отыскиваемых запросами. Чем меньшее количество строк подпадет под условия поиска, тем меньше блокировок будет установлено. Полное описание этого процесса и возможностей для улучшения планов доступа выходит за рамки данного документа. Ограничимся перечислением типовых методов доступа к таблице, от наиболее предпочтительных до наименее предпочтительных с точки зрения блокировок:
    • Сканирование индекса с ключом начала и ключом остановки (в идеале ключи начала и остановки совпадают)
    • Сканирование индекса с ключом начала или ключом остановки
    • Полное сканирование индекса (без ключей начала/остановки)
    • Сканирование таблицы

Избежание эскалации блокировки

Для избежания эскалации блокировки можно использовать следующие два подхода: (a) сокращение количества возникающих блокировок; (b) увеличение ресурсов памяти, доступных для проведения блокировок.

Подход (a) является более предпочтительным, поскольку он экономит ресурсы центрального процессора, в результате чего может существенно повыситься производительность. Однако использование этого подхода может потребовать значительных усилий. Хотя любой из описанных выше подходов для сокращения количества возникающих блокировок способен помочь избежать эскалации, существует и другая возможность: принудительно заставить DB2 использовать блокировку таблицы вместо блокировки строк. Конечная цель состоит в том, чтобы воспользоваться преимуществами эскалации без ее реального осуществления. Однако, хотя блокировка таблицы и способна решить проблему эскалации, она сама может вызвать ожидания блокировки или взаимоблокировку (например, если на таблицу наложена S- или X-блокировка, никакая другая единица работы не сможет обновить никакую строку в этой таблице). По указанной причине блокировку таблицы обычно следует использовать только в одной из перечисленных ниже ситуаций.

  • Пакетная среда, в которой существует всего одна единица работы, обращающаяся к данной таблице (таблицам).
  • К таблице одновременно имеют доступ несколько подключений, но исключительно в режиме чтения.

Для непосредственной блокировки таблицы существуют следующие два способа:

  • Используйте оператор LOCK TABLE в той единице работы, в которой обычно удерживается большое количество блокировок строк. Использование оператора LOCK TABLE <таблица> IN SHARE MODE позволит другим подключениям обращаться к данной таблице в режиме «только для чтения». При использовании интерфейса CLI или CLP не забывайте, что по умолчанию фиксация транзакции осуществляется автоматически. Таким образом, если автоматическая фиксация не отключена, то блокировка таблицы с помощью оператора LOCK TABLE будет немедленно освобождена.
  • Используйте оператор ALTER TABLE <таблица> с параметром LOCKSIZE TABLE для принудительного установления блокировки таблицы при последующих обращениях к ней. Это условие будет действовать до следующего оператора ALTER TABLE с параметром LOCKSIZE ROW.

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

Подход (b), наращивающий доступные для проведения блокировок ресурсы памяти, реализуется увеличением значения конфигурационного параметра базы данных LOCKLIST (совокупная память для всех блокировок в базе данных) или MAXLOCKS (доступная для одиночной транзакции доля значения LOCKLIST, превышение которой вызывает эскалацию блокировки). С этим подходом связаны две проблемы – увеличение объема связанной памяти при увеличении параметра LOCKLIST и дополнительные затраты ресурсов центрального процессора на установление и высвобождение дополнительных блокировок строк. Тем не менее в некоторых случаях необходимо в обязательном порядке избежать эскалации вследствие ее отрицательного воздействия на уровень параллелизма. Если имеет место именно такой случай, вы можете использовать информацию журнала административных уведомлений (notify log) для соответствующей корректировки параметра LOCKLIST или MAXLOCKS. Весьма подробные инструкции приведены в разделе «Correcting lock escalation problems» (Устранение проблем эскалации блокировок) документа Performance Guide. При «тонкой настройке» параметра LOCKLIST можно оценить число блокировок, которые сможет поддерживать заданное значение этого параметра. Краткая сводка по потребностям блокировок:

На 32-разрядных платформах в версии v8 каждая блокировка требует:

  • 80 байтов для удержания блокировки на объекте, на который не наложено других блокировок.
  • 40 байтов для записи блокировки объекта, на который уже наложена одна блокировка.

На 32-разрядных платформах в версии v9 каждая блокировка требует:

  • 96 байтов для удержания блокировки на объекте, на который не наложено других блокировок.
  • 48 байтов для записи блокировки объекта, на который уже наложена одна блокировка.

На 64-разрядных платформах в версиях v8 и v9 каждая блокировка требует:

  • 128 байтов для удержания блокировки на объекте, на который не наложено других блокировок.
  • 64 байта для записи блокировки объекта, на который уже наложена одна блокировка.

Новая опция в версии V9: по умолчанию параметры LOCKLIST и MAXLOCK имеют значение AUTOMATIC. В этом случае компонент Self Tuning Memory Manager (STMM) автоматически настраивает разные «кучи» памяти (heap) посредством отбора памяти у недоиспользуемых куч и передачи этой памяти в максимально используемые кучи. STMM попытается предотвратить эскалацию блокировок посредством автоматической настройки параметров LOCKLIST и MAXLOCK, где это возможно. В отсутствие недоиспользуемых куч памяти компонент STMM может оказаться не в состоянии выделять больше памяти параметру LOCKLIST, но он по-прежнему сможет настроить параметр MAXLOCK таким образом, чтобы предотвратить эскалацию блокировок. Подробная информация по компоненту STMM приведена в документе Performance Guide.


Разрешение ожидания блокировки

Предположим, что в результате мониторинга или наблюдения за временем отклика возникло предположение, что ожидания блокировок представляют проблему. В качестве первых шагов выполните оператор LIST APPLICATIONS SHOW DETAIL и получите снимки текущего состояния базы данных с суммарными показателями за сравнительно длительный промежуток времени. Эти снимки позволят увидеть, происходят ли ожидания блокировок достаточно часто либо в достаточно крупных масштабах, чтобы вызвать беспокойство. Если в данный момент времени выполняется лишь небольшое количество приложений, будьте внимательны, чтобы не пропустить возможные в будущем проблемы с блокировками.

Если вы пришли к выводу, что ожидания блокировок действительно представляют проблему, вам следует получить достаточное количество снимков текущего состояния, чтобы зарегистрировать несколько действующих в текущий момент времени ожиданий блокировок. При этом вы можете использовать инструменты, описываемые в Приложении B. Проанализируйте снимки текущего состояния приложения и блокировок с целью выявления перечня различных проблем и установите приоритет этих проблем по таким факторам, как число вовлеченных приложений и продолжительность ожидания. Полезный прием – для приложений со статусом Lock-wait нарисуйте схему, связывающую дескрипторы этих приложений, в которой числа представляют собой дескрипторы приложений, а стрелка означает «в состоянии ожидания объекта…» (см. пример ниже).

13 --> 10 --> 5
11 --> 16 --> 10 --> 5
24 --> 30
35 --> 30
37 --> 30

Очевидно, что в данном случае имеют место две отдельные проблемы. Одна проблема связана с цепочкой приложений, ждущих приложений с дескрипторами 5 и 10, другая связана с ожиданием приложения с дескриптором 30. С помощью процедуры am_get_lock_chns подобный «граф» можно получить автоматически. Рассмотрим простой пример, в котором приложение с дескриптором 15 ждет приложения с дескриптором 11. Мы хотим увидеть, не присутствуют ли в этой цепочке блокировок какие-либо другие участники:


C:\>db2 call sysproc.am_get_lock_chns(15,?)
Value of output parameters
--------------------------
Parameter Name : LOCK_CHAINS
Parameter Value : >db2bp.exe (Agent ID: 15) (Auth ID: YASIR )
<db2bp.exe (Agent ID: 15) (Auth ID: YASIR )
  <db2bp.exe (Agent ID: 11) (Auth ID: YASIR )
    <db2bp.exe (Agent ID: 7) (Auth ID: YASIR )
Return Status = 0
			

Полученные результаты подтверждают, что в этой цепочке блокировок присутствуют и другие участники:

15 --> 11 --> 7

Приложение с дескриптором 15 ждет приложения с дескриптором 11, которое, в свою очередь, ждет приложения с дескриптором 7.

Если Вы хорошо знакомы с данным приложением, то, возможно, вы сможете выяснить проблему на основании показанных выше шагов. Однако в большинстве случаев потребуется несколько больший объем работы, чтобы выявить последовательность событий, приводящих к каждому ожиданию блокировки. Создайте и активируйте монитор для событий оператора и событий взаимоблокировки. Пока этот монитор событий будет находиться в активном состоянии, воспроизведите ситуацию (ситуации) ожидания блокировки и сделайте несколько снимков текущего состояния с действующими ожиданиями блокировки. Затем для каждого ожидания блокировки в снимке текущего состояния используйте метку времени этого снимка, дескриптор приложения и текст оператора для отыскания ждущего оператора в выходной информации монитора событий. (Предполагается, что ситуация ожидания блокировки уже разрешена; в противном случае оператор будет по-прежнему находиться в состоянии ожидания, поэтому для него событие Close или Execute будет отсутствовать. В этом случае вам следует найти для данного оператора предыдущее событие; как правило, это событие Open или Prepare.) Продвигайтесь назад от этого оператора, анализируя предыдущие события для ждущего приложения и приложения, которого оно ждет. Ограничьте эту процедуру предыдущим событием Commit (зафиксировать транзакцию) или Rollback (откатить транзакцию) для каждого приложения (поскольку любые блокировки, установленные до этого момента времени, уже будут освобождены). Эта процедура позволит выявить все операторы и таблицы, вовлеченные в ситуацию ожидания блокировки, и выяснить причины возникновения ожидания блокировки. Не забудьте использовать информацию из документа Performance Guide относительно блокировок, устанавливаемых различными операторами, и совместимости между этими блокировками

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

  • Избегайте эскалаций блокировок, используя описанные выше методы.
  • Выполняйте операцию фиксации транзакций чаще, но не слишком часто – в противном случае транзакции перестанут соответствовать определению бизнес-транзакций.
  • Задавайте в операторе SELECT параметр FOR UPDATE, где это окажется возможным. Параметр FOR UPDATE позволяет избежать проблем в случае, когда два разных приложения выбирают одну и ту же строку, а затем пытаются обновить ее. Без параметра FOR UPDATE возможна одна из следующих ситуаций (в зависимости от уровня изоляции):
    • Оба приложения способны читать и блокировать определенную строку, но ни одно из них не в состоянии обновить ее, поскольку она заблокирована другим приложением (X-блокировка конфликтует с NS-блокировкой).
    • Одно приложение читает строку и извлекает из нее соответствующие значения, однако прежде, чем оно обновит эту строку, другое приложение успевает обновить эту строку и совершить операцию фиксации. Затем первое приложение осуществляет обновление указанной строки с целью сохранения прочитанных значений, которые конфликтуют со значениями второго приложения.

При наличии параметра FOR UPDATE оператор SELECT применяет к строке U-блокировку, в результате чего второе приложение будет ждать до тех пор, пока первое приложение не освободит наложенную им блокировку.

  • Сделайте так, чтобы разные подключения обрабатывали наборы строк, различающиеся в максимально возможной степени. Избегайте использования «горячих точек» (таких, как строки, обновляемые в каждой транзакции) для хранения таких значений, как «последний использовавшийся идентификатор». Если такие горячие точки все же необходимы, отложите обновление до конца транзакции, а после обновления совершайте операцию фиксации как можно скорее.
  • Высвобождайте блокировки чтения, установленные при уровне изоляции RS или RR, с помощью оператора CLOSE с параметром WITH RELEASE <название курсора>, а не ждите их освобождения в результате зависящих от обстоятельств событий Commit или Rollback.

Разрешение взаимоблокировок

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

  • Для избежания ожиданий блокировки каждое подключенное приложение должно обрабатывать свой собственный набор строк. Если это не всегда возможно, то приложение должно быть спроектировано таким образом, что транзакции каждого типа обращались к разным таблицам в одинаковом порядке.
  • Тайм-аут блокировки не намного лучше, чем взаимная блокировка, поскольку любое из этих явлений вызывает откат транзакции. Тем не менее, если по каким-либо причинам необходимо минимизировать именно число взаимных блокировок, то эта задача может быть успешно решена. Для этого следует гарантировать, что тайм-аут блокировки будет в большинстве случаев наступать до того, как может быть обнаружена соответствующая возможность взаимной блокировки. Для этого задайте значение LOCKTIMEOUT намного меньше, чем значение DLCHKTIME (оба этих параметра являются конфигурационными параметрами базы данных; не забывайте, что параметр LOCKTIMEOUT задается в секундах, а параметр DLCHKTIME – в миллисекундах). Однако если параметр LOCKTIMEOUT не будет равен нулю, то детектор взаимной блокировки сможет активироваться сразу же после возникновения ситуации взаимной блокировки и обнаружить ее прежде, чем произойдет тайм-аут блокировки.
  • По возможности избегайте параллельных DDL-операций. Например, выполнение операторов DROP TABLE способно привести к большому объему обновлений каталога, поскольку может потребоваться удаление индексов таблицы, первичных ключей, контрольных ограничений и т.д., а также самой таблицы. Если другие DDL-операции удаляют или создают объекты, могут иметь место конфликты блокировки и даже спорадические взаимные блокировки.
  • У приложения, особенно многопоточного, может возникнуть ситуация взаимной блокировки, связанная с ожиданием блокировки DB2 и ожиданием стороннего ресурса, например, семафора. Например, подключение A может ждать блокировки, удерживаемой подключением B, а подключение B может ждать семафора, удерживаемого подключением A. Датчик взаимных блокировок DB2 не способен распознать и разрешить такую ситуацию, поэтому соответствующие защитные механизмы должны быть реализованы на этапе проектирования приложения.

Специфические проблемы блокировки в разделенной базе данных

С точки зрения блокировки разделенная база данных может рассматриваться как набор почти независимых баз данных, в каждой из которых блокировки устанавливаются как в обычной, неразделенной базе данных. В этом случае необходимо разрешить одну специфическую проблему. Если в разделе 1 приложение A ждет окончания блокировки, удерживаемой приложением B, а в разделе 2 приложение B ждет окончания блокировки, удерживаемой приложением A, то датчики взаимной блокировки, исполняющиеся независимо в каждом разделе, никогда не смогут обнаружить эту ситуацию взаимной блокировки. Для разрешения этой проблемы предназначен глобальный детектор взаимных блокировок. В разделенной базе данных каждый раздел посылает графы блокировки в раздел базы данных, который содержит представления системного каталога. Обнаружение глобальной взаимной блокировки производится в этом разделе, однако взаимная блокировка помечается флагом только после того, как глобальный датчик взаимной блокировки сработает второй раз после наступления взаимной блокировки.

Как и в случае любых конфигурационных параметров базы данных, удостоверьтесь в том, что связанные с блокировками параметры имеют одинаковые значения в каждом разделе. С помощью команды db2_all вы сможете выполнить команду db2 update db cfg для каждого раздела.

Переменные реестра, имеющие отношение к блокировке

Ниже приводится список переменных системного реестра, которые могут улучшить параллелизм, если измененное поведение будет по-прежнему отвечать вашим бизнес-требованиям. Для активации перечисленных ниже переменных системного реестра выполните команду

db2set <переменная реестра>=on

и затем перезапустите экземпляр базы данных (db2stop/db2start).

  1. DB2_SKIPINSERTED

    Значение по умолчанию «off». Предположим, что сканирование таблицы производится при уровне изоляции CS или RS. Если в этом случае курсор захватит строку, которая в данный момент вставляется, но пока не зафиксирована, то сканирующее приложение получит статус Lock-wait и будет ждать до тех пор, пока для этой строки не произойдет событие Commit (зафиксировать транзакцию) или Rollback (откатить транзакцию). Существует большой класс приложений, которые не нуждаются в подобном поведении. В этом случае нет смысла ждать до момента фиксации вставок. В интересах таких приложений вы можете активировать эту переменную реестра. Если эта переменная активирована, то курсор «перескакивает» через вставляемую строку без ожидания и не представляет пропущенные строки в результирующий набор.

  2. DB2_EVALUNCOMITTED

    Если для вашего приложения допускается оценка данных в незафиксированных строках, то вы можете улучшить параллелизм с помощью этой переменной системного реестра. Если сканирование производится при уровне изоляции CS или RS, та каждая строка блокируется, а затем оценивается на соответствие. Если на момент сканирования какая-либо строка является заблокированной, то сканирующее приложение переходит в состояние ожидания блокировки. После активации данной опции приложение сможет оценивать незафиксированные данные без необходимости ожидания освобождения блокировки. Рассмотрим столбец ORDERID, в котором значение одной из строк было обновлено с 20 на 5, но пока не зафиксировано. Имеет место сканирование следующего вида: «select ... where orderid > 10». При параметрах «по умолчанию» процесс сканирования будет ждать фиксации, после чего будет оценивать зафиксированное значение. Если данная переменная реестра активирована, то процесс сканирования перескочит через эту строку, поскольку она не удовлетворяет предикату. Теперь предположим, что строка в столбце ORDERID была обновлена на 15 и не зафиксирована. В этом случае процесс сканирования по-прежнему будет ждать фиксации этой строки, поскольку значение 15 удовлетворяет предикату и подлежит включению в результирующий набор.

  3. DB2_SKIPDELETED

    Если сканирование производится при уровне изоляции CS или RS, то курсор ждет, чтобы наложить блокировку на любую незафиксированную удаленную строку. Если данная переменная реестра активирована, то курсор будет перескакивать через незафиксированные удаленные строки. Эти незафиксированные удаленные строки не будут показаны в результирующем наборе. Если впоследствии будет произведен откат удалений и осуществлена другая процедура сканирования, то отсутствовавшие в первом проходе строки будут показаны в результирующем наборе. Снова напоминаем, что вы должны быть уверены в том, что такое поведение является приемлемым для вашей среды.


Заключение

Мы надеемся, что в результате прочтения этого документа вы получили хорошее представление о выявлении и разрешении большинства проблем блокировки в DB2. Следите за объявлениями: в ближайшее время будут реализованы дальнейшие усовершенствования продукта DB2 в области параллелизма.


Приложение A - Конкретный пример

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

Проблема ожидания блокировки: Шаг 1: = Почему время отклика оказалось настолько велико?

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

Итак, была обнаружена проблема ожидания блокировок, последующее разрешение которой осуществлялось в рамках общей задачи уменьшения времени отклика. Первым признаком того, что ожидания блокировок создают проблему, явились выходные данные команды vmstat, которые демонстрировали очень низкое потребление ресурсов центрального процессора при сравнительно малом времени ожидания ввода/вывода (подробнее об этом – ниже). Подтверждение ожидания блокировки вскоре было получено с помощью монитора снимков текущего состояния ("get snapshot for all on <база данных>"). Ниже показан относящийся ко всей базе данных раздел снимка текущего состояния, соответствующий счетному интервалу примерно 8,4 мин.

Locks held currently = 934
Lock waits = 438
Time database waited on locks (ms) = 2770173
Lock list memory in use (Bytes) = 82692
Agents currently waiting on locks = 17

На момент совершения данного снимка текущего состояния к базе данных было подключено 37 приложений, 17 из которых находились в состоянии ожидания блокировки. Более того, среднее время ожидание блокировки в пересчете на приложение за эти 8,4 минут составило (2770 с/60 с)/37 приложений = 1,24 мин. Если вычесть время, на протяжении которого каждое приложение было неактивно, то время ожидания блокировок на промежутке активности приложений окажется весьма большим – примерно 50%.

Дополнительное подтверждение наличия проблемы ожидания блокировки поступило из двух других источников:

(a) Ниже показана информация снимка текущего состояния для одного динамического SQL-запроса, который использует представление VR_VIEW. Счетный интервал также был равен 8,4 мин. Обратите внимание, что полное время исполнения составило 2511 с на 1150 исполнений оператора (в среднем примерно 2 с на исполнение), при этом на все 1150 операции было израсходовано менее 1,5 с процессорного времени. Итак, что же делала DB2 все это время? Наиболее правдоподобное объяснение – ожидание блокировок, даже без учета показанной выше информации по блокировкам.

Number of executions = 1150
Number of compilations = 0
Worst preparation time (ms) = 81
Best preparation time (ms) = 81
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 101121
Internal rows updated = 0
Rows written = 0
Statement sorts = 3405
Total execution time (sec.ms) = 2511.616892
Total user cpu time (sec.ms) = 1.340000
Total system cpu time (sec.ms) = 0.080000
Statement text = SELECT * FROM VR_VIEW WHERE DATAVALUE = ? AND
P_ID = ?
ORDER BY A_ID

(b) Ниже приведена информация от монитора событий (db2evmon) для однократного исполнения одного и того же оператора, для которого мы только что рассмотрели суммарную информацию в снимке текущего состояния динамического SQL-запроса. Обратите внимание, что для одного и того же исполнения данного оператора имели место два предшествующих события (Prepare и Open), однако их времена исполнения были пренебрежимо малы, поэтому эти события неинтересны с точки зрения ожидания блокировки. Строка «Exec Time:» показывает, что время исполнения оператора составило 6,28 с, при этом было израсходовано всего 0,01 с процессорного времени. Мы снова можем задать вопрос, почему исполнение оператора заняло так много времени, и снова единственным объяснением будет ожидание блокировки.

4083) Statement Event ...
Appl Handle: 10
Appl Id: 0A0A1071.DDA4.030429211640
Appl Seq number: 0001
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Close
Section : 4
Creator : NULLID
Package : SQLLF000
Cursor : SQLCUR4
Cursor was blocking: FALSE
Text : SELECT * FROM VR_VIEW
WHERE DATAVALUE = ? AND P_ID = ?
ORDER BY A_ID
-------------------------------------------
Start Time: 04-29-2003 16:12:20.951335
Stop Time: 04-29-2003 16:12:27.238560
Exec Time: 6.287225 seconds
Number of Agents created: 1
User CPU: 0.010000 seconds
System CPU: 0.000000 seconds
Fetch Count: 25
Sorts: 1
Total sort time: 0
Sort overflows: 0
Rows read: 200
Rows written: 0
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

С целью отыскания вышеупомянутых проблемных операторов мы использовали следующие команды, которые выводят на печать 25 максимальных значений общего времени исполнения (из снимка текущего состояния Dynamic SQL Snapshot) и 10 максимальных значений времени отдельных исполнений (из выходной информации db2evmon). Наличие этих данных существенно упрощает отыскание нужного блока информации для заданного оператора.

grep "Total execution time" snapshot_file.txt      | sort -k 6,6rn | head -n 25
grep "Exec Time:"           event_monitor_file.txt | sort -k 3,3rn | head -n 10

(В качестве еще одного источника подтверждающей информации можно было бы использовать команду LIST APPLICATIONS SHOW DETAIL.)

Проблема ожидания блокировки: Шаг 2: = Где происходило ожидание блокировки и почему?

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

(a) Метод «грубой силы» – очень частое совершение снимков текущего состояния (примерно каждые пять секунд) с последующим просмотром файлов, полученных в момент фактического ожидания блокировок. К примеру, можно сделать снимки текущего состояния, а затем выполнить следующую команду:

grep "Agents currently waiting on locks" snapshot_file_*.txt

в результате выполнения которой могут быть получены следующие строки:

snapshot_file_1.txt:Agents currently waiting on locks = 0
snapshot_file_2.txt:Agents currently waiting on locks = 17
snapshot_file_3.txt:Agents currently waiting on locks = 2

Очевидно, что дальнейшие усилия необходимо сосредоточить на файле snapshot_file_2.txt.

(b) Более систематический подход основан на многократном использовании команды «db2 list applications show detail | grep -i lock-wait | wc –l», которая выводит на печать число приложений со статусом Lock-wait (находящихся в состоянии ожидания блокировки). По результатам выполнения этой команды можно принимать решение о совершении снимка текущего состояния, если число таких приложений больше нуля. Для автоматизации указанного процесса был создан скрипт, показанный ниже в Приложении B.

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

(i) Нахождение приложения в состоянии ожидания. Для этого отыщите значение Lock-wait, которое может находиться в начале снимка текущего состояния приложения, как показано ниже.

Application Snapshot (Снимок текущего состояния приложения)
Application handle = 21
Application status = Lock-wait

Продвигаясь к концу снимка текущего состояния приложения, мы увидим следующие строки:

Dynamic SQL statement text:
SELECT * FROM VR_VIEW WHERE DATAVALUE = ? AND P_ID = ? ORDER BY A_ID
Agent process/thread ID = 933992
Agent process/thread ID = 933992
ID of agent holding lock = 265
Application ID holding lock = 0A0A1071.AEBC.030428180321
Lock object type = Row
Lock mode = Exclusive Lock (X)
Lock mode requested = Next Key Share (NS)
Name of tablespace holding lock = USERSPACE1
Schema of table holding lock = SOURCE
Name of table holding lock = OBJECTS
Lock wait start timestamp = 04-28-2003 13:46:44.571922
Lock is a result of escalation = NO

В показанном выше фрагменте присутствует масса полезной информации. Во-первых, присутствует текст оператора, исполнявшегося в том момент, когда произошло ожидание блокировки. Оказывается, что это тот же самый оператор, который мы уже видели раньше. Кроме того, мы видим, что этот оператор пытался наложить NS-блокировку на строку в таблице OBJECTS, однако эта блокировка не могла быть установлена, поскольку приложение с дескриптором 265 удерживало X-блокировку на той же самой строке. (Напоминаем, что подробности относительно блокировок и конфликтов между различными режимами блокировки продукта DB2 можно найти в Руководстве администратора)

На ранних стадиях нашего исследования ожиданий блокировки мы обнаружили, что почти все ожидания блокировок имеют сходные характеристики: необходимость наложения NS-блокировки на строку OBJECTS вступала в конфликт с X-блокировкой, удерживаемой одной транзакцией, чем и были вызваны практически все ожидания блокировок. Таким образом, мы получили высокоуровневое объяснение ситуации, но не знали точных причин возникновения ожиданий. Теперь следовало получить дополнительную информацию о «плохой» транзакции, которую «все ждали», и о том, что делали ждущие транзакции.

Цепочка блокировок может включать большое число приложений. В рассматриваемом случае несколько приложений находились в состоянии ожидания одного приложения. Как только вы выясните дескриптор одного из приложений в цепочке блокировки, то остальные элементы в цепочке блокировок могут быть легко идентифицированы с помощью команды sysproc.am_get_lock_chns. Использование этой процедуры подробно описывалось в разделе «Разрешение ожидания блокировки».

Плохая транзакция

Для выявления «плохой» транзакции, вызвавшей множество ожиданий, мы использовали значение дескриптора из показанной выше информации об ожидании блокировки (265) и нашли снимок текущего состояния приложения для этого дескриптора. Ниже показано несколько особенно интересных строк, извлеченных из этого снимка текущего состояния:

UOW start timestamp = 04-28-2003 13:46:27.646738
Snapshot timestamp = 04-28-2003 13:46:47.138356
Application status = UOW Waiting
Locks held by application = 694
Dynamic SQL statement text:
UPDATE OFFERS SET STATUS=?, [другие столбцы опущены] ,
TDATIMEUPDATED = (SELECT DBDATE FROM DATABASEDATE) WHERE OFFER_ID=?

Мы видим, что текущая транзакция началась в 1:46:27 и что снимок текущего состояния была сделан спустя 20 с, а именно в 1:46:47. Рассматриваемое приложение имеет статус "UOW Waiting". Это означает, что DB2 ждет, когда это приложение пришлет дополнительный объем работы. Предыдущий выполненный оператор осуществил обновление таблицы OFFERS, поэтому не представляет особого интереса, поскольку ожидание блокировки происходит в таблице OBJECTS. Тем не менее эта информация позволяет выяснить, что делала данная транзакция. Самое плохое состоит в том, что приложение удерживает 694 блокировки, и, поскольку транзакция исполнялась на протяжении 20 с, эти блокировки уже удерживаются в среднем по 10 с, а мы не знаем, когда эта транзакция будет наконец зафиксирована и высвободит эти блокировки.

Мы можем узнать об этой транзакции больше, посмотрев на ее снимок текущего состояния блокировок. В случае этой плохой транзакции мы увидели последовательность из девяти заблокированных таблиц, в некоторых из которых на сотни строк была наложена X-блокировка:

(четыре таблицы со сравнительно небольшим числом заблокированных строк в каждой)
OBJECTS (X-блокировка 1 строки)
OFFERS (множество X-блокировок строк)
(три таблицы, в каждый из которых на момент снимка текущего состояния на сотни строк была наложена X-блокировка)

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

За все время наблюдений наибольшее число зарегистрированных блокировок, удерживаемых единственным приложением, оказалось огромным – 16563. Разработчик расценил это как влияние второй транзакции. Эта транзакция использовала другой шаблон наложения блокировок, при этом некоторые таблицы имели более 1000 X-блокировок:

(две таблицы со сравнительно небольшим числом заблокированных строк в каждой)
OBJECTS (X-блокировка 1 строки)
(две таблицы, в каждый из которых на тысячи строк наложена X-блокировка)
(две таблицы, в каждой из которых заблокировано по несколько строк)
(одна таблица, в которой на момент снимка текущего состояния на сотни строк была наложена X-блокировка)

Каждая из этих транзакций осуществляет обновление строки в таблице OBJECTS на относительно раннем этапе исполнения, в результате чего масса времени тратится на удержание блокировки строк в таблице OBJECTS до момента, когда последующая фиксация в конце концов освободит эти блокировки. Разрешение этой ситуации описывается далее на Шаге 3.

Итак, теперь мы увидели, что в этих плохих транзакциях осуществляется блокировка строк в таблице OBJECTS, после чего эти блокировки удерживаются на протяжении длительных периодов времени. Но почему эти блокировки влияют на другие транзакции? Разве каждая транзакция не должна иметь дело с независимыми строками таблицы OBJECTS и избегать таким образом конфликта блокировки? Ответ на этот вопрос был получен при первом выявлении ждущих операторов SELECT, которые, как указывалось выше, почти всегда имели следующий вид:

SELECT * FROM VR_VIEW WHERE DATAVALUE = ? AND P_ID = ? ORDER BY A_ID

Этот запрос осуществляет соединение четырех таблиц: INFO, OBJECTS, PROPERTIES и PRINCIPALS. Чтобы узнать, какую роль играет таблица OBJECTS в этом запросе, мы применили к нему команду Explain, которая показала, что выполнялось сканирование таблицы OBJECTS. При таких сканированиях опрашивалась каждая строка таблицы OBJECTS, что неизбежно приводило к следующим последствиям: если на какую-либо строку была наложена X-блокировка, то оператор SELECT ждал, пока эта блокировка не будет освобождена, что, как мы видели, могло произойти много секунд спустя.

Проблема ожидания блокировки: Шаг 3: = Разрешение проблемы

Итак, мы убедились в том, основная проблема блокировки имеет два аспекта: (i) работающие на протяжении длительного времени транзакции, удерживающие X-блокировки на строках таблицы OBJECTS, и (ii) запросы, осуществляющие сканирование таблицы, которое блокировалось упомянутыми X-блокировками. Эти аспекты обуславливали применение разных решений, и мы реализовали каждое из них.

Возможно, дальнейшее исследование показало бы, что для достижения приемлемой производительности вполне достаточно одного решения, однако желательно было реализовать оба решения.

Применительно к X-блокировкам строк в таблице OBJECTS разработчик смог изменить код таким образом, чтобы обновление OBJECTS осуществлялось в самом конце каждой транзакции. В результате X-блокировки в таблице OBJECTS удерживались на протяжении очень коротких промежутков времени, благодаря чему конфликты с операторами SELECT были сведены к минимуму.

Применительно к сканированию таблицы OBJECTS необходимо сделать следующее пояснение. Часто сканирование таблицы применяется в том случае, когда отсутствует какой-либо подходящий индекс. В случае с таблицей OBJECTS имела место несколько другая ситуация:

(a) Обращение к таблице PROPERTIES осуществлялось с помощью неоптимального индекса. Мы сформировали новый индекс, после чего этот индекс был выбран для последующего использования.

(b) Таблица INFO подлежала сканированию, поэтому мы изменили ее свойства на VOLATILE (чтобы уведомить оптимизатор о том, что размеры таблицы могли увеличиться по сравнению с предыдущим исполнением команды Runstats). После этого начал использоваться индексный доступ.

(c) Для возврата к согласованной исходной точке перед прогонами тестов производительности приложений обычно выполнялось восстановление тестовой базы данных. Мы обнаружили, что, согласно статистическим данным, некоторые таблицы считались пустыми (CARD=0 в SYSCAT.TABLES), что делало сканирование этих таблиц весьма привлекательной операцией для оптимизатора (это приводило к усиливающимся проблемам по мере того, как размеры этих таблиц увеличивались с течением времени). К концу анализа мы создали новую резервную копию, которая включала все сделанные нами изменения, и обновили статистику с целью учета непустых таблиц.

Реализовав пункты (a) - (c), мы изменили метод доступа к таблице OBJECTS на поиск по первичному ключу (A_ID). Поскольку теперь нужно было обращаться только к одной строке в таблице OBJECTS, возможности конфликта блокировок были устранены почти полностью.

Другие проблемы ожидания блокировок

Приведенные выше рассуждения концентрировались на таблице OBJECTS и на ожиданиях блокировок, с которыми сталкивался оператор SELECT * FROM VR_VIEW при работе с этой таблицей, поскольку это, безусловно, было основной проблемой блокировки. Тем не менее, мы выявили и другие проблемы ожидания блокировок, после чего исследовали их с помощью описанного выше подхода. Большинство этих проблем было решено посредством изменения индекса. Обратите внимание, что в сканирование таблиц были вовлечены не все проблемные планы доступа. В некоторых случаях проводилось сканирование индекса, однако обращение осуществлялось или ко всему индексу, или к его достаточно большому подмножеству, что повышало вероятность конфликтов с X-блокировками. Иногда единственно возможным выходом из этой ситуации была перезапись запроса, поскольку он, возможно, являлся недостаточно селективным.


Приложение B - Типовые скрипты

В качестве примера ниже показано несколько UNIX-скриптов для решения различных задач, связанных с блокировками. На случай, если вы не знакомы с UNIX-скриптами, для третьего UNIX-скрипта приведен близкий ему эквивалент для среды Windows, позволяющий получить общее представление о возможностях скриптов.

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

#!/bin/ksh
# loop (until Ctrl-c) and take a snapshot if there are applications in lock
wait
# Arguments: (1) database name
# (2) number of secs. between checks of application status
# (3) number of secs. to wait before taking snapshot (should usually
be 0)
# Snapshot files are named like this: snap_0618-194724.out (MMDD-HHMMSS)
while (true) do
WAITERS=`db2 list applications for db $1 show detail | grep "Lock-wait" | wc -l`
if [ $WAITERS -ge 1 ]
then
SNAPFILE=snap_`date +"%m%d-%H%M%S"`.out
db2 +o update monitor switches using lock on statement on uow on
sleep $3
db2 get snapshot for locks on $1 > $SNAPFILE
db2 get snapshot for applications on $1 > $SNAPFILE
db2 +o update monitor switches using lock off statement off uow off
echo Number of applications in lock-wait: $WAITERS -- see $SNAPFILE
else
echo Number of applications in lock-wait: $WAITERS
fi
sleep $2
done

Этот скрипт командной оболочки UNIX показывает, каким образом с помощью функции snap_get_lockwait можно выявить и принудительно завершить приложения, вызывающие ожидания блокировки. Автоматическое принудительное завершение приложения не всегда является наилучшим вариантом. Данный скрипт – это не более чем простой пример того, как можно использовать функцию snap_get_lockwait.

#!/bin/ksh
# force applications holding lock(s) that lock-wait applications are waiting for
# Argument: database name
# DBM lock switch must be on for snap_get_lockwait to return rows; SYSADM authority is
# required to set it here!
db2 update dbm cfg using dft_mon_lock on
db2 connect to $1
APPLIST=`db2 -x "select agent_id_holding_lk from table(snap_get_lockwait( '$1', -1)) 
         \ as slw"`
for APP in $APPLIST
do
db2 -v "force applications ($APP)"
done
db2 terminate

Этот скрипт командной оболочки UNIX можно запускать в цикле (вплоть до нажатия комбинации клавиш Ctrl-C). Для каждого конфликта блокировки он показывает дескрипторы вовлеченных приложений, название таблицы, и режимы запрошенных и удерживаемых блокировок. Цель этого скрипта состоит в том, чтобы быстро получить представление о конфликтах блокировки за определенное время, причем в более удобочитаемой форме, чем совокупность выходных столбцов команды snap_get_lockwait.

После определенной доработки этот скрипт может осуществлять вывод информации только после обнаружения ожиданий блокировок (см. первый скрипт выше).

#!/bin/ksh
# loop forever and show subset of information from snap_get_lockwait
# Arguments: (1) database name
# (2) number of secs. to sleep between displays
# DBM lock switch must be on for snap_get_lockwait to return rows db2 update dbm cfg
         using dft_mon_lock on
db2 connect to $1
while (true) do
db2 "select agent_id as WAITING_FOR_LOCK, agent_id_holding_lk as HOLDING_LOCK,
         table_name, cast(lock_mode_requested as smallint) as WANTED,
		 cast(lock_mode as smallint) 
		 as HELD from table(snap_get_lockwait( '$1', -1)) as slw"
sleep $2
done
db2 terminate

Пример выходной информации (при каждом прохождении цикла будет отображаться заголовок, а также нулевое или отличное от нуля число строк):

   WAITING_FOR_LOCK     HOLDING_LOCK     TABLE_NAME     WANTED HELD
 -------------------- ---------------- -------------- ---------------
          575                  72 ORG        1
          564                  72 ORG        1
2 record(s) selected (выбрано две записи).

Ниже приведена версия предыдущего скрипта в виде командного файла Windows. Основные различия состоят в том, что в командах Connect и Select переменная "1$" была заменена переменной "%1%". Кроме того, поскольку в Windows нет команды sleep, вместо нее была использована команда pause (другими словами, для запуска очередного прохода цикла пользователю необходимо каждый раз нажимать клавишу Enter).

@echo off
rem loop forever and show subset of information from snap_get_lockwait
rem Argument: (1) database name
rem DBM lock switch must be on for snap_get_lockwait to return rows
@echo on
db2 update dbm cfg using dft_mon_lock on
db2 connect to %1%
@echo off
:startloop
db2 "select agent_id as WAITING_FOR_LOCK, agent_id_holding_lk as HOLDING_LOCK, table_name,
         cast(lock_mode_requested as smallint) as WANTED, cast(lock_mode as smallint)
		 as HELD from table(snap_get_lockwait( '%1%', -1))
as slw"
pause
goto startloop
db2 terminate

Приложение C - Пример вывода снимка текущего состояния блокировок

Следующий пример снимка текущего состояния блокировок соответствует ситуации, когда каждая строка в таблице блокируется в процессе сканирования таблицы, осуществляемого при уровне изоляции RS. Примечания:

  • Как обычно, блокировки представлены в обратном хронологическом порядке, т.е. от самых новых для самых старых. Этот снимок может претерпеть изменения в случае эскалации или наращивания блокировок.
  • Первые две наложенные блокировки – это внутренние блокировки пакета, относящиеся к выполняемому оператору.
  • Следующая блокировка, IS-блокировка таблицы, была наложена до обращения к строкам таблицы.
  • Все остальные блокировки являются NS-блокировками строк таблицы. Первые три элемента в шестнадцатеричном имени блокировки строки, представляющие собой идентификатор табличной области (2, для userspace1), идентификатор таблицы (2, значение syscat.tables.tableid для wilkins.org) и идентификатор строки (RID), отображаются на имя объекта блокировки. В приведенном ниже примере высшее значение имени объекта блокировки (11) получено отображением шестнадцатеричного имени 0x000B00. Еще один пример. 0x000001 – это имя блокировки, которое было бы представлено как имя объекта блокировки 256. Этого вполне достаточно, имя блокировки не имеет большого значения для дальнейшего понимания: информация о самых важных компонентах отображается отдельно. Полезно знать, что, если в таблице заблокированы следующие друг за другом строки, то обычно они имеют и последовательные имена объектов блокировки.
  • Приведенный ниже снимок текущего состояния блокировок был получен посредством выполнения следующих команд DB2 CLP:
    db2 connect reset
    db2 change isolation to rs
    db2 connect to sample
    db2 +c select * from org
    db2 get snapshot for locks on sample

(Снимок текущего состояния блокировок базы данных)

Database name                       = SAMPLE
Database path                       = D:\DB2\NODE0000\SQL00002\
Input database alias                = SAMPLE
Locks held                          = 11
Applications currently connected    = 1
Agents currently waiting on locks   = 0
Snapshot timestamp                  = 08-21-2003 12:19:06.468584

Application handle                  = 44
Application ID                      = *LOCAL.DB2.00B7C1161826
Sequence number                     = 0001
Application name                    = db2bp.exe
CONNECT Authorization ID            = WILKINS
Application status                  = UOW Waiting
Status change time                  = Not Collected
Application code page               = 1252
Locks held                          = 11
Total wait time (ms)                = 0

List Of Locks
Lock Name                           = 0x020002000B0000000000000052
Lock Attributes                     = 0x00000000
Release Flags                       = 0x00000001
Lock Count                          = 1
Hold Count                          = 0

Lock Object Name                    = 11
Object Type                         = Row
Tablespace Name                     = USERSPACE1
Table Schema                        = WILKINS
Table Name                          = ORG
Mode                                = NS

Lock Name                           = 0x020002000A0000000000000052
Lock Attributes                     = 0x00000000
Release Flags                       = 0x00000001
Lock Count                          = 1
Hold Count                          = 0
Lock Object Name                    = 10
Object Type                         = Row
Tablespace Name                     = USERSPACE1
Table Schema                        = WILKINS
Table Name                          = ORG
Mode                                = NS

***** пропущены строки для имен объектов блокировки ORG с 6 по 9 *****

Lock Name                           = 0x02000200050000000000000052
Lock Attributes                     = 0x00000000
Release Flags                       = 0x00000001
Lock Count                          = 1
Hold Count                          = 0
Lock Object Name                    = 5
Object Type                         = Row
Tablespace Name                     = USERSPACE1
Table Schema                        = WILKINS
Table Name                          = ORG
Mode                                = NS

Lock Name                           = 0x02000200040000000000000052
Lock Attributes                     = 0x00000000
Release Flags                       = 0x00000001
Lock Count                          = 1
Hold Count                          = 0
Lock Object Name                    = 4
Object Type                         = Row
Tablespace Name                     = USERSPACE1
Table Schema                        = WILKINS
Table Name                          = ORG
Mode                                = NS

Lock Name                           = 0x02000200000000000000000054
Lock Attributes                     = 0x00000000
Release Flags                       = 0x40000001
Lock Count                          = 1
Hold Count                          = 0
Lock Object Name                    = 2
Object Type                         = Table
Tablespace Name                     = USERSPACE1
Table Schema                        = WILKINS
Table Name                          = ORG
Mode                                = IS

Lock Name                           = 0x41414141414A485253334E4441
Lock Attributes                     = 0x00000000
Release Flags                       = 0x40000000
Lock Count                          = 1
Hold Count                          = 0
Lock Object Name                    = 0
Object Type                         = Internal P Lock
Mode                                = S

Lock Name                           = 0x434F4E544F4B4E3153544E4441
Lock Attributes                     = 0x00000000
Release Flags                       = 0x40000000
Lock Count                          = 1
Hold Count                          = 0
Lock Object Name                    = 0
Object Type                         = Internal P Lock
Mode                                = S

Ресурсы

Научиться

  • Оригинал статьи: Diagnose and resolve lock problems with DB2 for Linux, UNIX, and Windows (EN).
  • Руководства по DB2 (EN)
  • Указанные учебные пособия доступны по этой ссылке (EN)
    • DB2 V9 Performance Guide; Chapter 5, Concurrency issues (Руководство по поддержанию производительности DB2 V9, Глава 5 «Проблемы параллелизма»). В начале этой главы приведено обширное описание проблем параллелизма и блокировки.(EN)
    • DB2 V9 Command Reference (Справочник по командам DB2 V9). Детальное описание всех команд DB2, включая GET SNAPSHOT и get evmon.(EN)
    • DB2 V9 Administrative SQL Routines and views (Административные SQL-процедуры и представления DB2 V9). В этом руководстве описаны различные административные функции и представления, имеющие отношение к снимкам текущего состояния, в том числе SNAP_GET_LOCK.(EN)
    • V9 SQL Reference Vol 2. “Statements” section (Справочник по SQL для DB2 V9, том 2, раздел «Операторы») В документе описаны, в частности, следующие операторы: ALTER TABLE, CREATE EVENT MONITOR, FLUSH EVENT MONITOR, LOCK TABLE и SET EVENT MONITOR STATE.(EN)
    • DB2 V9 System Monitor Guide and Reference (Руководство и справочник по компоненту DB2 V9 System Monitor). Описаны различные инструменты мониторинга и предоставляемая ими информация. В части 4 рассмотрен инструмент Health Center и некоторые методы разрешения проблемами, выявляемых с помощью индикаторов параллелизма.(EN)
    • DB2 Technical Support -- DB2 Problem Determination Tutorial Series (Техническая поддержка DB2 – Серия учебных пособий по выявлению проблем DB2). Эти Web-руководства являются хорошим введением в разрешение проблем блокировки.(EN)
    • Tutorial 5: Database Engine Problem Determination; Section 4: Troubleshooting locking problems (Руководство 5: Выявление проблем процессора СУБД. Раздел 4. «Устранение проблем блокировки»).(EN)
    • Tutorial 6: Performance Problem Determination, Section 5: Locking - lock waits, timeouts, escalations and deadlocks. (Руководство 6: Выявление проблем производительности. Раздел 5. «Блокировка – ожидание блокировки, тайм-аут, эскалация блокировки, взаимная блокировка».(EN)

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=323705
ArticleTitle=Выявление и разрешение проблем блокировки в DB2 для Linux, UNIX и Windows
publish-date=07252008