Invalid Math on Spam API Requests

Im having an issue with an API and I am lost how to correct it.

I have two models "users" and "charity".

Users

  • id
  • balance

Charity

  • id
  • balance

In simple form, the API takes a request and deducts from users->balance and increases charity->balance

Now, everything works fine except when a request is spammed:

Working Order:

  • User Balance | Charity Balance
  • 5 1
  • 4 2
  • 3 3
  • 2 4

Spammed Order

  • User Balance | Charity Balance
  • 5 1
  • 5 2
  • 4 3
  • 4 4

As you can see, the data integrity is corrupted because the database cannot catch up before the next request is processed.

Im running out of luck trying to solve this issue. I've tried transactions, separate statements etc.

Im not sure what to try.

It seems that my routes are not stopping execution when overloaded. Being so it does not increment the DB properly. For instance. If i set a 2 second delay using usleep() before executing the transaction I can make multiple requests and have a result that inflates charity but only reduces users by 1

My route is a post witha php://input

edited Mar '17

This is not php problem, this is common problem in pretty much every language, maybe you could use some pthreads or something like this, maybe you could solve it with memcached/session. Like instead of everytime select current value from database which don't always has to be correct one you better work on some thing in memory or session or something like this.

Shouldnt the transaction lock it and queue the process?

No, it shouldn't, transaction isn't locking anything. Locking wouldn't save it either.

I solved this race condition by updating my query to perform the necessary validation logic.

Before: Select, Verify Data, Update After: Update & Verify Data