CODEWITHSUNDEEP
mysqlconcurrencytransactions
LoneCuriousWolf
LoneCuriousWolf

Reputation: 660

Is this mysql thread-safe?

I have a table with following columns : id(int), wallet_id (int), amount (int), recharge_date (timestamp), status (int). status represents pending(0), success(1) or failed(2). I need to make sure before recharging that total amount recharged for current month doesn't exceed 10,000. I also need to cover scenarios with 2 or more simultaneous requests for recharging.

I am thinking following steps could help avoid race-conditions:

  1. Insert into table with status 0 (pending) for a wallet_id say "1234"
  2. Select sum(amount) from table where wallet_id = 1234 AND month(recharge_date) = current month and status in (0,1)
  3. If this sum is more than 10,000 then update status to 2(failed) and return.
  4. Do some application level operations and then update status to either 1(success) or 2(failed)

Do these steps ensure thread-safety ? Also, is there any other simpler way in mysql of ensuring thread-safety for this use case ?

Edit: Explaining Actual flow for more clarity:

  1. Server receives request to add amount of 500 for a certain wallet_id(say 99).
  2. Insert a new order in pending status: INSERT INTO orders VALUES (wallet_id = 99, amount = 500, status = pending)
  3. Need to make sure you can do atmost 10,000 amount recharge in a month. So SELECT SUM(amount) WHERE wallet_id = 99 AND month=current_month AND status IN(pending, success)
  4. If sum in step 3 is >= 10,000 then update status of order created in step 2 to failed. Return
  5. If sum is < 10,000 then do some other stuff, come back and update order created in step 2 to success or failed.

Confusion arises if more than one simultaneous request is received for this flow.

Upvotes: 1

Views: 303

Answers (1)

O. Jones
O. Jones

Reputation: 108651

Good catch on the concurrency thing. Database transactions are made for this.

You can do this with such a transaction. (not debugged)

BEGIN TRANSACTION;

SELECT SUM(amount) INTO @sum
  FROM mytable
 WHERE status IN (0,1)
   AND recharge_date >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
   AND recharge_date < LAST_DAY(CURDATE()) + INTERVAL 1 DAY 
   AND wallet_id = (((whatever))
   FOR update;

UPDATE mytable SET status=2
 WHERE status IN (0,1)
   AND recharge_date >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
   AND recharge_date < LAST_DAY(CURDATE()) + INTERVAL 1 DAY 
   AND wallet_id = (((whatever))
   AND @sum > 1000;

/* then do your application logic, and come back and do */

UPDATE mytable SET status=(((whatever)))
 WHERE status IN (0,1)
   AND recharge_date >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
   AND recharge_date < LAST_DAY(CURDATE()) + INTERVAL 1 DAY 
   AND wallet_id = (((whatever))
   AND @sum > 1000;

/* finally, commit your transaction. */
COMMIT;

Notice how the SELECT...FOR UPDATE and the two UPDATE statements all have the same WHERE statement. That's important because you must avoid updating rows you didn't select for update. When you're done, COMMIT the transaction. If you decide you don't want to proceed at any time after the BEGIN TRANSACTION you can ROLLBACK the transaction and things return to the state they were in before you began.

This pattern

   AND recharge_date >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
   AND recharge_date < LAST_DAY(CURDATE()) + INTERVAL 1 DAY

is the best way to choose dates in the present calendar month. It allows the use of an index on the column to search for relevant records, where MONTH(recharge_date) = MONTH(CURDATE()) doesn't.

I'm a bit confused about one thing. You said you need to do a SUM, which indicates that multiple records in your table are affected by your intended status change. Is that truly what you want?

Upvotes: 1

Related Questions