How to handle a PHP updating one row in a MySQL table from concurrent Android App Service calls?

Here is what is happening right now and I could use some wisdom.

I have an Android App that calls PHP code using intentservice about every 10 minutes. The PHP code is supposed to update one column in a single row in one MySQL table. Now obviously as the users using the App grew to about 3500,which I didn't think it would reach that number (since it was just my little project on the side), the number of concurrent requests to be processed grew exponentially and deadlock situations occurred and the mysql updates to this table column fails intermittently. Database engine is InnoDB on AWS RDS.

Updating that column in that row of the table is critical 开发者_StackOverflowand has to happen if not realtime near about realtime.

What could be scalable solution where even if the number of users making the request grows to 5000 - 50000 this update can be handled somehow in a clean way.

Please feel free to ask about any other information that you might need from me.

Thanks for your help.

Some more info(example)

To better describe the problem let me give you an example. Let's say the one row that I need updated is an advertisement submitted by an advertiser from a website which has the budget in a column for that advertisement. Now every time an impression or click occurs from an Android phone I write the impression and clicks which are distinct for every user every ad in two separate tables but after that with every impression, every click happening in high concurrence I still need to update that budget column in that distinct advertisement row in the ad table. That is where the problem occurs.

You might need to redesign your schema so that you can update (or insert) a row per user, instead of the same row for each.

Add a separate table to track clicks, where your only have columns for advertizer, campaign with a timestamp.

Insert 1 row each time a click occurs (should not lock the table)

Every X minutes run a script on the server that compiles the number of clicks for each advertizer and deletes older entries in the "clicks" table.

You have two tables: The advertisement info ID Advertiser Budget isActive (True/false)

The click info AdvertisementID ClickInfo (DateTime etc?) User (Optinal?)

i assume there is a cost per click that is precalculated? If so simply do an ON UPDATE trigger that calls a function gets a count of the amount of times the advertsiementID is found in the click info table and multiply it by cost per click. if its equal to or over budget simply set isACtive to false. Have your app only pull adverts that have isActive to true.

This suggestion makes a ton of assumptions but it is one option.