Should I retry a query that failed because of a connection error?

Question

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.

Answer 1

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.

Answer 2

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.