openlang
Базы данных и запросы

Дневной лимит превышается при 20 горутинах, но проверка баланса работает. Один баг или два?

Разговор продолжается 3 ↓

Go + пул pgx + Postgres, дефолтный Read Committed, одна транзакция на каждое снятие. Баланс 1000, daily_limit 50, 20 горутин, каждая снимает по 10. Ожидаю 5 успешных, получаю 7, 8 или 9. sum(amount) за сегодня выходит от 70 до 90. С MaxConns=1 ровно 5.

Проверка баланса вроде в порядке. Если баланс 15, выигрывает одна, остальные просто получают ноль строк от UPDATE. Не блокируются и не падают с ошибкой.

INSERT INTO withdrawals (account_id, amount)
SELECT $1, $2
WHERE (SELECT coalesce(sum(amount), 0)
         FROM withdrawals
        WHERE account_id = $1 AND day = current_date) + $2
      <= (SELECT daily_limit FROM accounts WHERE id = $1)
UPDATE accounts
SET balance = balance - $2
WHERE id = $1 AND balance >= $2

Перепроверяет ли Postgres WHERE для INSERT ... SELECT так же, как для UPDATE? Или это две разные вещи? В логах ничего странного, и -race чистый.

Документация по изоляции, которую я читаю: https://www.postgresql.org/docs/current/transaction-iso.html

Переведено с английскогоПоказать оригинал

Разговор продолжается 3

Следуя доводам

  1. Насколько я понимаю, это две разные вещи. В Read Committed UPDATE, который натыкается на строку, только что изменённую другой транзакцией, ждёт эту транзакцию, а потом заново проверяет WHERE на новой версии строки, поэтому ваша проверка баланса и работает. У INSERT ... SELECT такой строки, которую можно ждать, нет: он читает только снапшот, снятый в момент начала запроса, так что все двадцать могут увидеть одну и ту же сумму и все пройти. Если сначала взять блокировку строки через SELECT ... FROM accounts WHERE id = $1 FOR UPDATE или выполнить UPDATE баланса до INSERT, то они должны сериализоваться по аккаунту, как описано здесь: https://www.postgresql.org/docs/current/explicit-locking.html.

    Переведено с английскогоПоказать оригинал
  2. В ответ на предыдущую реплику ↗

    Это совпадает с тем, что вижу я. Одна оговорка по варианту «сначала блокировка»: FOR UPDATE должен быть отдельным запросом перед INSERT. Если засунуть его в CTE того же запроса, снапшот уже снят в начале запроса, так что сумма всё равно может оказаться устаревшей. Если же это отдельный запрос, INSERT получает свежий снапшот после того, как блокировка получена, и видит то, что закоммитило предыдущее снятие.

    SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE;
-- then the INSERT ... SELECT as before

    Блокировка держится до коммита или отката, так что транзакцию лучше делать короткой. Не делайте сетевых вызовов между блокировкой и коммитом.

    Сначала выполнить UPDATE баланса тоже работает, но только потому, что он заодно берёт ту же блокировку строки. Это побочный эффект, так что я бы лучше написал блокировку явно, чтобы следующий человек её видел.

    Это же объясняет, почему с MaxConns=1 выходит ровно 5. Одно соединение, значит, блокировкой служит сам пул.

    Раздел про Read Committed, где прямо описан снапшот на каждый запрос: https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED

    Переведено с английскогоПоказать оригинал

  3. Отвечу на вопрос в том виде, как вы его задали сначала: нет, Postgres не перепроверяет WHERE у INSERT ... SELECT так, как делает это для UPDATE, а причина в том, что тут нет существующей версии строки, за которой нужно идти. Перепроверка (EvalPlanQual, если захотите поискать) происходит только когда запрос пытается изменить или заблокировать строку, которую уже изменила параллельная транзакция. Ваш подзапрос SELECT просто читает из снапшота запроса, поэтому все двадцать видят одну и ту же сумму и проходят. Поэтому же числа и плавают, 7, 8 или 9: всё зависит от того, сколько запросов успеют начаться до того, как лягут коммиты предыдущих.

    Кроме блокировки строки есть ещё два пути. Если проверка должна охватывать что-то, у чего нет одной строки для блокировки, то pg_advisory_xact_lock(account_id) в начале транзакции делает ту же работу и сам снимается на коммите. Или можно запускать транзакции в SERIALIZABLE и повторять при SQLSTATE 40001: это корректно без всякой явной блокировки, но придётся писать цикл повторов, а при двадцати горутинах на одном аккаунте повторять вы будете довольно много.

    Для одного «горячего» аккаунта я бы всё равно взял обычный FOR UPDATE.

    Переведено с английскогоПоказать оригинал

Другие стороны того же вопроса

  1. Базы данных и запросы

    rows.Next вернул false, а ошибка потерялась

    Маленькая утилита на database/sql вернула половину списка. Без ошибки. Код такой. Контекст с дедлайном. Дедлайн истёк посреди…

    Ответы 3sqlgo
  2. Базы данных и запросы

    Частичный индекс и параметр в условии WHERE

    Пишу небольшой job runner на Go для учебного проекта. Стек: pgx v5 через database/sql, локально Postgres 16, таблица jobs при…

    Ответы 10sqlgo
  3. Базы данных и запросы

    итоговая сумма выводит склеенные цифры, а не сумму

    Строки по дням в порядке (12500, 3400, 900 won), а вот TOTAL выводит 0125003400900 KRW. Откуда лишний ноль? tsc проходит, Pos…

    Ответы 10typescriptsql