How to achieve Concurrency in mysql when there are 500+ requests simultaneously to the server

I am using Mysql (InnoDb), in this I am facing problem when we checked the curl request upto 500 connections at a time (simultaneously) but when we try more than that then my servers goes to idle condition and then we have to restart it.

In simply manner for more understanding I have a DB table in MYSQL (InnoDB) which contains 'remaining amount' field. Ex. field containing the values of $500 and the $10 will deduct per user.

Now when users request concurrently (at a time), server doesn't know which users has more priority or what to do when request is accepted but no more amount remains.

Here are two questions:

How to manage the deductions when concurrent requests received
How to set maximum requests for server so that will not be Idle after some more requests (500+)

Edit

If I suppose to use LOCK Mechanism of mysql then it would be work perfectly or not ?

I am using php script for communicating with mysql

Solution

Modern server environments can operate with a multitude of languages, processes, environments etc. which makes them easy to debug, monitor, change and combine in various ways.

If this request alone is putting big stress on your MySQL server, I would suggest a helper process to handle it in a single-threaded way. Below I present a very simple helper-server in Tcl and the glue code for Php. Other languages and communication protocols can be chosen for performance, but HTTP and Tcl are super-easy to understand, and can easily handle hundreds of requests per second.

This will handle your processing nicely even without locking. If the problem is with MySQL server not being able to process update requests at the incoming speed, then it is a hardware problem, and you need a completely different infrastructure with multiple database servers.

Php code delegates processing to the helper server:

<?php
$result = "FAILURE";
$result = file_get_contents('http://localhost:2222?id=123&amount=10');
echo "result=$result!";
?>

Tcl server is as follows, uncomment and put your actual processing in procedure "Respond":

package require mysqltcl
set db [mysqlconnect -host localhost -port 3306 -user root -db test -encoding utf-8 ]
proc sql {sql} {
    global db
    if [catch {::mysql::exec $db $sql} err] {puts $err}
};#sql
proc select {sql} {::mysql::sel $::db $sql -flatlist}

set listenSocket [socket -server Accept 2222]

proc Accept {sock addr port} {
   puts "Accepted $sock from $addr port $port"
   fconfigure $sock -buffering line
   fileevent $sock readable "Respond $sock"
}

proc Respond {sock} {
   gets $sock request
   puts request=$request!
   set result "ERROR IN THE INVOKING PHP PROGRAM"
   if [regexp {id=(\d+)&amount=(\d+)} $request x id amount] {
    puts "request for amount=$amount id=$id"
    #set balance [select "SELECT balance FROM `accounts` WHERE somefield=$id"]
    #sql "INSERT Table2(name,field1,field2) VALUES('xxx',$balance,$amount)"
    #sql "UPDATE `accounts` SET amount=amount-$amount WHERE id=$id LIMIT 1"
    #set result [select "SELECT * FROM `whatever` WHERE condition"]
    set result "{\"balance\": \"$balance\",\"value\":\"$result\"}";#PHP will get this string, choose convenient format
   }
   puts $sock "HTTP/1.1 200 OK\n\n$result"
   close $sock
   puts "closed $sock"
}

vwait forever

All the "puts" are for debugging and can be deleted. As I said, the communication can be optimized immensely (to Unix pipes, message queues, shared memory), C can be chosen instead of Tcl etc. I just chose what was simple for me.

The request of "?id=123&amount=10" is, of course, completely arbitrary and you can design a different protocol.