I have the following use case. A user can make transactions but the total amount of those transactions should not go above a limit in a day.
i.e, if the user tries to perform 2 transactions of 100 and daily limit is 150, the 2nd one should be rejected, because (100+100) > 150.
The problem is not so simple to solve as just do a SELECT with SUM (calculate total for the day), and perform comparison with limit, to check if is above or not, and then store transaction in database.
Taking in consideration the Isolation Level is READ_COMMITED and Auto-Commit is true (all queries are transactions), If both transactions are done at same time concurrently we run the risk of missing the other in-flight transaction and both transactions go through breaking the daily limit constraint.
Is there a way to prevent this problem, without using locks or using transactionality explicitly?
I was thinking in reverting the order of the operations. First do insert and then do the SELECT with SUM, and then calculate if the new total amount including the new transaction is above the limit. If not just leave it and process it, if is above the limit, cancel the transaction and remove it from DB. With this approach I believe the daily cannot be breached.
Are there better ways to solve this problem?