Как увеличить таймаут блокировок sql
Перейти к содержимому

Как увеличить таймаут блокировок sql

  • автор:

Как увеличить таймаут блокировок sql

Очень часто во время работы выходит сообщение о блокировки при выполнении транзакции. Нашла в PostgreSQL/Сервис/Конфигурация сервера/postgresql.conf deadlock_timeout со значением 1000. Как мне изменить это значение до 100 000?

Будет только хуже.
(0) смотри в конфигураторе «Администрирование» — «Параметры информационной базы»
Нужно искать причины блокировок и переписывать модули.

Блокировка происходит во время выгрузки для локальной информац. базы, а также при создании заказа на одного клиента двумя или более продавцами. Это в модуле никак не поправишь, а от этого никуда не уйдешь — надо чтоб работало пусть подольше, но РАБОТАЛО. Если в конфигураторе увеличить время ожидания, то sql все равно будет блокировать процесс. Как изменяется postgresql.conf? Кто нибудь с ним работал?

Что почему? Почему в модуле не поправишь саму блокировку — потому что нужно увеличивать время ожидания блокировки, а оно увеличивается в postgresql.conf.

«аказа на одного клиента двумя или более продавцами» — немного неправильно, ИМХО

«Как мне изменить это значение до 100 000»

Никак, максимально возможное значение параметра чуть более 2000

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

Класс событий Lock:Timeout (timeout > 0)

Класс событий Lock:Timeout (timeout > 0) указывает, что запрос на блокировку ресурса, например страницы, истекает, так как другая транзакция держит блокировку блокировки требуемого ресурса. Данный класс событий работает аналогично классу событий Lock:Timeout за тем исключением, что не включает события со значением времени ожидания 0.

Включите класс событий Lock:Timeout (timeout > 0) в трассировки, где вы используете пробы блокировки или другие процессы с значениями времени ожидания ноль. Это позволяет отслеживать возникновение случаев фактического превышения времени ожидания, не включая в трассировку нулевые значения времени ожидания.

Столбцы данных класса событий Lock:Timeout (timeout > 0)

Имя столбца данных Тип данных Описание: Идентификатор столбца Доступно для фильтрации
ApplicationName nvarchar Имя клиентского приложения, создавшего подключение к экземпляру SQL Server. Этот столбец заполняется значениями, передаваемыми приложением, а не отображаемым именем программы. 10 Да
BinaryData Изображение Идентификатор ресурса блокировки. 2 Да
ClientProcessID int Идентификатор, присвоенный главным компьютером сервера процессу, в котором работает клиентское приложение. Этот столбец данных заполняется в том случае, если клиент предоставляет идентификатор клиентского процесса. 9 Да
DatabaseID int Идентификатор базы данных, в которой истекло время ожидания. Sql Server Profiler отображает имя базы данных, если столбец данных ServerName фиксируется в трассировке и сервер доступен. Определите значение для базы данных, используя функцию DB_ID. 3 Да
имя_базы_данных nvarchar Имя базы данных, в которой истекло время ожидания. 35 Да
Длительность bigint Длительность события (в микросекундах). 13 Да
EndTime datetime Время окончания события. Этот столбец не заполняется для классов событий запуска, таких как SQL:BatchStarting или SP:Starting. 15 Да
EventClass int Тип события = 189. 27 No
EventSequence int Последовательность данного события в запросе. 51 No
GroupID int Идентификатор группы рабочей нагрузки, в которой запускается событие трассировки SQL. 66 Да
HostName nvarchar Имя компьютера, на котором выполняется клиентская программа. Этот столбец данных заполняется, если клиент предоставляет имя узла. Чтобы определить имя узла, используйте функцию HOST_NAME. 8 Да
IntegerData2 int Указано только в ознакомительных целях. Не поддерживается. Совместимость с будущими версиями не гарантируется. 55 Да
IsSystem int Указывает, произошло событие в системном или в пользовательском процессе. 1 = системный, 0 = пользовательский. 60 Да
LoginName nvarchar Имя имени входа пользователя (имя для входа в систему безопасности SQL Server или учетные данные входа Microsoft Windows в формате DOMAIN\username). 11 Да
LoginSid Изображение Идентификатор безопасности вошедшего в систему пользователя. Эти сведения можно найти в представлении каталога sys.server_principals. Значение идентификатора безопасности уникально для каждого имени входа на сервере. 41 Да
Режим int Состояние, полученное или запрошенное событием.

SET LOCK_TIMEOUT (Transact-SQL)

Указывает количество миллисекунд, в течение которых инструкция ожидает снятия блокировки.

Синтаксис

SET LOCK_TIMEOUT timeout_period 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

timeout_period
Число миллисекунда, которое будет передаваться до того, как Microsoft SQL Server возвращает ошибку блокировки. Значение -1 (по умолчанию) указывает на отсутствие времени ожидания (то есть инструкция будет ждать всегда).

Когда ожидание блокировки превышает значение времени ожидания, возвращается ошибка. Значение «0» означает, что ожидание отсутствует, а сообщение возвращается, как только встречается блокировка.

Замечания

В начале соединения этот параметр имеет значение -1. После изменения новое значение остается в силе в течение работы соединения.

Настройка SET LOCK_TIMEOUT установлена на запуск во время выполнения, но не во время синтаксического анализа.

Рекомендация блокировки READPAST представляет собой альтернативу данному параметру SET.

Инструкции CREATE DATABASE, ALTER DATABASE и DROP DATABASE не поддерживают настройки SET LOCK_TIMEOUT.

Разрешения

Необходимо быть членом роли public.

Примеры

А. Установка времени ожидания блокировки равным 1800 миллисекундам

В следующем примере время ожидания блокировки устанавливается на 1800 миллисекунд.

SET LOCK_TIMEOUT 1800; GO 

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

B. Установка для времени ожидания бесконечного ожидания снятия блокировки.

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

SET LOCK_TIMEOUT -1; 

В следующем примере время ожидания блокировки устанавливается на 1800 миллисекунд. В этом выпуске Azure Synapse Analytics успешно анализирует инструкцию, но будет игнорировать значение 1800 и продолжать использовать поведение по умолчанию.

SET LOCK_TIMEOUT 1800; 

SQL-Ex blog

Блокировки, блокирование и тупики в SQL Server

Добавил Sergey Moiseenko on Суббота, 20 февраля. 2021

Терминология имеет значение: Locks, blocks и deadlocks

Я потерял счет тому, сколько раз мне говорили о существовании тупика в базе данных, но стоило проверить и посмотреть, так никаких тупиковых ситуаций не обнаруживалось. В этом сценарии обычно они пытались описать блокировку. Администратору баз данных или разработчику важно знать различие между блокировками (Lock), блокированием (block) и тупиками (deadlock).

Что такое блокировки в SQL Server

Блокировки играют важную роль в обеспечении свойств транзакции ACID. Различные команды SELECT, DML и DDL генерируют блокировки на ресурсы. Например, в процессе обновления строки таблицы накладывается блокировка, гарантирующая, что те же самые данные не могут читаться или модифицироваться в то же время. Это обеспечивает чтение и модификацию только зафиксированных данных в базе данных. Последующее обновление может иметь место после первоначального, не они не могут конкурировать. Каждая транзакция должна полностью завершиться или откатиться, никаких полумер.

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

Типы блокировок

  1. Если данные не модифицируются, конкурирующие пользователи могут читать одни и те же данные.
    1. Пока уровень изоляции является значением по умолчанию для SQL Server (Read Commited — чтение зафиксированных данных).
    2. Однако это поведение меняется при более высоком уровне изоляции, таком как сериализуемый.

    Что такое блокирование

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

    Таким образом, блокировка — это вполне естественное явление в SQL Server. Фактически, это жизненно важно для обеспечения ACID-транзакций. На хорошо оптимизированных системах это трудно заметить и не вызывает проблем.

    Проблемы возникают, когда блокирование затягивается на длительное время, т.к. это приводит к замедлению выполнения транзакций. Типичный тайм-аут соединения для веб-приложений составляет 30 секунд, поэтому превышение приводит к множеству исключений. Даже при 10 или 15 секундных задержках пользователи могут быть разочарованы. Очень длительное блокирование может остановить весь сервер на время, пока главные блокировщики не будут убраны.

    Обнаружение блокирований

    Я просто использую хранимую процедуру Адама Мачаника sp_whoisactive. Вы можете использовать sp_who2, если принципиально не используете сторонние скрипты, но, на всякий случай, это процедура написана на чистом T-SQL.

    EXEC sp_whoisactive @find_block_leaders = 1

    Убивать или не убивать

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

    Множественные идентичные блокировщики

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

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

    Что такое тупики?

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

    Как проверить наличие тупика

    Мне нравится использовать процедуру sp_blitzlock Брента Озара. В пожарном режиме я просто проверю предыдущий час. Вы также можете выбрать тупики из журнала ошибок SQL Server или же установить расширенные события для их захвата.

    -- Тупики последнего часа 
    DECLARE @StartDateBlitz datetime = (SELECT DATEADD(HH,-1,GETDATE())),@EndDateBlitz DATETIME = (SELECT GETDATE())
    EXEC sp_BlitzLock @EndDate = @EndDateBlitz, @StartDate = @StartDateBlitz

    Моделирование блокирования

    Если вы хотите смоделировать блокирование, то можете попробовать сделать это на базе данных Wide World Importers.

    /* 
    Выполните каждый из этих запросов по порядку в различных окнах SSMS.
    */
    -- Запрос 1 (Этот беззаботный персонаж запустил, но не зафиксировал свое обновление)
    BEGIN TRANSACTION
    UPDATE [WorldWideImporters].[Sales].[Customers]
    SET CustomerName = 'SpinTail Toys (Head Office)'
    WHERE customerID = 1
    -- COMMIT
    -- Выполните этот commit выше только после того, как будут выполнены все запросы и вы увидите блокирование. -- Запрос 2 сразу выполнится.
    -- Запрос 2 (Я просто хочу получить результаты выборки, но незавершенная транзакция блокирует меня).
    SELECT *
    FROM [WorldWideImporters].[Sales].[Customers]
    WHERE customerID = 1
    -- Запрос 3 (Проверяем wait_info)
    USE DBA
    EXEC sp_whoisactive @find_block_leaders = 1
    -- Вы должны увидеть тип ожидания LCK_M_S для вашего запроса select. Это означает, что поток ожидает получения разделяемой блокировки.

    Изображение ниже показывает рядом вывод трех запросов. Запрос 1 завершается быстро, но отмечается, что он незафиксирован. Запрос 2 не завершается, пока запрос 1 не будет зафиксирован или сделан откат. Выполнение запроса 3 (sp_whoisactive) позволяет узнать, какие процессы вызывают блокирование, и какие заблокированы.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *