SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |

Блокировки SQL Server 7.0/2000 - теория и практика устранения проблем

ПУБЛИКАЦИИ  

По материалам статьи KB224453 Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
Перевод Ирины Наумовой

Данная статья является новой редакцией для SQL Server 7.0 и выше статьи 162361 INF: Understanding and Resolving SQL Server Blocking Problems базы знаний Майкрософт, которая была написана для SQL Server 6.х
Большая часть информации, содержащейся в вышеупомянутой статье, уже обновлена и включена в справочную систему SQL Server 7.0 в раздел "Understanding and Avoiding Blocking" (Понимание и предотвращение блокировок). Вначале, тщательно изучите информацию из этой статьи, т.к. тут она не будет повторяться. Кроме того, эта статья использует ту же самую терминологию что и в 162361
Данная статья посвящена мониторингу SQL Server для сбора и анализа информацию о блокировках и разрешения вызванных ими проблем.
В контексте настоящей статьи термин "connection" (подключение) понимается как одна зарегистрированная сессия с базой данных. Каждое подключение ассоциируется со своим идентификатором системного процесса (SPID). Каждый такой SPID часто рассматривается как процесс, хотя это - не отдельный процесс в обычном понимании. Скорее, каждый SPID состоит из ресурсов сервера, и структур данных, необходимых для того, чтобы обслужить запросы отдельного подключения от клиента. Одно клиентское приложение может иметь одно или более подключений. Для SQL Server нет различия между несколькими подключениями из одного клиентского приложения с отдельного клиентского компьютера и множеством подключений множества клиентских приложений или нескольких клиентских компьютеров. Одно подключение может блокировать другое, независимо от того, порождены ли они одним приложением или отдельными приложениями на двух разных клиентских компьютерах.

СОДЕРЖАНИЕ

Введение

Блокировки присутствуют в любой реляционной системе управления базами данных (СУБД), т.к. на блокировках основана работа механизм обслуживания параллельных запросов. SQL Server порождает блокировку, когда один SPID блокирует определенный ресурс, а второй SPID пытается применить несовместимый тип блокировки на том же самом ресурсе. Как правило, интервал времени, на который первый SPID блокирует ресурс, является очень маленьким. Когда первый SPID снимает блокировку и высвобождает ресурс, второе подключение накладывает свою блокировку на ресурс и продолжает свою работу. Это - нормальная ситуация и такое может происходить по многу раз за день без заметного влияния на производительности сервера.
Продолжительность выполнения запроса и тот факт, выполняется ли он в контексте транзакции, определяют, как долго он блокирует необходимый ему ресурс, а также его воздействие на другие запросы. Если запрос выполняется не в контексте транзакции (и не используются хинты для блокировок), блокировка ресурса для инструкций SELECT будет установлена только на время чтения данных, а не на всё время выполнения запроса. Для операций INSERT, DELETE и UPDATE блокировки накладываются на всё время выполнения запроса. Это необходимо для сохранения целостности данных и для того, чтобы в случае необходимости можно было "откатить" сделанные изменения.
Для запросов, выполняющихся в контексте одной транзакции, продолжительность блокирования ресурса определяется типом запроса, уровнем изоляции транзакций и фактом наличия хинтов, использующихся в запросе. Для изучения блокировок, хинтов и уровней изоляции транзакций, необходимо прочитать следующие статьи SQL Server 7.0 Books Online:

  • "Understanding Locking in SQL Server" (Понятие блокировок в SQL Server)
  • "Locking Architecture" (Архитектура блокирования)
  • "Lock Compatibility" (Совместимость блокировок)
  • "Locking Hints" (Хинты блокирования)
  • "Changing Default Locking Behavior in Oracle and SQL Server" (Изменение блокирования по умолчанию в Oracle и SQL Server)

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

  • SPID удерживает блокировку длительное время перед тем как высвободить ресурс. Этот тип блокирования через некоторое время разрешается самостоятельно, но может сказаться на производительности.
  • SPID удерживает блокировку на ресурсе и никогда её не снимает. Этот тип блокировки не разрешается самостоятельно и исключает доступ к заблокированному им ресурсу на неопределенное время.

В первом случае, проблема решается через некоторое время, когда SPID снимет блокировку. Однако данную ситуацию трудно отследить с помощью SQL Server Enterprise Manager или используя запросы на T-SQL, в том случае, если различные SPID блокируют разные ресурсы длительное время, потому что картина постоянно меняется. Вторая ситуация приводит к неизменному состоянию, которое легче диагностировать.

[В начало]

Сбор информации о блокировках

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

  • 251004 INF: How to Monitor SQL Server 7.0 Blocking
  • 271509 INF: How to Monitor SQL Server 2000 Blocking

Сценарии, приведенные в этих статьях, решают задачи, представленные ниже:

1. Нахождение самого первого SPID в цепочке блокирования.

    В добавление к скриптам из вышеупомянутых статей, также для этой цели Вы можете использовать Enterprise Manager:

      a. Раскрыть дерево Server group, затем выбрать сервер.
      b. Раскрыть контейнер Management; затем Current Activity.
      c. Выбрать Locks / Process ID. Наряду с информацией о блокировании, там имеется информация и о том какие SPID блокируют друг друга.
    Однако, в некоторых случаях необходимо использовать запросы вместо Enterprise Manager, поскольку некоторые типы блокировок базы tempdb могут препятствовать Вам выполнять запросы, использующие временные таблицы.

2. Определение запроса, который выполняется в контексте этого SPID:

    Для этого можно воспользоваться командой DBCC INPUTBUFFER (<spid>)
    Также можно использовать и SQL Enterprise Manager:

      a. Раскрыть дерево Server group, затем выбрать сервер.
      b. Раскрыть контейнер Management; затем Current Activity
      c. Выбрать Process Info. В панели детализации появится список SPID.
      d. Дважды нажать на блокирующем SPID, чтобы увидеть какую последнюю команду на T- SQL он выполняет.

3. Определение типа блокировки, которую удерживает SPID:

    Эту информацию можно получить с помощью хранимой процедуры sp_lock.
    Также можно использовать и SQL Enterprise Manager:

      a. Раскрыть дерево Server group, затем выбрать сервер.
      b. Раскрыть контейнер Management; затем Current Activity.
      c. Раскрыть Locks / Process ID. Здесь можно просмотреть подробную информацию о блокировках, установленных конкретным SPID.

4. Определение уровня вложенности транзакций и статуса выполнения блокирующего SPID.

    Уровень вложенности транзакций можно определить с помощью глобальной переменной @@TRANCOUNT. Также это можно определить, запросив выборку из таблицы sysprocesses:

    
    SELECT open_tran FROM SYSPROCESSES WHERE SPID=<номер блокирующего SPID>
    go

    Значение, которое возвращает переменная @@TRANCOUNT - относится к SPID, и показывает уровень вложенности транзакций, который в свою очередь объясняет причину удерживания блокировки. Например, если значение больше нуля, это означает, что SPID выполняет транзакцию (в этом случае предполагается что блокировка сохраняется в зависимости от уровня изоляции транзакций).
    Также, с помощью инструкции DBCC OPENTRAN database_name, можно проверить имеются ли в базе данных долго выполняющиеся транзакции.

[В начало]

Получение информации с помощью профайлера (SQL Server Profiler)

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

1. Запустите SQL Server Profiler.

2. В меню Tools выберите пункт Options.

3. Убедитесь в том, что выбраны опции All Event Classes и All Data Columns.

4. Нажмите OK.

5. В меню File выберите пункт New, затем Trace.

6. На вкладке General введите имя трассировки и файл, в котором будут сохранены результаты трассировки.

7. На вкладке Events добавьте следующие события в свою трассу:

Заголовок

Событие

Описание

Error and Warning

Exception

Это событие показывает, что имеется исключение. Если его уровень значимости менее 25, это говорит о том, что клиенту от SQL Server была возвращена ошибка. Ошибки с уровнем значимости от 25 и выше - это внутренние ошибки SQL Server, каким способом они должны быть отфильтрованы будет рассказано ниже.

Для SQL Server 7.0 - Misc., Для SQL Server 2000 - Error and Warning

Attention

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

Sessions

Connect

Это событие показывает, что появилось новое соединение с сервером.

Sessions

Disconnect

Показывает, что клиент разорвал соединение.

Sessions

Existing Connection

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

TSQL

RPC:Starting

Событие показывает, что начал выполняться удаленный вызов процедуры (RPC)

TSQL

SQL:BatchStarting

Показывает, что начал выполняться блок T-SQL команд.

Stored Procedures

SP: StmtStarting

Это событие показывает начало выполнения инструкции внутри хранимой процедуры. Имя хранимой процедуры отображается в колонке Text.

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

Заголовок

Событие

Описание

Для SQL Server 7.0 - Misc., Для SQL Server 2000 - Performance

Execution Plan

Это событие показывает дерево плана исполнения выполняющегося T-SQL запроса

Transactions

DTCTransaction

Показывает информацию о распределенных транзакциях Microsoft Distributed Transaction Coordinator (MS DTC).

Transactions

SQLTransaction

Показывает начало, сохранение, подтверждение и откат транзакции (BEGIN, SAVE, COMMIT и ROLLBACK TRANSACTION)

TSQL

RPC:Completed

Показывает завершение удаленного вызова процедуры (RPC)

TSQL

SQL:BatchCompleted

Показывает окончание исполнения блока команд T-SQL.

Stored Procedures

SP:StmtCompleted

Показывает что исполнение команд хранимой процедуры завершено.

8. На закладке Data Columns убедитесь, что выбраны следующие колонки: Start Time, End Time, Connection ID, SPID, Event Class, Text, Integer Data, Binary Data, Application Name, NT User Name, и SQL User Name. Если Вы используете дополнительные события из второй таблицы, добавьте также: Duration, CPU, Reads, и Writes.

9. На вкладке Filters исключите внутренние ошибки SQL Server. Для этого в окне Trace Event Criteria выберите Severity и введите значение 24 в поле:

  • Maximum (SQL Server 7.0)
  • Less than or equal (SQL Server 2000)

10. Нажмите OK.

Для получения более подробной информации о мониторинге ошибок, возвращенных клиенту от SQL Server, читайте статью из базы знаний Microsoft 199037 INF: Trapping Error Messages Sent to Clients from a SQL Server. (Отслеживание сообщений об ошибках, возвращенных клиентам от SQL Server)
Для получения информации об использовании профайлера, обратитесь к справочной системе SQL Server.

[В начало]

Типовые сценарии нахождения и разрешения блокировок

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

[В начало]

Обзор результатов типовых сценариев поиска блокировок

  • Использование таблицы sysprocesses для определения головного SPID в цепочке блокирования.

    Если Вы не укажите в скрипте быстрый режим "fast", тогда в результирующем наборе, в колонке "SPIDs at the head of blocking chains", Вы получите список всех SPID, блокирующих другие:

    
    SPIDs at the head of blocking chains
    spid  
    ------ 
    9
    10

    Если же Вы указали быстрый режим, Вы все еще можете идентифицировать по sysprocesses головные SPID в цепочке блокирования. Вот сокращенный результат выборки из sysprocesses:

    
    spid   status              blocked
    ----   --------            -------
    9      sleeping            0
    10     sleeping            0
    11     sleeping            13
    12     sleeping            10
    13     sleeping            9
    14     sleeping            12

    Здесь видно, что SPID 9 и 10 в столбце blocked имеют значение 0, а это означает, что такие SPID не заблокированы другими и поэтому являются головными SPID в своих собственных цепочках блокирования.

  • Анализ результатов, полученных из таблицы sysprocesses относительно головных SPID в цепочке блокирования.

    Важно оценить следующие поля sysprocesses:

    1. Status - это поле определяет статус отдельного SPID. Как правило, статус sleeping означает что SPID закончил обработку запроса или пакета и ожидает от приложения следующего. Статус runnable показывает, что SPID в текущий момент выполняет запрос. В следующей таблице даны краткая информация о значениях поля status:

      Status

      Описание

      Background

      SPID выполняет фоновую задачу

      Sleeping

      SPID в данный момент не выполняет запросы. Обычно это означает, что он ждет команды от приложения

      Runnable

      SPID обрабатывает в текущий момент запрос или пакет

      Dormant

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

      Rollback

      SPID находится в процессе отката транзакции

      Defwakeup

      SPID ожидает ресурс, который находится в процессе освобождения. Поле waitresource указывает на ресурс в запросе

      Spinloop

      Процесс находится в состоянии ожидания после попытки установить короткую блокировку (spinlock), используемую для управления параллельным выполнением на SMP системах

    2. Open_tran - это поле показывает уровень вложенности транзакций для отдельного SPID. Если значение больше 0, это означает, что SPID обрабатывает транзакцию и возможно одна из команд внутри транзакции удерживает блокировку.
    3. Lastwaittype, waittype и waittime. Поле lastwaittype показывает последний или текущий тип ожидания SPID. Это поле появилось в SQL Server 7.0 и является строковым представлением поля waittype (которое представлено в шестнадцатеричном виде). Если значение waittype = 0x0000, SPID ничего не ожидает и поле lastwaittype равно предыдущему типу ожидания для этого SPID. Если значение поля waittype не равно нулю, то в поле lastwaittype записывается информация о текущем типе ожидания для SPID.
      Чтобы получить краткое описание различий между значениями полей lastwaittype и waittype, можно прочитать следующую статью базы знаний Microsoft: 244455 INF: Definition of Sysprocesses Waittype and Lastwaittype Fields (Описание полей Waittype и Lastwaittype таблицы sysprocesses).
      С помощью значения waittime можно отслеживать динамику создаваемых SPID блокировок Когда запрос к таблице sysprocesses возвращает значение в столбце waittime, которое меньше чем значение waittime из предыдущего запроса к sysprocesses, это означает, что предшествующая блокировка была наложена, а затем снята и теперь SPID ожидает с новой блокировкой, принимая ненулевой waittime). Это может быть проверено путем сравнения значения поля waitresource между выборками из sysprocesses.

    4. Waitresource - это поле показывает ресурс, освобождения которого ожидает SPID. В таблице ниже представлен список значений поля waitresource, его формат и описание:

      Тип ресурса

      Формат поля waitresource

      Пример

      Table (Таблица)

      DatabaseID:ObjectID

      TAB: 5:261575970
      В данном случае, это база данных с ID=5, pubs, и ее объект с ID 261575970, это таблица titles

      Page (Страница)

      DatabaseID:FileID:PageID

      PAG: 5:1:104
      База данных с ID=5, pubs. ID файла равно 1, это первичный файл данных. Страница 104, эта страница принадлежит таблице titles

      Key (Ключ)

      DatabaseID:ObjectID:IndexID
      (Hash value for index key)

      KEY: 5:261575970:1 (5d0164fb1eac)
      База данных с ID=5, pubs. ID объекта 261575970, таблица titles, ID индекса 1, означает что это кластерный индекс и значение хэш-функции показывает значение индексного ключа для определенной строки

      Row (Строка)

      DatabaseID:FileID:PageID:Slot(row)

      RID: 5:1:104:3
      ID базы данных =5 (база данных pubs), ID файла =1(основная файловая группа), страница 104 принадлежит таблице titles, слот 3 указывает позицию строки на странице

      Compile (Компиляция)

      DatabaseID:ObjectID

      TAB: 5:834102012 [[COMPILE]]
      ID базы данных =5 (база данных pubs), ID объекта = 834102012 (это хранимая процедура). Показывает что SPID ожидает компиляции плана хранимой процедуры

    5. Другие поля. Изучение полей sysprocesses может облегчить понимание сути проблемы. Количество полей для изучения определяется в зависимости от необходимости. Например, можно определить что проблема возникает только от некоторых клиентов (hostname), на некоторых сетевых библиотеках (net_library), когда выполнялся последний пакет (last_batch) и т.д. Для краткого описания полей sysprocesses, воспользуйтесь разделом "sysprocesses (T-SQL)" справочной системы SQL Server.
      ОБРАТИТЕ ВНИМАНИЕ: столбец SUID не включен в результат сценария блокирования, потому что это вычисляемый столбец, который используется только для обратной совместимости. Он не используется внутри SQL Server, и Вы можете получить снижение производительности, запрашивая этот столбец (потому что он вычисляется).

  • Изучение результата DBCC INPUTBUFFER

    Для каждого головного SPID в цепочке блокирования, или для тех SPID, у которых тип ожидания не равен нулю, в сценарии блокирования будет выполнена команда DBCC INPUTBUFFER, с помощью которой можно определить запрос, который выполняется в контексте данного SPID:

    
    DBCC INPUTBUFFER FOR SPID 9
    
    EventType       Parameters  EventInfo                  
    --------------  ----------  -------------------------------------------- 
    Language Event  0           update titles set title = title

    В большинстве случаев этот запрос как раз и является причиной блокировки. Однако, если внутри SPID выполняется транзакция, состоящая из нескольких запросов, то блокировка могла быть наложена предыдущим запросом, а не текущим. Поэтому следует использовать не только результаты, полученные с помощью inputbuffer, но и профайлер.
    ОБРАТИТЕ ВНИМАНИЕ: Поскольку сценарий блокирования состоит из нескольких шагов, то на первом шаге SPID может стать главой цепочки блокирования, но в момент выполнения DBCC INPUTBUFFER запрос может быть уже выполнен и блокировка будет снята, тогда INPUTBUFFER не сможет зафиксировать эту блокировку. Это показывает, что блокирование разрешается само для этого SPID и проблемы может и не возникнуть. В этом случае Вы можете запустить сценарий блокирования в быстром режиме, чтобы просмотреть содержимое входного буфера, пока он не очистился (хотя и в этом случае не гарантируется, что Вы получите нужный результат), либо можно отследить с помощью профайлера какие запросы выполняет SPID в этот временной промежуток.

  • [В начало]

    Анализ данных, полученных с помощью профайлера

    Изучение данных профайлера может повысить эффективность решения проблем с блокированием. Не понадобится собирать лишнюю информацию, и профайлер предоставляет возможности для более эффективного сбора данных. В диалоговом окне Properties (Меню FILE -->Properties), профайлер позволяет ограничить количество получаемых данных путем исключения лишних столбцов данных или событий, группировать данные и применять к ним фильтры. Вы можете осуществлять поиск по всей трассе или искать определенные значения определенного столбца данных. (Меню Edit-' Find). Также имеется возможность сохранять трассу в таблицу SQL Server (Меню File -' Save As -' Table).
    Применяйте фильтр только к заранее сохраненному файлу трассировки. Когда Вы применяете фильтр к активной трассировке, Вы рискуете потерять данные, которые были получены до этого с момента запуска трассы. Сохраните сначала активную трассу в файл или таблицу (File ' Save As), затем откройте (File ' Open) и только потом применяйте фильтр. Когда Вы работаете с заранее сохраненным файлом трассы, профайлер не удаляет отфильтрованные данные из файла, а просто их не выводит, и Вы также можете добавлять или удалять события и колонки данных.

    Что искать:

    • Какие команды выполняет SPID, являющийся головным в цепочке блокирования? Отфильтруйте данные трассировки для этого SPID (File'Properties'Filters и введите значение SPID). Вы также можете определить команды, которые выполнял SPID, в то время, пока блокировал другие. Включив в трассу событие Transaction можно легко определить время начала транзакции. Для этого можно просмотреть поле Text на предмет появления там операторов BEGIN, SAVE, COMMIT и ROLLBACK TRANSACTION. С помощью значения open_tran из таблицы sysprocesses, можно убедиться в том, что в трассе отражены все события транзакции. Знание команд, выполняющихся в контексте транзакции, позволяет определить, почему SPID удерживает блокировку.
      Помните, что Вы можете удалять события и колонки данных из трассы. Вместо того, чтобы отлавливать события и начала, и окончания, используйте одно из них. Если блокирующий SPID выполняет не хранимую процедуру, удалите одно из следующих событий: либо SP:Starting, либо SP:Completed, события SQLBatch и RPC будут показывать вызовы процедур. Выбирайте события SP только в случае если Вам нужно увидеть все уровни детализации.
    • Каково время выполнения запросов для SPID, являющегося головным в цепочке блокирования? Если Вы включите в трассу событие окончания (о нем рассказано выше), в колонке Duration будет показано время выполнения запроса. Это значение можно использовать для определения длительно выполняющихся запросов, которые приводят к блокировкам. Для определения причины медленного выполнения запроса, в трассу можно включить колонки CPU, Read, Writes, а также событие Execution Plan.

    [В начало]

    Основные категории сценариев устранения блокировок

    В приведенной ниже таблице указаны основные причины блокировок и возможности их разрешения. Число в столбце "Сценарий" соответствует номеру в разделе "Типовые сценарии нахождения и разрешения блокировок" данной статьи. Столбцы Waittype, Open_Tran и Status берутся из таблицы sysprocesses. Столбец "Разрешается?" показывает, разрешается ли проблема блокирования.

    Сценарий

    Waittype

    Open_Tran

    Status

    Разрешается?

    Другие признаки

    1

    Не ноль

    >= 0

    runnable

    Да, когда закончится выполнение запроса.

    Значение в колонках Physical_IO, CPU и/или Memusage будет постепенно увеличиваться. Duration будет иметь максимальное значение когда выполнение запроса закончится.

    2

    0x0000

    >0

    sleeping

    Нет, потому что подключение SPID может быть разорвано.

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

    3

    0x0000

    >= 0

    runnable

    Нет. Не будет разрешена пока клиенту не будут отправлены все строки или пока соединение не будет разорвано. Подключение SPID может быть разорвано, но на это может уйти до 30 секунд.

    Это часто происходит когда open_tran=0 и SPID удерживает блокировку в то время как установлен уровень изоляции транзакций по умолчанию (READ COMMMITTED)

    4

    Изменяется

    >= 0

    runnable

    Нет. Не будет решена пока клиент не закончит выполнение запроса или пока не закроет соединение. Подключение SPID может быть разорвано, но это может занять до 30 секунд.

    Значение колонки hostname в sysprocesses для SPID во главе цепочки блокирования будет такое же, как и для одного из SPID, которых он блокирует.

    5

    0x0000

    >0

    rollback

    Да

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

    6

    0x0000

    >0

    sleeping

    Когда Windows NT обнаруживает что сессия долгое время неактивна, соединение с SQL Server разрывается.

    Значение поля last_batch из sysprocesses намного меньше текущего времени.

    [В начало]

    Применение сценариев разрешения блокировок

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

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

      Решение:
      Решением проблемы блокирования этого типа является оптимизация запроса. Фактически, этот класс проблем блокирования относится к проблемам на стадии исполнения Для получения информации о решении проблем долго выполняющихся запросов, смотрите следующую статью из базы знаний Microsoft:
      243589 INF: Troubleshooting Slow-Running Queries on SQL Server 7.0
      Для более подробного изучения проблемам на стадии исполнения смотрите статью базы знаний Microsoft:
      224587 HOW TO: Troubleshoot Application Performance with SQL Server
      Если у Вас имеется долго выполняющийся запрос, который блокирует других пользователей и не может быть оптимизирован, рассмотрите его перемещение из OLTP среды в систему принятия решений.

    2. Блокирование по причине того, что SPID, находящийся в состоянии "Sleeping", потерял возможность трассировки уровня вложенности транзакции.

      Этот тип блокирования часто идентифицируется тем, что SPID находится в состоянии "sleeping" или "awaiting command", и у него уровень вложенности транзакций (@@TRANCOUNT, поле open_tran из sysprocesses) больше нуля. Причиной может служить превышение времени выполнения запроса или отмена выполнения без указания операторов ROLLBACK или COMMIT. В этом случае SPID прекращает выполнение текущего запроса и пакета, но автоматически не откатывает / не завершает транзакцию. Это должно быть предусмотрено в приложении, т.к. SQL Server не предполагает что нужно откатывать всю транзакцию из-за отдельного запроса. Превышение времени выполнения или окончание запроса проявляются в виде события ATTENTION для данного SPID в трассе профайлера.
      Продемонстрировать это можно следующим запросом:

      
      BEGIN TRAN 
      SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
      
      -- Запустите это после выполнения запроса
      SELECT @@TRANCOUNT
      ROLLBACK TRAN

      Во время исполнения запроса, прервите его работу. После того, как выполнение запроса прекратится, переменная @@TRANCOUNT покажет, что уровень вложения транзакций =1. Если бы запрос был на удаление или изменение данных, или в операторе SELECT с использованием опции HOLDLOCK, сохранились бы все блокировки, установленные этими операторами. Даже в представленном выше примере, если другой запрос в транзакции установил блокировку раньше, чем начал выполняться SELECT, она бы не устранились и после того как выполнится оператор SELECT.

      Решение:

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

        1. При обработке ошибок в клиентском приложении делать проверку условия: IF @@TRANCOUNT > 0 ROLLBACK TRAN после любой ошибки, даже если в приложении нет открытых транзакций. Это требуется для того, чтобы предотвратить следующую ситуацию, когда хранимая процедура, выполняющаяся в пакете, открыла транзакцию, но приложению о ней ничего неизвестно. Обратите внимание, что при некоторых условиях, таких как, например прерывание выполнения запроса, прерывается выполнение именно текущего запроса, и таким образом даже если в процедуре есть проверка условия IF @@ERROR <> 0 ROLLBACK TRAN, этот код не будет выполнен.
        2. Используйте установку SET XACT_ABORT ON при подключении к серверу или в любой процедуре, где определяются транзакции, которые не откатываются после возникновения ошибки. В случае возникновения ошибки превышения времени выполнения транзакции, эта установка прервет открытые транзакции и вернет управление клиенту. Обратите внимание, что инструкции T-SQL идущие после инструкции, которая вызвала ошибку, не будут выполнены.
        3. Если используется пул подключений в приложении, которое открывает подключение и выполняет небольшое количество запросов перед передачей подключения назад к пулу, таких как Web-приложения, временное отключение пула подключений может помочь решить проблему, пока не будет предусмотрена обработка ошибок в приложении-клиенте. Отключение пула подключений разорвет соединение с SQL Server, приводя к тому, что сервер откатит все открытые транзакции.
        4. Если допускается использование пула подключений и используется SQL Server 2000, необходимо установить MDAC 2.6 или выше. Эта версия MDAC добавляет код к ODBC драйверу и OLE DB провайдеру, чтобы подключение было "сброшено" прежде, чем оно будет снова использовано Вызов sp_reset_connection прервет любую инициированную сервером транзакцию (не затрагивая транзакции DTC, инициированные клиентским приложением), установку базы данных по умолчанию, опции SET и т.д. Обратите внимание, что подключение не сбрасывается до тех пор, пока оно не будет заново востребовано из пула. Таким образом, возможен случай, когда пользователь открыл транзакцию и затем создал подключение в пуле, но оно может не использоваться в течение некоторого времени, в то время как транзакция все еще открыта. Если подключение не используется и происходит превышение лимита времени, оно удаляется из пула подключений. Таким образом, оптимально для клиентского приложения: в обработчике ошибок прервать выполнение транзакции или использовать SET XACT_ABORT ON чтобы избежать потенциальной задержки.

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

        Для получения информации о проблемах, вызванных тяжёлыми запросами, изучите следующую статью в базе знаний Microsoft.
        243589 INF: Troubleshooting Slow-Running Queries on SQL Server 7.0
        Для полного представления об исследовании проблем производительности приложений, прочитайте следующую статью:
        224587 HOW TO: Troubleshoot Application Performance with SQL Server
        Если у Вас имеется выполняющийся долго запрос, который блокирует других пользователей и не может быть оптимизирован, рассмотрите его перемещение из OLTP системы в систему принятия решений.

    3. Блокирование по причине того, что SPID не вернул все строки результата клиенту.

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

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

    4. Блокирование вследствие возникновения распределенной клиент-серверной тупиковой блокировки.

      В отличие от обычной тупиковой блокировки распределенная тупиковая блокировка не обнаруживается с помощью менеджера блокировок RDBMS. Это происходит вследствие того, что только один из ресурсов, вовлеченных в тупиковую блокировку - это блокировка SQL Server. С другой стороны, тупиковая блокировка зарождается на уровне клиентского приложения, которым SQL Server не может управлять. Следующий пример показывает, как такое может произойти и как решается эта проблема:

      1. Клиент / серверная распределенная тупиковая блокировка с одним клиентским потоком.

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

        
         SPID1------blocked on lock------->SPID2
          /\                         (waiting to write results
          |                           back to client)
          |                                 |
          |                                 |                      Server side
          | ================================|==================================
          |     <-- single thread -->       |                      Client side
          |                                 \/
         dbproc1   <-------------------   dbproc2
         (waiting to fetch             (effectively blocked on dbproc1, awaiting
          next row)                     single thread of execution to run)

        На рисунке показан случай, когда один поток клиентского приложения имеет два открытых соединения. И в нем dbproc1 выполняет SQL запрос. Затем приложение выполняет другую SQL инструкцию в рамках dbproc2 и ожидает результаты, чтобы начать дальнейшую их обработку. Когда сервер начинает возвращать клиенту результаты (предполагается что данные сначала возвращаются dbproc1), dbproc для завершения должен обработать все полученные им данные. Он получает свои данные до тех пор, пока SPID2 не заблокирует SPID1 (потому что два запроса на сервере выполняются асинхронно). В этом случае dbproc1 будет неопределенно долгое время ожидать следующих данных. SPID2 не блокируется никаким другим SPID, он пытается передать результат своему клиенту - dbproc2, однако dbroc1 блокирует dbproc2, поскольку на уровне приложения они выполняются в одном потоке. Эту тупиковую блокировку SQL Server не может определить и разрешить, поскольку только один из вовлечённых ресурсов принадлежит SQL Server.

      2. Клиент / серверная распределенная блокировка с одним потоком на каждое соединение.

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

        
        SPID1------blocked on lock-------->SPID2
          /\                         (waiting on net write)        Server side
          |                                 |
          |                                 |
          | INSERT                          |SELECT
          | ================================|==================================
          |     <-- thread per dbproc -->   |                      Client side
          |                                 \/
         dbproc1   <-----data row-------   dbproc2
         (waiting on                     (blocked on dbproc1, waiting for it
          insert)                         to read the row from its buffer)

        Этот случай похож на Пример "a" за исключением того, что dbproc2 и SPID2 выполняют инструкцию SELECT с намерением выполнить одновременно обработку строки и отсылку каждой строки через буфер к dbproc1 для вставки, модификации или удаления её из той же самой таблицы. В конечном счете, SPID1 (выполняющий вставку, удаление или модификацию) блокируется SPID2 (выполняющим инструкцию Select). SPID2 отсылает строку результата клиенту dbproc2. Dbproc2 пробует передавать строку в буфер к dbproc1, но видит, что dbproc1 занят (SPID 2 блокирует SPID1). В данном примере dbproc2 блокирован на прикладном уровне dbproc1, у которого SPID (SPID1) блокирован на уровне базы данных SPID2. Снова, это приводит к тупиковой блокировке, которую SQL Server не может обнаружить или разрешить, потому что только один из вовлеченных ресурсов относится к SQL Server.
        Примеры "a" и "b" - это основные причины подобного рода блокировок, о которых разработчики приложений должны помнить и писать приложения учитывая такие случаи.

      Решение:

      Два эффективных решения состоят в том, чтобы использовать превышение времени выполнения запроса или объединение подключений.

      1. Превышение времени выполнения запроса
        Когда происходит превышение времени выполнения запроса, в случае тупиковой распределенной блокировки она будет снята. Для получения более подробной информации об использовании превышения времени выполнения запроса, см. документацию по DB-Library или ODBC.

      2. Объединение подключений
        Эта особенность позволяет клиенту, имеющему множественные подключения связывать их в единое операционное пространство, так что подключения не блокируют друг друга. Для подробной информации, см. статью "Using Bound Connections" (Использование объединений подключений) в справочной системе SQL 7.0.

    5. Блокирование по причине того что SPID находится в состоянии "golden" или "rollback"

      Если выполнение запроса на модификацию данных прервано или запрос отменен вне определенной пользователем транзакции, изменения должны быть отменены. Такое может произойти в случае, если клиентский компьютер был перезагружен, и/или сетевое подключение было разорвано. Аналогично, изменения, которые внес процесс, выбранный в качестве жертвы при тупиковой блокировке, тоже будет отменен. Откат изменений, вносимых запросами на модификацию данных, часто производится дольше, чем вносятся сами изменения. Например, если оператор Update, Insert или Delete выполнялся час, на откат изменений потребуется не менее часа, потому что сделанные изменения должны быть отменены или должна быть обеспечена транзакционная и физическая целостность в базе данных. Когда это происходит, SQL Server помечает SPID как "golden" или устанавливает ему состояние "rollback" (это означает, что он не может быть прерван или выбран в качестве жертвы при разрешении тупиковой блокировки). Как правило эту ситуацию можно отследить с помощью команды sp_who. Поле Status таблицы sysprocesses показывает статус ROLLBACK, который также можно увидеть с помощью sp_who или в окне текущей активности в Enterprise Manager.

      Решение:

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

    6. Блокирование по причине потерянного соединения.

      Если клиентское приложение перезапущено или рабочая станция перезагружена, при некоторых условиях сервер не может сразу же разорвать сетевое соединение. Со стороны сервера клиентское соединение все еще имеется и установленные им блокировки сохраняются. Для получения более подробной информации по этому вопросу см. статью BOL: SQL Server 7.0 "Orphaned Connections"

      Решение:

      Если клиентское приложение было отключено без очистки его ресурсов, Вы можете прервать его при помощи команды KILL. В качестве параметра команде Kill требуется SPID. Например, чтобы прервать SPID 9, просто используйте команду KILL 9.
      Запомните: Выполнение команды KILL может занять до 30 секунд, это вызвано продолжительностью интервала опроса исполнения этой команды.

    [В начало]

    Влияние приложений на возникновение проблем блокировок

    Существует тенденция для решения проблем с блокировками фокусировать внимание на настройке серверной части и проблемах платформы. Однако, обычно это не приводит к решению проблемы а только лишь отнимает много времени и энергии, которые следовало бы направить на изучение приложения-клиента и запросов, которые им используются.
    Если в приложении не будет явно предусмотрено управление подключениями, обработка отмены выполнения запроса, ограничение времени ожидания выполнения запроса и т.д., проблема блокирования может стать неразрешимой. Все это должно быть учтено при выборе инструмента разработки приложения для SQL Server, особенно для OLTP сред.
    Очень важно уделять больше внимания проектированию системы, а также стадии разработки базы данных и приложения. В частности для каждого запроса должны быть оценены следующие факторы: потребление ресурса, уровень изоляции и стоимость его выполнения. Каждый запрос и транзакция должны быть как можно более "легковесны".
    Должно быть обеспечено управление подключениями. Если это не предусмотреть, то возможно при небольшом количестве пользователей работа приложения может оказаться приемлемой, но при увеличении количества пользователей, его выполнение может значительно ухудшиться.
    При надлежащем дизайне приложения и запросов, Microsoft SQL сервер способен поддерживать одновременную работу на одном сервере многих тысяч пользователей с небольшим числом блокировок.
    Для получения дополнительной информации, изучите разделы SQL Server 7.0 Books Online: "Application Design" (Разработка приложений) и "Understanding and Avoiding Blocking" ("Понимание и предотвращение блокирования". При использовании методов, описанных в этих разделах, достигается успешная работа системы даже при большом количество пользователей.

    [В начало]

    Ссылки

    Для получения дополнительной информации изучите следующие книги:

    Microsoft SQL Server 7.0 System Administration Training Kit Microsoft Press, 2001
    MCSE Training Kit: Microsoft SQL Server 2000 System Administration Microsoft Press, 2001

    А также предлагается пройти следующие курсы:

    2072 Administering a Microsoft SQL Server 2000 Database
    2073 Programming a Microsoft SQL Server 2000 Database

    Информация в этой статье относится к:

    • Microsoft SQL Server 7.0
    • Microsoft SQL Server 2000 (все редакции)

    [В начало]

    Перевод: Ирины Наумовой  2004г.

    Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
    Copyright: SQL.Ru 2000-2013