Реляционные базы данных отлично подходят для транзакционных рабочих нагрузок. Но ситуация может усложниться, когда несколько транзакций начинают одновременно обращаться к одним и тем же данным.
К счастью, во многих базах данных SQL есть решение этой проблемы: SELECT FOR UPDATE.
Постановка вопроса
Представим что у нас есть приложение, которое может одновременно создать несколько транзакций изменяющих одни и те же строки. Очевидно, что у нас возникнут проблемы или с согласованностью или с производительностью, ведь другие транзакции, пытающиеся получить доступ к этим строкам, помещаются в очередь ожидания и выполняются в хронологическом порядке после завершения первой транзакции.
Но есть и другой способ — это как раз SELECT FOR UPDATE. Это предложение в SQL, которое добавляется к оператору SELECT. Оно блокирует выбранные строки, не позволяя другим транзакциям изменять их до тех пор, пока
текущая транзакция не будет зафиксирована или отменена.
Это полезно, потому что оно предотвращает перегрузку и ненужные повторные попытки транзакций, которые в противном случае возникали бы, когда несколько транзакций пытались бы прочитать одни и те же строки. При этом, по сравнению с тем, что мы бы увидели без этого оператора использования, получим повышение производительности и уменьшение задержки, ведь нам не надо каждый раз делать снэпшот базы данных, а транзакции не связанные с заблокированными строками не будут ждать в очереди на исполнение.
Синтаксис SELECT FOR UPDATE
BEGIN;
SELECT stock
FROM items
WHERE item_id = ?
FOR UPDATE;
-- Check stock and update if available
UPDATE items
SET stock = stock - 1
WHERE item_id = ?;
COMMIT;Здесь предложение FOR UPDATE в конце указывает на то, что выбранные строки должны быть заблокированы на время выполнения транзакции.
Механизм работы SELECT FOR UPDATE
Когда транзакция выполняет оператор SELECT FOR UPDATE, происходят следующие действия:
- Выбор строк: механизм базы данных определяет строки, соответствующие заданному условию.
- Блокировка строк: выбранные строки блокируются, что не позволяет другим транзакциям получить блокировки, которые могут конфликтовать с текущей транзакцией. Обычно это означает, что другие транзакции не могут обновлять или удалять эти строки до тех пор, пока блокировка не будет снята.
К затронутым транзакциям относятся:- UPDATE
- DELETE
- SELECT FOR UPDATE
- SELECT FOR NO KEY UPDATE
- SELECT FOR SHARE
- SELECT FOR KEY SHARE
Это означает, что запросы только для чтения (SELECT) не заблокированы.
- Доступ к данным: транзакция может считывать и потенциально обновлять заблокированные строки.
- Завершение транзакции: после фиксации или отката транзакции блокировки снимаются, и строки становятся доступными для других транзакций.
Однако не стоит забывать, что помимо блокировки строк есть и блокировки на уровне таблицы. В приведённом ниже списке показаны доступные режимы блокировки и контексты, в которых они автоматически используются PostgreSQL. Помните, что все эти режимы блокировки являются блокировками на уровне таблицы, даже если в названии есть слово «строка»; названия режимов блокировки являются историческими. В некоторой степени названия отражают типичное использование каждого режима блокировки, но семантика у всех одинаковая. Единственное реальное различие между режимами блокировки заключается в наборе режимов блокировки, с которыми каждый из них конфликтует. Две транзакции не могут одновременно удерживать блокировки конфликтующих режимов на одной и той же таблице.
| Наименование | Конфликтует с | Описание |
ACCESS SHARE (AccessShareLock) | Конфликтует только с ACCESS EXCLUSIVE режимом блокировки. | Команда SELECT устанавливает блокировку в этом режиме для указанных таблиц. В целом, любой запрос, который только считывает данные из таблицы и не изменяет их, устанавливает блокировку в этом режиме. |
ROW SHARE (RowShareLock) | Конфликтует с режимами EXCLUSIVE и ACCESS EXCLUSIVE блокировки. | SELECT Команда получает блокировку этого режима для всех таблиц, для которых указан один из параметров FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE или FOR KEY SHARE (в дополнение к ACCESS SHARE блокировкам любых других таблиц, на которые ссылаются без какого-либо явного FOR ... параметра блокировки). |
ROW EXCLUSIVE (RowExclusiveLock) | Конфликтует с режимами SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE и ACCESS EXCLUSIVE блокировки. | Команды UPDATE, DELETE, INSERT, и MERGE получают этот режим блокировки для целевой таблицы (в дополнение к ACCESS SHARE блокировкам для любых других таблиц, на которые есть ссылки). В целом, этот режим блокировки будет получен любой командой, которая изменяет данные в таблице. |
SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) | Конфликтует с режимами блокировки SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, и ACCESS EXCLUSIVE. Этот режим защищает таблицу от одновременных изменений схемы и VACUUM. | Устанавливается при использовании VACUUM (без FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY и некоторых вариантов ALTER INDEX и ALTER TABLE (подробнее см. документацию по этим командам). |
SHARE (ShareLock) | Конфликтует с режимами блокировки ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE и ACCESS EXCLUSIVE. Этот режим защищает таблицу от одновременных изменений данных. | Устанавливается при использовании CREATE INDEX (без CONCURRENTLY). |
SHARE ROW EXCLUSIVE (ShareRowExclusiveLock) | Конфликтует с режимами блокировки ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE и ACCESS EXCLUSIVE. Этот режим защищает таблицу от одновременных изменений данных и является эксклюзивным, то есть только один сеанс может использовать его одновременно. | Устанавливается при использовании CREATE TRIGGER и некоторыми формами ALTER TABLE. |
EXCLUSIVE (ExclusiveLock) | Конфликтует с режимами ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE SHARE, SHARE ROW EXCLUSIVE EXCLUSIVE, ACCESS EXCLUSIVE и блокировки. Этот режим допускает только параллельные ACCESS SHARE блокировки, т. е. Только операции чтения из таблицы могут выполняться параллельно с транзакцией, поддерживающей этот режим блокировки. | Устанавливается при использовании REFRESH MATERIALIZED VIEW CONCURRENTLY. |
ACCESS EXCLUSIVE (AccessExclusiveLock) | Конфликтует с блокировками всех режимов. Этот режим гарантирует, что держатель является единственной транзакцией, которая каким-либо образом обращается к таблице. | Устанавливается при использовании команд DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL и REFRESH MATERIALIZED VIEW (без CONCURRENTLY). Многие формы ALTER INDEX и ALTER TABLE также получают блокировку на этом уровне. Это также режим блокировки по умолчанию для операторов LOCK TABLE, которые не указывают режим явно. |
Преимущества использования SELECT FOR UPDATE
- Согласованность данных: блокируя строки во время транзакции,
SELECT FOR UPDATEгарантирует, что считываемые данные не будут изменены другими транзакциями, обеспечивая согласованность. - Как избежать взаимоблокировок: правильное использование
SELECT FOR UPDATEможет помочь избежать взаимоблокировок за счёт контролируемого управления блокировками. - Управление параллелизмом: позволяет эффективно обрабатывать одновременный доступ к данным и их изменение, особенно в приложениях с большим количеством транзакций.
Варианты использования SELECT FOR UPDATE
- Банковские системы: обеспечивают точность баланса счетов, блокируя строки, представляющие счета, во время транзакций.
- Управление запасами: предотвращение одновременной продажи одного и того же товара нескольким клиентам путем блокировки записей о запасах.
- Системы бронирования: управление бронированием путем блокировки строк, представляющих доступные места, в процессе бронирования.
Заключение
SELECT FOR UPDATE — это важный инструмент для управления целостностью и согласованностью данных в реляционных базах данных.
Понимая его функциональность и продуманно применяя его, разработчики могут эффективно обрабатывать параллельные транзакции, обеспечивая надёжную и точную обработку данных.
Однако не менее важно понимать, как эта функция влияет на производительность и архитектуру системы, чтобы максимально эффективно использовать её.