Здравствуйте.

Есть такая задача: пополнение баланса пользователя.

По запросу на пополнение баланса выполняется простой алгоритм: Ищем в таблице балансов запись, которая принадлежит пользователю. Если у пользователя есть баланс, то такая запись присутствует, если баланса нет - записи тоже нет.
Если приходит одновременно несколько запросов на пополнение баланса одному юзеру, у которого нет записи (нулевой баланс), то случается deadlock.




id - не является автоинкрементом, на самом деле это USER_ID

Пробовал INSERT ... ON ON DUPLICATE KEY UPDATE - также не решает проблемы.

Как решить проблему?

MasterZiv,

tanglirРешение напрашивается само собой - сразу создавать для нового пользователя запись с нулевым балансом.
Не хотелось бы... юзеров будет очень много, поэтому решили вынести баланс в отдельную таблицу, чтобы работа с балансом была максимально быстрой

тут еще интереснее стало...
даже если приходят запрос на пополнение баланса от разных юзеров. все равно вываливается deadlock




Мне кажется, это связано с блокировкой следующего ключа, но пока я плаваю в этой теме... В эту сторону копать?

MasterZiv,
ну это просто для того чтоб легче примеры читались.
ситуация воспроизводится при такой структуре.

Давайте рассмотрим эту проблему не с точки зрения архитектуры, а с точки зрения решения самой проблемы.
Структура таблицы есть. Воспроизвести проблему на локале - дело 1 минуты, достаточно открыть 2 терминала.
Deadlock есть. Как от него избавится?

Akina,
вот кстати копаю в этом направлении ))
только не ужесточить, а наоборот понизить помогло.




тогда такой запрос работает как надо.




но это еще только первые вести с полей... возможно будут побочные эффекты

AkinaА был какой?
был по дефолту REPEATABLE-READ

MasterZivпопробуй только одну сессию пустить ...

по отдельности отрабатывают нормально

MasterZivМожно попробовать не делать SELECT... FOR UPDATE вообще,
делать UPDATE, что-то про UPDATE-илось -- ок, хорошо, нет -- вставлять.

Без FOR UPDATE нельзя.
Это редкий случай - когда пришел запрос, а баланса у юзера нету.
Чаще происходит ситуации, когда у юзера есть баланс.

Если в начале транзакции будет просто SELECT ... WHERE id=1, а в конце UPDATE ... SET balance = balance+10 WHERE id=1
То при паралельных запросах один из них потеряется

ScareCrowinsert on duplicate key update
а еще можно прочесть все сообщения в теме.
уже обсуждалось....

ScareCrowinsert on duplicate key update
На самом деле это пока наилучший вариант, но требуется понизить уровень изоляции.
Это пока смущает...

Вобщем сделал понижение уровня изоляции транзакции до READ COMMITTED с использованием INSERT ON DUPLICATE KEY UPDATE.
И вот как чувствовал, что что-то не то...

На локале все потестил, все нормально, со всех ракурсов.
На бой вынес и повалились ошибки

Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engi ne limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
Так что проблема все еще актуальна...

Arhat109,

select for update нужен для того чтобы заблочить запись, если таковая имеется, что чаще всего и случается.
а вот если ее нет, тогда нужно добавить новую строку

Arhat109,

Селектом в начале транзакции, мы ищем баланс юзера. Если баланс юзера есть, значит в таблице есть запись. Мы ее блочим, для того, чтобы проделав некоторые операции с балансом (например, сохранить значение баланса до и после операции. т.е. обычный лог), в конце транзакции увеличить этот самый баланс.

Ежели у юзера нет баланса, значит нет записи. Тогда добавляем запись, опять же логгируя значения баланса до и после.

Пока вижу 2 пути:
1) после первого select for update, если записи нет, то заканчивать транзакцию, сбрасывая тем самым gap-ы. А дальше извращаться с insert on duplicate key update (пока вижу проблему с логгированием)

2) для юзера сразу заводить запись с нулевым балансом. Это, кажется более правильный путь, но смущают размеры таблицы. Юзеров реально до хрена, политика партии допускает наличие у пользователя неограниченного кол-ва аккаунтов. Из этой таблицы делаются ежеминутные выборки с целью списания абонентской платы. Сейчас объем таблицы под 10 млн. записей + ежесуточный прирост в 50-100 тыс. Поэтому и было решено вести балансы только активных юзеров.

Arhat109Arhat109,

там интересен совсем другой вопрос: каким способом второй селект для апдейта ваще проходит?
Вы можете попробовать самостоятельно. tanglir чуть выше описал полную последовательность операций, которая приводит к дедлоку.

ScareCrowпросто отсортируй инсерты по ID
сначала 5 потом 10
К сожалению, это невозможно, т.к. это фактически идентификаторы юзеров.
Да и если честно, я не представляю, как это можно сделать в разных потоках.

Arhat109ну, во-первых: "операции с балансом". Если это "лог", то выборка значения "до" - осмыслена, а вот смысла в логировании "между" чтением и сохранением - собственно для чего нужна блокировка - уже нет.

Какая нафиг разница, когда физически запишется лог? У вас есть исходное значение на момент начала транзакции, есть конечное... оттого, что вы лог чирканете "задним числом" (после изменения значения) - плохо? А если "заранее"?


Если мы читаем значение баланса в начале транзакции, и не будем блокировать запись, то к моменту записи в лог это значение может быть уже неактуально, т.к. другая транзакция также в этот же момент времени прочитала значение баланса "до" и изменила баланс. Поэтому, без блокировки записи баланса, в логе запросто могут быть такие записи, при 2-х одновременных запросах

user_id баланс до баланс после1 0 101 0 10

А в таблице баланса будет верное значение 20

Arhat109А если отдельно вставлять в лог предварительно пустую запись "заранее", а потом одним, многотабличным апдейтом одновременно менять баланс и запись лога? Никак?

Надо подумать, пока плохо понял смысл... голова уже плохо соображает )

Arhat109Я к тому, что если в процессе транзакции нет логики, изменяющей поведение транзакции, то она скорее всего избыточна и вполне достаточно "встроенных" блокировок на каждый запрос. Как-то так.
Логгирование - не единственная операция в транзакции. Есть и другие, которые как раз меняют логику, в зависимости от вида совершенного платежа.