55p03 ошибка выполнение оператора отменено из за тайм аута блокировки
Обновлено: 28.06.2024
Повторю команду:
strace -f -s 2048 -etrace='!gettimeofday,clock_gettime' 2>&1 pg_dump -v -b -C -f p03.pg_dump_tar oblbase | tee -a pg_dump.log
давайте так, запустите strace pg_dump как и раньше, только добавьте туда опцию -t для фиксации времени
и запустите второй strace, но его уже натравите на бэкенд постгреса в котором выполняется дамп, его можно увидеть в таблице процессов, он светится там с командой COPY. команда будет выглядеть примерно так strace -p PID. В обоих случаях у strace опустите флаги "-f" и "-s 4096", -etrace оставьте.
strace -t -f -s 2048 -etrace='!gettimeofday,clock_gettime' 2>&1 pg_dump -v -b -C -f p03.pg_dump_tar oblbase | tee -a pg_dump.1.log
Команда вторая (отставание от первой по времени запуска около 10 секунд, потрачено на выяснение pid):
strace -t -f -s 2048 -p 2291 -etrace='!gettimeofday,clock_gettime' 2>&1 | tee -a pg_dump.2.log
14:05:26 sendto(7, "Z\0\0\0\5E", 6, 0, NULL, 0) = 6
14:05:26 recvfrom(7, "X\0\0\0\4", 8192, 0, NULL, NULL) = 5
ну и дальше мы уже знаем процесс постгреса завершается, пгдамп вываливает ошибку:
Error message from server: ОШИБКА: выполнение оператора отменено по запросу пользователя
strace -tt -d -f -s 2048 -p 26699 -etrace='!gettimeofday,clock_gettime' 2>&1 | tee -a pg_dump.2.log
Мы уже поговорили о некоторых блокировках на уровне объектов (в частности — о блокировках отношений), а также о блокировках на уровне строк, их связи с блокировками объектов и об очереди ожидания, не всегда честной.
Сегодня у нас сборная солянка. Начнем с взаимоблокировок (вообще-то я собирался рассказать о них еще в прошлый раз, но та статья и так получилась неприлично длинной), затем пробежимся по оставшимся блокировкам объектов, и в заключение поговорим про предикатные блокировки.
При использовании блокировок возможна ситуация взаимоблокировки (или тупика). Она возникает, когда одна транзакция пытается захватить ресурс, уже захваченные другой транзакцией, в то время как другая транзакция пытается захватить ресурс, захваченный первой. Это проиллюстрировано на левом рисунке ниже: сплошные стрелки показывают захваченные ресурсы, пунктирные — попытки захватить уже занятый ресурс.
Визуально взаимоблокировку удобно представлять, построив граф ожиданий. Для этого мы убираем конкретные ресурсы и оставляем только транзакции, отмечая, какая транзакция какую ожидает. Если в графе есть контур (из вершины можно по стрелкам добраться до нее же самой) — это взаимоблокировка.
Конечно, взаимоблокировка возможна не только для двух транзакций, но и для любого большего числа.
Если взаимоблокировка возникла, участвующие в ней транзакции не могут ничего с этим сделать — они будут ждать бесконечно. Поэтому все СУБД, и PostgreSQL тоже, автоматически отслеживают взаимоблокировки.
Однако проверка требует определенных усилий, которые не хочется прилагать всякий раз, когда запрашивается новая блокировка (все-таки взаимоблокировки достаточно редки). Поэтому когда процесс пытается захватить блокировку и не может, он встает в очередь и засыпает, но взводит таймер на значение, указанное в параметре deadlock_timeout (по умолчанию — 1 секунда). Если ресурс освобождается раньше, то и хорошо, мы сэкономили на проверке. А вот если по истечении deadlock_timeout ожидание продолжается, тогда ожидающий процесс будет разбужен и инициирует проверку.
Если проверка (которая состоит в построении графа ожиданий и поиска в нем контуров) не выявила взаимоблокировок, то процесс продолжает спать — теперь уже до победного конца.
Ранее в комментариях меня справедливо упрекнули в том, что я ничего не сказал про параметр lock_timeout, который действует на любой оператор и позволяет избежать неопределенно долгого ожидания: если блокировку не удалось получить за указанное время, оператор завершается с ошибкой lock_not_available. Его не стоит путать с параметром statement_timeout, который ограничивает общее время выполнения оператора, неважно, ожидает ли он блокировку или просто выполняет работу.
Если же взаимоблокировка выявлена, то одна из транзакций (в большинстве случаев — та, которая инициировала проверку) принудительно обрывается. При этом освобождаются захваченные ей блокировки и остальные транзакции могут продолжать работу.
Взаимоблокировка двух команд UPDATE
Иногда можно получить взаимоблокировку там, где, казалось бы, ее быть никак не должно. Например, удобно и привычно воспринимать команды SQL как атомарные, но возьмем UPDATE — эта команда блокирует строки по мере их обновления. Это происходит не одномоментно. Поэтому если одна команда будет обновлять строки в одном порядке, а другая — в другом, они могут взаимозаблокироваться.
Получить такую ситуацию маловероятно, но тем не менее она может встретиться. Для воспроизведения мы создадим индекс по столбцу amount, построенный по убыванию суммы:
Чтобы успеть увидеть происходящее, напишем функцию, увеличивающую переданное значение, но мееедленно-мееедленно, целую секунду:
Еще нам понадобится расширение pgrowlocks.
Первая команда UPDATE будет обновлять всю таблицу. План выполнения очевиден — последовательный просмотр:
Поскольку версии строк на странице нашей таблицы лежат в порядке возрастания суммы (ровно так, как мы их добавляли), они и обновляться будут в том же порядке. Запускаем обновление работать.
А в это время в другом сеансе мы запретим использование последовательного сканирования:
В этом случае для следующего оператора UPDATE планировщик решает использовать сканирование индекса:
Под условие попадают вторая и третья строки, а, поскольку индекс построен по убыванию суммы, строки будут обновляться в обратном порядке.
Запускаем следующее обновление.
Быстрый взгляд в табличную страницу показывает, что первый оператор уже успел обновить первую строку (0,1), а второй — последнюю (0,3):
Проходит еще секунда. Первый оператор обновил вторую строку, а второй хотел бы это сделать, но не может.
Теперь первый оператор хотел бы обновить последнюю строку таблицы, но она уже заблокирована вторым. Вот и взаимоблокировка.
Одна из транзакций прерывается:
А другая завершает выполнение:
Занимательные подробности об обнаружении и предотвращении взаимоблокировок можно почерпнуть из README менеджера блокировок.
На этом про взаимоблокировки все, а мы приступаем к оставшимся блокировкам объектов.
Когда требуется заблокировать ресурс, не являющийся отношением в понимании PostgreSQL, используются блокировки типа object. Таким ресурсом может быть почти все, что угодно: табличные пространства, подписки, схемы, роли, перечислимые типы данных… Грубо говоря все, что только можно найти в системном каталоге.
Посмотрим на простом примере. Начинаем транзакцию и создаем в ней таблицу:
Теперь посмотрим, какие блокировки типа object появились в pg_locks:
Чтобы разобраться, что именно тут блокируется, надо смотреть на три поля: database, classid и objid. Начнем с первой строки.
Database — это OID базы данных, к которой относится блокируемый ресурс. В нашем случае в этом столбце ноль. Это означает, что мы имеем дело с глобальным объектом, который не принадлежит к какой-либо конкретной базе.
Classid содержит OID из pg_class, который соответствует имени таблицы системного каталога, которая и определяет тип ресурса. В нашем случае — pg_authid, то есть ресурсом является роль (пользователь).
Objid содержит OID из той таблицы системного каталога, которую нам указал classid.
Таким образом, заблокирована роль student, из-под которой мы работаем.
Теперь разберемся со второй строкой. База данных указана, и это база test, к которой мы подключены.
Classid указывает на таблицу pg_namespace, которая содержит схемы.
Таким образом, заблокирована схема public.
Итак, мы увидели, что при создании объекта блокируются (в разделяемом режиме) роль-владелец и схема, в которой создается объект. Что и логично: иначе кто-нибудь мог бы удалить роль или схему, пока транзакция еще не завершена.
Когда число строк в отношении (то есть в таблице, индексе, материализованном представлении) растет, PostgreSQL может использовать для вставки свободное место в имеющихся страницах, но, очевидно, в какой-то момент приходится добавлять и новые страницы. Физически они добавляются в конец соответствующего файла. Это и понимается под расширением отношения.
Чтобы два процесса не кинулись добавлять страницы одновременно, этот процесс защищен специальной блокировкой с типом extend. Та же блокировка используется и при очистке индексов, чтобы другие процессы не могли добавлять страницы во время сканирования.
Конечно, эта блокировка снимается, не дожидаясь конца транзакции.
Раньше таблицы расширялись только на одну страницу за раз. Это вызывало проблемы при одновременной вставке строк несколькими процессами, поэтому в версии PostgreSQL 9.6 сделали так, чтобы к таблицам добавлялось сразу несколько страниц (пропорционально числу ожидающих блокировку процессов, но не более 512).
Блокировка с типом page на уровне страницы применяется в единственном случае (если не считать предикатных блокировок, о которых позже).
GIN-индексы позволяют ускорять поиск в составных значениях, например, слов в текстовых документах (или элементов в массивах). Такие индексы в первом приближении можно представить как обычное B-дерево, в котором хранятся не сами документы, а отдельные слова этих документов. Поэтому при добавлении нового документа индекс приходится перестраивать довольно сильно, внося в него каждое слово, входящее в документ.
Чтобы улучшить производительность, GIN-индексы обладают возможностью отложенной вставки, которая включается параметром хранения fastupdate. Новые слова сначала по-быстрому добавляются в неупорядоченный список ожидания (pending list), а спустя какое-то время все накопившееся перемещается в основную индексную структуру. Экономия происходит за счет того, что разные документы с большой вероятностью содержат повторяющиеся слова.
Чтобы исключить перемещение из списка ожидания в основной индекс одновременно несколькими процессами, на время переноса метастраница индекса блокируется в исключительном режиме. Это не мешает использованию индекса в обычном режиме.
В отличие от других блокировок (таких, как блокировки отношений), рекомендательные блокировки (advisory locks) никогда не устанавливаются автоматически, ими управляет разработчик приложения. Их удобно использовать, например, если приложению для каких-то целей требуется логика блокирования, не вписывающаяся в стандартную логику обычных блокировок.
Допустим, у нас есть условный ресурс, не соответствующий никакому объекту базы данных (который мы могли бы заблокировать командами типа SELECT FOR или LOCK TABLE). Нужно придумать для него числовой идентификатор. Если у ресурса есть уникальное имя, то простой вариант — взять от него хеш-код:
Вот таким образом мы захватываем блокировку:
Как обычно, информация о блокировках доступна в pg_locks:
Чтобы блокирование действительно работало, другие процессы также должны получать его блокировку, прежде чем обращаться к ресурсу. Соблюдение этого правила, очевидно, должно обеспечиваться приложением.
В приведенном примере блокировка действует до конца сеанса, а не транзакции, как обычно.
Ее нужно освобождать явно:
Существуют большой набор функций для работы с рекомендательными блокировками на все случаи жизни:
- pg_advisory_lock_shared получает разделяемую блокировку,
- pg_advisory_xact_lock (и pg_advisory_xact_lock_shared) получает блокировку до конца транзакции,
- pg_try_advisory_lock (а также pg_try_advisory_xact_lock и pg_try_advisory_xact_lock_shared) не ожидает получения блокировки, а возвращает ложное значение, если блокировку не удалось получить немедленно.
Термин предикатная блокировка появился давно, при первых попытках реализовать полную изоляцию на основе блокировок в ранних СУБД (уровень Serializable, хотя стандарта SQL в те времена еще не существовало). Проблема, с которой тогда столкнулись, состояла в том, что даже блокировка всех прочитанных и измененных строк не дает полной изоляции: в таблице могут появиться новые строки, попадающие под те же условия отбора, что приводит к появлению фантомов (см. статью про изоляцию).
Идея предикатных блокировок состояла в блокировке не строк, а предикатов. Если при выполнении запроса с условием a > 10 заблокировать предикат a > 10, это не даст добавить в таблицу новые строки, попадающие под условие и позволит избежать фантомов. Проблема в том, что в общем случае это вычислительно сложная задача; на практике ее можно решить только для предикатов, имеющих очень простой вид.
В PostgreSQL уровень Serializable реализован иначе, поверх существующей изоляции на основе снимков данных. Термин предикатная блокировка остался, но смысл его в корне изменился. Фактически такие «блокировки» ничего не блокируют, а используются для отслеживания зависимостей по данным между транзакциями.
Доказано, что изоляция на основе снимков допускает аномалию несогласованной записи и аномалию только читающей транзакции, но никакие другие аномалии невозможны. Чтобы понять, что мы имеем дело с одной из двух перечисленных аномалией, мы можем анализировать зависимости между транзакциями и находить в них определенные закономерности.
Нас интересуют зависимости двух видов:
- одна транзакция читает строку, которая затем изменяется другой транзакцией (RW-зависимость),
- одна транзакция изменяет строку, которую затем читает другая транзакция (WR-зависимость).
Еще раз повторюсь: несмотря на название, предикатные блокировки ничего не блокируют. Вместо этого при фиксации транзакции выполняется проверка и, если обнаруживается «нехорошая» последовательность зависимостей, которая может свидетельствовать об аномалии, транзакция обрывается.
Давайте посмотрим, как происходит установка предикатных блокировок. Для этого создадим таблицу с достаточно большим числом строк и индекс на ней.
Если запрос выполняется с помощью последовательного сканирования всей таблицы, то предикатная блокировка устанавливается на всю таблицу (даже если под условия фильтрации попадают не все строки).
Любые предикатные блокировки всегда захватываются в одном специальном режиме SIReadLock (Serializable Isolation Read):
А вот если запрос выполняется с помощью индексного сканирования, ситуация меняется в лучшую сторону. Если говорить о B-дереве, то достаточно установить блокировку на прочитанные табличные строки и на просмотренные листовые страницы индекса — тем самым мы блокируем не только конкретные значения, но и весь прочитанный диапазон.
Можно заметить несколько сложностей.
Во-первых, на каждую прочитанную версию строки создается отдельная блокировка, но потенциально таких версий может быть очень много. Общее число предикатных блокировок в системе ограничено произведением значений параметров max_pred_locks_per_transaction × max_connections (значения по умолчанию — 64 и 100 соответственно). Память под такие блокировки отводится при запуске сервера; попытка превысить это число будет приводить к ошибкам.
Поэтому для предикатных блокировок (и только для них!) используется повышение уровня. До версии PostgreSQL 10 действовали жестко зашитые в код ограничения, а начиная с нее повышением уровня можно управлять параметрами. Если число блокировок версий строк, относящихся к одной странице, превышает max_pred_locks_per_page, такие блокировки заменяются на одну блокировку уровня страницы. Вот пример:
Вместо трех блокировок типа tuple видим одну типа page:
Аналогично, если число блокировок страниц, относящихся к одному отношению, превышает max_pred_locks_per_relation, такие блокировки заменяются на одну блокировку уровня отношения.
Других уровней не бывает: предикатные блокировки захватываются только для отношений, страниц или версий строк, и всегда с режимом SIReadLock.
Конечно, повышение уровня блокировок неизбежно приводит к тому, что большее число транзакций будет ложно завершаться ошибкой сериализации и в итоге пропускная способность системы будет снижаться. Здесь нужно искать баланс между расходом оперативной памяти и производительностью.
Вторая сложность состоит в том, что при различных операциях с индексом (например, из-за расщепления индексных страниц при вставке новых строк) число листовых страниц, покрывающих прочитанный диапазон, может измениться. Но реализация это учитывает:
К слову, предикатные блокировки не всегда снимаются сразу по завершению транзакции, ведь они нужны, чтобы отслеживать зависимости между несколькими транзакциями. Но в любом случае управление ими происходит автоматически.
Далеко не все типы индексов в PostgreSQL поддерживают предикатные блокировки. Раньше этим могли похвастать только B-деревья, но в версии PostgreSQL 11 ситуация улучшилась: к списку добавились хеш-индексы, GiST и GIN. Если используется индексный доступ, а индекс не работает с предикатными блокировками, то блокировка накладывается на весь индекс целиком. Конечно, это тоже увеличивает число ложных обрывов транзакций.
В заключение отмечу, что именно с использованием предикатных блокировок связано ограничение, что для гарантий полной изоляции все транзакции должны работать на уровне Serializable. Если какая-либо транзакция будет использовать другой уровень, она просто не будет устанавливать (и проверять) предикатные блокировки.
postgresql 9.6 на windows, там же сервер 1с
Периодически вылетает ошибка в 1с:
Конфликт блокировок при выполнении транзакции:
55P03 ERROR: canceling statement due to lock timeout
STATEMENT: SET STATEMENT_TIMEOUT TO 20000;
SELECT pg_advisory_xact_lock((SELECT oid::bigint FROM pg_class WHERE relname = 'различные имена талбиц'));
SET STATEMENT_TIMEOUT TO DEFAULT;
Причем бываю даже дедлоки из 2ух подобных запросов.
Транзакция 1 берёт лок на foo
Транзакция 2 берёт лок на bar
Транзакция 1 хочет bar
Транзакция 2 хочет foo
Очевидно, что здесь нет проблемы взаимоблокировок, здесь просто какой-то сеанс поставил блокировку и "забыл" убрать. При этом проблема грозила серьезными последствиями - не проводился документ Реализации товаров и услуг. В базе единовременно работает около 100 человек, и невозможно выполнить типовую и частую операцию!
Решения было два - перезагрузка сервера или поиск сбойного сеанса. Первое решение простое и быстрое, но, как здесь уже кто-то писал - ребутать сервер можно до тех пор, пока тебя не уволят. Решил пойти по второму пути.
Первый день - проблема появилась днем, поначалу казалось, что проблема в удаленном пользователе, который засел в Конфигураторе. Было похоже, что просто выполнение остановилось на точке, и блокировка, естественно, не снялась. Через пару часов удалось освободить конфигуратор, но проблема не ушла. Убивать принудительно конфигуратор было крайне нежелательно, возможно, в нем работали. После этого в ход пошел гугл. Нашел статью на этом сайте, в которой пишется, как найти блокировки в СУБД MS SQL, проверил, блокировок на уровне СУБД не было. Странно. Далее были попытки настроить тех. журнал. Настроил, а дальше что? За 15 минут пара гигов логов! Как их читать, что искать? Неизвестно.
Нашел статью, как посмотреть, что заблокировано через SQL Trace. Да даже если найду, дальше что? Мне нужен сеанс!
Ближе к 16:00, когда я понял, что дальше тянуть нельзя, я сделал ребут. В надежде, что такого больше не повторится (а это был первый случай за полгода работы), вздохнул с облегчением, все заработало. А зря. Второй день - та же ситуация. Копался часа полтора, опять непонятные попытки гуглить и прочее. Без результатов. Ребут. Под конец дня произошло еще раз. Ну, думаю, замечательно, спокойно приеду домой и посижу, поковыряюсь. Приезжаю домой, все уже нормально. Печально.
На третий день глянул вебинар, рассказали про интересный и эффективный способ поиска проблемы. Запомнил, но проблема больше не возникала. Прошла неделя и вот оно - опять блокировки! Потираю руки и начинаю действовать.
Первое - настраиваем журнал. Да, без него никак, но теперь я умею его читать. Ставим два события: первое TLOCK, второе TTIMEOUT. Первое отображает все события блокировки, второе показывает блокировки, которые не смогли установиться в отведенное им время. На самом деле, скорее всего, достаточно только TTIMEOUT.
Переходим в папку rphost_PID, находим текстовые файли и делаем поиск по слову TTIMEOUT. Видим строку:
К слову, папок rphost_PID может быть несколько, все зависит от того, сколько рабочих процессов запущено на сервере.
А дальше все просто: смотрим в конец строки - WaitConnections = 8239, это наш номер СОЕДИНЕНИЯ. Заходим в консоль сервера, переходим в Соединения, находим этот номер и смотрим номер сеанса. В моем случае на одного пользователя было два сеанса - сбойный и какой-то другой. Грохнул сеанс, на который указывал техжурнал. И о чудо! Все заработало, радости нет предела! Но, как выяснилось позже, сеанс был не зависший :), в нем работали. Поэтому на будущее - желательно связываться с пользователем и предупреждать.
На мой взгляд, достаточно типовое решение достаточно типовой проблемы. Неизвестно, почему оно мне не попалось, возможно из-за того, что приходилось его искать по тревоге, а когда пользователи не поджимали, то и тесты проводить не получалось - ошибки же нет.
Пример взаимоблокировки
Обычная причина возникновения взаимоблокировок — разный порядок блокирования строк таблиц.
Простой пример. Первая транзакция намерена перенести 100 рублей с первого счета на второй. Для этого она сначала уменьшает первый счет:
В это же время вторая транзакция намерена перенести 10 рублей со второго счета на первый. Она начинает с того, что уменьшает второй счет:
Теперь первая транзакция пытается увеличить второй счет, но обнаруживает, что строка заблокирована.
Затем вторая транзакция пытается увеличить первый счет, но тоже блокируется.
Возникает циклическое ожидание, который никогда не завершится само по себе. Через секунду первая транзакция, не получив доступ к ресурсу, инициирует проверку взаимоблокировки и обрывается сервером.
Теперь вторая транзакция может продолжить работу.
Правильный способ выполнения таких операций — блокирование ресурсов в одном и том же порядке. Например, в данном случае можно блокировать счета в порядке возрастания их номеров.
Читайте также: