Что блокирует SELECT FOR UPDATE

Что проверяют

Понимаете ли вы разницу между блокировкой строки и блокировкой таблицы и что именно перестанет работать у соседней транзакции. В Java-собеседованиях вопрос почти всегда ведёт к пессимистичным блокировкам в JPA и к гонкам на списании денег или на разборе очереди задач.

Как отвечать

SELECT ... FOR UPDATE берёт монопольную блокировку на каждую возвращённую строку и держит её до конца транзакции. Ждать будут:

• UPDATE и DELETE этих строк;
• другой SELECT ... FOR UPDATE, а также FOR NO KEY UPDATE и FOR SHARE по тем же строкам.

Обычные чтения не блокируются: в PostgreSQL благодаря версионности читатель видит прежнюю версию строки и не ждёт. Таблица целиком тоже не блокируется, берётся слабая табличная блокировка, которая мешает разве что ALTER TABLE.

В Java это LockModeType.PESSIMISTIC_WRITE, в Spring Data аннотация @Lock над методом репозитория. Запрос обязан идти внутри транзакции, иначе она закроется сразу после выборки и блокировка снимется раньше, чем вы что-то сделаете.

Полезные модификаторы: NOWAIT падает с ошибкой вместо ожидания, SKIP LOCKED пропускает занятые строки, на нём строят разбор очереди несколькими обработчиками.

Где спотыкаются

Говорят, что при большом числе строк блокировка вырастает до табличной. Эскалации блокировок в PostgreSQL нет. Похожий эффект даёт MySQL, но по другой причине: без подходящего индекса InnoDB блокирует всё, что просмотрел при поиске, вместе с промежутками между значениями.

Второе: блокируют строки в разном порядке в разных местах кода. Две транзакции берут их встречно, получают взаимную блокировку, и одну сервер снимает с ошибкой. Лечится единым порядком, например сортировкой по идентификатору перед блокировкой.

Третье: считают, что блокировка сама по себе спасает от потерянного обновления. На уровне Read Committed строка перечитывается после коммита соседа, и условие, по которому вы её выбирали, может уже не выполняться, поэтому остаток проверяют после получения блокировки, а не до неё.