Исследуем блокировки в PostgreSQL

Сегодня предлагаю вам вольный перевод весьма увлекательной и забавной статьи «Exploring Query Locks in Postgres».

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

Для начала создадим «песочницу»:

Откроем два терминала, в каждом из них подключимся к только что созданной базе данных sandbox. Чтобы не путаться, дадим им имена. Пусть это будут Алиса и Боб. Изменить подсказку командной строки можно с помощью команды \set :

Первой появляется Алиса и осматривает игрушки:

Обратите внимание, что оператор begin начинает транзакцию явно. В этом случае она будет продолжаться до тех пор, пока мы не зафиксируем её, сделаем commit , или не откатим, сделаем rollback .

Если бы Боб сейчас посмотрел на игрушки, он увидел бы то же самое:

Таким образом параллельное выполнение двух операторов select не мешает работе каждого из них. Именно такого поведения мы ожидаем от надёжной и высокопроизводительной базы данных.

Однако, транзакции Алисы и Боба до сих пор открыты. Чтобы посмотреть какие блокировки были установлены, откроем третий терминал и назовём его Ева:

Представление pg_lock показывает активные блокировки. Условие (db.datname = 'sandbox' or db.datname is null) оставляет только те записи которые относятся к «песочнице», а условие not pid = pg_backend_pid() исключает записи текущей сессии. Наконец, чтобы колонка relation стала более информативной было использовано приведение типа к regclass .

Посмотрим на пятую строку:

Виртуальной транзакцией 1/282, на таблицу toys наложена блокировка AccessShareLock , при этом блокировка считается выданной (is granted). Пока всё идёт хорошо, Боб и Алиса счастливы, ведь они оба видят — игрушки можно взять.

Обратите внимание, каждая транзакция удерживает блокировку ExclusiveLock на своей виртуальной транзакции virtualxid .

Алиса решает взять машинку:

Никаких проблем. Посмотрим как выглядит таблица блокировок теперь:

В таблице toys на записи с машинкой теперь стоит блокировка RowExclusiveLock . Также появился реальный идентификатор транзакции transactionid на котором удерживается блокировка ExclusiveLock . Такой идентификатор появляется у каждой транзакции потенциально меняющей состояние базы данных.

Поскольку транзакция Алисы не зафиксирована, Боб видит прежние данные:

Мы не знаем, будет ли Алиса фиксировать или откатывать свою транзакцию. Следовательно, Боб видит содержимое таблицы неизменным.

Для того, чтобы каждый пользователь видел согласованное стостояние базы данных, постгрес использует механизм управления конкурентным доступом с помощью многоверсионности MVCC (Multi Version Concurrency Control).

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

но ничего не происходит. Ему нужно подождать пока Алиса завершит свою транзакцию. Снова посмотрим в таблицу блокировок:

Теперь у Боба тоже есть transactionid и он просит выдать ему ShareLock на transactionid Алисы — «Мам, я тоже хочу поиграть машинкой». Поскольку две блокировки конфликтуют друг c другом, запрос Боба не удовлетворён (is not granted). Он будет висеть в таком состоянии до тех пор, пока Алиса не снимет ExclusiveLock , завершив свою транзакцию.

pg_stat_activity ещё одно интересное представление (view) из pg_catalog’а. Оно показывает запросы выполняющиеся в данный момент:

Мы видим, что запрос Алисы простаивает в ожидании поддтверждения транзакции (idle in transaction), в то время как запрос Боба активен и подвис (is waiting).

Чтобы увидеть кто кого заблокировал, объединим два запроса в один:

Если бы Алиса решила откатить или зафиксировать свою транзакцию, блокировка ExclusiveLock была бы снята и Боб получил бы ShareLock . После этого он мог бы зафиксировать свою транзакцию, и запись в таблице была бы обновлена независимо от решения Алисы.

Конечно, если бы Боб и Алиса решили играть разными игрушками, конфликтной ситуации между ними не возникло бы вообще.

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

Хотя Алиса и не взяла ни одной игрушки, Боб всё равно должен ждать.

Таблица блокировок теперь выглядит так:

Поскольку Алиса удерживает AccessExclusiveLock без изменения состояния базы данных, то она не получила свой transactionid . У Боба его тоже нет, потому что он не получил RowExclusiveLock на таблицу toys . В этой ситуации, запрос для отображения блокировок который мы использовали ранее, нам не поможет, т. к. он использует объединение по transactionid .

Таким образом, Ева думает, что всё хорошо, в то время как следующий запрос:

показывает обратное. Обратите внимание на столбец waiting_duration , он рассчитывается как разница между now() и query_start . Благодаря этому, видно сколько времени запрос уже висит.

Сделав объединение по столбцам relation и locktype , мы снова можем видеть кто кого блокирует:

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

Но его транзакция всё ещё открыта. Если мы посмотрим в таблицу блокировок, то увидим следующее:

Лишь после того как Боб получил RowExclusiveLock , к его транзакции был добавлен transactionid . Боб рад и делает коммит:

Поскольку Алиса не знает какую игрушку она хочет взять, а ставить явную блокировку ей не разрешили, она пробует другой подход:

На детском языке это бы звучало примерно так: «Хочу видеть все игрушки и может быть я возьму одну, но пока не знаю какую. А до тех пор я не хочу чтобы кто-то другой прикасался к ним».

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

Ева видит следующую ситуацию:

Боб совершенно ясно хочет изменить состояние базы данных поэтому он получил transactionid равный 19310, но снова вынужден ждать получения ShareLock на транзакцию Алисы с номером 19309.

Пришло время объединить таблицу блокировок и таблицу активности вместе, так, чтобы всегда видеть кто кого заблокировал:

Для оценки времени блокирования запроса был добавлен столбец waiting_duration , а также функция current_database() используемая в условии.

Ева не может запомнить этот длиннющий запрос и создаёт представление:

С помощью него, она легко узнает что задумали её дети:

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