Пошук уроків, статей та іншого контенту
Розглянете режими блокування окремих рядків і їхній вплив на конкурентні UPDATE та DELETE.
У PostgreSQL кілька транзакцій можуть одночасно читати й змінювати одні й ті самі таблиці. Механізм MVCC дає змогу звичайним SELECT не блокуватися через зміни, які ще не зафіксовані.
Однак для операцій, що змінюють дані, потрібно узгодити доступ до конкретних рядків. Наприклад:
дві транзакції не повинні одночасно змінювати баланс одного рахунку;
рядок, який одна транзакція збирається видалити, не можна паралельно змінити іншій;
кілька працівників не повинні одночасно взяти одну й ту саму задачу в обробку.
Для цього PostgreSQL використовує блокування рядків.
Блокування рядка:
стосується окремого рядка, а не всієї таблиці;
зазвичай утримується до завершення транзакції;
може змусити іншу транзакцію чекати;
не блокує звичайний SELECT, який лише читає дані.
UPDATE і DELETEКоли транзакція виконує UPDATE, PostgreSQL блокує змінені рядки. Інша транзакція, яка намагається змінити той самий рядок, чекатиме завершення першої транзакції.
Розглянемо приклад:
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);У першій сесії:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
-- Транзакція ще не завершена, тому блокування утримується.У другій сесії:
BEGIN;
UPDATE accounts
SET balance = balance + 50
WHERE id = 1;Другий UPDATE чекатиме, тому що рядок із id = 1 уже заблокований першою транзакцією.
Якщо в першій сесії виконати:
COMMIT;друга транзакція продовжить виконання. Після цього її можна завершити:
COMMIT;Якщо перша транзакція виконає ROLLBACK, друга транзакція також продовжить роботу, але вже після скасування першої зміни.
Блокування утримується до COMMIT або ROLLBACK, а не до завершення окремої SQL-команди.
Тому не варто залишати відкриту транзакцію під час:
очікування введення користувача;
виконання повільних зовнішніх запитів;
обробки великих обсягів даних, якщо це можна зробити окремо;
тривалих операцій, не пов’язаних із базою даних.
Чим довше утримується блокування, тим довше інші транзакції можуть чекати.
SELECT ... FORІноді рядок потрібно заблокувати ще до того, як виконується UPDATE або DELETE. Для цього використовується SELECT ... FOR.
Найсильніший режим — FOR UPDATE:
BEGIN;
SELECT id, owner, balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Тут виконується перевірка або розрахунок на основі заблокованого рядка.
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;
COMMIT;Після SELECT ... FOR UPDATE інша транзакція не зможе паралельно змінити або заблокувати цей рядок у конфліктному режимі.
Такий підхід корисний, коли операція складається з кількох кроків:
знайти рядок;
перевірити його поточний стан;
виконати обчислення;
змінити або видалити рядок.
Без блокування дві транзакції можуть прочитати одне й те саме старе значення та прийняти рішення на основі вже неактуальних даних.
PostgreSQL має чотири режими блокування, які можна явно отримати через SELECT ... FOR:
FOR UPDATE;
FOR NO KEY UPDATE;
FOR SHARE;
FOR KEY SHARE.
FOR UPDATEFOR UPDATE — найсильніший режим блокування рядка.
Він використовується, коли транзакція планує:
змінити рядок;
видалити рядок;
виконати операцію, для якої потрібен ексклюзивний доступ до поточного стану рядка.
Приклад:
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Інша транзакція не зможе змінити або видалити цей рядок,
-- доки поточна транзакція не завершиться.
COMMIT;FOR UPDATE конфліктує з усіма чотирма режимами блокування рядків.
FOR NO KEY UPDATEFOR NO KEY UPDATE слабший за FOR UPDATE. Він означає, що рядок можна змінювати, але його ключові значення не захоплюються найсильнішим режимом.
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR NO KEY UPDATE;
COMMIT;Звичайний UPDATE зазвичай отримує блокування, еквівалентне FOR NO KEY UPDATE.
Якщо UPDATE змінює ключ, на який посилається зовнішній ключ, PostgreSQL може використовувати сильніше блокування, еквівалентне FOR UPDATE.
FOR SHAREFOR SHARE використовується, коли транзакція хоче переконатися, що рядок не буде змінено або видалено до завершення її роботи.
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR SHARE;
-- Рядок можна читати узгоджено,
-- але конфліктні зміни чекатимуть.
COMMIT;Кілька транзакцій можуть одночасно отримати FOR SHARE для одного рядка. Проте UPDATE, DELETE і сильніші блокування конфліктують із цим режимом.
FOR KEY SHAREFOR KEY SHARE — найслабший режим блокування рядка. Він захищає ключові значення від змін або видалення, але допускає деякі інші зміни.
Цей режим, зокрема, використовується механізмами перевірки зовнішніх ключів.
BEGIN;
SELECT id
FROM accounts
WHERE id = 1
FOR KEY SHARE;
COMMIT;FOR KEY SHARE конфліктує з операціями, які можуть змінити або видалити ключ рядка, але не конфліктує з іншими блокуваннями FOR KEY SHARE.
Режими блокування можна уявити так:
| Запит | Конфліктує з | |---|---| | FOR UPDATE | усіма режимами | | FOR NO KEY UPDATE | FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE | | FOR SHARE | FOR UPDATE, FOR NO KEY UPDATE | | FOR KEY SHARE | FOR UPDATE |
Це означає, наприклад:
два FOR SHARE можуть працювати одночасно;
два FOR KEY SHARE можуть працювати одночасно;
FOR SHARE і FOR KEY SHARE можуть працювати одночасно;
FOR UPDATE чекатиме завершення будь-якого іншого конфліктного блокування;
DELETE блокується активним FOR SHARE;
UPDATE блокується активним FOR SHARE, якщо він отримує FOR NO KEY UPDATE.
Проста перевірка у двох сесіях:
Перша сесія:
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR SHARE;Друга сесія:
BEGIN;
UPDATE accounts
SET balance = balance + 10
WHERE id = 1;Другий запит чекатиме, доки перша транзакція не завершиться:
-- Перша сесія
COMMIT;Після цього друга сесія зможе продовжити:
COMMIT;UPDATEРозглянемо сценарій із перевіркою балансу.
У першій сесії:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;Припустімо, результат — 1000.00.
У цей момент друга сесія може виконати звичайний SELECT:
SELECT balance
FROM accounts
WHERE id = 1;Звичайне читання не заблокується. Воно побачить доступну версію рядка відповідно до рівня ізоляції транзакції.
Але спроба змінити рядок чекатиме:
UPDATE accounts
SET balance = balance - 900
WHERE id = 1;Після завершення першої транзакції друга транзакція продовжить роботу. PostgreSQL повторно перевіряє умови зміни з урахуванням актуального стану рядка.
Блокування не скасовує необхідність правильної умови WHERE. Якщо рядок змінюється за певною ознакою, цю ознаку потрібно включати в умову:
UPDATE accounts
SET balance = balance - 100
WHERE id = 1
AND balance >= 100;Так база даних не дозволить виконати операцію, якщо після очікування блокування баланс уже не відповідає умові.
DELETEDELETE отримує сильне блокування рядка. Тому він конфліктує з іншими операціями, які утримують відповідне блокування.
Перша сесія:
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR SHARE;Друга сесія:
BEGIN;
DELETE FROM accounts
WHERE id = 1;DELETE чекатиме, доки перша транзакція не завершиться:
-- Перша сесія
COMMIT;Після цього друга сесія може видалити рядок і завершити транзакцію:
COMMIT;Якщо ж перша транзакція виконає ROLLBACK, рядок залишиться в таблиці, і DELETE зможе продовжити роботу.
NOWAIT: не чекати блокуванняЗа замовчуванням конфліктна операція чекає. Якщо чекати не потрібно, використовується NOWAIT.
BEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE NOWAIT;Якщо рядок уже заблокований іншою транзакцією, запит одразу завершиться помилкою замість очікування.
Це корисно, коли застосунок може повідомити користувача:
запис зараз редагується іншим користувачем;
операцію потрібно повторити пізніше;
ресурс тимчасово недоступний.
NOWAIT застосовується до блокування, яке запит намагається отримати. Він не змінює вже взяті блокування і не скасовує очікування в інших запитах.
SKIP LOCKED: пропустити заблоковані рядкиSKIP LOCKED наказує PostgreSQL не чекати на заблоковані рядки, а пропустити їх.
BEGIN;
SELECT id, owner, balance
FROM accounts
ORDER BY id
FOR UPDATE SKIP LOCKED;
COMMIT;Це корисно, коли транзакція може працювати з будь-яким доступним рядком, а не з конкретним рядком. Наприклад, кілька обробників можуть розподіляти між собою незалежні записи.
Однак результат із SKIP LOCKED може бути неповним: заблоковані рядки навмисно не потрапляють до вибірки. Тому цей режим не підходить для звичайного читання, де потрібні всі рядки.
Створимо просту чергу задач:
CREATE TABLE jobs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload text NOT NULL,
status text NOT NULL DEFAULT 'pending'
);
INSERT INTO jobs (payload)
VALUES
('Надіслати лист'),
('Створити звіт'),
('Оновити індекси'),
('Очистити тимчасові файли');Два обробники можуть брати різні задачі:
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;Після вибору задачі той самий обробник може змінити її статус:
UPDATE jobs
SET status = 'processing'
WHERE id = 1;
COMMIT;Інший обробник, який одночасно виконає такий самий SELECT, пропустить заблокований рядок і візьме іншу задачу.
У реальному коді ідентифікатор потрібно передавати з результату SELECT, а не жорстко задавати як 1. Повна послідовність у SQL-сесії може мати такий вигляд:
BEGIN;
WITH next_job AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
UPDATE jobs
SET status = 'processing'
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.payload;
COMMIT;Усі дії від вибору задачі до зміни її статусу виконуються в одній транзакції. Тому інший обробник не візьме той самий рядок.
SELECTЗвичайний запит без FOR не бере блокування рядка, яке конфліктує з UPDATE або DELETE:
SELECT *
FROM accounts
WHERE id = 1;Такий запит може виконуватися, поки інша транзакція змінює той самий рядок. Це одна з основних властивостей MVCC.
Якщо потрібен саме захищений стан рядка для подальшої дії, треба явно вказати режим:
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;Отже, різниця така:
SELECT — прочитати доступну версію без блокування рядка для запису;
SELECT ... FOR UPDATE — прочитати рядок і зарезервувати його для наступної зміни;
SELECT ... FOR SHARE — прочитати рядок і не дозволити конфліктну зміну до завершення транзакції.
Якщо транзакція заблокована, вона може чекати довго. Для сценаріїв, де очікування неприпустиме, використовуйте NOWAIT або налаштовуйте обмеження часу очікування блокування на рівні сесії.
Блокування не звільняється після SELECT або UPDATE. Воно утримується до COMMIT чи ROLLBACK.
Потрібно завершувати транзакції одразу після завершення критичної операції.
SKIP LOCKED для повного читанняSKIP LOCKED призначений для розподілу роботи, а не для отримання повного узгодженого набору даних. Заблоковані рядки будуть пропущені.
Блокування не замінює перевірку бізнес-умов:
UPDATE accounts
SET balance = balance - 100
WHERE id = 1
AND balance >= 100;Умова має гарантувати, що після очікування блокування операція все ще допустима.
Якщо одна транзакція спочатку блокує рядок 1, а потім рядок 2, а інша — спочатку 2, а потім 1, вони можуть утворити взаємне блокування — deadlock.
Щоб зменшити ризик, рядки варто блокувати в однаковому порядку, наприклад за id.
PostgreSQL виявляє deadlock і скасовує одну з транзакцій. Застосунок має бути готовим повторити таку операцію.
UPDATE і DELETE блокують змінені або видалені рядки до завершення транзакції.
Звичайний SELECT не конфліктує з такими блокуваннями завдяки MVCC.
SELECT ... FOR UPDATE дає найсильніше блокування для безпечної послідовності «прочитати — перевірити — змінити».
FOR NO KEY UPDATE, FOR SHARE і FOR KEY SHARE дають слабші режими для точнішого контролю конкуренції.
NOWAIT одразу повертає помилку, якщо рядок заблокований.
SKIP LOCKED пропускає заблоковані рядки й корисний для конкурентної обробки черг.
Блокування утримуються до COMMIT або ROLLBACK, тому транзакції мають бути короткими.
Для безпеки потрібно враховувати умови WHERE, порядок блокування і можливість deadlock.