Как решить проблему когда две запущенные транзакции изменяют одну и ту же строку, но одна из транзакций видит старые данные а не новые?

Question

[szQocks]

Есть таблица пользователя, и его баланс который равен 100.

Сейчас работает так: 2 транзакции запускаются и выбирают одну и ту же запись пользователя для UPDATE, одна из этих транзакций блокирует её, вторая транзакций видит это и ждет когда первая транзакция завершится. В итоге первая транзакция изменяет баланс пользователю со 100 на 200 и завершается удачно. Вторая транзакция видит что первая завершилась и начинает тоже изменять эту запись, но она работает с балансом 100, а не 200, то есть она видит старый баланс у пользователя, а не новый.

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

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

Answer 1

[Пума Тайланд]

уровни изоляции тут как раз при чём, это классический lost update)) ты SELECT-ом читаешь баланс, потом UPDATE-ом пишешь то что прочитал, вторая транзакция конечно работает со старым значением. Самый простой фикс:

UPDATE users SET balance = balance + 100 WHERE id = 1

, без промежуточного SELECT. А если между чтением и записью нужна логика, используй SELECT ... FOR UPDATE — он заблокирует строку и вторая транзакция при разблокировке перечитает актуальное.

Comments

[szQocks]

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

[szQocks]

SELECT ... FOR UPDATE — он заблокирует строку и вторая транзакция при разблокировке перечитает актуальное.

это как раз я хочу добиться, что бы она перечитывала, но этого не происходит, так как они делают снапшот записи и работают с ней

[Пума Тайланд]

Похоже у тебя REPEATABLE READ — снапшот фиксируется на старте транзакции и FOR UPDATE после разблокировки всё равно вернёт старое. Переключи на READ COMMITTED, тогда SELECT FOR UPDATE после ожидания перечитает актуальную версию строки.

[szQocks]

Пума Тайланд, у меня и стоит READ COMMITTED и этот lost update, создается при этом уровне изоляции с блокировкой записи

[szQocks]

есть момент, что транзакции начинаются вот так

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
START TRANSACTION

и предположу что одна из транзакций не получает должный уровень изоляции, а берет по умолчанию из mysql которой я пользуюсь, то есть REPEATABLE READ и поэтому одна из транзакций, то есть вторая которая запускается с REPEATABLE READ, работает со снапшотом, других мыслей у меня нет что ещё может быть, есть идеи как решить это ?

[Пума Тайланд]

Да, похоже на это — SET TRANSACTION без SESSION в MySQL действует только на ближайшую транзакцию и «сгорает». Ставь SET TRANSACTION прямо перед каждым START TRANSACTION в каждом коннекте отдельно.

Answer 2

[Daemon23RUS]

Если ты просто увеличиваешь баланс, можно обойтись без чтения:

UPDATE users SET balance = balance + 50 WHERE id = 1;

Это атомарно, не требует блокировок в приложении, и не страдает от lost update.

Но если логику select'а не обойти

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM users WHERE id = 1 FOR UPDATE;
-- вернёт актуальное значение после ожидания
UPDATE users SET balance = ...;
COMMIT;

Comments

[szQocks]

у меня и стоит READ COMMITTED и этот lost update, создается при этом уровне изоляции с блокировкой записи

[szQocks]

есть момент, что транзакции начинаются вот так

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
START TRANSACTION

и предположу что одна из транзакций не получает должный уровень изоляции, а берет по умолчанию из mysql которой я пользуюсь, то есть REPEATABLE READ и поэтому одна из транзакций, то есть вторая которая запускается с REPEATABLE READ, работает со снапшотом, других мыслей у меня нет что ещё может быть, есть идеи как решить это ?

[szQocks]

«Пользователь удалён», согласен, вот и думаю как бы устранить, у меня несколько серверов запущенных и на каждом из них бывает так что они запускают одну и ту же транзакцию в одно и тоже время, то есть на одном сервере одна и вторая на другом, вот в таком виде:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
START TRANSACTION

[Daemon23RUS]

szQocks, База то хоть какая ? А то гадаем тут ...

[Daemon23RUS]

szQocks, Старое добрый костыль version тоже никто не отменял. Добавить поле version . Обновляем только если version не изменился

UPDATE users 
            SET balance = ?, version = version + 1 
            WHERE id = ? AND version = ?

И логика, если обновлено 0 строк то повторить попытку с новым SELECT и всей математикой

Answer 3

[Владимир]

есть момент, что транзакции начинаются вот так
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
START TRANSACTION

szQocks, у вас же вложенные транзакции, конечно они обе получат один снапшот и будут работать с одними и теми же данными. Не знаю, зачем вам это понадобилось, но просто вытащите вложенную транзакцию наружу и все будет отлично, тем более, что если это происходит в одном участке кода, то конкуренцию запросы друг другу не составят при любом развитии событий, это же один поток, он выполнится последовательно. Т.е. в данном случае даже просто предложенное выше UPDATE users SET balance = balance + 100 WHERE id = 1, выполненное одно за другим сколько угодно раз без транзакций сработает так, как нужно. При отсутствии параллельных обращений, конечно. Транзакции и нужны только чтоб работающие параллельно потоки/процессы не ломились наперегонки в БД, а чинно ждали тех, кто пришел раньше.

Comments

[szQocks]

у меня не вложенные транзакции, просто я тупой по жизни и порой вижу то чего не существует

у меня 2 сервера, у каждого сервера своя сессия и эти запросы ни как не связаны друг с другом так как они из разных сессий, зря я их привел в пример, проблема была в другом, либо проблема была в том что у той записи которую я блокировал, запрос выбирал и связные записи из других таблиц через left join, и поэтому не происходит перерасчет, либо я хз в чем ещё могло бы быть дело, но теперь всё работает исправно

[Владимир]

szQocks, понятно, ну, бывает) Главное, что заработало как нужно.