Web service SocketTimeOutException causes database inconsistency

Question

I want to overcome the following scenario when calling a Rails Web service from Android device with the help of Apache HTTP Client library

Scenario :

Client (Android) : Initiates HTTP POST request (Using Apache HTTP Client, JSON as exchange format) to inserts few records into the server database (MySQL).

Server (Web service in Rails) : Process the request and inserts records into the database successfully BUT at the same moment time out (SocketTimeOutException) occurs at the client side.

Client (Android) : On time out, retries to execute the same HTTP POST request and once again the same records get's inserted into the database and Database INCONSISTENCY occurs.

Can any one please help me to overcome this situation.

Thanks in advance

EDIT:

My current scenario:

1) User tries to register using the Android app by filling and submitting data. So basically POST service contains all the required user into which is sent to the server (Rails and MySql)

2) POST request is processed by server and this process is basically responsible for inserting records into the database and providing response to client with the USER_ID for which data is inserted.

3) BOOM!! The data is inserted in the MySql database but for some reason there is SocketTimeOutException on the Android side and because of which there is no USER_ID on Android side and user tries to register again........ Data base inconsistency and I am SAD :(

NOTE : Occurrence of step 3) is very rare.. BUT it is frequent when server is too busy or is having some processing load on it..

Answer 1

For the specific case you describe above I would suggest you add a session cookie to the proceedings. When the user posts the data you check if there is already a USER_ID for that session, if it is you return it, if not you do the database insert. So even if the initial insert happens, followed by a timeout before the user gets the response, you will be able to serve the response when the user retries the registration. Without getting your DB trashed.

You can also do a lookup in the database for an identical (or otherwise unique) record (created very recently) and return that USER_ID on the second request. This will depend on what data the user sends in. But if it's a registration and you get two posts with the same email in short succession it's a safe bet that it's from the same user ...

Before the edit:

Since you are using Rails you could simply leverage the create_or_update method to create the records. Basically you check if the records already exist and then update them instead of blindly inserting new ones.

This is a problem you would probably have to handle anyway since two clients could potentially post identical information at the same time even if you didn't have the problem with reposts.

If you want a more detailed description of how this works you'll have to provide more information about what the post contains. The keys and unique fields are interesting in this case as are any information about if the records are bound to a specific user etc...