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.