Пошук уроків, статей та іншого контенту
Застосуєте SELECT ... FOR UPDATE для безпечного читання та подальшої зміни вибраних рядків.
SELECT ... FOR UPDATEЗвичайний SELECT читає рядки, але не забороняє іншій транзакції змінити їх одразу після читання.
Це небезпечно, коли логіка має вигляд:
прочитати рядок;
перевірити його стан;
змінити цей самий рядок;
зафіксувати зміни.
Між кроками 1 і 3 інша транзакція може змінити рядок. Для захисту від такої ситуації PostgreSQL підтримує блокування рядків:
SELECT ...
FROM ...
WHERE ...
FOR UPDATE;FOR UPDATE:
читає вибрані рядки;
встановлює на них блокування;
не дозволяє іншим транзакціям змінювати або видаляти ці рядки до завершення поточної транзакції;
утримує блокування до COMMIT або ROLLBACK.
Блокування діє лише в межах транзакції. Якщо виконати SELECT ... FOR UPDATE поза явною транзакцією, транзакція завершиться одразу після запиту, тому практичної користі від блокування не буде.
Нехай є таблиця рахунків:
CREATE TABLE accounts (
id integer PRIMARY KEY,
owner_name text NOT NULL,
balance numeric(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (id, owner_name, balance)
VALUES
(1, 'Олена', 1000.00),
(2, 'Петро', 500.00);Переказ має виконуватися атомарно: гроші потрібно зняти з одного рахунку та додати до іншого в межах однієї транзакції.
BEGIN;
-- Блокуємо рахунок відправника до завершення транзакції
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Застосунок перевіряє баланс і продовжує операцію лише за достатньої суми
UPDATE accounts
SET balance = balance - 200.00
WHERE id = 1
AND balance >= 200.00;
-- Додаємо кошти на рахунок отримувача
UPDATE accounts
SET balance = balance + 200.00
WHERE id = 2;
COMMIT;Після FOR UPDATE інша транзакція не зможе паралельно змінити рахунок id = 1, поки перша транзакція не завершиться.
Однак у реальному коді важливо перевіряти, чи справді списання відбулося. Наприклад, якщо балансу недостатньо, UPDATE не змінить жодного рядка. Тоді транзакцію потрібно скасувати, а не зараховувати гроші отримувачу.
BEGIN;
-- Вибираємо рахунок і блокуємо його
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Якщо баланс недостатній, застосунок має виконати ROLLBACK
UPDATE accounts
SET balance = balance - 200.00
WHERE id = 1
AND balance >= 200.00;
-- Цей запит має виконуватися лише якщо списання змінило один рядок
UPDATE accounts
SET balance = balance + 200.00
WHERE id = 2;
COMMIT;Сам SQL-код переказу можна зробити без окремого читання балансу, перевіряючи кількість змінених рядків. Але FOR UPDATE корисний, коли між читанням і зміною потрібні додаткові перевірки або складніша бізнес-логіка.
Відкрийте два з’єднання з PostgreSQL.
У першому з’єднанні виконайте:
BEGIN;
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;Рядок буде заблокований, але транзакція залишиться активною.
У другому з’єднанні спробуйте змінити цей рядок:
UPDATE accounts
SET balance = balance + 100
WHERE id = 1;Цей запит чекатиме, поки перша транзакція завершиться.
Поверніться до першого з’єднання:
COMMIT;Після цього друга транзакція зможе продовжити виконання та змінити рядок.
Якщо замість COMMIT виконати:
ROLLBACK;зміни першої транзакції буде скасовано, а блокування звільниться.
Блокування FOR UPDATE призначене для захисту рядків, які можуть бути змінені поточною транзакцією.
Інші транзакції зазвичай можуть:
читати ці рядки звичайним SELECT;
виконувати запити, які не потребують конфліктного блокування.
Але вони чекатимуть під час операцій, які змінюють або видаляють заблоковані рядки:
UPDATE;
DELETE;
деяких інших locking-запитів, зокрема SELECT ... FOR UPDATE.
Це важлива відмінність: FOR UPDATE не приховує рядок від усіх читачів. Він захищає рядок від конфліктних змін.
Блокування отримують рядки, які повертає запит після застосування умов WHERE.
BEGIN;
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Заблоковано лише рахунок із id = 1
COMMIT;Тому умова WHERE має бути якомога точнішою. Не слід без потреби блокувати всю таблицю:
BEGIN;
-- Блокує всі рядки таблиці accounts
SELECT id, balance
FROM accounts
FOR UPDATE;
ROLLBACK;Чим більше рядків заблоковано, тим вища ймовірність:
очікування інших транзакцій;
зниження паралельності;
конфліктів блокувань;
взаємного блокування транзакцій.
FOR UPDATE з JOINБлокування можна використовувати разом із JOIN. За замовчуванням PostgreSQL блокує рядки таблиць, які є ціллю запиту.
BEGIN;
SELECT a.id, a.owner_name, a.balance
FROM accounts AS a
WHERE a.balance < 100
FOR UPDATE OF a;
COMMIT;Запис FOR UPDATE OF a явно вказує, що блокувати потрібно рядки таблиці з псевдонімом a.
Це корисно, коли запит читає кілька таблиць, але змінювати потрібно лише одну з них.
За замовчуванням FOR UPDATE чекатиме, коли інша транзакція звільнить блокування:
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE;Іноді очікування неприйнятне. Для цього є модифікатор NOWAIT:
SELECT id, balance
FROM accounts
WHERE id = 1
FOR UPDATE NOWAIT;Якщо рядок уже заблокований, PostgreSQL одразу поверне помилку замість очікування. Застосунок може перехопити цю помилку та повідомити користувачу, що операцію потрібно повторити.
Інший варіант — SKIP LOCKED:
SELECT id, balance
FROM accounts
WHERE balance >= 100
ORDER BY id
FOR UPDATE SKIP LOCKED;У цьому випадку заблоковані рядки пропускаються, а запит повертає доступні рядки. Такий режим корисний, коли кілька працівників обробляють незалежні записи та не повинні чекати один на одного.
NOWAIT і SKIP LOCKED змінюють поведінку очікування, але не скасовують вимогу працювати всередині транзакції.
Якщо транзакції блокують кілька рядків у різному порядку, може виникнути deadlock — взаємне блокування.
Наприклад:
транзакція A заблокувала рахунок 1 і чекає на рахунок 2;
транзакція B заблокувала рахунок 2 і чекає на рахунок 1.
PostgreSQL виявить таку ситуацію та скасує одну з транзакцій.
Для переказів між двома рахунками корисно завжди блокувати їх в однаковому порядку — наприклад, за зростанням id:
BEGIN;
-- Спочатку блокуємо рахунок із меншим id
SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;
-- Після цього виконуємо перевірки та зміни
UPDATE accounts
SET balance = CASE
WHEN id = 1 THEN balance - 200.00
WHEN id = 2 THEN balance + 200.00
END
WHERE id IN (1, 2);
COMMIT;У прикладі обидва рядки блокуються одним запитом і в передбачуваному порядку. Перед виконанням такого UPDATE застосунок має перевірити достатність коштів і коректність інших умов операції.
SELECTРозглянемо дві транзакції.
Перша:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1;
-- Інша транзакція все ще може змінити цей рядок
COMMIT;Друга:
BEGIN;
SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;
-- Інші конфліктні зміни чекатимуть завершення цієї транзакції
COMMIT;Звичайний SELECT підходить для простого читання даних. SELECT ... FOR UPDATE потрібен тоді, коли прочитане значення буде використано для наступної зміни цього самого рядка.
Загальна структура операції така:
BEGIN;
SELECT column_1, column_2
FROM some_table
WHERE id = $1
FOR UPDATE;
-- Перевірка стану рядка в коді застосунку
-- або виконання залежної зміни
UPDATE some_table
SET column_2 = ...
WHERE id = $1;
COMMIT;Якщо будь-яка перевірка або зміна завершується помилкою:
ROLLBACK;Застосунок має обробляти помилки транзакції та не продовжувати операцію після невдалої перевірки.
FOR UPDATE без транзакціїSELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;Якщо клієнт автоматично завершує кожен запит окремою транзакцією, блокування буде звільнено одразу після завершення цього SELECT.
Потрібно об’єднати читання та подальшу зміну в одну транзакцію.
COMMITBEGIN;
SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;
COMMIT;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;Після COMMIT блокування вже немає. Інша транзакція могла змінити рядок між читанням і UPDATE.
Поки транзакція відкрита, рядки залишаються заблокованими. Не варто виконувати всередині такої транзакції довгі зовнішні операції, очікування користувацького вводу або непов’язані запити.
SELECT *
FROM accounts
FOR UPDATE;Такий запит може заблокувати всі рахунки й суттєво зменшити паралельність роботи.
Сам факт наявності FOR UPDATE не гарантує правильність бізнес-операції. Потрібно перевіряти:
чи знайдено рядок;
чи відповідає його стан очікуваному;
чи змінив UPDATE потрібну кількість рядків;
чи не виникла помилка під час транзакції.
SELECT ... FOR UPDATE читає та блокує вибрані рядки.
Блокування захищає рядки від конфліктних UPDATE і DELETE.
Блокування діє до COMMIT або ROLLBACK.
Читання та подальшу зміну потрібно виконувати в одній транзакції.
Звичайний SELECT інші транзакції зазвичай не блокує.
NOWAIT одразу повертає помилку, якщо рядок зайнятий.
SKIP LOCKED пропускає зайняті рядки.
Для кількох рядків потрібно дотримуватися однакового порядку блокування, щоб зменшити ризик deadlock.
Не слід блокувати більше рядків або тримати блокування довше, ніж потрібно.