MySQL - изоляция транзакций для оплаты заказа и списания с баланса

Question

Всем привет!

Вопрос про изоляцию транзакций друг от друга.

Есть 2 таблицы.

user
--
id int
balance int

order
--
id int
amount int
user_id int
status int

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

START TRANSACTION;
SELECT balance FROM user WHERE id = 1;
SELECT amount FROM order WHERE id = 10 AND user_id = 1;
# далее тут идет проверка в коде, что баланс пользователя больше или равен стоимости заказа 
# и если да, то обновляем записи - вычисляем сколько у пользователя останется на балансе 
# и выставляем статус оплачено на заказ.
UPDATE user SET balance = balance - 20 WHERE id = 1;
UPDATE order SET status = 2 WHERE id = 10;
COMMIT;

А что, если одновременно придет 2 запроса на оплату 2 заказов у одного пользователя. То есть дважды почти одновременно произойдут запросы, которые описаны выше. Получается, оба запроса прочитают баланс и увидят, что денег хватает. Затем они оба обновят все таблицы и баланс уйдет в минус. Проверил на реальной БД MySQL с дефолтным уровнем изоляции: при второй транзакции которая идет параллельно с первой она считывает баланс который уже изменила предыдущая транзакция и дальше на обновление она просто ждет когда сделает COMMIT первая транзакция и не смотря на то, что данные там уже другие, она успешно утрабатывает, перезаписывая обновленные первой транзакцией данные своими данными.

Вопрос следующий. Какой лучший способ, чтобы избежать подобных ситуаций? Например, чтобы вторая транзакция откатывалась, если первая уже изменила те же данные, которые хочет изменить вторая. Или чтобы вторая транзакция считывала только данные, которые получились после полной отработки первой транзакции? Может какие-то варианты еще лучше есть?

Answer 1

Пример реализации. Обработка выполнена в виде хранимой процедуры - чтобы не повторять сам запрос.

Процедура принимает номер ордера, который должен быть обработан. Если статус ордера равен 1, а баланс юзера не упадёт в минус, выполняется обработка, баланс корректируется, а статус ставится равным 2. В любом другом случае обработка не выполняется. Процедура возвращает 1, если обработка выполнена, и 0, если не выполнена.

Исходные данные

CREATE TABLE users (
  id int,
  balance int
  );
CREATE TABLE orders (
  id int,
  amount int,
  user_id int,
  status int
  );
INSERT INTO users VALUES (1,111), (2,222);
INSERT INTO orders VALUES (1,11,1,1), (2,22,1,1), (3,111,2,1), (4,222,2,1),(5,1,3,1),(6,1,1,0);
SELECT * FROM users;
SELECT * FROM orders;

id	balance
1	111
2	222

id	amount	user_id	status
1	11	1	1
2	22	1	1
3	111	2	1
4	222	2	1
5	1	3	1
6	1	1	0

Процедура обработки

CREATE PROCEDURE process_order (IN order_id INT, OUT status BOOLEAN)
BEGIN
  UPDATE users
  JOIN orders ON users.id = orders.user_id
  SET users.balance = users.balance - orders.amount,
      orders.status = 2
  WHERE orders.id = order_id
    AND users.balance >= orders.amount  -- проверка, что баланс достаточен
    AND orders.status = 1;              -- проверка, что статус единица
  SET status = (ROW_COUNT() = 2);
END

CALL process_order(1, @status);
SELECT * FROM users;
SELECT * FROM orders;
SELECT @status;

Баланс достаточен, обработка выполняется.

id	balance
1	100
2	222

id	amount	user_id	status
1	11	1	2
2	22	1	1
3	111	2	1
4	222	2	1
5	1	3	1
6	1	1	0

@status
1

CALL process_order(2, @status);
SELECT * FROM users;
SELECT * FROM orders;
SELECT @status;

Баланс достаточен, обработка выполняется.

id	balance
1	78
2	222

id	amount	user_id	status
1	11	1	2
2	22	1	2
3	111	2	1
4	222	2	1
5	1	3	1
6	1	1	0

@status
1

CALL process_order(3, @status);
SELECT * FROM users;
SELECT * FROM orders;
SELECT @status;

Баланс достаточен, обработка выполняется.

id	balance
1	78
2	111

id	amount	user_id	status
1	11	1	2
2	22	1	2
3	111	2	2
4	222	2	1
5	1	3	1
6	1	1	0

@status
1

CALL process_order(4, @status);
SELECT * FROM users;
SELECT * FROM orders;
SELECT @status;

Баланс НЕдостаточен, обработка НЕ выполняется.

id	balance
1	78
2	111

id	amount	user_id	status
1	11	1	2
2	22	1	2
3	111	2	2
4	222	2	1
5	1	3	1
6	1	1	0

@status
0

CALL process_order(5, @status);
SELECT * FROM users;
SELECT * FROM orders;
SELECT @status;

Несуществующий юзер, обработка НЕ выполняется.

id	balance
1	78
2	111

id	amount	user_id	status
1	11	1	2
2	22	1	2
3	111	2	2
4	222	2	1
5	1	3	1
6	1	1	0

@status
0

CALL process_order(6, @status);
SELECT * FROM users;
SELECT * FROM orders;
SELECT @status;

Исходный статус НЕ единица, обработка НЕ выполняется.

id	balance
1	78
2	111

id	amount	user_id	status
1	11	1	2
2	22	1	2
3	111	2	2
4	222	2	1
5	1	3	1
6	1	1	0

@status
0

fiddle

PS. Рекомендую расширить диагностику, и в переменной статуса возвращать условную причину отказа в выполнении операции.

Также можно добавить в запрос любые дополнительные проверки.

БЛОГ НА HUSL

MySQL - изоляция транзакций для оплаты заказа и списания с баланса

Ответы (1 шт):