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