CODEWITHSUNDEEP
phpmysqldatabase
Glen Romero
Glen Romero

Reputation: 319

PHP / Mysql issue with concurrent DB calls

Using innoDB tables and mysqli wrapper in PHP for queries.

We are currently having an issue where we're getting a spike in traffic requesting the same script 1,500 times per second.

The situtation is that the first X amount of users to access the script win a prize.

The prize is a single record on a table "prizes" that has a count of the # claimed and the # alotted.

Once the used amount >= the alotted amount, we stop awarding prizes.

What is happening is a number of requests to the script are READING the row at the same time before other instances of the script can UPDATE the row, thus showing them each that there is still a # of prizes left to be claimed. This causes us to award more than the alotted amount.

Any ideas on how to circumvent this?

Upvotes: 0

Views: 282

Answers (3)

tadman
tadman

Reputation: 211660

One way to solve this problem is to have a table containing individual prize records and try and "claim" one of them with a query like:

UPDATE prizes SET claimed_by=? WHERE prize_type=? AND claimed_by IS NULL LIMIT 1

If you impose a UNIQUE constraint on prize_type and claimed_by then it means nobody can claim more than one prize of a given type. This is enforced at the database level and cannot be circumvented by timing issues.

When you call that update you'll get either zero or one rows modified. Check the result's updated rows count to see if the claim was successful.

Upvotes: 0

Javier Larroulet
Javier Larroulet

Reputation: 3237

While you're not providing code, table structures or more in-depth database information, the first advice in these cases would be to use LOCK

If your table is innoDB, you can benefit from row-level locking, although this would be irrelevant if the table has a single row.

In pseudo code, on each hit you'd need:

LOCK TABLE prizes
SELECT claimed, alloted FROM prizes

if claimed < alloted award prize
UPDATE prizes set claimed = claimed +1

else
do_nothing
UNLOCK TABLE prizes

<<after unlocking>>
if the user got an award, do whatever you need to do to award the prize which is not "inventory-sensitive" and can be done asynchronously

the time for this to run would be in the milliseconds, so it shouldn't be an issue to have all hits queuing up if your DB server is well oiled, although you may hit a process limit or connection limit on your application server, so something like this would need some stress testing.

This may be tricky, complicated, finicky...

An easier path would be:

set up a claim_attempt table, with an auto-increment primary key and a field that references something about the user.

On each hit, insert a record (regardless of available inventory) and retrieve the inserted row's ID. Afterwards, compare the retrieved ID with the alloted award number. If id<=alloted, then run whatever process needs to run in order to give the prize to the user. If id>alloted, print a "try again next time" message

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562611

Right, you're describing a classic race condition.

One solution is to use SELECT...FOR UPDATE to establish a lock on the row in the prizes table, before updating it. InnoDB will establish the order of requests for the lock, making each request wait for its turn until it can acquire the lock.

However, this isn't a good solution, because it will cause every user's browser to spin and spin, waiting for a response. On the server, you'll quickly get 1500 lock requests per second queuing up. Even if each session takes only 10 milliseconds to do the SELECT FOR UPDATE and subsequent UPDATE (that's already pretty ambitious), that's still 15.0 seconds of work to do every second. By second 2, you'll have 30.0 seconds of work to do.

Meanwhile, users are viewing their browser hanging until their turn comes around. That's pretty much a deal-breaker of a design.

Basically, you need some solution to establish the order of requests that is:

  • Global
  • Atomic
  • Fast
  • Asynchronous

You could have each concurrent request do an INSERT to a table with an AUTO_INCREMENT key, which will guarantee their order. Then once there's X rows, subsequent requests don't bother to insert any more rows.

Another method is to use a message queue. Each request just pushes their own request into the queue. Then a single consumer pulls the first X requests from the queue, and awards prizes to them. The rest of the requests in the queue are dumped and they don't get a prize.

Upvotes: 2

Related Questions