High Concurrency Counter (For Credit Management)

I am wondering the best way to do this for speed and accuracy, here is what our application does:

1. Check if credit is 1 or above (Pre check)
2. 开发者_JS百科Process job (takes a little time)
3. Job complete, check if still credits exist to finish job (credit count 1 or above)
4. Deduct credit
5. Finish job

This process is repeated 50,000+ times (threaded, using a queue system) and is currently using a mysql database to handle the counter.

Is there any better solutions other than a mysql database style counter?

I was thinking a schema like:

user_id | credit_count

Is this the best schema I should use? And the thread just locks row than deducts credit, than release row for next thread.

---

Not sure about which technology are you using for the process.

You could somehow acumulate the counting in the languaje of your processes, and only dump the counter to the database from time to time. You won't be increasing the count on each request, but every n requests...

---

Ever look at memcached? If you want to stick with mysql you could change the table to a memory table.

Also read this.