Пошук уроків, статей та іншого контенту
Навчитеся розрізняти режими блокування таблиць і прогнозувати конфлікти між операціями.
Блокування таблиць координують паралельні операції над однією таблицею. PostgreSQL використовує їх, щоб несумісні операції не виконувалися одночасно.
Наприклад:
звичайний SELECT має безпечно читати структуру таблиці;
ALTER TABLE не повинен змінювати структуру під час виконання запиту, який її читає;
операція, що створює індекс або змінює службову інформацію таблиці, може конфліктувати з іншими операціями.
Блокування таблиці не є тим самим, що блокування окремих рядків. Операція UPDATE зазвичай бере:
блокування таблиці в режимі ROW EXCLUSIVE;
блокування змінених рядків.
У цьому уроці розглядається саме перший рівень — блокування таблиць.
PostgreSQL має вісім режимів блокування таблиць. Назви режимів описують їхню сумісність, а не рівень «сили» в абсолютно лінійному сенсі.
ACCESS SHAREРежим звичайного читання таблиці.
Його отримує команда SELECT. Він конфліктує лише з ACCESS EXCLUSIVE.
Тому звичайні SELECT можуть виконуватися паралельно з більшістю операцій над таблицею, але блокуються операціями, які потребують повного ексклюзивного доступу.
ROW SHAREРежим для операцій, які блокують окремі рядки, наприклад:
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;Такий запит не лише читає дані, а й планує блокувати вибрані рядки.
ROW EXCLUSIVEЦей режим отримують операції зміни даних:
INSERT;
UPDATE;
DELETE;
MERGE.
Він сумісний зі звичайним SELECT, але конфліктує з деякими режимами, потрібними для структурних або спеціальних операцій.
SHARE UPDATE EXCLUSIVEЦей режим використовується операціями, які змінюють службову інформацію таблиці або обслуговують її, зокрема деякими варіантами VACUUM, ANALYZE та CREATE INDEX CONCURRENTLY.
Він не дозволяє паралельно отримати інше блокування такого самого режиму або сильніше несумісне блокування.
SHAREДозволяє одночасно отримувати такий самий режим кількома транзакціями, але конфліктує з операціями зміни даних.
Звичайний приклад — створення індексу командою CREATE INDEX, без CONCURRENTLY.
SHARE ROW EXCLUSIVEСуворіший режим, який конфліктує і з операціями зміни даних, і з іншими блокуваннями спеціального призначення.
Цей режим рідше використовується безпосередньо розробником.
EXCLUSIVEЗабороняє паралельні операції, які можуть змінювати або блокувати рядки, але все ще допускає звичайне читання.
ACCESS EXCLUSIVEНайсуворіший режим. Він конфліктує з усіма іншими режимами, включно з ACCESS SHARE.
Його часто отримують операції зміни структури таблиці, наприклад:
ALTER TABLE;
DROP TABLE;
TRUNCATE;
REINDEX у звичайному режимі;
деякі інші команди DDL.
Поки утримується ACCESS EXCLUSIVE, навіть звичайний SELECT очікує завершення транзакції, яка взяла це блокування.
Конфлікт виникає, якщо вже надане блокування і нове блокування несумісні. У такому разі PostgreSQL зазвичай не завершує другий запит із помилкою, а переводить його в стан очікування.
Корисні правила для швидкого прогнозування:
ACCESS SHARE конфліктує лише з ACCESS EXCLUSIVE;
ROW EXCLUSIVE конфліктує з SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE та ACCESS EXCLUSIVE;
SHARE конфліктує з операціями зміни даних;
ACCESS EXCLUSIVE конфліктує з будь-яким іншим режимом;
кілька звичайних SELECT зазвичай не блокують один одного.
Наприклад, якщо транзакція вже виконує SELECT, що утримує ACCESS SHARE, то UPDATE не чекатиме через це блокування: ACCESS SHARE і ROW EXCLUSIVE сумісні.
Натомість ALTER TABLE зазвичай потребує ACCESS EXCLUSIVE, тому він чекатиме завершення активного SELECT.
LOCK TABLEТранзакція може явно запросити режим блокування:
BEGIN;
LOCK TABLE accounts IN SHARE MODE;
SELECT count(*)
FROM accounts;
COMMIT;Команда LOCK TABLE блокує таблицю в поточній транзакції. Блокування зберігається до COMMIT або ROLLBACK.
Якщо режим не вказати, використовується ACCESS EXCLUSIVE:
BEGIN;
LOCK TABLE accounts;
COMMIT;Через це режим краще вказувати явно. Надмірно суворе блокування може без потреби зупинити інші запити.
Можна вказати кілька таблиць:
BEGIN;
LOCK TABLE accounts, payments IN SHARE MODE;
COMMIT;Якщо транзакція повинна заблокувати кілька таблиць, корисно робити це в однаковому порядку в усіх частинах програми. Це зменшує ризик взаємного блокування.
Створимо тестову таблицю:
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id integer PRIMARY KEY,
balance numeric NOT NULL
);
INSERT INTO accounts (id, balance)
VALUES (1, 100);Тепер відкрийте два підключення до PostgreSQL.
У першому підключенні виконайте:
BEGIN;
LOCK TABLE accounts IN SHARE MODE;
SELECT *
FROM accounts;Транзакція ще не завершена, тому блокування залишається активним.
У другому підключенні виконайте:
BEGIN;
UPDATE accounts
SET balance = balance + 10
WHERE id = 1;UPDATE намагається отримати ROW EXCLUSIVE. Режими SHARE і ROW EXCLUSIVE конфліктують, тому другий запит чекатиме.
Поверніться до першого підключення:
COMMIT;Після цього блокування буде знято, а UPDATE у другому підключенні зможе продовжити виконання. Завершіть другу транзакцію:
COMMIT;Іноді очікування неприйнятне. Для цього використовують NOWAIT:
BEGIN;
LOCK TABLE accounts IN SHARE MODE NOWAIT;
COMMIT;Якщо потрібне блокування несумісне з уже наявним, команда одразу завершиться помилкою замість очікування.
Це корисно для задач, де застосунок повинен швидко повідомити, що ресурс зайнятий.
Більшість блокувань PostgreSQL бере автоматично. Розробнику важливо знати типові випадки:
SELECT — ACCESS SHARE;
SELECT ... FOR UPDATE і подібні варіанти — ROW SHARE;
INSERT, UPDATE, DELETE, MERGE — ROW EXCLUSIVE;
звичайний CREATE INDEX — SHARE;
багато операцій зміни структури таблиці — ACCESS EXCLUSIVE.
Конкретний режим може залежати від команди та її варіанта. Наприклад, створення індексу зі словом CONCURRENTLY використовує інший режим, ніж звичайне створення індексу.
Через це для оцінки впливу DDL-команди потрібно перевірити, яке блокування вона бере, а не робити висновок лише за її назвою.
Блокування таблиць, отримані в межах транзакції, зазвичай утримуються до її завершення.
Наприклад:
BEGIN;
UPDATE accounts
SET balance = balance - 10
WHERE id = 1;
-- Блокування ще утримуються
COMMIT;Якщо транзакція довга, блокування також можуть залишатися довго. Це збільшує імовірність того, що інші запити чекатимуть.
Тому важливо:
не залишати транзакції відкритими без потреби;
не виконувати повільні зовнішні операції всередині транзакції;
завершувати транзакцію одразу після критичної частини роботи;
не брати сильніше блокування, ніж необхідно.
Інформацію про блокування можна переглянути через системне представлення pg_locks:
SELECT
pid,
relation::regclass AS table_name,
mode,
granted
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY relation::regclass::text, pid;Основні стовпці:
pid — ідентифікатор серверного процесу;
table_name — таблиця, для якої запитується блокування;
mode — режим блокування;
granted — чи блокування вже надано.
Якщо granted має значення false, процес очікує на блокування.
Сам факт наявності блокування не означає проблему. Наприклад, багато паралельних SELECT можуть одночасно мати ACCESS SHARE. Проблема виникає, коли очікування триває довго або блокує важливі запити.
Дедлок виникає, коли дві транзакції чекають одна на одну:
транзакція A заблокувала таблицю або рядки X і чекає на Y;
транзакція B заблокувала Y і чекає на X.
PostgreSQL виявляє такі ситуації та скасовує одну з транзакцій із помилкою дедлоку.
Для зменшення ризику:
блокуйте кілька таблиць завжди в однаковому порядку;
підтримуйте транзакції короткими;
не змішуйте без потреби різні порядки оновлення;
обробляйте помилки транзакцій у застосунку та повторюйте операцію за потреби.
SELECT блокує UPDATEЗвичайний SELECT бере ACCESS SHARE, а UPDATE — ROW EXCLUSIVE. Ці режими сумісні, тому вони зазвичай не блокують один одного.
Це залежить від режиму. Наприклад, SHARE не сумісний із UPDATE, але сумісний зі звичайним SELECT. Лише ACCESS EXCLUSIVE конфліктує з усіма режимами.
Блокування не обов’язково знімається після завершення одного SQL-запиту. Якщо транзакція продовжується, блокування також може продовжувати діяти.
LOCK TABLE без указаного режимуУ такому разі застосовується ACCESS EXCLUSIVE, який блокує навіть читання. Краще вказувати мінімально необхідний режим явно.
Дві схожі команди можуть використовувати різні режими блокування. Важливо враховувати конкретний варіант операції та її вплив на паралельні транзакції.
PostgreSQL використовує вісім режимів блокування таблиць.
Звичайний SELECT отримує ACCESS SHARE.
Операції INSERT, UPDATE, DELETE та MERGE отримують ROW EXCLUSIVE.
ACCESS EXCLUSIVE конфліктує з усіма іншими режимами та часто використовується для зміни структури таблиці.
Несумісне блокування зазвичай змушує запит чекати, а не одразу завершується помилкою.
LOCK TABLE дозволяє явно вибрати режим блокування.
Блокування транзакції зазвичай утримуються до COMMIT або ROLLBACK.
Для уникнення тривалих очікувань і дедлоків потрібно підтримувати транзакції короткими та блокувати ресурси в узгодженому порядку.