Пошук уроків, статей та іншого контенту
Зрозумієте, як блокування координують одночасні операції читання й запису в базі даних.
Блокування — це механізм координації одночасних транзакцій. Воно визначає, які операції можуть виконуватися паралельно, а які повинні зачекати завершення іншої транзакції.
Наприклад, дві транзакції одночасно змінюють один і той самий рядок. Без координації одна зміна могла б випадково перезаписати іншу. PostgreSQL використовує блокування разом із MVCC, щоб забезпечити узгодженість даних.
Блокування потрібні для:
захисту рядків під час оновлення або видалення;
синхронізації операцій, які змінюють одні й ті самі дані;
узгодження змін структури таблиць;
явного резервування рядків для подальшої обробки.
PostgreSQL використовує MVCC — багатоверсійну модель конкурентного доступу.
Коли транзакція змінює рядок, PostgreSQL не просто «переписує» старе значення. Створюється нова версія рядка, а попередня версія залишається доступною для транзакцій, яким вона потрібна.
Через це звичайне читання зазвичай не блокує запис:
SELECT * FROM accounts WHERE id = 1;І запис зазвичай не блокує звичайне читання:
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;Проте це не означає, що блокувань немає. PostgreSQL все одно встановлює блокування для операцій запису та для спеціальних типів читання, наприклад SELECT ... FOR UPDATE.
Важливо розрізняти:
MVCC визначає, яку версію даних бачить транзакція;
блокування визначають, які операції можуть конфліктувати між собою.
Операції UPDATE, DELETE і деякі варіанти SELECT блокують окремі рядки.
Розглянемо таблицю рахунків:
CREATE TABLE accounts (
id integer PRIMARY KEY,
owner text NOT NULL,
balance numeric(12, 2) NOT NULL
);
INSERT INTO accounts (id, owner, balance)
VALUES (1, 'Олена', 1000.00);Відкрийте два з’єднання з PostgreSQL і виконайте в першому:
BEGIN;
UPDATE accounts
SET balance = balance + 100
WHERE id = 1;Транзакція змінила рядок, але ще не завершилася. Блокування рядка залишається активним.
У другому з’єднанні виконайте:
BEGIN;
UPDATE accounts
SET balance = balance - 50
WHERE id = 1;Цей UPDATE чекатиме, доки перша транзакція не виконає COMMIT або ROLLBACK.
У першому з’єднанні:
COMMIT;Після цього друга транзакція зможе продовжити виконання. Завершіть її:
COMMIT;Якщо перша транзакція виконає ROLLBACK, її зміни буде скасовано, а друга транзакція працюватиме з актуальним станом даних.
Блокування, отримане під час зміни рядка, зазвичай утримується до завершення транзакції:
BEGIN;
UPDATE accounts
SET balance = balance + 100
WHERE id = 1;
-- Рядок залишається заблокованим
COMMIT;
-- Блокування звільненеТому довгі транзакції можуть спричинити довге очікування інших транзакцій.
Іноді потрібно спочатку прочитати рядок, перевірити його стан, а потім змінити. Для цього використовують SELECT ... FOR UPDATE.
BEGIN;
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Рядок зарезервовано цією транзакцією
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;
COMMIT;FOR UPDATE блокує вибрані рядки від конфліктних змін в інших транзакціях. Інша транзакція може чекати на це блокування під час:
UPDATE;
DELETE;
іншого SELECT ... FOR UPDATE.
Звичайний SELECT без FOR UPDATE при цьому зазвичай не чекатиме:
SELECT id, balance
FROM accounts
WHERE id = 1;Він прочитає доступну версію рядка відповідно до рівня ізоляції транзакції.
Припустімо, з рахунка можна списувати кошти лише за достатнього балансу:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Після блокування можна безпечно перевірити баланс
UPDATE accounts
SET balance = balance - 200
WHERE id = 1
AND balance >= 200;
COMMIT;Без FOR UPDATE дві транзакції могли б одночасно прочитати один і той самий баланс і прийняти рішення на основі застарілого значення.
На практиці перевірку результату UPDATE також потрібно обробляти в коді застосунку: якщо UPDATE не змінив жодного рядка, коштів могло бути недостатньо або запис міг не існувати.
За замовчуванням конфліктна операція чекає, поки блокування буде звільнено.
NOWAITNOWAIT наказує не чекати на блокування, а негайно завершити операцію помилкою:
BEGIN;
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE NOWAIT;Це корисно, коли застосунок має одразу повідомити, що ресурс зайнятий.
SKIP LOCKEDSKIP LOCKED пропускає заблоковані рядки та повертає лише доступні:
SELECT id, owner, balance
FROM accounts
ORDER BY id
FOR UPDATE SKIP LOCKED;Такий підхід часто використовують для обробки черг: кілька працівників можуть вибирати різні доступні записи, не чекаючи один на одного.
Важливо, що SKIP LOCKED змінює семантику вибірки: результат може не містити рядків, які існують, але тимчасово заблоковані.
PostgreSQL також встановлює блокування на рівні таблиць. Вони потрібні, щоб узгодити операції зі структурою таблиці та деякі операції з її вмістом.
Наприклад:
BEGIN;
LOCK TABLE accounts IN SHARE MODE;
COMMIT;Рівні блокування таблиць відрізняються силою та сумісністю. Сильніше блокування конфліктує з більшою кількістю інших операцій.
Операції, які змінюють структуру таблиці, можуть отримувати сильні блокування. Наприклад:
ALTER TABLE accounts
ADD COLUMN currency text;Якщо інша транзакція довго утримує сумісне або конфліктне блокування на цій таблиці, ALTER TABLE може чекати.
У більшості прикладного коду явне блокування всієї таблиці не потрібне. Зазвичай краще блокувати конкретні рядки, якщо бізнес-операція стосується саме їх.
Блокування конфліктують не з усіма операціями. Наприклад:
два звичайні SELECT зазвичай можуть виконуватися паралельно;
UPDATE одного рядка може чекати на UPDATE цього самого рядка;
UPDATE одного рядка не обов’язково блокує SELECT без FOR UPDATE;
SELECT ... FOR UPDATE конфліктує з іншими операціями, які намагаються змінити або заблокувати цей рядок;
зміна структури таблиці може чекати на транзакції, що використовують цю таблицю.
Ключовим є не лише тип SQL-команди, а й об’єкт, із яким вона працює.
Два UPDATE різних рядків однієї таблиці зазвичай не блокують один одного:
-- Перша транзакція
UPDATE accounts
SET balance = balance + 100
WHERE id = 1;
-- Друга транзакція
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;Водночас вони все одно можуть отримувати блокування на рівні таблиці, потрібні для узгодження операції. Такі блокування зазвичай сумісні між собою та не спричиняють очікування.
Взаємне блокування, або deadlock, виникає, коли транзакції чекають одна на одну.
Перша транзакція:
BEGIN;
UPDATE accounts
SET balance = balance + 10
WHERE id = 1;Друга транзакція:
BEGIN;
UPDATE accounts
SET balance = balance + 10
WHERE id = 2;Тепер перша транзакція намагається змінити другий рядок:
UPDATE accounts
SET balance = balance + 10
WHERE id = 2;А друга — перший:
UPDATE accounts
SET balance = balance + 10
WHERE id = 1;Перша транзакція чекає на другу, а друга — на першу. PostgreSQL виявить цей цикл і скасує одну з транзакцій із помилкою deadlock.
Найважливіше правило — змінювати кілька ресурсів у стабільному порядку.
Наприклад, якщо транзакція працює з рахунками 1 і 2, завжди спочатку блокуйте рахунок із меншим ідентифікатором:
BEGIN;
SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;
-- Подальші зміни виконуються в тому самому порядку
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;Також допомагають:
короткі транзакції;
однаковий порядок доступу до таблиць і рядків;
відсутність зайвих операцій усередині транзакції;
повторення всієї транзакції після помилки deadlock на рівні застосунку.
Deadlock не можна безпечно «проковтнути» лише повторенням окремого SQL-запиту: після помилки транзакція перебуває в стані помилки, тому її потрібно відкликати або повторити транзакцію повністю.
Для аналізу очікування можна переглянути системне представлення pg_locks:
SELECT
locktype,
relation::regclass AS relation_name,
mode,
granted,
pid
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY relation_name, pid;Поле granted показує, чи отримано блокування:
true — блокування вже надано;
false — процес очікує на нього.
Щоб побачити активні запити та їхній стан, використовуйте pg_stat_activity:
SELECT
pid,
state,
wait_event_type,
wait_event,
query_start,
query
FROM pg_stat_activity
WHERE state <> 'idle';Якщо wait_event_type має значення Lock, запит очікує на блокування.
Завершувати чужі сесії потрібно обережно. Спочатку варто визначити причину довгої транзакції та перевірити, чи не призведе її завершення до втрати незбереженої роботи.
Щоб запит не чекав на блокування безмежно, можна встановити lock_timeout:
BEGIN;
SET LOCAL lock_timeout = '3s';
UPDATE accounts
SET balance = balance + 50
WHERE id = 1;
COMMIT;SET LOCAL діє лише до завершення поточної транзакції.
Якщо блокування не буде отримано за вказаний час, PostgreSQL завершить команду помилкою. Застосунок має обробити цю помилку: повторити операцію, повідомити користувача або обрати іншу стратегію.
lock_timeout не скасовує транзакцію як концепцію. Після помилки SQL-команди транзакцію зазвичай потрібно завершити через ROLLBACK, перш ніж виконувати нові команди.
Переказ між двома рахунками змінює два рядки. Тому обидва рядки потрібно блокувати в однаковому порядку:
BEGIN;
-- Блокуємо рахунки у стабільному порядку
SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;
-- Списуємо кошти з рахунка 1
UPDATE accounts
SET balance = balance - 100
WHERE id = 1
AND balance >= 100;
-- Зараховуємо кошти на рахунок 2
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;
COMMIT;У реальному застосунку потрібно додатково перевірити:
чи існують обидва рахунки;
чи справді з рахунка-джерела списано потрібну суму;
чи не виникла помилка під час будь-якої операції.
Якщо будь-яка частина операції не може бути виконана, слід виконати ROLLBACK, щоб не залишити лише половину переказу.
Якщо транзакція відкривається перед виконанням повільної операції або очікуванням введення користувача, вона може довго утримувати блокування.
Погано:
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Тут виконується довга операція поза базою даних
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;Краще виконувати всі необхідні дії всередині транзакції швидко й не залишати транзакцію відкритою між запитами користувача.
Запит за замовчуванням може чекати дуже довго. Для критичних операцій варто розглянути lock_timeout, NOWAIT або SKIP LOCKED — залежно від потрібної поведінки.
Блокування всієї таблиці, коли достатньо заблокувати кілька рядків, зменшує паралельність і може створити зайве очікування.
Якщо різні частини застосунку блокують одні й ті самі рядки в різному порядку, зростає ризик deadlock. Потрібно домовитися про стабільний порядок доступу.
Deadlock, тайм-аут блокування або помилка конфліктної транзакції — нормальні сценарії для конкурентної системи. Застосунок має коректно завершувати невдалу транзакцію та, за потреби, повторювати всю операцію.
PostgreSQL поєднує MVCC із блокуваннями для безпечної роботи конкурентних транзакцій.
Звичайні читання зазвичай не блокують запис і не чекають на нього.
UPDATE і DELETE блокують змінені рядки до завершення транзакції.
SELECT ... FOR UPDATE дає змогу явно заблокувати рядки перед перевіркою та зміною.
NOWAIT завершує операцію одразу, якщо рядок зайнятий.
SKIP LOCKED пропускає заблоковані рядки.
Тривалі транзакції можуть утримувати блокування та затримувати інші запити.
Deadlock виникає через циклічне очікування; стабільний порядок блокування зменшує його ризик.
pg_locks і pg_stat_activity допомагають знаходити запити, які очікують на блокування.