4

Let's say I want to add a row to a table.

I send the query from the client (the website, not the user) to the database which is on another physical server. The query is sent over the network.

The query is executed successfully. The database then sends a message back to the client saying that the query succeeded. At this moment, the network fails and the message never reaches the client.

Now, the client has no way to know if the query succeeded or not. It only knows that it lost connection with the database.

What would be the best way to handle this kind of scenario?

Even if the connection is restored, the problem still persists.

Retrying the query is risky. The row could be added twice. In fact, any kind of update could happen twice or more times.

Treating the query as "failed" is misleading. Telling the user that the request failed might be a problem. The user might try again to do something that has already been done, like completing a purchase.

  • 2
    Idempotent operations: they produce the same result, given the same input. Database operations that must work correctly in such scenarios generally are made idempotent. They do not corrupt the database when a duplicate is executed. – Frank Hileman Apr 26 '18 at 21:38
4

Let's assume a complex case, a query that modifies an unknown number of records, in a way that isn't idempotent. And we assume that it is the connection between you and the database that goes wrong, the database itself is fine.

You create a unique id which will go into a transaction table. Then you send a query which adds the unique number to the transaction table, then perform your original query.

If your combined query never reached the server, the unique id is not added to the transactions. If the query is executed, the unique id is in the transaction table. So for the retry, you just check if the unique id is there or not.

If your query was supposed to return results, you will need an alternative query that can return the same results, without side effects.

  • 1
    Just one additional hint: make sure that you use proper transaction handling with Commit and Rollback, and have any Auto-Commit "feature" switched off. – Ralf Kleberhoff Apr 27 '18 at 8:26
1

If this is a scenario you are concerned with, one solution is to create your own unique key either as the primary key or in a column with a uniqueness constraint. Then you can retry and you will get a failure because the DB will not allow a duplicate value. The general approach is termed 'duplicate transaction detection'. In many cases, this is not a concern since it is highly unlikely and if it does happen the system will fail to allow for manual review and remediation. Or alternately, you have a end user who can check to see whether the record was created.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.