Транзакционный DDL и блокирование строк в PostgreSQL

Как известно, СУБД PostgreSQL поддерживает транзакции при выполнении операций DDL, причем чаще всего мы можем выполнять внутри одной транзакции несколько DDL-запросов, придерживаясь стратегии «всё или ничего». Однако у этого подхода есть большой минус: если мы меняем несколько объектов, нам придется заблокировать все из них. А блокировка нескольких таблиц, с одной стороны, делает возможной взаимную блокировку (deadlock), а с другой — вынуждает пользователей ожидать выполнения всей транзакции. Именно поэтому рекомендуется использовать отдельную транзакцию для каждого запроса.

Тут стоит отметить, что параллельное создание индексов является особым случаем. СУБД PostgreSQL запрещает выполнение CREATE INDEX CONCURRENTLY внутри явно описанной транзакции — зато вместо этого PostgreSQL создает транзакции самостоятельно и управляет ими. Если же по каким-нибудь причинам построение индекса будет прервано до успешного завершения, то может возникнуть необходимость вручную удалить его, прежде чем попытаться еще раз. Однако такой индекс в любом случае не будет никогда применяться для обслуживания запросов.

Блокирование строк

У PostgreSQL существует много различных уровней блокировки, однако нас в рамках данной статьи будут интересовать в большей степени блокировки уровня таблицы, т. к. DDL обычно оперирует на этом уровне: • ACCESS EXCLUSIVE: запрещается любое использование заблокированной таблицы; • SHARE ROW EXCLUSIVE: запрещаются DDL-команды, которые выполняются параллельно, а также модификация строк (при этом чтение разрешено); • SHARE UPDATE EXCLUSIVE: запрещаются лишь DDL-команды, которые выполняются параллельно.

Важно отметить, что понятие “команды DDL, которые выполняются параллельно”, в этом контексте включают операции VACUUM и ANALYZE.

Итак, все DDL-операции блокируют таблицу одним из вышеописанных способов. Например, выполнив ALTER TABLE foos ADD COLUMN bar INTEGER; СУБД PostgreSQL попробует получить блокировку уровня ACCESS EXCLUSIVE, причем на всей таблице foos.

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

Основные подходы

Но вместо того чтобы надеяться на СУБД PostrgeSQL, вы можете выполнять явную блокировку самостоятельно — это даст возможность аккуратно контролировать время, на которое ваши запросы будут откладываться в очередь. Если же у вас не получается выполнить блокировку в течение нескольких секунд, то рекомендуют добавлять небольшую задержку непосредственно перед следующей попыткой. В результате вы позволите выполниться отложенным запросам, не создавая чересчур большую нагрузку в будущем. Кроме того, прежде чем пробовать запускать блокировку, запросите из pg_locks перечень долго выполняющихся запросов — это даст возможность избежать постановки в очередь тех команд, которые, по всей видимости, не выполнятся.

Уже начиная с PostgreSQL 9.3, вы сможете настроить параметр lock_timeout. Это позволит контролировать, насколько долго СУБД будет ждать получения контроля над таблицей. К примеру, если вы используете версию 9.2 либо даже более раннюю (т. к. они не поддерживаются, вам необходимо обновиться), у вас есть возможность достичь такого же результата, применяя параметр statement_timeout с явным выражением LOCK <table>.

Нередко блокировка уровня ACCESS EXCLUSIVE по настоящему необходима лишь на весьма короткий период, требуемый PostgreSQL для обновления его catalog tables (таблицы с метаданными). Иногда, чтобы избежать долгой приостановки SELECT/INSERT/UPDATE/DELETE, вполне достаточно более слабой блокировки либо применения альтернативных подходов.

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

По материалам «PostgreSQL at Scale: Database Schema Changes Without Downtime».