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

i using mysql (innodb), in facing problem when checked curl request upto 500 connections @ time (simultaneously) when try more servers goes idle condition , have restart it.

in manner more understanding have db table in mysql (innodb) contains 'remaining amount' field. ex. field containing values of $500 , $10 deduct per user.

now when users request concurrently (at time), server doesn't know users has more priority or when request accepted no more amount remains.

here 2 questions:

1. how manage deductions when concurrent requests received
2. how set maximum requests server not idle after more requests (500+)

edit

if suppose use lock mechanism of mysql work or not ?

i using php script communicating mysql

modern server environments can operate multitude of languages, processes, environments etc. makes them easy debug, monitor, change , combine in various ways.

if request alone putting big stress on mysql server, suggest helper process handle in single-threaded way. below present simple helper-server in tcl , glue code php. other languages , communication protocols can chosen performance, http , tcl super-easy understand, , can handle hundreds of requests per second.

this handle processing nicely without locking. if problem mysql server not being able process update requests @ incoming speed, hardware problem, , need different infrastructure multiple database servers.

php code delegates processing helper server:

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

tcl server follows, uncomment , put 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 $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 invoking php program"    if [regexp {id=(\d+)&amount=(\d+)} $request x id amount] {     puts "request amount=$amount id=$id"     #set balance [select "select balance `accounts` somefield=$id"]     #sql "insert table2(name,field1,field2) values('xxx',$balance,$amount)"     #sql "update `accounts` set amount=amount-$amount id=$id limit 1"     #set result [select "select * `whatever` condition"]     set result "{\"balance\": \"$balance\",\"value\":\"$result\"}";#php string, choose convenient format    }    puts $sock "http/1.1 200 ok\n\n$result"    close $sock    puts "closed $sock" }  vwait forever 

all "puts" debugging , can deleted. said, communication can optimized immensely (to unix pipes, message queues, shared memory), c can chosen instead of tcl etc. chose simple me.

the request of "?id=123&amount=10" is, of course, arbitrary , can design different protocol.