Avoid Race Condition in Transaction

Question

I am developing a mobile based shopping app. What the app does is, user deposits money in his account and spend it later. Race condition is one of the problem I am trying to avoid. such that user account balance won't be miscalculated.

I am using mysql 5.5, php.

Here is what I have come up.

create table orders ( order_id int, user_id int, title varchar, item_price decimal, is_active int default null, constraint primary key (order_id), constraint unq unique (user_id, is_active) )

The idea is to set unique constraint on user_id and is_active so that only one active order(deposit money or use balance) can be processed. active order will have is_active set to 1. is_active is updated to a timestamp so the unique constraint will be satisfied once the order is completed. Deposit money is similar logic.

Here is the pseudo code for purchase item with account balance:

if  user has enough balance,
  start transaction
  insert into order with user_id, order_id, is_active=1
  update user balance = balance - item_price where balance >= item_price
  commit

if transaction success,
  update order set is_active= current_timestamp where user_id=, order_id=

Is there any problem with this logic?

Or the race condition can be avoided without the unique constraint with this line: update user balance = balance - item_price where balance >= item_price

UPDATE 1

I have missed a case that will make things more complicated. Here is the detail:

User can choose to pay the remaining via an external payment service when an item price is greater than his account balance.

// first http request
try to cancel any previous active external payment by the same user
if user has enough balance,
    get a secure token from external payment service
    insert into order with user_id, order_id, is_active=1

// second http request
user paid and external payment service notifies my backend about the success payment. Then 
    start transaction
    update user balance = balance - balance_pay_amount where balance >= balance_pay_amount
    update order set is_active= current_timestamp where user_id=, order_id=
    commit

Since the payment and account balance update happen in a span of requests. transaction along won't work here.

So I choose to cancel any previous active order paid via external service by the same user before creating another active order. This will have a side effect of slowing down user who submit many orders without pay in a short period of time. This serves as additional cleanup in case any existing abandoned active order prevent user making new order.

is_active is the safeguard to prevent race condition from happening.

Answer 1

The is_active flag is not necessary. You need to make sure that you lock the user's balance before you do the check.

start transaction 
if  user has enough balance (lock the rows using a select query with FOR UPDATE)
  insert into order with user_id, order_id, is_active=1
  update user balance = balance - item_price where balance >= item_price
  commit
else 
  rollback
  show some error or something

This guarantees that the user balance can't be changed by another thread while the transaction is active. It also guarantees that the if user has enough balance will only be evaluated for a user which does not have a transaction active at the moment.