2

I'm writing a Web app with Play Framework and MariaDB. The user has a balance on his account that can be consumed with a request to the app. Today, when the user sends 2 requests to the app, there is no way to be sure that the second request will take the balance state from the first one. It makes de data inconsistent.

What's the "by default" way to deal with this? Using a lock column to update and read after/before each update of the balance? Having a lock mechanism built-in MariaDB? Rethinking the async requests?

Thanks for your advice.

didil
  • 693
  • 8
  • 22
  • What's wrong with transactions? – Kayaman Sep 13 '19 at 12:17
  • I see transactions as a way to keep data consistent for multiple DB requests in one transaction. If one request fails, the whole process rollback. Here I have to deal with multiple HTTP requests actually. I already use DB transactions for multiple DB requests during one HTTP request. – didil Sep 13 '19 at 12:20
  • Then you have a [very simple view of transactions](https://stackoverflow.com/questions/974596/what-is-a-database-transaction). – Kayaman Sep 13 '19 at 12:24
  • This is actually how I experience them. This is why I'm asking the question. – didil Sep 13 '19 at 12:25
  • Transactions can also ensure (depending on the isolation level) that everything is done as if it was an isolated operation (that is, without "allowing" another conflicting operation to happen during the transaction) – Pepper Sep 13 '19 at 12:50
  • That didn't work that way between two different HTTP requests with the transactions API of Ebean ORM. It only ensures that multiple DB requests would be atomic if one fails. – didil Sep 13 '19 at 12:56
  • What do your transactions do? Do they set the balance to a value computed programatically, or do they add an offset to the balance on the database side? If they follow what @Tudor Constantin mentioned in the answer below it definitely should work – Pepper Sep 13 '19 at 13:01
  • I will definitively try the `forUpdate()` option: https://ebean.io/docs/query/option – didil Sep 13 '19 at 13:11

2 Answers2

2

You can use the FOR UPDATE transaction isolation level which the InnoDB storage engine provides.

That will guarantee that the second request won't even see the locked records until the first transaction finishes.

Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
0

Another option is to use an atomic statement with a guard clause.

UPDATE account SET balance = balance - X WHERE id = Y and balance >= X

This will deduct X from balance, when there's more than X of balance. It's not possible for two requests to perform anything "weird". If zero rows were affected, the withdrawal failed, otherwise it returns 1 row affected.

It's also possible to use a tighter isolation level (REPEATABLE READ), but it can become more complicated than the alternatives. You would need to retry the failed transactions, whereas the other solutions avoid this completely.

Kayaman
  • 72,141
  • 5
  • 83
  • 121